Boletim técnico do SQL Server - como resolver um deadlock

IMPORTANTE: Este artigo foi traduzido pelo software de tradução automática da Microsoft e eventualmente pode ter sido editado pela Microsoft Community através da tecnologia Community Translation Framework (CTF) ou por um tradutor profissional. A Microsoft oferece artigos traduzidos automaticamente por software, por tradutores profissionais e editados pela comunidade para que você tenha acesso a todos os artigos de nossa Base de Conhecimento em diversos idiomas. No entanto, um artigo traduzido pode conter erros de vocabulário, sintaxe e/ou gramática. A Microsoft não é responsável por qualquer inexatidão, erro ou dano causado por qualquer tradução imprecisa do conteúdo ou por seu uso pelos nossos clientes.

Clique aqui para ver a versão em Inglês deste artigo: 832524

Boletim técnico do SQL Server

Tópico abordado nesta edição: como resolver um deadlock
Meta
Para identificar, solucionar problemas e recomendar uma solução para resolver um deadlock.
Introdução
Este artigo examina uma situação de deadlock e fornece etapas para resolver o deadlock. Cada bloqueio pode ser diferente e pode ser causado por diversas variáveis de ambiente diferente. As informações fornecidas neste artigo podem ajudá-lo a identificar e resolver um deadlock.
Estudo de caso
Em um estudo de caso, examinamos um sistema 911 que possui seis operadores. Durante a atividade de pico, o aplicativo front-end do Microsoft Visual Basic que estão usando experiências as conexões interrompidas. Devido a conexões interrompidas, os operadores devem inserir novamente os dados. Para um sistema 911 que opera 24 horas por dia, sete dias por semana, esse comportamento é inaceitável.
O que é um deadlock?
Um deadlock ocorre quando dois processos de servidor de sistema IDs(SPIDs) está aguardando um recurso e nenhum processo pode avançar porque theother processo está impedindo de obter o recurso.

Segmento do lockmanager procura deadlocks. Quando o detectionalgorithm de deadlock do Gerenciador de bloqueio detecta um deadlock, Gerenciador de bloqueio escolhe um dos SPIDs como avictim. O Gerenciador de bloqueio inicia uma mensagem de erro 1205 é enviada para theclient e o Gerenciador de bloqueio elimina o SPID. Eliminar o SPID libera os recursos e permite que o SPID continuar. Eliminar o SPID é vítima de thedeadlock é o que faz com que a conexão interrompida experiências no Visual Basicfront-fim do aplicativo.

Em um aplicativo bem projetado, o aplicativo front-end deve interceptar o erro 1205, reconectar-se ao SQL Server e submeter novamente a transação.

Althoughdeadlocks pode ser minimizada, não podem ser totalmente evitados. É por isso que thefront de aplicativos deve ser projetado para lidar com deadlocks.
Como identificar um deadlock
Etapa 1

Para identificar um deadlock, você deve primeiro obter loginformation. Se você suspeitar de um deadlock, você deve coletar informações sobre the(SPIDs) e os recursos que estão envolvidos no deadlock. Para fazer isso, addthe-T1204 e -parâmetros de inicialização T3605 do SQL Server. Para adicionar esses parâmetros twostartup, execute essas etapas:
  • Inicie o SQL Server Enterprise Manager.
  • Selecione e, em seguida, clique com botão direito no servidor.
  • Clique em Propriedades.
  • Clique em parâmetros de inicialização.
  • Na caixa de diálogo Parâmetros de inicialização , digite -T1204 no texto parâmetros de caixa e, em seguida, clique em Adicionar.
  • Na caixa de texto parâmetros , digite -T3605e, em seguida, clique em Adicionar.
  • Clique em OK.

Os parâmetros de inicialização terão efeito quando Serveris SQL interrompido e iniciado novamente.

-Collectsinformation do parâmetro de inicialização T1204 sobre o processo e os recursos quando detectionalgorithm o deadlock encontra um deadlock. -T3605 inicialização parâmetro gravações thisinformation os logs de erro do SQL Server.

Coleta de startupparameter-T1205 informações toda vez que o algoritmo de deadlock verifica para um deadlock, bloqueio de whena não são encontradas. Não é necessário usar o parâmetro de inicialização-T1205.

Se você usar o parâmetro de inicialização-T1205, o seguinte é um sampleof, a saída será no log de erros do SQL Server:

2003-05-14 11:46:26.76 spid4     Starting deadlock search 12003-05-14 11:46:26.76 spid4     Target Resource Owner:2003-05-14 11:46:26.76 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf3402003-05-14 11:46:26.76 spid4      Node:1       ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf3402003-05-14 11:46:26.76 spid4     2003-05-14 11:46:26.76 spid4     End deadlock search 1 ... a deadlock was not found.2003-05-14 11:46:26.76 spid4     ----------------------------------2003-05-14 11:46:31.76 spid4     ----------------------------------2003-05-14 11:46:31.76 spid4     Starting deadlock search 2


Às vezes, pode não ser capaz de parar e reiniciar o SQL Server. Nesse caso, é possível usar o Query Analyzer para executar o comando a seguir para habilitar os sinalizadores deadlocktrace.

Observação: Dessa forma, que você pode coletar informações sobre o deadlocksimmediately. "-1" indica todos os SPIDs.

dbcc traceon (1204, 3605, -1)godbcc tracestatus(-1)go

Etapa 2

Em seguida, você deverá coletar um rastreamento do SQL Profiler. Se quiser ativar o sinalizador de rastreamento de deadlock, você obterá mais o requiredinformation, mas nem sempre. Por exemplo, estudo de acase a identifiedthat de saída do sinalizador de rastreamento um sistema sp_cursoropen procedimento armazenado e um "UPDATE tblQueuedEvents setnotifyid = 3, ResynchDate" instrução estavam envolvidos em adeadlock. Infelizmente, você não sabe thedefinition do procedimento armazenado do sistema sp_cursoropen . Alsodo você não tem a instrução de atualização completa porque ele wastruncated.

SQL Profiler pode obter as instruções completas além de planos de execução das instruções. Um rastreamento do SQL Profiler também tem um lockevent para o "bloqueio" e "cadeia de bloqueio". "Bloqueio" corresponde ao sinalizador de T1204 e "cadeia de bloqueio" corresponde ao sinalizador - T1205. Ativando os sinalizadores de rastreamento de bloqueio e executar um rastreamento do SQL Profiler durante o occurrenceof um deadlock devem fornecer os dados necessários para solucionar problemas de adeadlock. Nesse caso e em outros, executando o SQL Profiler altera a execução timingof suficiente para evitar o deadlock. Portanto, você irá typicallycapture as informações de bloqueio com os sinalizadores de rastreamento e, em seguida, executar o SQLProfiler.
Solucionando problemas de travamento
Após a ocorrência de um deadlock, é possível reunir informações sobre o bloqueio usando o utilitáriosqldiag e usando SQL Profiler. Na saída do arquivo theSQLDiag.txt, procure por uma entrada "Aguardar para gráfico". A "espera-do graph" entryindicates que foi encontrado um deadlock.

A seguir está uma sampleof a saída você pode ver no log de erros do SQL Server ao usar o parâmetro de inicialização do T1205.

2003-05-05 15:11:50.80 spid4    Wait-for graph2003-05-05 15:11:50.80 spid4    Node:12003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x1932003-05-05 15:11:50.80 spid4    Victim Resource Owner:2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: X SPID:60 ECID:0 Ec:(0x1F1BB5B0) Value:0x1932003-05-05 15:11:50.80 spid4    Requested By: 2003-05-05 15:11:50.80 spid4    Input Buf: RPC Event: sp_cursoropen;12003-05-05 15:11:50.80 spid4    SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 12003-05-05 15:11:50.80 spid4    Owner:0x1937f2a0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:02003-05-05 15:11:50.80 spid4    Grant List 0::2003-05-05 15:11:50.80 spid4    KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0 2003-05-05 15:11:50.80 spid4    Node:22003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x1932003-05-05 15:11:50.80 spid4    Requested By: 2003-05-05 15:11:50.80 spid4    Input Buf: Language Event: Update tblQueuedEvents Set NotifyID = 2, ResynchDate2003-05-05 15:11:50.80 spid4    SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 12003-05-05 15:11:50.80 spid4    Owner:0x1936e420 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:02003-05-05 15:11:50.80 spid4    Grant List 0::2003-05-05 15:11:50.80 spid4    KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0


Na entrada "Aguardar para gráfico", você tem o nó 1 e Node2. Em cada nó, você pode ter uma seção de concessão e uma seção de solicitação. O grantsection é a "lista de concessão" e a seção de solicitação é "RequestBy".
Em cada nó, você pode identificar o seguinte:
  • O SPID.
  • O comando que o SPID estava sendo executado.
  • O recurso.
  • O modo de bloqueio do recurso.

Por exemplo, no nó 1, a lista Grant, SPID 55 tenha recebido um bloqueio de atualização, modo: U, recurso chave: 8:1653632984:2. 8 = DBID, 1653632984 = ObjectID e 2 = Indid. Para obter o número de identificação do banco de dados, execute o procedimento sp_helpdb armazenados. Para obter a tabela, execute o seguinte código:
select * from sysobjects where id = 1653632984


Para obter o índice, execute o seguinte código:
select * from sysindexes where indid = 2 and id = 1653632984

Se IndexId é igual a 2, você saberá que o índice é um nonclusteredindex. O comando 55 SPID estava sendo executado era o procedimento sp_cursoropen armazenados.

No nó 2, a lista Grant, 60 SPID recebeu um bloqueio exclusivo, modo: X, recurso chave: 8:1653632984:1.8 = DBID, 1653632984 = ObjectID, 1 = Indid. Isso é na mesma tabela mas índice 1 é o índice de cluster. O comando 60 SPID estava sendo executado era:
Update tblQueuedEvents Set NotifyID = 2, ResynchDate

Um IndexId é igual a 1 é um índice de cluster.

AnIndexId é igual a 2 é um índice de cluster.

Observação: Os deadlocks são muito sensíveis ao tempo.

Em seguida, no nó 1, solicitar por 55 SPID solicitado um bloqueio compartilhado, modo: S em IndexId = 1. No nó 2, solicitar por 60 SPID solicitado um bloqueio exclusivo, modo: X, em IndexId = 2. Solicitações de bloqueio de Becausethese ocorrerem ao mesmo tempo, o deadlock ocorre. Cada SPID'sgranted bloqueios estão impedindo os bloqueios solicitados de continuar.

Tabela asseguintes mostra o gráfico de compatibilidade de bloqueio. Para compatibilidade com aboutlock mais informações, consulte o tópico "Compatibilidade de bloqueio" no SQL Server 2000 BooksOnline.

Gráfico de compatibilidade de bloqueio
RequestedmodeÉSUIXSEISX
Shared(IS) intençãoSimSimSimSimSimNão
Compartilhado (S)SimSimSimNãoNãoNão
Atualização (U)SimSimNãoNãoNãoNão
Exclusive(IX) intençãoSimNãoNãoSimNãoNão
Compartilhados com o propósito exclusivo (seis)SimNãoNãoNãoNãoNão
Exclusive(X)NãoNãoNãoNãoNãoNão


Em seguida, ao examinar a saída, identifyObjectId 1653632984 como a tabela de tblQueuedEvents e obter um procedimento sp_help armazenado para a tabela de saída. Havia dois índices na tabela. Dois índices foram ix_tblQueuedEvents e PK_tblQueuedEvent. ix_tblQueuedEvents é um índice agrupado em ResynchDate e PK_tblQueuedEvent é uma chave primária, o índice não está em cluster exclusivo no EventSID.

O rastreamento SQL Profiler não foi capaz de capturar a deadlockoccurrence. Lembre-se de que os bloqueios sejam muito tempo dependente. A sobrecarga de SQLProfiler provavelmente adicionado algum tempo para a execução de um andthat de processos impediu o SQL Profiler obtendo em uma situação de deadlock. No entanto, itdid fornecem informações autilização pode usar para solucionar o problema. Encontrado aproveitartodo update tblQueuedEvents instrução seja semelhante à seguinte:

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16' where eventSID = 73023
Você também descobriu o plano de execução. Você ainda não tem a declaração de procedimento completo sp_cursoropen armazenadas, mas você tem suficiente torecommend informações sobre uma solução que resolverá o deadlock.

Este é o plano de theexecution.

Observação: Este plano de execução específica é lido da direita para a leftand de baixo para cima.

StmtText                                                      				                                                                           				   				                                  --------------------------------------------------------------------------------------------------------------------------------------------------------------------				Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16'				where eventSID = 73023                                                     				   				                    |--Clustered Index				Update(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[ix_tblQueuedEvents ]),				SET:([tblQueuedEvents].[NotifyID]=[@1],				[tblQueuedEvents].[ResynchDate]=[Expr1004]))       |--Top(1)                                                                 				                                                                           				   				                          |--Compute Scalar(DEFINE:([Expr1004]=Convert([@2])))                				                                                                           				   				                                 |--Index				Seek(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[PK_tblQueuedEvents]),				SEEK:([tblQueuedEvents].[EventSID]=[@3]) 
Recomendar uma solução para resolver thedeadlock
Observe que a instrução UPDATE é executar uma "atualização de índice em cluster" no índice em cluster. Portanto, o nonclusteredindex e o índice de cluster devem ambos ser atualizados. O índice de cluster é ix_tblQueuedEvents e o índice não está em cluster é PK_tblQueuedEvents. Para executar as atualizações, a instrução de atualização deve obtainexclusive bloqueios nos dois índices. Esses dois índices são os índices que areinvolved no deadlock. Da revisão rastreamentos do SQL Profiler, você fez não consultas de seeany que usavam o ResynchDate na cláusula WHERE. Todos os statementswere muito específico, e o EventSID usados na cláusula WHERE. A melhor opção de uma clusteredindex seria EventSID. Com essas informações e uma discussão com thecustomer, descobrimos que o índice ResynchDate era antigo e não era necessário. É recomendável que o cliente descartar o índice de ix_tblQueuedEvents em ResynchDate e que faça PK_tblQueuedEvent um índice de cluster. Isso resolveu o deadlocksituation.

Este é apenas um exemplo de um deadlock caso esse involveslocks. Deadlocks também podem envolver paralelismo e envolvem threads. Eles caninvolve um, dois, três ou mais SPIDs e recursos. Com qualquer caso de bloqueio, você deve obter – T1204 traceto o SQL Profiler identificar, solucionar problemas e resolver o impasse e saída de parâmetro de inicialização. O deadlocksituation envolverá recursos e processos diferentes. Portanto, solutionswill variar de caso para caso. Você pode usar para resolver deadlocksinclude de métodos típicos:
  • Adicionar e descartar os índices.
  • Adicionando as dicas de índice.
  • Modificar o aplicativo para acessar recursos em um padrão semelhante.
  • Removendo a atividade da transação como disparadores. Por padrão, os disparadores são transacionais.
  • Manter transações mais curtas possível.

Aviso: este artigo foi traduzido automaticamente

Propriedades

ID do Artigo: 832524 - Última Revisão: 04/10/2016 04:55:00 - Revisão: 2.0

Microsoft SQL Server 2000 Standard Edition

  • kbsqlsetup kbtypenonkb kbpubtypett kbresource kbquery kbperformance kbserver kbdatabase kbhowto kbinfo kbcode kbmt KB832524 KbMtpt
Comentários