Monitorando bloqueios com o uso de Recursive Common Table ExpressionsNos ultimos meses tenho presenciado diversas situacoesenvolvendo bloqueios. Isso nao somente no ambiente de producao do meu atualemprego, mas tambem em alguns clientes bem como eventuais duvidas em sala deaula, foruns e comunidades. A ultima vez foi bem recente e por conta de algunsbloqueios em conjunto com alguns BUGs de aplicacao me deparei com uma baseabsurdamente critica, uma quantidade enorme de bloqueios e algumas dezenas demilhoes de reais envolvidas.Ja perdi a conta de quantas vezes vi ou ouvi falar de uma cadeiaextensa de bloqueios em um ambiente critico com tudo "parado". Ossintomas sao bem classicos, ou seja, CPU baixa, memoria disponivel e bastantelentidao. Algumas consultas na sysprocesses, sys.dm_exec_requests, sys.dm_os_waiting_tasksou ainda as tradicionais sp_who2 e sp_lock sao suficientes para diagnosticarvarios bloqueios. O grande desafio e encontrar por onde comecar. Em algunsambientes, eliminar varios processos de uma vez ou reiniciar um servidor saoalgumas possibilidades, mas em ambientes de missao critica resolver o problemaao inves de contorna-lo pode nao ser uma opcao, mas sim o unico caminho. O quefazer para encontrar os verdadeiros culpados? Quais sao os processos que estao"travando" tudo? Qual a "bendita" transacao, usuario ouaplicacao que esta provocando os incidentes?Desde o lancamento da versao 2005, o SQL Servermelhorou consideravelmente as possibilidades de diagnostico. No momento em queele passou a disponibilizar as DMVs e "abrir" a "caixapreta" ficou muito facil ser assertivo nos problemas e nas solucoes. Outroexcelente recursos foi o uso das Common Table Expressions (CTEs) emsua modalidade recursiva (Recursive CTEs). A combinacao das CTEs recursivas coma natureza hierarquica das cadeias de bloqueios atraves das DMVs possibilitaser muito preciso em encontrar o "culpado" pela forte presenca debloqueios em um servidor SQL Server.Um exemplo praticoParapoder simular uma contensao no ambiente utilizarei uma pequena estrutura eprovocarei alguns bloqueios. O script abaixo cria cinco tabelas (T1 a T5) comapenas uma unica coluna (cod) e preenche essas tabelas com numeros de 1 a 5.Muda o contexto de banco de dadosUSE TempDBCria uma tabelaCREATETABLE T1 (COD TINYINT)Insere os dados em T1INSERTINTO T1 VALUES (1)INSERTINTO T1 VALUES (2)INSERTINTO T1 VALUES (3)INSERTINTO T1 VALUES (4)INSERTINTO T1 VALUES (5)Cria replicasSELECT COD INTO T2 FROM T1SELECT COD INTO T3 FROM T1SELECT COD INTO T4 FROM T1SELECT COD INTO T5 FROM T1Agoraque estao presentes 5 tabelas (ou melhor dizendo 5 recursos), e possivelprovocar uma cadeia de bloqueios entre processos que acessem esses recursos. Oprimeiro passo e provocar uma contensao por parte de uma conexao. A consultaabaixo (deve ser executada com o SQL Server Management Studio) bloquearaexclusivamente as tabelas recem criadas, pois, a transacao ficara em aberto.Muda o contexto de banco de dadosUSE TempDB;Muda o nivel de isolamento (o mais rigido)SETTRANSACTION ISOLATION LEVEL SERIALIZABLEBEGINTRAN Leos 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 Leos dados da quinta tabela de forma compartilhada, mas mantendo o bloqueio SELECT COD FROM T5 WITH (HOLDLOCK)Umcomando SELECT demonstra os