ID do artigo: 832524 - Última revisão: quarta-feira, 25 de abril de 2007 - Revisão: 1.6

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

Vista lado a ladoClique aqui para exibir o artigo traduzido e o artigo original em inglês, lado a lado.
Tradução automáticaExibir Aviso de Isenção de Tradução Automática
Exibir os produtos aos quais esse artigo se aplica.
Dica do SistemaEste artigo aplica-se a um sistema operativo diferente do que está a utilizar. Foi desactivado o conteúdo do artigo, que pode não ser relevante para si.

Nesta página

Expandir tudo | Recolher tudo

Boletim técnico do SQL Server

Tópico abordou nesta edição: como resolver um deadlock
Voltar para o início

Objetivo

Para identificar, para solucionar problemas e recomendar uma solução para resolver um deadlock.
Voltar para o início

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 ajudar a identificar e resolver um deadlock.
Voltar para o início

Estudo de caso

Um estudo de caso, examinamos um sistema 911 que tenha seis operadores. Durante o atividade de pico, o aplicativo front-end do Microsoft Visual Basic que estiverem usando experiências conexões interrompidas. Devido a conexões desfeitas, os operadores devem re-input dados. Para um sistema 911 que opera 24 horas por dia, sete dias por semana, esse comportamento é inaceitável.
Voltar para o início

O que é um deadlock?

Um deadlock ocorre quando duas identificações de sistema servidor processos (SPIDs) estão aguardando para um recurso e nenhum processo pode avançar porque outro processo está impedindo que ele Obtendo o recurso.

Thread o Gerenciador de bloqueio ?s procura deadlocks. Quando o algoritmo de detecção de deadlocks ?s um Gerenciador de bloqueio detecta um deadlock, o Gerenciador de bloqueio escolhe um os SPIDs como vítima. O Gerenciador de bloqueio inicia uma mensagem de erro 1205 que é enviada para o cliente e o Gerenciador de bloqueio cancela o SPID. Eliminar o SPID libera os recursos e permite que outros SPID continuar. Eliminar o SPID é a vítima de deadlock é o que faz com que a conexão interrompida que o aplicativo de front-end Visual Basic experiências.

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

Embora deadlocks podem ser minimizados, eles não podem ser evitados completamente. É por isso que o aplicativo front-end deve ser projetado para lidar com deadlocks.
Voltar para o início

Como identificar um deadlock

etapa 1

Para identificar um deadlock, primeiro você deve obter informações de log. Se você suspeitar de um deadlock, você deve coletar informações sobre os SPIDs () e os recursos envolvidos no deadlock. Para fazer isso, adicione-T1204 e os - parâmetros de inicialização T3605 para SQL Server. Para adicionar esses dois parâmetros de inicialização, execute estas etapas:
  • Inicie o SQL Server Enterprise Manager.
  • Selecione e, em seguida, clique com o botão direito do mouse 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 caixa e, em seguida, clique em Adicionar .
  • Na caixa de texto Parameters , digite -T3605 e em seguida, clique em Adicionar .
  • Clique em OK .

Os parâmetros de inicialização terão efeito quando SQL Server é interrompido e, em seguida, reiniciado.

-T1204 inicialização parâmetro coleta informações sobre o processo e os recursos quando o algoritmo de detecção de deadlock encontra um deadlock. -T3605 parâmetro de inicialização grava essas informações os logs de erro do SQL Server.

As - T1205 parâmetro coleta informações de inicialização toda vez que o algoritmo de deadlock verifica para um deadlock, não quando um deadlock é encontrado. Não é necessário usar o parâmetro de inicialização-T1205.

Se você usa o parâmetro de inicialização-T1205, o seguinte é um exemplo da saída que será no log de erro do SQL Server: 

2003-05-14 11:46:26.76 spid4     Starting deadlock search 1
2003-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:0x42bdf340
2003-05-14 11:46:26.76 spid4      Node:1       ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-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, talvez não seja capaz de parar e reiniciar o SQL Server. Nesse caso, você pode usar Query Analyzer para executar o comando a seguir para ativar os sinalizadores de rastreamento de deadlock.

Observação Dessa forma você pode coletar informações sobre as deadlocks imediatamente. O "-1" indica todos os SPIDs. 

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

etapa 2

Em seguida, você deve coletar um rastreamento SQL Profiler. Se você ativar o sinalizador de rastreamento de deadlock, você receberá mais as informações necessárias, mas nem sempre. Por exemplo, em um estudo de caso a saída de sinalizador de rastreamento identificado que um sistema sp_cursoropen procedimento armazenado e um "UPDATE tblQueuedEvents definir notifyid = 3, ResynchDate" instrução estavam envolvidos em um deadlock. Infelizmente, você não souber a definição do procedimento armazenado do sistema sp_cursoropen . Você também não tem a instrução UPDATE completa porque ele foi truncado.

SQL Profiler pode obter as instruções completas juntamente com os planos de execução das instruções. Um rastreamento SQL Profiler também tem um evento de bloqueio para o "bloqueio" e "cadeia de bloqueio". "Bloqueio" corresponde ao sinalizador-T1204 e "cadeia de bloqueio" corresponde ao sinalizador-T1205. Ativando os sinalizadores de rastreamento de bloqueio e executar um rastreamento SQL Profiler durante a ocorrência de um deadlock devem fornecer os dados que você deve ter para solucionar problemas de um deadlock. Nesse caso e em outros, executando o SQL Profiler altera o tempo de execução suficiente para evitar o deadlock. Portanto, você normalmente irá capturar as informações de bloqueio com os sinalizadores de rastreamento e, em seguida, você executa o SQL Profiler.
Voltar para o início

Solucionando problemas de um deadlock

Após um deadlock, você pode coletar informações sobre o bloqueio usando o utilitário sqldiag e usando SQL Profiler. Na saída do arquivo SQLDiag.txt, procure uma entrada "Aguardar para gráfico". A "espera - para gráfico" entrada indica que um bloqueio foi encontrado.

A seguir está um exemplo da saída que você pode ver no log de erro do SQL Server ao usar o parâmetro de inicialização-T1205. 

2003-05-05 15:11:50.80 spid4    Wait-for graph
2003-05-05 15:11:50.80 spid4    Node:1
2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-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:0x193
2003-05-05 15:11:50.80 spid4    Requested By: 
2003-05-05 15:11:50.80 spid4    Input Buf: RPC Event: sp_cursoropen;1
2003-05-05 15:11:50.80 spid4    SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 1
2003-05-05 15:11:50.80 spid4    Owner:0x1937f2a0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0
2003-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:2
2003-05-05 15:11:50.80 spid4    ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-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, ResynchDate
2003-05-05 15:11:50.80 spid4    SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2003-05-05 15:11:50.80 spid4    Owner:0x1936e420 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0
2003-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 nó 1 e nó 2. Em cada nó, você tem uma seção de concessão e uma seção de solicitação. A seção de concessão é "Lista de conceder" e a seção de solicitação é o "solicitação por."
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 tinha sido concedidos um bloqueio de atualização, modo: U, no recurso KEY: 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 código a seguir: 
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 índice que não estão em cluster. O comando que SPID 55 estava executando foi o procedimento sp_cursoropen armazenados.

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

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

Um IndexId é igual a 2 é um índice que não está em cluster.

Observação Deadlocks são muito tempo confidencial.

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

A tabela a seguir mostra o gráfico de compatibilidade de bloqueio. Para obter mais informações sobre compatibilidade de bloqueio, consulte o tópico "Bloquear compatibilidade" nos manuais online do SQL Server 2000.

gráfico de compatibilidade de bloqueio
Recolher esta tabelaExpandir esta tabela
modo solicitado ÉSUIXSEISX
Tentativa de compartilhado (IS)SimSimSimSimSimNão
Compartilhado (S)SimSimSimNãoNãoNão
Atualização (U) SimSimNãoNãoNãoNão
IX intenção exclusiva)SimNãoNãoSimNãoNão
Compartilhado com intenção exclusiva (SIX) SimNãoNãoNãoNãoNão
Exclusivo (X)NãoNãoNãoNãoNãoNão


Em seguida, ao examinar a saída, identificar ObjectId 1653632984 como tabela 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 que não estão em cluster exclusivo no EventSID.

O rastreamento SQL Profiler não foi possível capturar a ocorrência de deadlock. Lembre-se, deadlocks são muito tempo dependente. A sobrecarga do SQL Profiler provavelmente adicionado algum tempo para a execução de um dos processos e que impediu SQL Profiler obtendo uma situação de deadlock. No entanto, ele fornecem informações que você pode usar para solucionar o problema. Você encontrou a instrução de tblQueuedEvents atualização completa para ser semelhante ao 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ê têm informações suficientes para recomendar uma solução que resolverá o deadlock.

Aqui está o plano de execução.

Observação Este plano de execução específico é lido da direita para esquerda e inferior para superior.

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])
Voltar para o início

Recomendar uma solução para resolver o deadlock

Observe que a instrução UPDATE está realizando uma "atualização de índice de cluster" no índice de cluster. Portanto, o índice que não estão em cluster e o índice de cluster devem ambos ser atualizados. O índice de cluster é ix_tblQueuedEvents e o índice que não estão em cluster é PK_tblQueuedEvents . Para executar as atualizações, a instrução UPDATE deve obter bloqueios exclusivos em ambos os índices. Esses dois índices são os índices que estão envolvidos no deadlock. Da revisão rastreamentos do SQL Profiler, não encontrou nenhuma consulta usado o ResynchDate na cláusula WHERE. Todas as instruções foram muito específicas e usadas o EventSID na cláusula WHERE. A melhor opção de um índice de cluster seria EventSID. Com essas informações e uma discussão com o cliente, descobrimos que o índice ResynchDate era antigo, e não era necessário. É recomendável que o cliente descartar o índice ix_tblQueuedEvents em ResynchDate e que eles tornam PK_tblQueuedEvent um índice de cluster. Isso resolveu a situação de deadlock.

Isso é apenas um exemplo de uma ocorrência de bloqueio que envolve bloqueios. Deadlocks também podem envolver paralelismo e envolvem threads. Eles podem envolver um, dois, três, ou mais SPIDs e recursos. Caso qualquer bloqueio, você deve obter a saída de parâmetro de inicialização ?T1204 e o rastreamento SQL Profiler para identificar, solucionar problemas e resolver o deadlock. Sua situação de deadlock envolverá diferentes processos e recursos. Portanto, soluções irão variar caso a caso. Você pode usar para resolver deadlocks os métodos de típicos incluem:
  • Adicionar e descartar os índices.
  • Adicionar dicas do í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 as transações mais breve possível.
Voltar para o início

Leitura adicional

Para obter mais informações sobre bloqueios, visite os seguintes sites:

http://msdn2.microsoft.com/en-us/library/Aa213040 (http://msdn2.microsoft.com/en-us/library/Aa213040)

http://msdn2.microsoft.com/en-us/library/aa213042(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213042(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa213028(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213028(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx)

http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213041(SQL.80).aspx)
Voltar para o início
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 2000 Standard Edition
Voltar para o início
Tradução automáticaTradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine Translation ou MT), não tendo sido portanto traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 832524  (http://support.microsoft.com/kb/832524/en-us/ )
Voltar para o início