Monitorando bloqueios com o uso de Recursive Common Table Expressions

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)

Não vou entrar nos detalhes das CTEs recursivas uma vez que há vários assuntos sobre elas na Internet além do que as mesmas são muito bem abordadas pelo colaborador Thiago Zavaschi em Entendendo as Common Table Expressions – CTE – Parte 1. Eu já abordei um exemplo de CTE recursiva emDicas e Truques sobre consultas complexas no SQL Server, mas as apresentações do Zavaschi sobre CTEs (tanto em seus artigos quanto no SQL Server Day) são certamente superiores.

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.