Gerenciando Bloqueios no SQL Server

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 porquê dos travamentos, promovendo mudanças no modelo 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ça difícil de 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”.

Os métodos convencionais utilizados no rastreamento de bloqueios nem sempre são eficientes:

• A visualização de processos e/ou bloqueios ativos pelo Enterprise Manager não possui boa performance durante picos de utilização do servidor;
• Quando existem muitas conexões ativas, identificar a conexão responsável pela avalanche de travamentos pode consumir um tempo precioso: as procedures SP_WHO, SP_WHO2 e SP_LOCK e Enterprise Manager não possuem um mecanismo eficiente para solucionar esse problema. Sabemos que a conexão 52 está travada pela 65, que a 32 está bloqueada pela 433, ....., mas não somos informados “de maneira explícita” que o spid 34 é quem está ocasionando todos esses bloqueios.

Analisando tabela de sistema SYSPROCESSES

Quando os modelos prontos não atendem nossas necessidades, temos que partir para soluções específicas, feitas sob encomenda para nossas necessidades – é aí que entra a tabela de sistema SYSPROCESSES.

A tabela de sistema SYSPROCESSES localizada no database MASTER é responsável por armazenar o metadata de todos os processos ativos no servidor SQL Server 2000 (ver Figura 4)

Figura 4: Select na tabela de sistema SYSPROCESSES

Bem, e daí? Como a tabela SYSPROCESSES poderia ajudar a resolver a questão do travamento?

A coluna BLOCKED na tabela SYSPROCESSES (ver Figura 4) pode ser utilizada para rastrear o spid responsável pelos bloqueios da seguinte forma:

• Sempre que existir um spid registrado nessa coluna, ESSE spid estará bloqueando pelo menos uma conexão. Na Figura 1, o spid 54 é o responsável pelo bloqueio da conexão 51;
• Podemos tirar proveito dessa informação se analisarmos a situação de bloqueio para cada spid listado na coluna BLOCKED. Em outras palavras, verificaremos se os spids listados TAMBÉM estão em estado de espera (bloqueados) ou se estão com bloqueios ativos (bloqueadores):

Como verificar os spids que estão bloqueados:

Listagem 1. Verificando os spids bloqueados através de consulta na tabela SYSPROCESSES

select spid, blocked, hostname=left(hostname,20), program_name=left(program_name,20),
       WaitTime_Seg = convert(int,(waittime/1000))  ,open_tran, status
From master.dbo.sysprocesses 
where blocked > 0
order by spid

Figura 5: Resultado da Listagem 1

Como verificar os spids responsáveis pelos bloqueios (=bloqueadores):

Figura 6: Como verificar os spids responsáveis pelos bloqueios

Note as diferenças de filtro nas duas listagens:

• Para saber quem está em estado de espera (o popular “travado”) a Listagem 1 emprega o filtro ... blocked > 0 ;
• Para determinar o spid bloqueador, precisaremos saber as conexões que não estão em estado de espera, mas possuem bloqueios ativos responsáveis por travamentos noutras sessões. Para identificar as conexões responsáveis por bloqueios utiliza-se a subquery ... select blocked from master.dbo.sysprocesses where blocked > 0. Para determinar, dentre essas conexões, quais são aquelas que “bloqueiam sem ser bloqueadas” utiliza-se o filtro blocked=0.

Conclusão

A partir do select presente na Figura 6, pode-se desenvolver uma procedure que liste as conexões responsáveis por travamentos, levando-se em conta o parâmetro de tempo de espera (coluna WAITTIME). Essa procedure pode ser utilizada na geração de um job, que rode periodicamente analisando bloqueios e informando o DBA se existirem bloqueios persistentes – por exemplo, com duração superior a 3 segundos.

Bem, é isso. Até a próxima!