ENTENDENDO OS DEADLOCKS
Os DeadLocks ocorrem quando existe um ciclo de dependência entre duas ou mais transações pelo mesmo recurso. Uma transação (T1) pode adquirir um ou mais recursos (por exemplo, locks). Se o recurso sendo adquirido por T1 já estiver sendo utilizado por outra transação (T2), T1 terá que aguardar a segunda transação (T2) liberar o recurso desejado. Neste caso, dizemos que T1 tem uma dependência de T2, que mantêm o recurso.
Agora imagine que a transação que mantêm o recurso (T2), também quer adquirir um recurso que está sendo utilizado pela primeira transação (T1). Esta situação ocasiona o famoso deadlock – ambas as transações não podem liberar os recursos que elas utilizam até elas serem concluídas (commit) ou desfeitas (rollbak) e estas transações não podem ser concluídas ou desfeitas porque elas estão aguardando por recursos uma das outras. Confuso?
Para tentar melhorar o entendimento, vamos a um exemplo:
Imagine que você possui uma aplicação e que a mesma abre duas transações no servidor, T1 e T2. A transação T1, adquiri um lock exclusivo na tabela Supplier. A transação T2, adquiri um lock exclusivo na tabela Part e também tenta adquirir um lock na tabela Supplier. A transação T2 não pode obter o lock porque T1 já possui o recurso, sendo assim, a transação T2 é bloqueada e fica aguardando pela conclusão da transação T1.
Enquanto isso, a transação T1 também tenta adquirir um lock na tabela Part, mas não pode obter o lock porque a transação T2 já possui o recurso. As transações não podem liberar os recursos até que elas sejam efetivadas (commit) ou desfeitas (rollback), porém, as transações não podem ser efetivadas ou desfeitas porque para continuar, estão aguardando pelo recurso uma da outra. Ou seja, T1 espera pela conclusão de T2 que espera pela conclusão de T1.
Na ilustração abaixo, a Transação 1 está aguardando a Transação 2 liberar o Lock na tabela Part, enquanto a Transação 2 também está aguardando a Transação 1 liberar o Lock na tabela Supplier.
Para resolver o deadlock, uma das transações do ciclo precisa ser derrubada e para escolher a “vítima” do deadlock, o SQL Server 2000 verifica todas as threads que estão participando da transação e o quanto estão sendo utilizadas. Geralmente, o SQL Server escolhe a thread que esta trabalhando menos para ser a “vítima” do deadlock. Quando um deadlock ocorre, o usuário recebe a seguinte mensagem de erro (Error 1205):
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.
O DBA também pode verificar a ocorrência de deadlocks no error log do SQL Server. Por default, os deadlocks não são logados no error log do SQL Server mas o DBA pode habilitar esta funcionalidade ativando os trace flags 3605 e 1204 no QA.
— Ativa os trace flags
dbcc traceon (1204, 3605, -1)
go
— Mostra os trace flags ativos no servidor
dbcc tracestatus(-1)
go
O trace flag 1204 coleta informações sobre os processos e os recursos utilizados quando um deadlock for detectado e o trace flag 3605 escreve estas informações para o error log do SQL Server.
Uma vez estando ativos estes trace flags, sempre que ocorrer um deadlock o SQL Server adicionará em seu erro log uma mensagem semelhante a apresentada abaixo:
Deadlock encountered …. Printing deadlock information
2005-04-06 10:31:09.01 spid3
2005-04-06 10:31:09.01 spid3 Wait-for graph
2005-04-06 10:31:09.01 spid3
2005-04-06 10:31:09.01 spid3 Node:1
2005-04-06 10:31:09.01 spid3 RID: 16:1:112430:3 CleanCnt:1 Mode: U Flags: 0x2
2005-04-06 10:31:09.01 spid3 Grant List 2::
2005-04-06 10:31:09.01 spid3 Owner:0x6f724b60 Mode: U Flg:0x0 Ref:0 Life:00000001…
2005-04-06 10:31:09.01 spid3 SPID: 191 ECID: 0 Statement Type: UPDATE Line #: 352
2005-04-06 10:31:09.01 spid3 Input Buf: RPC Event: sp_deadlock1
2005-04-06 10:31:09.01 spid3 Grant List 3::
2005-04-06 10:31:09.01 spid3 Requested By:
2005-04-06 10:31:09.01 spid3 ResType:LockOwner Stype:’OR’ Mode: U SPID:68 ECID:0….
2005-04-06 10:31:09.01 spid3
2005-04-06 10:31:09.01 spid3 Node:2
2005-04-06 10:31:09.01 spid3 RID: 16:1:101962:22 CleanCnt:1 Mode: X Flags: 0x2
2005-04-06 10:31:09.01 spid3 Grant List 3::
2005-04-06 10:31:09.01 spid3 Owner:0x6b119400 Mode: X Flg:0x0 Ref:0 Life:02000000…
2005-04-06 10:31:09.01 spid3 SPID: 68 ECID: 0 Statement Type: UPDATE Line #: 251
2005-04-06 10:31:09.01 spid3 Input Buf: RPC Event: sp_deadlock2
2005-04-06 10:31:09.01 spid3 Requested By:
2005-04-06 10:31:09.01 spid3 ResType:LockOwner Stype:’OR’ Mode: U SPID:191…
2005-04-06 10:31:09.01 spid3 Victim Resource Owner:
2005-04-06 10:31:09.01 spid3 ResType:LockOwner Stype:’OR’ Mode: U SPID:191…
Com o log acima é possível obter as seguintes informações:
— As conexões que causaram o deadlock ==> SPID: 191 e SPID: 68
— A base de dados ==> RID: 16 (select db_name(16), para saber o nome da base)
— Os objetos envolvidos no deadlock ==> procedures sp_deadlock1 e sp_deadlock2
— O número das linhas dentro das procedures onde provavelmente ocorreu o deadlock ==> UPDATE Line #: 352 e UPDATE Line #: 251
Neste exemplo, se abrirmos as procedures sp_deadlock1 e sp_deadlock2 e navegarmos até o número das linhas indicado, teremos os statements que causaram o deadlock.
MINIMIZANDO OS DEADLOCKS
É muito difícil eliminar os DeadLocks completamente, entretanto, o número de deadlocks pode ser minimizado. Minimizando os deadlocks, podemos aumentar o throughput das transações e reduzir o overhead do sistema.
Algumas das atividades que um DBA ou desenvolvedor podem estar reallizando para minimizar a ocorrência de deadlocks são as seguintes:
A) Acessar os objetos na mesma ordem: Se todas as transações acessarem os objetos na mesma ordem, será pouco provável ocorrer um deadlock. No exemplo abaixo, se duas transações concorrentes obterem lock na tabela Supplier e depois na tabela Part, uma delas ficará bloqueada na tabela Supplier até a outra transação terminar, então, a segunda continua e o DeadLock não ocorrerá.
B) Evitar a interação de usuários nas transações: Deve-se evitar transações de escritas que incluem a interação de usuários, porque a velocidade de execução de um batch sem a intervenção de usuários é muito maior em relação ao tempo de resposta de uma query executada manualmente por um usuário. Por exemplo, se uma transação está esperando por uma entrada (input) de um usuário e o usuário foi tomar um cafezinho, a transação ficará esperando para poder ser concluida. Mesmo se não ocorrer uma situação de deadlock, outras transações que acessem o mesmo recurso serão bloqueadas esperando que a transação seja concluída ou desfeita.
C) Fazer transações curtas e em um único batch: Um deadlock ocorre tipicamente quando executamos transações de longa duração concorrendo no mesmo objeto. Em uma transação muito longa, a possibilidade de locks exclusivos ou de update é muito maior, o que bloqueia outras atividades e ocasiona possíveis situações de DeadLocks. Procure manter as transações em um único batch (uma batch termina quando você coloca a palavra GO), assim minimizamos o tráfego de rede durante a transação e reduzimos possíveis atrasos para completar a transação.
D) Use um nível de isolamento baixo: Determinar se a transação pode rodar em um nível de isolamento baixo. Implementando um nível de isolamento alto como o “read commited”, somente será permitido que a transação leia dados que não estão sendo alterados, ou seja, se estiver sendo efetuada alguma alteração, a transação de leitura ficará bloqueada até que a alteração seja concluída. Um recurso muito utilizados pelos desenvolvedores é a utilização da HINT NOLOCK nos statements menos significativos.
Exemplo: SELECT au_lname FROM authors WITH (NOLOCK)
Links Relacionados:
KB832524: SQL Server technical bulletin – How to resolve a deadlock
Tips for Reducing SQL Server Deadlocks
Para saber mais sobre Hints no SQL Server, consulte por “locking, hints” no Books Online do SQL Server, ou abra o link mk:@MSITStore:C:Program%20FilesMicrosoft%20SQL%20Server80ToolsBooksacdata.chm::/ac_8_con_7a_1hf7.htm em uma máquina que tenha o BOL instalado.
Um abraço a todos
Nilton Pinheiro