Na SQL Magazine 20 tivemos a primeira parte desta matéria. Vimos diversos exemplos sobre transações, bloqueios, conflitos de concorrência e situações que requerem a intervenção do DBA. Nesta continuação, prosseguiremos com assuntos correlatos: identificação de ocorrências de deadlocks; utilização das ferramentas Performance Monitor e Profiler; criação de Alertas, Lock Hints e uma introdução ao Lock Manager, componente do SQL Server responsável pela gerência de bloqueios. Fechamos o artigo com algumas regras para consolidar os conhecimentos apresentados.
Deadlocks
Como já citado, deadlocks representam um problema mais grave do que as esperas causadas por bloqueios. Além das desagradáveis perdas de tempo, existe ainda perda de trabalho, já que, necessariamente, apenas uma das transações envolvidas no deadlock será confirmada. Nativamente, a única forma de identificar a ocorrência de deadlocks consiste em analisar as mensagens causadas pelo erro 1205:
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 52) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.
A análise isolada do erro tem pouca valia já que identificamos o problema de forma reativa, ou seja: percebemos o problema depois que ele aconteceu, sabemos inclusive qual sessão teve sua transação desfeita (no exemplo acima, a 52), mas não a que teve seus trabalhos confirmados. O mais importante, isto é, quais comandos que ocasionaram o deadlock – não é detalhado no corpo da mensagem. Felizmente, o SQL Server oferece uma ampla gama de recursos que possibilitam tanto conhecer o conflito em detalhes, quanto deduzir suas causas. Antes, porém, gostaria de relatar uma experiência marcante vivenciada em 2004.
Na ocasião estava prestando consultoria em uma importante administradora imobiliária quando, inesperadamente, começaram a surgir diversas reclamações de usuários quanto à lentidão geral do sistema. Após algumas análises preliminares, cheguei à conclusão de que o problema era causado pela incidência excessiva de deadlocks.
Estabelecidos os mecanismos necessários de identificação de deadlocks, rapidamente pude constatar que as inúmeras ocorrências sempre envolviam uma tabela minúscula. Analisando os comandos envolvidos, ficou claro que duas sessões tentavam alterar linhas da tabela, uma via comando INSERT (a tabela também possuía uma trigger de INSERT) e outra por intermédio de um comando UPDATE mediante uma condição. Prosseguindo com minha investigação, também descobri um fato curioso: a tal tabela não tinha índices! Ora, por não ter índices, cada comando que precisasse de uma linha bloquearia a tabela inteira! Bastou criar um índice para eliminar por completo a incidência de deadlocks sobre a tabela!
Obtendo informações mais completas sobre deadlocks
Para que se tenha acesso a informações detalhadas sobre deadlocks, é necessário ligar o trace flag 1204. Trace flags representam sinalizações indicando que ocorrências de um determinado problema devem ser detalhadas nos arquivos de log ou que determinados comportamentos devem ser ativados. Há inúmeros trace flags, mas, em geral, devem ser utilizados por DBAs experientes, já que há pouca documentação e os riscos de má utilização são grandes. Apenas como exemplo, após ligar o trace flag 3604 têm-se acesso a vários comandos DBCC não documentados tal como DBCC PAGE que permite visualizar conteúdos de páginas.
Liga-se o trace flag 1204 especificando –T1204 ao iniciar o serviço MSSQLServer. Pelo Enterprise Manager, clica-se com o botão direito do mouse sobre o nome do servidor, ativa-se Properties e, com a caixa de diálogo aberta, clica-se em Startup Parameters. O resultado da adição é mostrado na Figura 1.
Vale lembrar que o trace flag 1204 só irá vigorar após a próxima inicialização do serviço, quando definido como parâmetro de inicialização. Uma alternativa seria ligá-lo via sessão DOS, a partir do diretório onde residem os executáveis (geralmente, C:\Program Files\Microsoft SQL Server\MSSQL\Bin):
sqlservr -c -T1204Uma forma alternativa de ligar o trace flag acima consiste em executar o comando: DBCC TRACEON(1204). O inconveniente desta opção seria a necessidade de executá-la cada vez que o servidor fosse iniciado.
Para saber se o trace flag está ligado, deve-se utilizar, em uma sessão do Query Analyzer, o comando mostrado na Figura 2.
Uma vez ativado, o trace flag 1204 nos fornecerá farta documentação sobre os próximos deadlocks no arquivo de log. Para que possamos visualizar esse detalhamento, vamos simular a ocorrência de um deadlock executando alternadamente os comandos apresentados na Tabela 1. O script para geração do database e tabelas utilizadas nessa matéria – monta_ambiente.sql - encontra-se disponível para download no site da SQL Magazine.
...
