Junior Galvão - MVP - SQL Server

Para rastrear eventos de deadlock, adicione a classe de evento Deadlock graph a um rastreamento. Essa classe de evento popula a coluna de dados TextData no rastreamento com dados XML sobre o processo e os objetos envolvidos no deadlock. O SQL Server Profiler pode extrair o documento XML para um arquivo XML de deadlock (.xdl), que pode ser examinado posteriormente no SQL Server Management Studio. Você pode configurar o SQL Server Profiler para extrair eventos Deadlock graph para um único arquivo contendo todos os eventos Deadlock graph ou para arquivos separados. Essa extração pode ser feita de qualquer uma destas formas:

• No momento da configuração do rastreamento, usando a guia Configurações de Extração de Eventos. Observe que essa guia não aparece a menos que você selecione o evento Deadlock graph na guia Seleção de Eventos.
  
  
• Usando a opção Extrair Eventos do SQL Server no menu Arquivo.
  
  
• Eventos individuais também podem ser extraídos e salvos clicando-se com o botão direito do mouse em um evento específico e escolhendo Extrair Dados de Evento.
  

Gráficos de deadlock

O SQL Server Profiler e o SQL Server Management Studio usam um gráfico de espera de deadlock para descrever um deadlock. O gráfico de espera de deadlock contém nós de processo, nós de recurso e bordas representando as relações entre os processos e os recursos. Os componentes dos gráficos de espera estão definidos na tabela a seguir:

Nó de recurso

Um objeto de banco de dados; por exemplo, uma tabela, índice ou linha.

Borda

Uma relação entre um processo e um recurso. Uma borda request ocorre quando um processo espera por um recurso. Uma borda owner ocorre quando um recurso espera por um processo. O modo de bloqueio encontra-se na descrição da borda.

Nó de processo de deadlock

Nó de recurso de deadlock

Exemplo do sinalizador de rastreamento 1204

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
BEGIN TRANSACTION
EXEC usp_p2
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0 
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
     BEGIN TRANSACTION
EXEC usp_p1
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Exemplo do sinalizador de rastreamento 1222

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868 
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444 
   transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0 
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54 
   sbid=0 ecid=0 priority=0 transcount=2 
   lastbatchstarted=2005-09-05T11:22:42.733 
   lastbatchcompleted=2005-09-05T11:22:42.733 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310444 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks.dbo.usp_p1 line=6 stmtstart=202 
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1     
    inputbuf
      BEGIN TRANSACTION
EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380 
   waitresource=KEY: 6:72057594057457664 (350007a4d329)   
   waittime=5015 ownerId=310462 transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U 
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0 
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077 
   lastbatchcompleted=2005-09-05T11:22:44.077 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310462 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks.dbo.usp_p2 line=6 stmtstart=200 
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2     
    inputbuf
      BEGIN TRANSACTION
EXEC usp_p2    
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks.dbo.T2 
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks.dbo.T1 
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X 
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

A implementação de um manipulador de erros que intercepte a mensagem de erro 1205 permite que um aplicativo manipule a situação de deadlock e tome uma ação paliativa (por exemplo, enviar automaticamente de novo a consulta que estava envolvida no deadlock). Ao enviar a consulta novamente de forma automática, o usuário não precisa saber que ocorreu um deadlock.

O aplicativo deveria pausar brevemente antes de enviar novamente a consulta. Isso dá a outra transação envolvida no deadlock uma chance para completar e liberar seus bloqueios que formaram parte do ciclo de deadlock. Diminuindo a probabilidade do deadlock ocorrer novamente quando a consulta reenviada solicitar seus bloqueios.

Minimizando deadlocks

Embora deadlocks não possam ser completamente evitados, seguir certas convenções de codificação pode minimizar a chance de gerar um deadlock. Minimizar deadlocks pode aumentar a taxa de transferência da transação e pode reduzir a sobrecarga do sistema pois poucas transações são:

• Revertidas, desfazendo todo o trabalho executado pela transação;
  
• Reenviadas por aplicativos pois elas foram revertidas quando bloqueadas.

Para ajudar a minimizar deadlocks:

• Acesse objetos na mesma ordem;
  
• Evite a interação de usuário durante as transações;
  
• Mantenha as transações curtas e em um lote
  
• Use um nível de isolamento inferior;
  
• Use um nível de isolamento com base em controle de versão de linha:
  
• Configure a opção de banco de dados READ_COMMITTED_SNAPSHOT em ON, para habilitar que as transações confirmadas por leitura utilizem controle de versão de linha;
• Use transação de isolamento;
• Use associações de saída.

Continuaremos...

Detectando e encerrando deadlocks

Um deadlock acontece quando duas ou mais tarefas bloqueiam uma à outra permanentemente, sendo que cada uma tem o bloqueio de um recurso, que a outra tarefa está tentando bloquear. Por exemplo:

• A transação A adquire um bloqueio compartilhado da linha 1.
  
  
• A transação B adquire um bloqueio compartilhado da linha 2.
  
  
• A transação A agora solicita um bloqueio exclusivo na linha 2 e é bloqueado até que a transação B termine e libere o bloqueio compartilhado que tem na linha 2.
  
  
• A transação B agora solicita um bloqueio exclusivo na linha 1 e é bloqueado até que a transação A termine e libere o bloqueio compartilhado que tem na linha 1.
  
  

A transação A não pode terminar até que a transação B termine, mas a transação B está bloqueada pela transação A. Essa condição é também chamada de dependência cíclica: a transação A tem uma dependência da transação B, e a transação B fecha o círculo tendo uma dependência da transação A.

Ambas as transações em um deadlock esperarão indefinidamente, a menos que o deadlock seja quebrado por um processo externo. O monitor de deadlock do Microsoft Mecanismo de Banco de Dados do SQL Server verifica periodicamente as tarefas que estão em um deadlock. Se o monitor detectar uma dependência cíclica, ele escolhe uma das tarefas como vítima e termina sua transação com um erro. Isso permite que a outra tarefa complete sua transação. O aplicativo com a transação que terminou com um erro pode repetir a transação, a qual normalmente é concluída depois que a outra transação em deadlock é encerrada.

Usando certas convenções de codificação em aplicativos reduz a chance de que os aplicativos causarão deadlocks. Para obter mais informações, consulte Minimizando deadlocks.

O deadlock é freqüentemente confundido com bloqueio normal. Quando uma transação solicita um bloqueio em um recurso bloqueado por outra transação, a transação solicitante espera até que o bloqueio seja liberado. Por padrão, as transações SQL Server não têm tempo limite, a menos que LOCK_TIMEOUT seja configurado. A transação solicitante é bloqueada, não em deadlock, por que ela não fez nada para bloquear a transação que deve o bloqueio. Finalmente, a transação proprietária vai terminar e liberar o bloqueio e a transação solicitante terá o bloqueio atribuído e processado.

Os deadlocks às vezes são chamados de abraço mortal.

Deadlock é uma condição que pode ocorrer em qualquer sistema com vários threads, não só em sistemas de gerenciamento de banco de dados relacional, e pode ocorrer para outros recursos, além de bloqueios de objetos em bancos de dados. Por exemplo, um thread em um sistema operacional de vários threads pode adquirir um ou mais recursos, como bloqueios de memória. Se o recurso sendo adquirido é atualmente propriedade de outro thread, o primeiro thread pode ter que esperar o thread proprietário liberar o recurso alvo. O thread em espera tem uma dependência do thread proprietário para aquele recurso em particular. Em uma instância do Mecanismo de Banco de Dados, sessões podem fazer um deadlock ao adquirir recursos que não são de banco de dados, como memória ou threads.