O que poucos sabem é que a partir do SP3, a Microsoft adicionou três novas colunas na sysprocesses e uma função de sistema chamada fn_get_sql que resolve todos estes problemas e permite saber a linha de código que esta sendo executada no momento do bloqueio.
Na dica desta semana estarei demonstrando como a função fn_get_sql pode lhe ajudar na hora de solucionar problemas de bloqueio de conexões.
Causando o bloqueio de conexões:
Para que eu possa demonstrar como a função fn_get_sql trabalha, precisamos primeiramente simular uma situação de bloqueio de conexões, para isto, abra uma conexão com o QA e crie a procedure abaixo no banco pubs.
CREATE PROCEDURE upd_author
AS
BEGIN TRANSACTION
update authors SET state=’SP’
where state = ‘UT’
waitfor delay ’00:01:00′
ROLLBACK TRANSACTION
Pegue o ID desta conexão pois precisaremos dele mais tarde:
SELECT @@SPID à 55 para este exemplo
Esta procedure faz uma atualização na tabela authors e aguarda por 1 minuto antes de efetuar o Rollback da transação. Com isto, qualquer conexão que consultar os registros afetados ficará bloqueada.
Abra uma segunda conexão no QA e escreva o stament abaixo:
SELECT * FROM authors where state = ‘UT’
Pegue o ID desta conexão pois precisaremos dele mais tarde:
SELECT @@SPID à 54 para este exemplo
Volte para a conexão 55 e execute a procedure upd_author, volte para a conexão 54 e execute o statement.
Monitorando as conexões:
Abra uma nova conexão no QA e execute a sp_who, podemos ver aqui que a conexão 54 esta sendo bloqueada pela conexão 55.
spid ecid status loginame hostname blk dbname cmd
—— —— ——– ——– ——– —- —— ——
54 0 sleeping sa WINXPPRO 55 pubs SELECT
55 0 sleeping sa WINXPPRO 0 pubs WAITFOR
56 0 runnable sa WINXPPRO 0 master SELECT
Neste ponto sempre nos perguntamos: O que será que a conexão 55 está executando para ter bloqueado a conexão 54? Para responder a esta pergunta normalmente utilizamos o DBCC INPUTBUFFER, o qual neste caso nos trará o seguinte resultado:
EventType Parameters EventInfo
————– ———- ————
Language Event 0 upd_author
Como podemos ver, o DBCC INPUTBUFFER nos mostra apenas a procedure sendo executada pela conexão 55. Não seria interessante se pudéssemos saber exatamente o código sendo executado dentro da procedure upd_author? É aí que entra a função de sistema fn_get_sql adicionada pelo SP3 do SQL Server.
Para ver como funciona esta função, crie no database master a procedure sp_usrinputbuffer (código completo) e na mesma conexão onde foi executado o DBCC INPUTBUFFER, execute o statement abaixo:
dbcc inputbuffer (55)
go
sp_usrinputbuffer 55
Obs: Pode ser preciso executar as conexões 55 e 54 novamente.
Temos como resultado:
EventType Parameters EventInfo
————– ———- ————
Language Event 0 upd_author
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
********STATEMENT SENDO EXECUTADO NO MOMENTO ************
waitfor delay ’00:01:00′
Aqui podemos ver que enquanto o DBCC INPUTBUFFER me retorna apenas o nome da procedure sendo executada, a sp_usrinputbuffer retorna o statement sendo executado pela procedure no exato momento do bloqueio.
Assim, podemos concluir que o bloqueio esta ocorrendo devido ao delay de 1 minuto da procedure upd_author. Se tirarmos este delay, o problema de bloqueio será resolvido.
Para saber mais sobre a função fn_get_sql, consulte o artigo Microsot KB 325607 – FIX: The fn_get_sql Function Returns SQL Text for Handle in the Sysprocesses System Table