Nos últimos meses tenho presenciado diversas situações
envolvendo bloqueios. Isso não somente no ambiente de produção do meu atual
emprego, mas também em alguns clientes bem como eventuais dúvidas em sala de
aula, fóruns e comunidades. A última vez foi bem recente e por conta de alguns
bloqueios em conjunto com alguns BUGs de aplicação me deparei com uma base
absurdamente crítica, uma quantidade enorme de bloqueios e algumas dezenas de
milhões de reais envolvidas.
Já perdi a conta de quantas vezes vi ou ouvi falar de uma cadeia
extensa de bloqueios em um ambiente crítico com tudo "parado". Os
sintomas são bem clássicos, ou seja, CPU baixa, memória disponível e bastante
lentidão. Algumas consultas na sysprocesses, sys.dm_exec_requests, sys.dm_os_waiting_tasks
ou ainda as tradicionais sp_who2 e sp_lock são suficientes para diagnosticar
vários bloqueios. O grande desafio é encontrar por onde começar. Em alguns
ambientes, eliminar vários processos de uma vez ou reiniciar um servidor são
algumas possibilidades, mas em ambientes de missão crítica resolver o problema
ao invés de contorná-lo pode não ser uma opção, mas sim o único caminho. O que
fazer para encontrar os verdadeiros culpados? Quais são os processos que estão
"travando" tudo? Qual a "bendita" transação, usuário ou
aplicação que está provocando os incidentes?
Desde o lançamento da versão 2005, o SQL Server
melhorou consideravelmente as possibilidades de diagnóstico. No momento em que
ele passou a disponibilizar as DMVs e "abrir" a "caixa
preta" ficou muito fácil ser assertivo nos problemas e nas soluções. Outro
excelente recursos foi o uso das Common Table Expressions (CTEs) em sua modalidade recursiva (Recursive CTEs).
A combinação das CTEs recursivas com a natureza hierárquica das cadeias de
bloqueios através das DMVs possibilita ser muito preciso em encontrar o
"culpado" pela forte presença de bloqueios em um servidor SQL Server.
Um exemplo prático
Para
poder simular uma contensão no ambiente utilizarei uma pequena estrutura e
provocarei alguns bloqueios. O script abaixo cria cinco tabelas (T1 a T5) com
apenas uma única coluna (cod) e preenche essas tabelas com números de 1 a 5.
– Muda
o contexto de banco de dados
USE TempDB
–
Cria uma tabela
CREATE
TABLE T1 (COD TINYINT)
–
Insere os dados em T1
INSERT
INTO T1 VALUES (1)
INSERT
INTO T1 VALUES (2)
INSERT
INTO T1 VALUES (3)
INSERT
INTO T1 VALUES (4)
INSERT
INTO T1 VALUES (5)
–
Cria réplicas
SELECT COD INTO T2 FROM T1
SELECT COD INTO T3 FROM T1
SELECT COD INTO T4 FROM T1
SELECT COD INTO T5 FROM T1
Agora
que estão presentes 5 tabelas (ou melhor dizendo 5 recursos), é possível
provocar uma cadeia de bloqueios entre processos que acessem esses recursos. O
primeiro passo é provocar uma contensão por parte de uma conexão. A consulta
abaixo (deve ser executada com o SQL Server Management Studio) bloqueará
exclusivamente as tabelas recém criadas, pois, a transação ficará em aberto.
–
Muda o contexto de banco de dados
USE TempDB;
–
Muda o nível de isolamento (o mais rígido)
SET
TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN
TRAN
– Lê
os dados da primeira tabela de forma exclusiva
SELECT COD FROM T1 WITH (XLOCK)
–
Atualiza os dados da segunda tabela
UPDATE T2 SET COD
= COD + 1
–
Exclui os dados da terceira tabela
DELETE FROM T3
–
Exclui os dados da quarta tabela com o uso do truncate
TRUNCATE TABLE T4
– Lê
os dados da quinta tabela de forma compartilhada, mas mantendo o bloqueio
SELECT COD FROM T5 WITH (HOLDLOCK)
Um
comando SELECT demonstra os vários bloqueios impostos por essa transação.
–
Muda o contexto do Banco
USE TempDB;
SELECT
db_name(resource_database_id) As Banco,
T.name As Tabela,
request_mode As Modelo,
request_session_id As Sessao
FROM
sys.dm_tran_locks As B
INNER JOIN sys.tables As T ON B.resource_associated_entity_id
= T.object_id
WHERE
resource_type = ‘OBJECT’ AND resource_associated_entity_id
> 10
ORDER
BY Tabela
O
resultado mostra alguns bloqueios sobre as tabelas da transação e mostra o SPID
da mesma (no meu caso é 52)
|
Banco
|
Tabela
|
Modelo
|
Sessão
|
|
tempdb
|
T1
|
X
|
52
|
|
tempdb
|
T2
|
X
|
52
|
|
tempdb
|
T3
|
X
|
52
|
|
tempdb
|
T4
|
Sch-M
|
52
|
|
tempdb
|
T5
|
S
|
52
|
Uma
vez que a sessão 52 (minha sessão no SSMS) esteja "segurando" os
recursos, os acessos a esses recursos tendem a ser bloqueados já que a maioria
dos bloqueios é exclusiva. O código abaixo em C# monta um pequeno aplicativo
com 25 conexões ao banco com o intuito de acessar os recursos que estão
bloqueados (T1 a T5).
using System;
using System.Collections.Generic;
using System.Threading;
using System.Data.Sql;
using System.Data.SqlClient;
namespace MB
{
class Program
{
static
void Main(string[]
args)
{
Program p
= new Program();
string[]
strCmds = new string[25];
strCmds[0] = "UPDATE T1 SET COD = COD + 2";
strCmds[1] = "SELECT COD FROM T2 WHERE COD <
3";
strCmds[2] = "SELECT COD FROM T2 WHERE COD >
2";
strCmds[3] = "DELETE FROM T5";
strCmds[4] = "TRUNCATE TABLE T5";
strCmds[5] = "SELECT COD FROM T4";
strCmds[6] = "UPDATE T2 SET COD = 10 WHERE COD
= 1";
strCmds[7] = "SELECT * FROM T1, T2, T3,
T5";
strCmds[8] = "DELETE FROM T5 WHERE COD IN
(SELECT COD FROM T3)";
strCmds[9] = "TRUNCATE TABLE T1";
strCmds[10]
= "SELECT * FROM T2, T4, T5";
strCmds[11]
= "DROP TABLE T1";
strCmds[12]
= "ALTER TABLE T2 ALTER COLUMN COD
CHAR(3)";
strCmds[13]
= "UPDATE T3 SET COD = COD +
20";
strCmds[14]
= "DELETE FROM T2";
strCmds[15]
= "SELECT COD FROM T1 WHERE COD >
2";
strCmds[16]
= "INSERT INTO T1 VALUES (6)";
strCmds[17]
= "INSERT INTO T5 VALUES (7)";
strCmds[18]
= "INSERT INTO T5 VALUES (8)";
strCmds[19]
= "DELETE FROM T1";
strCmds[20]
= "DELETE FROM T2";
strCmds[21]
= "SELECT COD FROM T1 WITH
(XLOCK)";
strCmds[22]
= "UPDATE T3 SET COD = 1 WHERE COD
IN (SELECT COD FROM T4)";
strCmds[23]
= "SELECT COD FROM T1, T4";
strCmds[24]
= "INSERT INTO T3 VALUES (10)";
Thread[]
trds = new Thread[25];
int i
= 0;
string vApp
= "";
for (i
= 0; i < 25; i++)
{
vApp = "App" +
i.ToString("00");
trds[i] = new Thread(delegate() {
p.Manipula(strCmds[i], vApp); });
trds[i].Start();
Thread.Sleep(500);
}
}
public
void Manipula(string strCmd, string strApp)
{
try
{
string strConn
=
"Server=localhost;Database=TempDb;Integrated
Security=SSPI;" +
"Connect
Timeout=7200;Application Name=" +
strApp + ";";
SqlConnection cn
= new SqlConnection(strConn);
SqlCommand cmd
= cn.CreateCommand();
cmd.CommandTimeout = 7200;
cmd.CommandText = strCmd;
cn.Open();
cmd.ExecuteNonQuery();
}
catch
{
Console.WriteLine("Um
processo foi eliminado");
}
}
}
}
Como
nem sempre o Visual Studio está disponível e é necessário compilar o código, se
o SQL Server estiver instalado, então necessariamente o .NET Framework também
estará presente juntamente com o compilador. Para compilar esse código, basta
seguir as instruções:
§
Abra o Notepad
§
Cole o código
§
Salve o arquivo em algum local com a
extensão .cs (Ex: H:\MB\MB.cs)
§
Abra um prompt de comando
§
Navegue até o diretório
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (ou superior se houver .NET
Framework mais recente)
§
Digite csc /out:H:\MB\MB.exe
H:\MB\MB.cs e pressione ENTER (pressupondo que o arquivo seja H:\MB\MB.cs)
O
código possui uma matriz com 25 comandos que é executado a cada meio segundo.
Como a transação do SQL Server Management Studio (ID 52) está bloqueando vários
dos recursos, a cada meio segundo um bloqueio irá surgir. É possível verificar
as conexões e os bloqueios utilizando a consulta abaixo:
–
Verifica as sessões e os bloqueios
SELECT
s.session_id As Sessao, program_name As Aplicacao,
R.status As StatusRequisicao,
blocking_session_id As SessaoBloqueadora
FROM sys.dm_exec_sessions As S
LEFT
OUTER JOIN sys.dm_exec_requests As R ON S.session_id
= R.session_id
WHERE S.session_id
> 50 and S.session_id != @@spid
A
consulta acima mostra todas as sessões e suas respectivas requisições (se
houver) conforme a tabela abaixo:
|
Sessão
|
Aplicação
|
Status da
Requisição
|
Sessão
Bloqueadora
|
|
51
|
Microsoft SQL Server Management
Studio
|
NULL
|
NULL
|
|
52
|
Microsoft SQL Server Management Studio
– Query
|
NULL
|
NULL
|
|
54
|
App00
|
suspended
|
52
|
|
55
|
App01
|
suspended
|
52
|
|
56
|
App02
|
suspended
|
55
|
|
57
|
App03
|
suspended
|
52
|
|
58
|
App04
|
suspended
|
57
|
|
59
|
App05
|
suspended
|
52
|
|
60
|
App06
|
suspended
|
55
|
|
61
|
App07
|
suspended
|
52
|
|
62
|
App08
|
suspended
|
57
|
|
63
|
App09
|
suspended
|
54
|
|
64
|
App10
|
suspended
|
59
|
|
65
|
App11
|
suspended
|
54
|
|
66
|
App12
|
suspended
|
55
|
|
67
|
App13
|
suspended
|
62
|
|
68
|
App14
|
suspended
|
55
|
|
69
|
App15
|
suspended
|
54
|
|
70
|
App16
|
suspended
|
54
|
|
71
|
App17
|
suspended
|
57
|
|
72
|
App18
|
suspended
|
57
|
|
73
|
App19
|
suspended
|
54
|
|
74
|
App20
|
suspended
|
55
|
|
75
|
App21
|
suspended
|
54
|
|
76
|
App22
|
suspended
|
59
|
|
77
|
App23
|
suspended
|
59
|
|
78
|
App24
|
suspended
|
62
|
Para
que as sessões sejam mantidas exatamente com os mesmos IDs é importante que o
SQL Server Management Studio possua apenas três conexões (uma para o object
explorer, uma para a transação e uma para as consultas. As conexões devem ser
abertas exatamente nessa ordem e não deve haver ninguém conectado).
Como
pode ser observado, algumas sessões não possuem bloqueios enquanto outras
encontram-se bloqueadas. A coluna "Sessão Bloqueadora" mostra o ID da
sessão que está bloqueando uma outra sessão. Como pode ser observado, algumas
sessões como a 52 e a 54 são responsáveis por bloquear várias requisições que
por sua vez acabam bloqueandos outras e assim é montada uma cadeia de
bloqueios. É possível por exemplo visualizar que a sessão 52 bloqueia a sessão
54 que bloqueia a sessão 73 por exemplo. Isso mostra uma cadeia em três níveis
embora haja mais níveis na cadeia de bloqueios.
Em
um cenário desses, os iniciantes optariam por parar o serviço ou ainda eliminar
aleatoriamente algumas sessões até que um nível de estabilidade fosse obtido.
Tais medidas podem não ser muito efetivas para resolver o problema. Parar o
serviço por exemplo provocará uma indisponibilidade nas aplicações que dependem
do SQL Server. Eliminar sessões de forma aleatória provocará a reversão de
transações que podem significar graves problemas futuros (imagine um usuário
que mandou efetivar uma aplicação financeira ter a sua requisição cancelada).
Ainda que funcionem, evitar descobrir a causa real dos bloqueios pode fazer com
eles voltem mesmo após reiniciar um serviço por exemplo. Tais soluções são
ainda pouco factíveis em ambientes de missão crítica.
A
medida mais eficiente é identificar a sessão que está no topo da cadeia, ou
seja, qual a requisição que está provocando o bloqueio de outras sessões e
assim sucessivamente. Uma primeira tentativa, é identificar o maior bloqueador,
pois, tratar o maior bloqueador significa pode significar liberar uma grande
quantidade de sessões. A consulta abaixo mostra a lista do maior bloqueador.
SELECT blocking_session_id As Sessao, COUNT(session_id) As TotalBloqueios
FROM sys.dm_exec_requests
WHERE blocking_session_id
> 0
GROUP
BY blocking_session_id
ORDER
BY TotalBloqueios DESC
A
lista abaixo mostra as quantidades de bloqueio:
|
Sessão
|
Total de
Bloqueios
|
|
54
|
6
|
|
55
|
5
|
|
52
|
5
|
|
57
|
4
|
|
59
|
3
|
|
62
|
2
|
A
ideia de eliminar o maior bloqueador é interessante, mas será que ela é
"efetiva" ? Façamos um teste com os comandos abaixo:
KILL 54
Anteriormente
haviam 25 bloqueios e a sessão 54 é responsável por 6 desses 25 bloqueios (mais
de 20%). Considerando que existem 28 sessões (minhas 3 e 25 da aplicação) matar
uma única sessão e liberar 20% dos bloqueios parece ser uma idéia bem
interessante. Após eliminar esse processo e efetuar novamente a consulta dos
maiores bloqueadores, é obtido o seguinte resultado:
|
Sessão
|
Total de
Bloqueios
|
|
55
|
5
|
|
63
|
5
|
|
52
|
4
|
|
57
|
4
|
|
59
|
3
|
|
62
|
2
|
|
77
|
1
|
Ao
invés de restarem apenas 19 bloqueios (6 a menos em relação aos 25), restaram
24 bloqueios. Isso significa que eliminar a sessão 54 e seus seis bloqueios
reduziu a quantidade de bloqueios de 25 para 24 significando muito pouco em
relação ao total de bloqueios. Isso ocorre porque embora a sessões 54 tenha
sido eliminada, as sessões que estavam aguardando por ela agora estão
bloqueadas por outras sessões. Seguindo ainda a mesma linha, os maiores
bloqueadores são as sessões 55 e 63 empatando com 5 bloqueios cada totalizando
10 bloqueios. O comando abaixo elimina essas sessões:
KILL 55
KILL 63
Após
eliminar as sessões 55 e 63, a relação dos maiores bloqueadores é exibida na
tabela abaixo:
|
Sessão
|
Total de
Bloqueios
|
|
52
|
4
|
|
56
|
4
|
|
57
|
4
|
|
65
|
4
|
|
59
|
3
|
|
62
|
2
|
|
77
|
1
|
No
início da análise havia 28 sessões e 25 bloqueios. Eliminou-se as sessões que
mantinham mais bloqueios e mesmo assim o número de bloqueios diminuiu
lentamente. Eliminou-se 3 sessões e o número de bloqueios caiu de 25 para 22.
Se o raciocínio for mantido poderá ser necessário eliminar muitos outros
bloqueios para que um resultado visível possa ser obtido. Cada sessão eliminada
é uma "esperança" de que as coisas voltem a funcionar, mas pode
significar uma transação incompleta e boas "dores de cabeça".
A
idéia de pesquisar os maiores bloqueadores é interessante, mas pode levar a
interpretações equivocadas. O interessante não é procurar os maiores
bloqueadores, pois, o resultado são quantos bloqueios "diretos" cada
sessão provoca. A sessões 54 provocava 6 bloqueios, mas se considerarmos que a
sessão 52 bloqueava a 54 seria visível que eliminar a sessão 52 é bem mais
efetivo já que mais bloqueios (quer sejam diretos ou indiretos) seriam
desfeitos. Se essa sessão bloqueasse somente a sessão 54 por exemplo, teríamos
5 bloqueios diretos (os que a sessão 52 bloqueia diretamente) e mais 6
bloqueios indiretos (os bloqueios impostos pela sessão 54) totalizando 9
bloqueios.
Esse
é a grande dificuldade quando se depara com cadeias de bloqueios. Como
identificar exatamente quais as sessões que são responsáveis pelo maior número
de bloqueios (diretos ou indiretos), ou seja, quais sessões que estão realmente
"travando" o banco ? Visualmente pode ser até fácil, mas em uma longa
cadeia de bloqueios, esse processo pode ser muito trabalhoso se feito de forma
visual. Um detalhe que pode ser muito útil é perceber que a cadeia de
bloqueios, ou seja, a relação entre as sessões e suas bloqueadoras é
hierárquica. Isso é uma excelente oportunidade para utilizar o recurso de CTEs
recursivas. Antes de propriamente prosseguir, é interessante que o aplicativo
MB seja fechado e reaberto. Isso fará com que os processos 54, 55 e 63 que
foram eliminados sejam considerados novamente.
O uso das Common Table Expressions Recursivas
(Recursive CTEs)
Uma
vez que exista uma relação hierárquica entre as sessões, é possível utilizar
uma CTE recursiva para explorar essas relações. Aconsulta abaixo demonstra a
quantidade real de bloqueios de cada sessão, ou seja, tanto os bloqueios que
uma sessão pode provocar diretamente em outras, quanto bloqueios provocados
indiretamente, ou seja, se A bloqueia B e B bloqueia C, indiretamente A
bloqueia C.
;WITH Sessoes
(Sessao, Bloqueadora) As (
SELECT Session_Id,
Blocking_Session_Id
FROM sys.dm_exec_requests As R
WHERE blocking_session_id
> 0
UNION ALL
SELECT Session_Id, CAST(0 As
SMALLINT)
FROM sys.dm_exec_sessions As S
WHERE EXISTS
(
SELECT * FROM sys.dm_exec_requests As R
WHERE S.Session_Id
= R.Blocking_Session_Id)
AND NOT EXISTS (
SELECT * FROM sys.dm_exec_requests As R
WHERE S.Session_Id
= R.Session_Id)
),
Bloqueios As (
SELECT
Sessao, Bloqueadora, Sessao As Ref,
1 As Nivel
FROM Sessoes
UNION ALL
SELECT S.Sessao,
B.Sessao, B.Ref, Nivel + 1
FROM Bloqueios As B
INNER JOIN Sessoes As S ON B.Sessao
= S.Bloqueadora)
SELECT Ref As Sessao,
COUNT(DISTINCT R.Session_Id) As BloqueiosDiretos,
COUNT(DISTINCT B.Sessao)
– 1 As BloqueiosTotal,
COUNT(DISTINCT B.Sessao)
– COUNT(DISTINCT R.Session_Id)
– 1 AsBloqueiosIndiretos
FROM Bloqueios As B
INNER JOIN sys.dm_exec_requests As R
ON B.Ref
= R.blocking_session_id
GROUP
BY Ref
O
resultado pode ser observado logo abaixo:
|
Sessão
|
Bloqueios Diretos
|
Bloqueios Total
|
Bloqueios
Indiretos
|
|
52
|
5
|
25
|
20
|
|
54
|
6
|
6
|
0
|
|
55
|
5
|
5
|
0
|
|
57
|
4
|
4
|
0
|
|
59
|
3
|
3
|
0
|
|
62
|
2
|
2
|
0
|
Eliminar
a sessão 54 significa liberar 6 bloqueios diretos. A sessão 52 bloqueia apenas
5 sessões diretamente, mas indiretamente bloqueia outras 20 já que as 5 sessões
bloqueadas diretamente bloqueam outras sessões. A eliminação da sessão 52
libera outras 25 sessões e visivelmente é a medida mais efetiva para ajudar que
os bloqueios sejam resolvidos.
Uma
pequena mudança no código é capaz de mostrar os comandos executados por cada
uma das sessões que impõe bloqueios sobre as outras.
;WITH Sessoes
(Sessao, Bloqueadora) As (
SELECT Session_Id,
Blocking_Session_Id
FROM sys.dm_exec_requests As R
WHERE blocking_session_id
> 0
UNION ALL
SELECT Session_Id, CAST(0 As
SMALLINT)
FROM sys.dm_exec_sessions As S
WHERE EXISTS
(
SELECT * FROM sys.dm_exec_requests As R
WHERE S.Session_Id
= R.Blocking_Session_Id)
AND NOT EXISTS (
SELECT * FROM sys.dm_exec_requests As R
WHERE S.Session_Id
= R.Session_Id)
),
Bloqueios As (
SELECT
Sessao, Bloqueadora, Sessao As Ref,
1 As Nivel
FROM Sessoes
UNION ALL
SELECT S.Sessao,
B.Sessao, B.Ref, Nivel + 1
FROM Bloqueios As B
INNER JOIN Sessoes As S ON B.Sessao
= S.Bloqueadora)
SELECT Ref As Sessao,
COUNT(DISTINCT R.Session_Id) As BloqueiosDiretos,
COUNT(DISTINCT B.Sessao)
– 1 As BloqueiosTotal,
COUNT(DISTINCT B.Sessao)
– COUNT(DISTINCT R.Session_Id)
– 1 AsBloqueiosIndiretos,
(SELECT
TEXT FROM sys.dm_exec_sql_text(
(SELECT most_recent_sql_handle FROM sys.dm_exec_connections
WHERE session_id
= B.Ref))) As Comando
FROM Bloqueios As B
INNER JOIN sys.dm_exec_requests As R
ON B.Ref
= R.blocking_session_id
GROUP
BY Ref
O
resultado pode ser observado logo abaixo:
|
Sessão
|
Bloqueios Diretos
|
Bloqueios Total
|
Bloqueios
Indiretos
|
Comando
|
|
52
|
5
|
25
|
20
|
– Muda o contexto de banco de
dados USE TempDB; — M
|
|
54
|
6
|
6
|
0
|
(@1 int)UPDATE [T1] set [COD] =
[COD]+@1
|
|
55
|
5
|
5
|
0
|
(@1 tinyint)SELECT [COD] FROM [T2]
WHERE [COD]<@1
|
|
57
|
4
|
4
|
0
|
DELETE FROM T5
|
|
59
|
3
|
3
|
0
|
SELECT COD FROM T4
|
|
62
|
2
|
2
|
0
|
DELETE FROM T5 WHERE COD IN (SELECT
COD FROM T3)
|
Como
era de se esperar, a sessão 52 (a aberta pelo SSMS) é a sessão que está no topo
da cadeia de bloqueios. O interessante dessa última consulta é que não somente
foi possível visualizar a quantidade de bloqueios aberta, mas principalmente o
comando que proporcionou que a cadeia se formasse.
O
uso das Recursive CTEs não se limita apenas a descobrir qual é a sessão que
impõe mais bloqueios diretos e indiretos. Com o uso da CTE é possível exibir as
cadeias de bloqueios e visualizar o que acontecerá em um primeiro momento
quando a sessão 52 for eliminada. A consulta abaixo mostra as cadeias de
bloqueio provocadas pela sessão 52 de forma indireta (exatamente 20 registros conforme
a tabela acima).
;WITH Sessoes
(Sessao, Bloqueadora) As (
SELECT Session_Id,
Blocking_Session_Id
FROM sys.dm_exec_requests As R
WHERE blocking_session_id
> 0
UNION ALL
SELECT Session_Id, CAST(0 As
SMALLINT)
FROM sys.dm_exec_sessions As S
WHERE EXISTS
(
SELECT * FROM sys.dm_exec_requests As R
WHERE S.Session_Id
= R.Blocking_Session_Id)
AND NOT EXISTS (
SELECT * FROM sys.dm_exec_requests As R
WHERE S.Session_Id
= R.Session_Id)
),
Bloqueios As (
SELECT
CAST(Sessao As VARCHAR(200)) As Cadeia,
Sessao, Bloqueadora, 1 As Nivel
FROM Sessoes
UNION ALL
SELECT CAST(B.Cadeia
+ ‘ -> ‘ + CAST(S.Sessao As
VARCHAR(5)) As
VARCHAR(200)),
S.Sessao, B.Sessao, Nivel + 1
FROM Bloqueios As B
INNER JOIN Sessoes As S ON B.Sessao
= S.Bloqueadora)
SELECT Cadeia FROM Bloqueios
WHERE Nivel
= (SELECT MAX(Nivel) FROM Bloqueios)
ORDER
BY Cadeia
O
resultado é obtido na tabela abaixo (a formatação foi proposital):
|
Linha
|
Cadeia
|
Linha
|
Cadeia
|
|
01
|
52 -> 54 -> 63
|
11
|
52 -> 55 -> 74
|
|
02
|
52 -> 54 -> 65
|
12
|
52 -> 57 -> 58
|
|
03
|
52 -> 54 -> 69
|
13
|
52 -> 57 -> 61
|
|
04
|
52 -> 54 -> 70
|
14
|
52 -> 57 -> 71
|
|
05
|
52 -> 54 -> 73
|
15
|
52 -> 57 -> 72
|
|
06
|
52 -> 54 -> 75
|
16
|
52 -> 59 -> 64
|
|
07
|
52 -> 55 -> 56
|
17
|
52 -> 59 -> 76
|
|
08
|
52 -> 55 -> 60
|
18
|
52 -> 59 -> 77
|
|
09
|
52 -> 55 -> 66
|
19
|
52 -> 62 -> 67
|
|
10
|
52 -> 55 -> 68
|
20
|
52 -> 62 -> 78
|
A
exemplo da linha 01 pode-se ver claramente que a sessão 52 bloqueia a sessão 54
que por sua vez bloqueia a sessão 63. A tabela exibe os 20 bloqueios indiretos.
Eliminar por exemplo a sessão 52, irá liberar a sessão 54 que irá liberar a
sessão 63. A consulta mostra todas as liberações que irão ocorrer se a sessão
52 for eliminada. Não é necessário eliminá-la ainda.
As
consultas anteriores juntamente com essa última mostram o poder que as CTEs
podem realizar no monitoramento e resolução dos bloqueios já que esses são
apresentados em uma relação hierárquica. Ainda assim, se o objetivo era apenas
encontrar a sessão que mais promove bloqueios, há uma maneira bem mais direta.
O raciocínio é bem simples. A sessão que forma a hierarquia será a primeira a
efetuar um bloqueio. A sessão que é bloqueada primeiro irá naturalmente ser a
que estará esperando por mais tempo, pois, à medida que ela é bloqueada é que
os demais bloqueios se formam. A consulta abaixo demonstra esse raciocínio
SELECT
TOP 10 Session_Id,
Blocking_Session_Id
FROM sys.dm_exec_requests
WHERE Blocking_Session_Id
> 0
ORDER
BY Wait_Time DESC
A
consulta exibe o seguinte resultado:
|
Session_Id
|
Blocking_Session_Id
|
|
54
|
52
|
|
55
|
52
|
|
56
|
55
|
|
57
|
52
|
|
58
|
57
|
|
59
|
52
|
|
60
|
55
|
|
61
|
57
|
|
62
|
52
|
|
63
|
54
|
A
consulta retorna todas as requisições que não estão bloqueadas
(blocking_session_id > 0) e as ordena pelo maior tempo de espera. A sessão
54 está bloqueada pela sessão 52 e é a sessão que espera a mais tempo. Isso é
um forte indício de que a sessão 52 possui forte influência na cadeia de
bloqueios. A resposta não é tão precisa quanto o uso das CTEs, mas normalmente
essa alternativa é muito certeira por conta da lógica.
A
idéia desse artigo é demonstrar como abordar longas cadeias de bloqueios em
ambientes de produção. Normalmente quando o bloqueio é formado não há um aviso
formal ao responsável pelo banco de dados. Isso é natural, pois, os bloqueios
são necessários para garantir a integridade dos dados (principalmente na ótica
pessimista presença na maioria dos bancos de dados). A demora no processo de
resolução desses bloqueios (seja por conexões órfãs, transações longas, etc)
pode formar um situação bem crítica. Um bloqueio de 10ms talvez passe
despercebido, mas uma cadeia de bloqueios formada por várias sessões e imposta
há alguns minutos provavelmente será descoberta e o DBA não terá tempo para
tentar analisar qual a sessão que provoca tantos bloqueios. Abordei aqui as
principais técnicas que costumo utilizar para encontrá-los.
Os
códigos abordam um cenário muito simplista. Nem sempre há uma única cadeia de
bloqueios. É possível que haja vários processos montado extensas cadeias. É
possível também que as cadeias possuam mais de três níveis de aninhamento e
assim por diante. Os códigos mostram o caminho das pedras, mas certamente podem
ser otimizados. Espero que esse artigo sirva de subsídio para entender e lidar
com os bloqueios quando eles incomodarem. Por fim, matar a sessão 52, irá fazer
com que todas as sessões sejam liberadas e o SQL Server se resolva. Aos
curiosos, vale a pena efetuar um KILL e conferir o resultado das consultas repassadas
até então.
