Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como gerar um histórico de deadlocks para análise de falhas em rotinas

Post Views 22,131 views
Reading time 9 minutes

Olá pessoal,
Tudo bem com vocês ? Espero que sim!

Nesse post eu gostaria de compartilhar com vocês uma situação onde ocorreu um deadlock na madrugada, interrompendo uma rotina crítica, e precisava identificar qual a sessão que ocasionou o deadlock para planejar ações que evitem essa situação. Para ajudar na identificação dessas situações e ter um histórico de deadlocks, vou apresentar algumas soluções para atingir esse objetivo.

O que é um deadlock?

Um deadlock é gerado, quando dois ou mais processos tentam acessar um mesmo objeto, aplicando locks nesse recurso. Sendo assim, esses processos tentam realizar a mesma ação, ao mesmo tempo, no mesmo objeto, e um processo fica aguardando o outro remover o lock para continuar a operação.

Exemplo, o processo A aplica um lock na tabela cliente para realizar um update. O processo B também aplica um lock na tabela cliente para realizar um delete, ao mesmo tempo do processo A. Sendo assim, o processo A fica aguardando o processo B terminar o delete e remover o lock para continuar e o processo B fica aguardando o processo A terminar o update e remover o lock para continuar.

Deadlock ilustrado

Caso o SQL Server não tome uma ação, eles iriam esperar um ao outro infinitamente, e isso é chamado de deadlock.

Como gerar/simular um deadlock

Para entender na prática o que é um deadlock e como ele funciona, vou mostrar a vocês como ele acontece no dia a dia e como o SQL Server se comporta quando o deadlock é identificado.

Agora vamos gerar os deadlocks:

Deadlock gerado:

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

Assim que o Deadlock Monitor Thread identificou que estava ocorrendo um deadlock na instância, ele tratou de eliminar a sessão mais recente (55), permitindo que a sessão 58 pudesse ser processada, “resolvendo” assim, o deadlock gerado.

Como funciona o Deadlock Monitor Thread

No SQL Server existe um recurso chamado Deadlock Monitor Thread, que é executado em background para identificar e “ajudar” a resolver deadlocks na instância, evitando assim, que as sessões fiquem infinitamente aguardando uma a outra.

Se você fizer uma consulta na DMV sys.dm_os_waiting_tasks, você vai perceber que existem sempre uma tarefa de sistema com o evento REQUEST_FOR_DEADLOCK_SEARCH.

Essa thread é acionada a cada 5 segundos para verificar se existem deadlocks na instância. Se ela encontrar algum deadlock, ela vai matar uma das sessões em deadlock para liberar os recursos travados para a outra sessão que está aguardando.

Como o SQL Server decide qual sessão ele vai eliminar? É bem simples, ele vai eliminar sempre a sessão que possui o menor custo (geralmente, a que foi “lockada” por último), facilitando assim, o rollback das transações realizadas pela sessão que foi escolhida para ser desconectada (vítima do deadlock) – Desde que elas possuam a mesma prioridade. Caso as sessões tenham a mesma prioridade e o mesmo custo, a vítima do deadlock será escolhido aleatoriamente.

Quando o Deadlock Monitor Thread elimina uma sessão por conta de deadlock, ele é executado novamente imediatamente para verificar se o deadlock foi resolvido. Se continuar existindo deadlocks na instância, ele vai eliminar mais sessões e vai diminuindo o tempo dos próximos ciclos de execução em 100ms (a cada ciclo), até que nenhum deadlock seja detectado.

Como definir a prioridade de uma sessão

Quando o Deadlock Monitor Thread identifica que ocorreu um deadlock e começa a avaliar qual sessão ele vai eliminar para “resolver” isso, o primeiro critério a ser considerado é a prioridade da sessão. Por padrão, todas as sessões no SQL Server possuem o valor de prioridade = 0 (NORMAL). Para alterar a prioridade de uma sessão, temos o comando SET DEADLOCK_PRIORITY.

Parâmetros:
– LOW: Prioridade abaixo de NORMAL, cujo valor é -5.
– NORMAL: Prioridade padrão, cujo valor é 0.
– HIGH: Prioridade acima da NORMAL, cujo valor é 5.
– <numeric-priority>: É um intervalo de valor inteiro (-10 a 10) para fornecer 21 níveis de prioridade de deadlock ao invés de apenas 3 caso utilize as pré-definidas.

Exemplos:

Vocês se lembram do exemplo que eu criei acima para simular o deadlock? E se a gente executasse novamente, mas alterando a prioridade da sessão que foi eliminada como vítima do deadlock? O que aconteceria?

Aqui é o trecho que conseguia terminar de processar – Agora virou a vítima do Deadlock

Já esse trecho, era a vítima do deadlock no exemplo anterior, pois as duas sessões tinham a mesma prioridade, mas a segunda sessão foi escolhida como vítima do deadlock, porque ela estava com o lock há menos tempo.

Vejam que aumentando a prioridade dela, conseguimos alterar o comportamento do Deadlock Monitor Thread para que esse comando, que considero muito importante, não seja vítima de deadlocks por conta de outros comandos que não tem tanta relevância para o meu negócio, nesse exemplo.

Como identificar os deadlocks que ocorreram na instância

Existem várias métodos que podem ser utilizados para identificar os locks que ocorreram na instância, de modo que você consiga identificar e avaliar os locks depois que eles ocorreram, uma vez que o DBA não vai ficar monitorando manualmente todos os deadlocks, em todas as instâncias do ambiente, o tempo todo.

Como identificar os deadlocks utilizando Trace

Uma maneira fácil e prática de se identificar os locks da instância, é ativar um trace utilizando a trace flag 1222, conforme demonstro abaixo:

Caso você queira verificar se essa traceflag está ativa, basta executar o comando abaixo:

Uma vez que esse trace está ativo, sempre que ocorrer um deadlock na instância, esse evento ficará gravado no log do SQL Server, no qual você pode consultar utilizando a sp_readerrorlog:

Como identificar os deadlocks utilizando DMV’s do System Health

O System Health é um monitoramento utilizando Extended Events (XE), tipo um Default Trace, que fica sendo executado em background e coletando informações da instância. Ele está disponível a partir do SQL Server 2008 e suas informações só podem ser acessadas utilizando query.

Apenas é importante ressaltar que o System Health não salva os registros de deadlock por muito tempo. Por este motivo, você pode criar uma rotina que faça isso por você ou utilize outro método para coletar seus logs de deadlock, como Extended Events.

Result:

Exemplo de XML:

Para criar um histórico de deadlocks manualmente, basta criar um Job que execute esse comando:

Como identificar os deadlocks utilizando Extended Events (XE)

O recurso de Extended Events é muito útil para diversas atividades no dia a dia do DBA, e identificação de deadlocks é uma excelente justificativa para começar a utilizá-los. Como a interface para configuração de nova sessão está disponível apenas a partir da versão 2012 do SSMS, vou demonstrar como criar a sessão utilizando a GUI e também por linha de comando.

Inicie uma nova sessão do XE pela interface do SSMS

Nomeie a sessão do XE que você está criando

Marque a opção de não utilizar template

Selecione o evento “xml_deadlock_report”

Selecione os campos globais que você deseja incluir na sessão (fique à vontade)

Aplique os filtros que você deseja (ou deixe em branco para não utilizar filtros)

Selecione onde você deseja salvar os logs, o tamanho máximo e outras configurações da sua sessão

Resumo das configurações da sessão

Após concluir a criação da sessão, lembre-se de ativá-la.

Script gerado:

Para consultar os dados dos eventos gerados pela sessão XE, basta executar a query abaixo:

Ou se você quiser já gravar na tabela os dados extraídos do XML, segue minha sugestão de consulta:

Result:

And that's it, folks!
Espero que tenham gostado do post e até a próxima.