Guia Deadlocks

Aplica-se a: SQL Server Banco de dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

Este artigo discute em detalhes os deadlocks no Mecanismo de Banco de Dados do SQL Server. Deadlocks são causados por bloqueios concorrentes e simultâneos no banco de dados, geralmente em transações de várias etapas. Para saber mais sobre o bloqueio de transações, consulte o Guia de controle de versão de linha e bloqueio de transações.

Para obter informações mais específicas sobre identificação e prevenção de deadlocks no Banco de Dados SQL do Azure, consulte Analisar e evitar deadlocks no Banco de Dados SQL do Azure.

Entenda os deadlocks

Um deadlock acontece quando duas ou mais tarefas bloqueiam permanentemente uma à outra; uma tarefa está bloqueando um recurso que a outra tarefa está tentando bloquear. Por exemplo:

• A transação A adquire um bloqueio compartilhado na linha 1.

• A transação B adquire um bloqueio compartilhado na linha 2.

• A transação A agora solicita um bloqueio exclusivo na linha 2 e é bloqueada até que a transação B termine e libere o bloqueio compartilhado existente na linha 2.

• A transação B agora solicita um bloqueio exclusivo na linha 1 e é bloqueada até que a transação A termine e libere o bloqueio compartilhado existente na linha 1.

A transação A não pode ser concluída até que a transação B seja concluída, 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.

As duas transações que estão em um deadlock ficarão esperando indefinidamente, a menos que o deadlock seja rompido por um processo externo. O monitor de deadlock do 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 em erro pode repetir a transação, que normalmente é concluída depois que a outra transação em deadlock é finalizada.

O deadlock é frequentemente 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 do SQL Server não têm um tempo limite, a menos que LOCK_TIMEOUT esteja definido. A transação solicitante está bloqueada, e não em deadlock, porque ela não fez nada para bloquear a transação que é proprietária do bloqueio. A transação proprietária é concluída e libera o bloqueio, então, o bloqueio é concedido à transação solicitante e ela prossegue. Deadlocks são resolvidos quase imediatamente, enquanto o bloqueio pode, em teoria, persistir indefinidamente. Os deadlocks às vezes são chamados de abraço mortal.

Um deadlock é uma condição que pode ocorrer em qualquer sistema com vários threads, não só em um RDBMS, e pode ocorrer em outros recursos, não apenas em bloqueios de objetos de banco 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 que está sendo adquirido for atualmente propriedade de outro thread, o primeiro thread poderá ter de 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 do SQL Server, as sessões podem sofrer deadlock ao adquirirem recursos que não são do banco de dados, como memória ou threads.

Na ilustração, a transação T1 depende da transação T2 para o recurso de bloqueio da tabela Part. Da mesma forma, a transação T2 depende da transação T1 para o recurso de bloqueio da tabela Supplier. Como essas dependências formam um ciclo, existe um deadlock entre as transações T1 e T2.

Deadlocks também podem ocorrer quando uma tabela é particionada e a configuração LOCK_ESCALATION de ALTER TABLE é definida como AUTO. Quando LOCK_ESCALATION é definido como AUTO, a simultaneidade aumenta permitindo que o Mecanismo de Banco de Dados do SQL Server bloqueie as partições de tabela no nível do HoBT em vez de no nível da tabela. Entretanto, quando transações separadas mantêm bloqueios de partição em uma tabela e querem um bloqueio em algum lugar de outra partição de transações, isso causa um deadlock. Esse tipo de deadlock pode ser evitado definindo LOCK_ESCALATION como TABLE. Porém, essa configuração reduz a simultaneidade forçando importantes atualizações em uma partição a aguardar um bloqueio de tabela.

Detectar e eliminar deadlocks

Um deadlock acontece quando duas ou mais tarefas bloqueiam permanentemente uma à outra; uma tarefa está bloqueando um recurso que a outra tarefa está tentando bloquear. O seguinte gráfico apresenta uma exibição de alto nível de um estado de deadlock em que:

• A tarefa T1 tem um bloqueio no recurso R1 (indicado pela seta de R1 para T1) e solicitou um bloqueio no recurso R2 (indicado pela seta de T1 para R2).

• A tarefa T2 tem um bloqueio no recurso R2 (indicado pela seta de R2 para T2) e solicitou um bloqueio no recurso R1 (indicado pela seta de T2 para R1).

• Como nenhuma tarefa pode continuar até que um recurso esteja disponível e nenhum recurso pode ser liberado até que uma tarefa continue, ocorre um estado de deadlock.

  

O Mecanismo de Banco de Dados do SQL Server detecta automaticamente os ciclos de deadlock no SQL Server. O Mecanismo de Banco de Dados do SQL Server escolhe uma das sessões como vítima de deadlock, e a transação atual é encerrada com um erro para interromper o deadlock.

Recursos que podem causar deadlock

Cada sessão de usuário pode ter uma ou mais tarefas sendo executadas em seu nome, sendo que cada tarefa pode adquirir ou aguardar para adquirir recursos. Os tipos de recursos a seguir podem causar bloqueio que pode resultar em um deadlock.

• Bloqueios. A espera para adquirir bloqueios em recursos, como objetos, páginas, linhas, metadados e aplicativos, pode causar um deadlock. Por exemplo, a transação T1 tem um bloqueio compartilhado (S) na linha r1 e está esperando para obter um bloqueio exclusivo (X) em r2. A transação T2 tem um bloqueio compartilhado (S) na linha r1 e está esperando para obter um bloqueio exclusivo (X) em r1. Isso resulta em um ciclo de bloqueio no qual T1 e T2 esperam que uma libere os recursos bloqueados da outra.

• Threads de trabalho. Uma tarefa enfileirada à espera de um thread de trabalho disponível pode causar um deadlock. Se a tarefa em fila for a proprietária dos recursos que estão bloqueando todos os threads de trabalho, ocorrerá um deadlock. Por exemplo, a sessão S1 inicia uma transação e adquire um bloqueio compartilhado (S) na linha r1 e, depois, fica suspenso. As sessões ativas em execução em todos os threads de trabalhado disponíveis estão tentando adquirir bloqueios exclusivos (X) na linha r1. Como a sessão S1 não pode adquirir um thread de trabalho, não pode fazer commit da transação e liberar o bloqueio na linha r1. Isso resulta em um deadlock.

• Memory. Quando solicitações simultâneas estão esperando concessões de memória que não podem ser atendidas com a memória disponível, pode ocorrer um deadlock. Por exemplo, duas consultas simultâneas, Q1 e Q2, são executadas como funções definidas pelo usuário que adquirem 10MB e 20MB de memória, respectivamente. Se cada consulta precisar de 30 MB e a memória disponível total for de 20 MB, Q1 e Q2 terão que esperar que ambas liberem memória, e isso resultará em um deadlock.

• Recursos relacionados à execução de consultas paralelas. Threads de coordenador, produtor ou consumidor associados a uma porta de troca podem bloquear uns aos outros, provocando um deadlock, normalmente ao incluir pelo menos outro processo que não faz parte da consulta paralela. Além disso, quando uma consulta paralela inicia a execução, o SQL Server determina o grau de paralelismo, ou o número de threads de trabalho, com base na carga de trabalho atual. Se a carga de trabalho do sistema for alterada inesperadamente, por exemplo, quando novas consultas forem executadas no servidor ou o sistema ficar sem threads de trabalho, poderá ocorrer um deadlock.

• Recursos MARS (conjunto de resultados ativos múltiplos). Esses recursos são usados para controlar a intercalação de várias solicitações ativas em MARS. Para obter mais informações, veja Usando MARS (conjuntos de resultados ativos múltiplos) no SQL Server Native Client.

  • Recurso do usuário. Quando um thread está esperando por um recurso que é potencialmente controlado por um aplicativo de usuário, o recurso é considerado como externo ou recurso de usuário e é tratado como um bloqueio.

  • Mutex da sessão. As tarefas que estão sendo executadas em uma sessão são intercaladas, ou seja, apenas uma tarefa pode ser executada na sessão em um determinado momento. Antes de a tarefa ser executada, deve ter acesso exclusivo ao mutex de sessão.

  • Mutex de transação. Todas as tarefas que estão sendo executadas em uma transação são intercaladas, ou seja, somente uma tarefa pode ser executada na transação em um determinado momento. Antes da tarefa ser executada, deve ter acesso exclusivo ao mutex de transação.

    Para que uma tarefa seja executada em MARS, ela deve adquirir o mutex da sessão. Se a tarefa estiver sendo executada em uma transação, deverá adquirir o mutex de transação. Isso garante que apenas uma tarefa esteja ativa em um determinado momento, sessão e transação. Quando os mutexes solicitados forem adquiridos, a tarefa poderá ser executada. Quando a tarefa é concluída, ou cede a preferência no meio da solicitação, primeiro ela libera o mutex de transação e, depois, o mutex de sessão na ordem reversa de aquisição. Porém, podem ocorrer deadlocks com esses recursos. No pseudocódigo a seguir, duas tarefas, a solicitação do usuário U1 e a solicitação do usuário U2, estão sendo executadas na mesma sessão.

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
    U2:    Rs2=Command2.Execute("select colA from sometable");
    

    O procedimento armazenado que está sendo executado na solicitação U1 adquiriu o mutex de sessão. Se o procedimento armazenado levar muito tempo para ser executado, o Mecanismo de Banco de Dados do SQL Server presumirá que ele está esperando uma entrada do usuário. A solicitação de usuário U2 está esperando pelo mutex de sessão, enquanto o usuário está esperando pelo conjunto de resultados de U2, e U1 está esperando por um recurso de usuário. Esse estado de deadlock é logicamente ilustrado como:

    

Detecção de deadlocks

Todos os recursos listados na seção Recursos que podem causar deadlock participam do esquema de detecção de deadlocks do Mecanismo de Banco de Dados do SQL Server. A detecção de deadlocks é realizada por um thread de monitor de bloqueio que inicia periodicamente uma pesquisa em todas as tarefas em uma instância do Mecanismo de Banco de Dados do SQL Server. Os seguintes pontos descrevem o processo de pesquisa:

• O intervalo padrão é de 5 segundos.

• Quando o thread de monitor de bloqueio localiza deadlocks, o intervalo de detecção de deadlock diminui de 5 segundos para até 100 milissegundos, dependendo da frequência de deadlocks.

• Se o thread do monitor de bloqueio parar de encontrar deadlocks, o Mecanismo de Banco de Dados do SQL Server aumentará os intervalos entre as pesquisas para 5 segundos.

• Se um deadlock for detectado, o pressuposto será de que os próximos threads que precisarem esperar um bloqueio estejam entrando no ciclo de deadlock. As primeiras esperas de bloqueio, após a detecção de um deadlock, disparam imediatamente uma pesquisa de deadlock em vez de esperar o próximo intervalo de detecção de deadlock. Por exemplo, se o intervalo atual for de 5 segundos, e um deadlock tiver acabado de ser detectado, a próxima espera de bloqueio iniciará o detector de deadlock imediatamente. Se essa espera de bloqueio for parte de um deadlock, será detectada imediatamente, e não durante a próxima pesquisa de deadlock.

Normalmente, o Mecanismo de Banco de Dados do SQL Server executa apenas a detecção periódica de deadlock. Como o número de deadlocks encontrado no sistema geralmente é pequeno, a detecção periódica de deadlock ajuda a reduzir a sobrecarga de detecção de deadlock no sistema.

Quando o monitor de bloqueio inicia a pesquisa de deadlock para um determinado thread, ele identifica o recurso em que o thread está esperando. O monitor de bloqueio localiza o proprietário desse recurso em particular e, recursivamente, continua a pesquisa de deadlock para esses threads até encontrar um ciclo. Um ciclo identificado dessa maneira forma um deadlock.

Depois que um deadlock é detectado, o Mecanismo de Banco de Dados do SQL Server encerra um deadlock escolhendo um dos threads como vítima do deadlock. O Mecanismo de Banco de Dados do SQL Server encerra o lote atual que está sendo executado para o thread, reverte a transação da vítima do deadlock e retorna um erro 1205 para o aplicativo. A reversão da transação da vítima de deadlock libera todos os bloqueios mantidos pela transação. Isso permite que as transações dos outros threads sejam desbloqueadas e prossigam. O erro 1205 da vítima de deadlock registra informações sobre os threads e recursos envolvidos em um deadlock no log de erros.

Por padrão, o Mecanismo de Banco de Dados do SQL Server escolhe como vítima do deadlock a sessão que está executando a transação mais barata para reverter. Como alternativa, um usuário pode especificar a prioridade de sessões em uma situação de deadlock usando a instrução SET DEADLOCK_PRIORITY. A DEADLOCK_PRIORITY pode ser definida como LOW, NORMAL ou HIGH, ou então como qualquer valor de inteiro no intervalo (-10 a 10). A prioridade de deadlock é definida como NORMAL por padrão. Se duas sessões tiverem prioridades de deadlock diferentes, a sessão com a prioridade mais baixa será escolhida como a vítima de deadlock. Se ambas as sessões tiverem a mesma prioridade de deadlock, a sessão com a transação menos dispendiosa para ser revertida será escolhida. Se as sessões envolvidas no ciclo de deadlock tiverem a mesma prioridade de deadlock e o mesmo custo, a vítima será escolhida aleatoriamente.

Quando trabalha com Common Language Runtime (CLR), o monitor de deadlock detecta automaticamente um deadlock para os recursos de sincronização (monitores, bloqueio de leitura/gravação ou união de threads) acessados dentro dos procedimentos gerenciados. Entretanto, o deadlock é resolvido ao se lançar uma exceção no procedimento selecionado como a vítima de deadlock. É importante entender que a exceção não libera automaticamente os recursos pertencentes à vítima; os recursos devem ser liberados explicitamente. Em consonância com o comportamento de exceção, a exceção usada para identificar uma vítima de deadlock pode ser capturada e ignorada.

Ferramentas de informações de deadlocks

Para exibir as informações de deadlock, o Mecanismo de Banco de Dados do SQL Server fornece ferramentas de monitoramento na forma da sessão de xEvent system_health, dois sinalizadores de rastreamento e o evento de gráfico de deadlock no SQL Profiler.

Evento estendido de deadlock

No SQL Server versão 2012 (11.x) e nas versões posteriores, o evento estendido xml_deadlock_report (xEvent) deve ser usado em vez da classe de evento de gráfico de Deadlock no Rastreamento do SQL ou no SQL Profiler.

Quando deadlocks ocorrem, a sessão de system_health já captura todos os xEvents xml_deadlock_report que contém o gráfico de deadlock. Como a sessão de system_health é habilitada por padrão, você não precisa configurar uma sessão de xEvent separada para capturar informações de deadlock. Não é necessária nenhuma ação adicional para capturar informações de deadlock com o xEvent xml_deadlock_report.

O grafo de deadlock capturado geralmente tem três nós distintos:

• victim-list. O identificador de processo da vítima do deadlock.
• process-list. Informações sobre todos os processos envolvidos no deadlock.
• resource-list. Informações sobre os recursos envolvidos no deadlock.

Ao abrir o arquivo de sessão de system_health ou o buffer de anel, se o xEvent xml_deadlock_report for registrado, o Management Studio apresentará uma representação gráfica das tarefas e dos recursos envolvidos em um deadlock, conforme o seguinte exemplo:

A consulta a seguir pode exibir todos os eventos de deadlock capturados pelo buffer de anel da sessão system_health:

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
       xdr.query('.') AS [Event_Data]
FROM (SELECT CAST ([target_data] AS XML) AS Target_Data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
      CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date] DESC;

Este é o conjunto de resultados.

O exemplo a seguir mostra a saída, depois de selecionar o primeiro link em Event_Data na primeira linha do resultado:

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

Para obter mais informações, veja Usar a sessão de system_health

Sinalizadores de rastreamento 1204 e 1222

Quando ocorrem deadlocks, o Sinalizador de Rastreamento 1204 e o Sinalizador de Rastreamento 1222 retornam informações que são capturadas no registro de erros do SQL Server. O Sinalizador de Rastreamento 1204 relata informações de deadlock formatadas por cada nó envolvido no deadlock. O Sinalizador de Rastreamento 1222 formata as informações de deadlock, primeiro por processos e depois por recursos. É possível permitir que ambos os sinalizadores de rastreamento obtenham duas representações do mesmo evento de deadlock.

Além de definir as propriedades dos Sinalizadores de Rastreamento 1204 e 1222, a tabela a seguir também mostra as semelhanças e diferenças.

Exemplo do Sinalizador de Rastreamento 1204

O exemplo a seguir mostra a saída quando o Sinalizador de Rastreamento 1204 está ativado. Neste caso, a tabela em Node 1 é um heap sem índices, e a tabela em Node 2 é um heap com índices não clusterizados. A chave de índice em Node 2 é atualizada quando o deadlock ocorre.

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

O exemplo a seguir mostra a saída quando o Sinalizador de Rastreamento 1222 está ativado. Neste caso, uma tabela é um heap sem índices, e a outra tabela é um heap com um índice não clusterizado. Na segunda tabela, a chave de índice é atualizada quando o deadlock ocorre.

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=2022-02-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=2022-02-05T11:22:42.733
   lastbatchcompleted=2022-02-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=AdventureWorks2022.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=2022-02-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=2022-02-05T11:22:44.077
   lastbatchcompleted=2022-02-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=AdventureWorks2022.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=AdventureWorks2022.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=AdventureWorks2022.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

Evento gráfico de deadlock do criador de perfil

Este é um evento no SQL Profiler que apresenta uma representação gráfica das tarefas e recursos envolvidos em um deadlock. O exemplo a seguir mostra a saída do SQL Profiler quando o evento de grafo de deadlock é ativado.

Para obter mais informações sobre o evento de deadlock, veja Classe de evento Lock:Deadlock. Para obter mais informações sobre a execução do gráfico de deadlocks do SQL Profiler, veja Salvar gráficos de deadlocks (SQL Server Profiler).

Existem equivalentes para as classes de eventos do Rastreamento do SQL em Eventos Estendidos, veja Visualizar equivalentes de eventos estendidos para classes de eventos do Rastreamento do SQL. Eventos estendidos são recomendados no lugar de Rastreamento do SQL.

Lidar com deadlocks

Quando uma instância do Mecanismo de Banco de Dados do SQL Server escolhe uma transação como vítima de deadlock, ela encerra o lote atual, reverte a transação e retorna a mensagem de erro 1205 ao aplicativo.

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Como qualquer aplicativo que envia consultas Transact-SQL pode ser escolhido como vítima de deadlock, os aplicativos devem ter um manipulador de erros que possa capturar a mensagem de erro 1205. Se um aplicativo não capturar o erro, poderá prosseguir sem perceber que sua transação foi revertida e que erros podem ocorrer.

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 maneira 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. Isso minimiza a probabilidade do deadlock ocorrer novamente quando a consulta reenviada solicitar seus bloqueios.

Lidar com TRY...CATCH

Você pode usar TRY... CATCH para lidar com deadlocks. O erro de vítima de deadlock 1205 pode ser capturado pelo bloco CATCH e a transação pode ser revertida até que os threads sejam desbloqueados.

Para obter mais informações, veja Lindando com erros.

Minimizar 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 do banco de dados READ_COMMITTED_SNAPSHOT em ON para habilitar que as transações de leitura confirmada utilizem controle de versão de linha.
  • Use transação de isolamento de instantâneo
• Use conexões associadas.

Acessar objetos na mesma ordem

Se todas as transações simultâneas acessarem objetos na mesma ordem, haverá menos chance de ocorrerem deadlocks. Por exemplo, se duas transações simultâneas obtiverem um bloqueio na tabela Supplier e depois na tabela Part, uma transação será bloqueada na tabela Supplier até que a outra transação seja concluída. Após a primeira transação ser confirmada ou revertida, a segunda continua e não ocorre deadlock. Usar procedimentos armazenados para todas as modificações de dados pode padronizar a ordem de acesso dos objetos.

Evitar interação de usuário durante as transações

Evite escrever transações que incluam interação de usuário, pois a velocidade de lotes executados sem intervenção de usuário é muito mais rápida que a velocidade que um usuário deve responder manualmente às consultas, como por exemplo, responder a um prompt para um parâmetro solicitado por um aplicativo. Por exemplo, se uma transação estiver esperando por entrada de usuário e o usuário sai para almoçar ou vai para casa durante o fim de semana, esse mesmo usuário atrasa o término da transação. Isto degrada a taxa de transferência do sistema, pois quaisquer bloqueios mantidos pela transação somente são liberados quando a transação é confirmada ou revertida. Mesmo que não surja uma situação de deadlock, outras transações que acessam os mesmos recursos serão bloqueadas enquanto esperam pela conclusão da transação.

Manter as transações curtas e em um lote

Um deadlock ocorre tipicamente quando várias transações demoradas são executadas simultaneamente no mesmo banco de dados. Quanto mais longa a transação, por mais tempo as atualizações e bloqueios exclusivos são mantidos, bloqueando outras atividades, e levando à possíveis situações de deadlock.

Manter as transações em um lote minimiza as viagens de ida-e-volta de rede durante uma transação, reduzindo possíveis retardos ao completar a transação e liberando bloqueios.

Para saber mais sobre bloqueios de atualização, consulte o Guia de controle de versão de linha e bloqueio de transações.

Usar um nível de isolamento inferior

Determine se uma transação pode ser executada em um nível inferior de isolamento. Implementar confirmação por leitura permite que uma transação leia dados lidos anteriormente (não modificados) por outra transação, sem esperar pela conclusão da primeira transação. Um nível de isolamento mais baixo, como commit de leitura, mantém bloqueios compartilhados por um período mais curto que um nível de isolamento mais alto, como serializável. Isto reduz a contenção de bloqueio.

Usar um nível de isolamento com base em controle de versão de linha

Quando a opção READ_COMMITTED_SNAPSHOT do banco de dados estiver configurada como ON, uma transação em execução sob o nível de isolamento de leitura confirmada utilizará controle de versão de linha, em vez de bloqueios compartilhados durante operações de leitura.

O isolamento de instantâneos também usa controle de versão de linha, o que não usa bloqueios compartilhados durante operações de leitura. Para que uma transação possa ser executada sob isolamento de instantâneo, a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION deve ser definida como ON.

Implemente estes níveis de isolamento para minimizar deadlocks, que podem ocorrer entre as operações de leitura e gravação.

Usar conexões associadas

Usar associações de saída usando, faz com que duas ou mais conexões abertas pelo mesmo aplicativo possam cooperar entre si. Qualquer bloqueio adquirido pelas conexões secundárias é mantido como se tivessem sido adquiridos pela conexão primária, e vice-versa. Portanto, eles não se bloqueiam mutuamente.

Interromper uma transação

Em um cenário de deadlock, a transação vítima é automaticamente interrompida e revertida. Não há necessidade de interromper uma transação em um cenário de deadlock.

Causa um deadlock

Para causar um deadlock, você precisa conectar duas sessões ao banco de dados AdventureWorksLT2019. Vamos nos referir a essas sessões como Sessão A e Sessão B. Você pode criá-las simplesmente criando duas janelas de consulta no SQL Server Management Studio (SSMS).

Na Sessão A, execute o Transact-SQL a seguir. Esse código inicia uma transação explícita e executa uma só instrução que atualiza a tabela SalesLT.Product. Para fazer isso, a transação adquire um bloqueio de atualização (U) em uma linha na tabela SalesLT.Product que é convertido em um bloqueio exclusivo (X). Deixamos a transação aberta.

BEGIN TRANSACTION;

UPDATE SalesLT.Product
    SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

Agora, na Sessão A, execute o Transact-SQL a seguir. Esse código não inicia uma transação explicitamente. Ele opera no modo de transação de confirmação automática. Essa instrução atualiza a tabela SalesLT.ProductDescription. A atualização faz um bloqueio de atualização (U) em 72 linhas da tabela SalesLT.ProductDescription. A consulta se une a outras tabelas, incluindo a tabela SalesLT.Product.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

Para concluir essa atualização, a Sessão B precisa de um bloqueio compartilhado (S) na tabela SalesLT.Product, incluindo a linha bloqueada pela Sessão A. A sessão B é bloqueada em SalesLT.Product.

Retorne à Sessão A. Execute a instrução Transact-SQL a seguir. Uma segunda instrução UPDATE é executada como parte da transação aberta.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

A segunda instrução de atualização na Sessão A é bloqueada pela Sessão B no SalesLT.ProductDescription.

Agora, a Sessão A e a Sessão B estão se bloqueando mutuamente. Nenhuma transação pode prosseguir, pois cada uma delas precisa de um recurso bloqueado pela outra.

Após alguns segundos, o monitor de deadlock identifica que as transações na Sessão A e na Sessão B estão se bloqueando mutuamente e que nenhuma delas consegue progredir. Você verá um deadlock ocorrer, com a Sessão A escolhida como a vítima do deadlock. A sessão B é concluída com sucesso. Uma mensagem de erro é exibida na Sessão A com um texto semelhante ao seguinte:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Se um deadlock não for gerado, verifique se READ_COMMITTED_SNAPSHOT está habilitado no banco de dados de amostra. Deadlocks podem ocorrer em qualquer configuração de banco de dados, mas este exemplo exige que READ_COMMITTED_SNAPSHOT esteja habilitado.

Você poderia, então, visualizar os detalhes do deadlock no alvo ring_buffer da sessão de Eventos Estendidos system_health, que está habilitada e ativa por padrão no SQL Server. Considere a consulta a seguir:

WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
    FROM sys.dm_xe_sessions AS xs
         INNER JOIN sys.dm_xe_session_targets AS xst
             ON xs.[address] = xst.event_session_address
    WHERE xs.[name] = 'system_health'
          AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS c
      CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;

Você pode exibir o XML na coluna Deadlock_XML dentro do SSMS, selecionando a célula que aparece como um hiperlink. Salve essa saída como um arquivo .xdl, feche e reabra o arquivo .xdl no SSMS para obter um gráfico visual de deadlocks. Seu gráfico de deadlocks deve se parecer com a imagem a seguir.

Bloqueios e deadlocks otimizados

Aplica-se a: Banco de Dados SQL do Azure

O bloqueio otimizado introduziu um método diferente para a mecânica de bloqueio que altera a forma como os deadlocks que envolvem bloqueios TID exclusivos podem ser relatados. Em cada recurso na <resource-list> do relatório de deadlock, cada elemento <xactlock> relata os recursos subjacentes e as informações específicas para bloqueios de cada membro de um deadlock.

Considere o seguinte exemplo em que o bloqueio otimizado está habilitado:

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);
GO

Os seguintes comandos Transact-SQL em duas sessões criarão um deadlock na tabela t2:

Na sessão 1:

--session 1
BEGIN TRANSACTION foo;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

Na sessão 2:

--session 2:
BEGIN TRANSACTION bar;

UPDATE t2
    SET b = b + 10
WHERE a = 2;

Na sessão 1:

--session 1:
UPDATE t2
    SET b = b + 100
WHERE a = 2;

Na sessão 2:

--session 2:
UPDATE t2
    SET b = b + 20
WHERE a = 1;

Esse cenário de instruções UPDATE concorrentes resulta em um deadlock. Nesse caso, um recurso de bloqueio de chave, no qual cada sessão mantém um bloqueio X no próprio TID e está aguardando o bloqueio S no outro TID, resultando em um deadlock. O XML a seguir, capturado como o relatório de deadlock, contém elementos e atributos específicos para o bloqueio otimizado:

Conteúdo relacionado

• Visão geral de eventos estendidos
• sys.dm_tran_locks (Transact-SQL)
• Classe de evento Deadlock Graph
• Deadlocks com o nível de isolamento repetível de leitura
• Classe de evento Lock:Deadlock Chain
• Classe de evento Lock:Deadlock
• SET DEADLOCK_PRIORITY (Transact-SQL)
• Analisar e evitar deadlocks no Banco de Dados SQL do Azure
• Abrir, visualizar e imprimir um arquivo de deadlock no SQL Server Management Studio (SSMS)