Monitorando deadlocks com o Profiler

por Paulo Ribeiro

O que é um deadlock

Deadlock é o termo utilizado para designar um erro que acontece quando duas sessões entram numa situação de conflito, cada qual aguardando pela liberação de bloqueios que a outra sessão mantém.

Bloqueios são utilizados pelos bancos de dados relacionais para garantir o isolamento necessário às transações. Um bloqueio acarreta na espera pela liberação de recursos mantidos por uma transação: enquanto a transação estiver ativa, serão mantidos os bloqueios nas linhas modificadas pela transação. Nesse contexto, um deadlock acontece quando dois processos entram numa situação de conflito, por exemplo: a transação-A está aguardando a liberação de recursos que estão bloqueados pela transação-B. A transação-B por sua vez está aguardando pela liberação dos recursos que foram bloqueados pela transação-A. O resultado de um deadlock culmina na finalização – ou se preferir “morte” – da transação que consumir menos recursos, ação essa disparada pelo SQL Server 2000 quando identifica esse tipo de conflito.

Identificando um DeadLock

Deadlocks podem ser identificados pelo erro #1205 listado a seguir:

Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.

Transações excessivamente longas, níveis de isolamento restrititivos, bases de dados não normalizadas, ausência – ou excesso – de índices, manipulação de dados por critérios diferentes (a transação-A atulaliza tab1 e depois tab2, já a transação-B atualiza primeiro tab2 e depois tab1) são fatores que induzem a ocorrência de deadlocks e devem ser evitados.

Como rastrear deadlocks com o Profiler

Existem dois eventos que podem ser utilizados no SQL Profiler para rastreamento de deadlocks:

• Lock: DeadLock : informa a ocorrência do erro #1205 ;
• Lock: DeadLockChain : irá apontar os comandos e respectivos spid´s relacionados ao deadlock.

Para ilustrar o monitoramento do deadlock será criada uma trace no Profiler à partir do template padrão SQLProfilerStandard. Na guia Events foram excluidos os eventos Security Audit e Sessions e adicionados Lock:Deadlock e Lock:DeadLockChain. O primeiro passo, portanto, será criar a trace com essas especificações.

Para reproduzir um deadlock serão abertas duas sessões, aqui denominadas sessao-1 e sessao-2. A seqüência de comandos para reproduzir um deadlock estão reproduzidas na Listagem 1 a seguir.

Listagem 1. Seqüência de comandos para reproduzir um DeadLock

-- Executar na sessao-1
use NorthWind
go
BEGIN TRAN
select * from orders (holdlock) where orderid=10249

-- Executar na sessao-2
use NorthWind
go
BEGIN TRAN
select * from orders (holdlock) where orderid=10249

-- Executar na sessao-1 (ficara aguardando o termino da sessao-2)
update orders set employeeid=4 where orderid=10249

-- Executar na sessao-2 (essa sessao sera finalizada com DeadLock)
update orders set employeeid=4 where orderid=10249

O deadlock visualizado no Profiler pode ser confirmado na Figura 1 a seguir.

Figura 1. Utilizando o Profiler no rastreamento de Deadlocks

Conclusão

No exemplo da Figura 1, conseguiríamos acabar com o deadlock removendo o hint HOLDLOCK do comando SELECT. Esse hint mantém ativos os bloqueios até o término da transação, prolongando o efeito dos bloqueios de leitura e, consequentemente, aumentando as chances de incidência de deadlocks.
O maior problema desse tipo de monitoramento é que não existe uma maneira para filtrar somente os eventos relacionados ao deadlock: nossa trace irá capturar todos os batchs e procedures executadas no database, e não só aqueles relacionados ao deadlock! Num monitoramento desse tipo, temos que “torcer” para que o deadlock aconteça durante o monitoramento, caso contrário nossa análise será em vão... Continuaremos com esse assunto na próxima matéria com uma solução para esse problema.
Até mais!

Paulo Ribeiro (psribeiro@hotmail.com) é Microsoft MCDBA e membro da equipe editorial da SQL Magazine. Atua como DBA sênior em SQL Server na Livraria e Papelaria Saraiva S/A.