Início / dba / Perguntas / 78683
Accepted
a_horse_with_no_name
Asked: 2014-10-09 05:33:33 +0800 CST

Detectando a tabela ou linha bloqueada no SQL Server

  • 772

Estou tentando entender/aprender a rastrear os detalhes de uma sessão bloqueada.

Então criei a seguinte configuração:

create table foo (id integer not null primary key, some_data varchar(20));
insert into foo values (1, 'foo');
commit;

Agora eu me conecto ao banco de dados duas vezes de dois clientes diferentes.

As questões da primeira sessão:

begin transaction
update foo set some_data = 'update'
  where id = 1;

Eu explicitamente não me comprometo lá para manter os bloqueios.

Na segunda sessão eu emito a mesma declaração e claro que uma espera devido ao travamento. Agora estou tentando usar as diferentes consultas que circulam para ver que a sessão 2 está aguardando a footabela.

sp_who2mostra o seguinte (removi algumas colunas para mostrar apenas as informações importantes):

SPID | Situação | BlkBy | DBName | Comando | SPID | IDENTIFICAÇÃO DO PEDIDO
-----+--------------+-------+----------+---------- --------+------+----------
52 | dormindo | . | comidab | AGUARDANDO COMANDO | 52 | 0        
53 | dormindo | . | comidab | AGUARDANDO COMANDO | 53 | 0        
54 | SUSPENSO | 52 | comidab | ATUALIZAÇÃO | 54 | 0        
56 | EXECUTÁVEL | . | comidab | SELECIONE EM | 56 | 0

Isso é esperado, a sessão 54 é bloqueada pelas alterações não confirmadas da sessão 52.

A consulta sys.dm_os_waiting_taskstambém mostra isso. A declaração:

select session_id, wait_type, resource_address, resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not null;

retorna:

session_id | tipo_espera | endereço_recurso | descrição_recurso                                                            
-----------+-----------+--------------------+----- -------------------------------------------------- --------------------------
        54 | LCK_M_X | 0x000000002a35cd40 | keylock hobtid=72057594046054400 dbid=6 id=lock4ed1dd780 mode=X associadoObjectId=72057594046054400

Novamente, isso é esperado.

Meu problema é que não consigo descobrir como encontrar o nome real do objeto que a sessão 54 está esperando.

Eu encontrei várias consultas que estão se juntando sys.dm_tran_lockse sys.dm_os_waiting_tasksassim:

SELECT ....
FROM sys.dm_tran_locks AS l
  JOIN sys.dm_os_waiting_tasks AS wt ON wt.resource_address = l.lock_owner_address

Mas no meu cenário de teste acima, essa junção não retorna nada. Portanto, essa junção está errada ou dm_tran_locksnão contém as informações que estou procurando.

Então, o que estou procurando é uma consulta que retorne algo como:
" sessão 54 está aguardando um bloqueio na tabelafoo ".

Algumas informações de fundo:

O problema da vida real que estou tentando resolver é um pouco mais complicado, mas se resume à pergunta "em qual mesa a sessão 54 está esperando". O problema em questão envolve um procedimento armazenado grande que atualiza várias tabelas e um select de uma view que acessa algumas dessas tabelas. A selectinstrução é bloqueada mesmo que tenhamos o isolamento de instantâneo e o instantâneo confirmado de leitura habilitado. Descobrir por que o select está bloqueado (o que eu pensei que não seria possível se o isolamento de instantâneo estivesse habilitado) será o próximo passo.

Como primeiro passo, gostaria de saber o que essa sessão está esperando.

locking sql-server-2012

2 respostas

  1. Best Answer

    Acho que isso faz o que você precisa.

    USE 'yourDB'
GO
SELECT  
    OBJECT_NAME(p.[object_id]) BlockedObject
FROM    sys.dm_exec_connections AS blocking
    INNER JOIN sys.dm_exec_requests blocked
        ON blocking.session_id = blocked.blocking_session_id
    INNER JOIN sys.dm_os_waiting_tasks waitstats
        ON waitstats.session_id = blocked.session_id
    INNER JOIN sys.partitions p ON SUBSTRING(resource_description, 
        PATINDEX('%associatedObjectId%', resource_description) + 19, 
        LEN(resource_description)) = p.partition_id
    • 24

  2. Você pode experimentá-lo : 

    SELECT 
db_name(rsc_dbid) AS 'DATABASE_NAME',
case rsc_type when 1 then 'null'
              when 2 then 'DATABASE' 
              WHEN 3 THEN 'FILE'
              WHEN 4 THEN 'INDEX'
              WHEN 5 THEN 'TABLE'
              WHEN 6 THEN 'PAGE'
              WHEN 7 THEN 'KEY'
              WHEN 8 THEN 'EXTEND'
              WHEN 9 THEN 'RID ( ROW ID)'
              WHEN 10 THEN 'APPLICATION' end  AS 'REQUEST_TYPE',

CASE req_ownertype WHEN 1 THEN 'TRANSACTION'
                   WHEN 2 THEN 'CURSOR'
                   WHEN 3 THEN 'SESSION'
                   WHEN 4 THEN 'ExSESSION' END AS 'REQUEST_OWNERTYPE',

OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME', 
PROCESS.HOSTNAME , 
PROCESS.program_name , 
PROCESS.nt_domain , 
PROCESS.nt_username , 
PROCESS.program_name ,
SQLTEXT.text 
FROM sys.syslockinfo LOCK JOIN 
     sys.sysprocesses PROCESS
  ON LOCK.req_spid = PROCESS.spid
CROSS APPLY sys.dm_exec_sql_text(PROCESS.SQL_HANDLE) SQLTEXT
    • 4

relate perguntas