Gerenciando Bloqueios–Parte I

O controle de bloqueios é uma atividade desenvolvida pelo Lock Manager, um dos componentes do SQL Server 2000 Relational Engine. Bloqueios são fundamentais para garantir o isolamento necessário às transações, mas cabe ao DBA ou responsável pelo banco estabelecer um método eficiente para identificar as conexões e, numa análise mais detalhada, os processos responsáveis por bloqueios de longa duração.

A verificação de bloqueios é uma ação passiva, isto é, o travamento só pode ser detectado DEPOIS  que acontece. Já que não podemos eliminar a questão do travamento, deveremos  trabalhar em duas frentes:

1.      A curto prazo: detectar e eliminar travamentos, de preferência ANTES das reclamações de  usuários

2.      A médio prazo: analisar o porque dos travamentos, promovendo mudanças no modêlo de dados e no código das transações

Essa matéria será dividida em duas partes: na primeira serão apresentados alguns métodos “prontos” para identificação de bloqueios. Na segunda parte será proposto um modelo simples para identificação do processo bloqueador, assim como o comando responsável pelo travamento. O assunto será finalizado com dicas para minimizar travamentos.

Métodos para detecção de travamentos

·        O Enterprise Manager

O método mais simples para identificar a conexão responsável por um travamento nos leva ao Enterprise Manager em Manangement\Currenct Activity. Na Figura 1, pode-se observar que o spid 52 está sendo bloqueado pelo 54.

Figura 1. Utilizando o Enterprise Manager na identificação de bloqueios

A opção do Enterprise Manager – apesar da magnífica apresentação -  mostra-se pouco eficiente: a carga de todas as informações necessárias para atualização da console e por vezes é muito lenta, transformando esse processo numa espera sem fim. As alternativas para contornar esse problema nos levam para comandos T-SQL, executados diretamente no Query Analyzer.

·        A procedure de sistema sp_Lock

A procedure sp_Lock apresenta uma listagem com a posição detalhada dos bloqueios vigentes. Numa busca rápida na coluna status, os processos marcados com WAIT informam que aquela conexão está aguardando pela liberação de um recurso que se encontra bloqueado. Já as colunas marcadas com GRANT informam as conexões bloqueadoras, isto é, aquelas responsáveis pela espera noutras conexões (ver Figura 2).

Figura 2. Utilizando a procedure SP_LOCK na identificação de bloqueios

Através da listagem de processos fornecida pela procedure SP_LOCK, concluímos que:

A conexão 52 está bloqueada (status = WAIT). O recurso que está sendo bloqueado é a tabela Orders, localizada no database NorthWind . O nome do database e tabela foram obtidos através das informações das colunas dbId e ObjId, utilizando-se as funções db_name() e object_name() ;

Para descobrir a conexão responsável pelo bloqueio é necessário procurar na listagem por uma linha com status GRANT para o mesmo recurso bloqueado. Numa rápida verificação, percebe-se que a conexão que responde pelo spid 54 está com um bloqueio ativo no mesmo recurso , portanto a conexão 54 é responsável pelo bloqueio na conexão 52 ;

O problema dessa listagem é que num ambiente de produção podem acontecer dezenas de travamentos oriundos de diversos spids. Nessa situação fica difícil identificar qual spid é o responsável pelos travamentos.

·        A procedure de sistema sp_Who2

A procedure sp_Who2 é a versão não documentada da procedure sp_Who. Ambas são utilizadas na

listagem de processos ativos no servidor, com pequenas diferenças. Um exemplo de execução de sp_Who2  pode ser encontrado na Figura 3.

Figura 3. Utilizando a procedure SP_WHO na identificação de bloqueios.

Para identificar as conexões bloqueadoras, basta procurar pelos spid’s que aparecem listados na coluna BlkBy: na listagem da Figura 3, o spid 54 está bloqueando o spid 52. Apesar do exemplo simplificado – existiam apenas duas conexões ativas – localizar a conexão que desencadeou um grande volume de bloqueios (o spid 10 bloqueou o spid 15 que bloqueou o spid 99...) pode se transformar num quebra-cabeças difícil de se montar.

Os spid´s das conexões bloqueadas são listados na coluna BlkBy; na Figura 3 a conexão representada pelo spid 52 está sendo bloqueada pelo spid 54. Apesar do output “enxuto” (existe somente uma linha por spid!), um travamento de grandes proporções num ambiente de produção  pode consumir um precioso tempo de análise na identificação do spid “bloqueador”.

A primeira parte da matéria fica por aqui; nos vemos na Parte II.

 Até a próxima!

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.