sys.dm_tran_locks (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Warehouse do PDW (Analytics Platform System) no Microsoft Fabric

Retorna informações sobre os recursos de gerenciador de bloqueio ativos atualmente no SQL Server. Cada linha representa uma solicitação ativa no momento para o gerenciador de um bloqueio que foi concedido ou que está aguardando concessão.

As colunas do conjunto de resultados são divididas em dois grupos principais: recurso e solicitação. O grupo de recurso descreve o recurso em que a solicitação de bloqueio está sendo feita, e o grupo de solicitações descreve a solicitação de bloqueio.

Permissões

No SQL Server e na Instância Gerenciada de SQL, requer a permissão VIEW SERVER STATE.

Nos objetivos de serviço do Banco de Dados SQL Básico, S0 e S1 e para bancos de dados em pools elásticos, é necessário ter a conta do administrador do servidor, a conta do administrador do Microsoft Entra ou a associação à ##MS_ServerStateReader## função de servidor. Em todos os outros objetivos de serviço do Banco de Dados SQL, a permissão VIEW DATABASE STATE no banco de dados ou a associação à função de servidor ##MS_ServerStateReader## são necessárias.

Permissões do SQL Server 2022 e posteriores

É necessária a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Comentários

Um status de solicitação concedida indica que um bloqueio foi concedido em um recurso ao solicitante. Uma solicitação de espera indica que a solicitação ainda não foi concedida. Os seguintes tipos de solicitação em espera são retornados request_status pela coluna:

• Um status de solicitação de conversão indica que o solicitante já recebeu uma solicitação referente ao recurso e está no momento aguardando a concessão de atualização para a solicitação inicial.

• Um status de solicitação de espera indica que o solicitante não mantém no momento uma solicitação concedida no recurso.

Como sys.dm_tran_locks é preenchido a partir de estruturas de dados do gerenciador de bloqueio interno, a manutenção dessas informações não adiciona sobrecarga extra ao processamento regular. A materialização da exibição requer acesso às estruturas de dados internos do gerenciador de bloqueio. Isso pode ter efeitos secundários no processamento regular no servidor. Esses efeitos não devem ser percebidos e devem afetar somente os recursos bastante usados. Como os dados dessa exibição correspondem ao estado do gerenciador de bloqueio ativo, os dados podem ser alterados a qualquer momento e as linhas são adicionadas e removidas à medida que os bloqueios são adquiridos e liberados. Os aplicativos que consultam essa exibição podem ter um desempenho imprevisível devido à natureza de proteger a integridade das estruturas do gerenciador de bloqueios. Essa exibição não tem nenhuma informação de histórico.

Duas solicitações só funcionarão no mesmo recurso se todas as colunas do grupo de recursos forem iguais.

Você pode controlar o bloqueio de operações de leitura usando as seguintes ferramentas:

• Use SET TRANSACTION ISOLATION LEVEL para especificar o nível de bloqueio de uma sessão. Para obter mais informações, confira SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• Use dicas de tabela de bloqueio para especificar o nível de bloqueio de uma referência individual de uma tabela em uma cláusula FROM. Para obter sintaxe e restrições, confira Dicas de tabela (Transact-SQL).

Um recurso que está sendo executado em um session_id pode ter mais de um bloqueio concedido. Entidades diferentes que estão sendo executadas em uma sessão podem possuir um bloqueio no mesmo recurso, e as informações são exibidas nas request_owner_type colunas e request_owner_id retornadas por sys.dm_tran_locks. Se existirem várias instâncias do mesmo request_owner_type , a request_owner_id coluna será usada para distinguir cada instância. Para transações distribuídas, as request_owner_type colunas e as request_owner_guid mostram as diferentes informações da entidade.

Por exemplo, a Sessão S1 possui um bloqueio compartilhado em Table1; e a transação T1, que está sendo executada na sessão S1, também possui um bloqueio compartilhado em Table1. Nesse caso, a resource_description coluna retornada por sys.dm_tran_locks mostra duas instâncias do mesmo recurso. A request_owner_type coluna mostra uma instância como uma sessão e a outra como uma transação. Além disso, a resource_owner_id coluna tem valores diferentes.

Vários cursores em execução em uma sessão são indistinguíveis e são tratados como uma entidade.

As transações distribuídas que não estão associadas a um session_id valor são transações órfãs e recebem o session_id valor de -2. Para obter mais informações, confira KILL (Transact-SQL).

Locks

Os bloqueios são mantidos nos recursos SQL Server , como linhas lidas ou modificadas durante uma transação, para evitar o uso simultâneo de recursos por transações diferentes. Por exemplo, se um bloqueio exclusivo (X) for mantido em uma linha de uma tabela por uma transação, nenhuma outra transação poderá modificar essa linha até que o bloqueio seja liberado. Minimizar bloqueios aumenta a simultaneidade, o que pode melhorar o desempenho.

Detalhes do recurso

A tabela a seguir lista os recursos representados na resource_associated_entity_id coluna.

A tabela a seguir lista os subtipos associados a cada tipo de recurso.

A tabela a seguir fornece o resource_description formato da coluna para cada tipo de recurso.

Exemplos

R. Use sys.dm_tran_locks com outras ferramentas

O exemplo a seguir trabalha com um cenário no qual uma operação de atualização é bloqueada por outra transação. Usando sys.dm_tran_locks e outras ferramentas, são fornecidas informações sobre o bloqueio de recursos.

USE tempdb;
GO

-- Create test table and index.
CREATE TABLE t_lock
    (
    c1 int, c2 int
    );
GO

CREATE INDEX t_lock_ci on t_lock(c1);
GO

-- Insert values into test table
INSERT INTO t_lock VALUES (1, 1);
INSERT INTO t_lock VALUES (2, 2);
INSERT INTO t_lock VALUES (3, 3);
INSERT INTO t_lock VALUES (4, 4);
INSERT INTO t_lock VALUES (5, 5);
INSERT INTO t_lock VALUES (6, 6);
GO

-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN
    SELECT c1
        FROM t_lock
        WITH(holdlock, rowlock);

-- Session 2
BEGIN TRAN
    UPDATE t_lock SET c1 = 10;

A consulta a seguir exibe informações de bloqueio. O valor de deve <dbid> ser substituído pelo database_id de sys.databases.

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks
    WHERE resource_database_id = <dbid>;

A consulta a seguir retorna informações de objeto usando resource_associated_entity_id na consulta anterior. Essa consulta deverá ser executada enquanto você estiver conectado ao banco de dados que contém o objeto.

SELECT object_name(object_id), *
    FROM sys.partitions
    WHERE hobt_id=<resource_associated_entity_id> ;

A consulta a seguir mostra informações de bloqueio.

SELECT
    t1.resource_type,
    t1.resource_database_id,
    t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
    ON t1.lock_owner_address = t2.resource_address;

Libere recursos revertendo as transações.

-- Session 1
ROLLBACK;
GO

-- Session 2
ROLLBACK;
GO

B. Vincular informações de sessão a threads do sistema operacional

O exemplo a seguir retorna informações que associam a session_id a a uma ID de thread do Windows. O desempenho do thread pode ser monitorado no Monitor de Desempenho do Windows. Essa consulta não retorna um session_id que está em suspensão no momento.

SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
    ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
GO

Conteúdo relacionado

• sys.dm_tran_database_transactions (Transact-SQL)
• Guia de controle de versão de linha e bloqueio de transações
• Exibições de gerenciamento dinâmico do sistema
• Funções e exibições de gerenciamento dinâmico relacionadas à transação (Transact-SQL)
• SQL Server, objeto Locks