O que são bloqueios no SQL Server?

Fala pessoal, tudo bem?
Vou iniciar com uma analogia e depois vamos aprofundando um pouco mais no tema principal do post que são bloqueios no SQL Server.

Seguindo então, bloqueios são esperas que acontecem quando várias pessoas tentam acessar os dados ao mesmo tempo. É como quando você quer mexer em um arquivo, mas outra pessoa já está mexendo nele, então você precisa esperar a pessoa terminar para poder mexer.

No SQL, quando diferentes transações estão acontecendo ao mesmo tempo e tentam acessar as mesmas informações, o banco de dados usa bloqueios para evitar problemas. Os bloqueios garantem que as transações sejam executadas corretamente e que os dados não fiquem bagunçados.

Existem diferentes tipos de bloqueios que podem ser usados, como bloqueios de linha, bloqueios de página ou bloqueios de tabela. Cada tipo de bloqueio age em diferentes partes do banco de dados, dependendo do que está sendo acessado.

Os bloqueios podem ser complicados porque, às vezes, uma transação precisa esperar outra transação terminar para conseguir acessar os dados que precisa. Isso pode causar atrasos e diminuir o desempenho do banco de dados.

Para evitar problemas com bloqueios no SQL Server, é importante ealizar uma boa modelagem do banco de dados, otimizar as consultas e configurar o isolamento adequado para as transações, levando em consideração os requisitos de consistência e desempenho do sistema. Além disso, é possível utilizar recursos como índices adequados, transações com duração mínima e otimização do código SQL para minimizar a ocorrência de bloqueios indesejados.

Segue um script que utiliza listar o processos bloqueantes e bloqueados, as consultas que os envolvem e mais algumas informações de cada processo.

;with rec as (
   Select     sp1.spid      , sp1.blocked      , db_name(sp1.dbid) as [Database]      , sp1.Open_tran as 'Transacoes Abertas'      , 
   sp1.Hostname as 'Estacao Trabalho'      , sp1.lastwaittype,  sp1.nt_username as 'Usuario Windows'      , sp1.loginame as 'Usuário SQL'      , 
   sp1.program_name as Aplicacao      , sp1.login_time as Hora_Login      , sp1.waittime as 'Tempo de Duração'      , 
   sp1.waittime / 1000 as 'segundos'      , sp1.status as 'Status'      , sp1.cmd as 'Comando SQL'   , 
   (select CAST([TEXT] AS VARCHAR(8000)) from ::fn_get_sql(sp1.sql_handle)) as query_completa  , 
   getdate() coleta 
   ,case when sp1.blocked = 0 then 'Bloqueante' else 'Bloqueado' end tipo
   From SYS.sysProcesses sp1  
   where  1=1 -- and sp1.STATUS in('suspended')   
    and sp1.blocked > 0
  -- and  sp1.cmd = 'AWAITING COMMAND'  -- and (select CAST([TEXT] AS VARCHAR(8000)) from ::fn_get_sql(sp1.sql_handle)) like '%update%'  
   union all
   
   Select    sp2.spid      , sp2.blocked      , db_name(sp2.dbid) as [Database]      , sp2.Open_tran as 'Transacoes Abertas'      , 
   sp2.Hostname as 'Estacao Trabalho'      , sp2.lastwaittype,  sp2.nt_username as 'Usuario Windows'      , sp2.loginame as 'Usuário SQL'      , 
   sp2.program_name as Aplicacao      , sp2.login_time as Hora_Login      , sp2.waittime as 'Tempo de Duração'      , 
   sp2.waittime / 1000 as 'segundos'      , sp2.status as 'Status'      , sp2.cmd as 'Comando SQL'   , 
   (select CAST([TEXT] AS VARCHAR(8000)) from ::fn_get_sql(sp2.sql_handle)) as query_completa  , 
   getdate() coleta 
,   case when sp2.blocked = 0 then 'Bloqueante' else 'Bloqueado' end tipo
   From SYS.sysProcesses sp2
   inner join rec on rec.blocked = sp2.spid
   where  1=1   

)
select * 
from rec
-- where blocked = 0
order by [Tempo de Duração] desc

O resultado é o seguinte:

Quando vc identifica que o ambiente está sofrendo com bloqueios temos algumas opções como:

1. Identificar o processo de bloqueio: Determine qual é o processo que está causando o bloqueio e identifique a transação ou consulta específica envolvida. Isso pode ser feito por meio dos scripts mencionados anteriormente ou por meio de ferramentas de monitoramento do SQL Server.
2. Analisar a causa do bloqueio: Compreender a causa do bloqueio é fundamental para tomar a melhor ação. Verifique se há consultas demoradas, transações em espera por recursos ou conflitos de concorrência. Analisar o plano de execução das consultas pode ajudar a identificar gargalos e otimizar o desempenho.
3. Avaliar a prioridade: Considere a importância das transações envolvidas no bloqueio. Se uma transação crítica estiver bloqueada, ela pode exigir uma ação mais imediata do que uma transação de baixa prioridade.
4. Encerrar a transação bloqueadora: Se você tiver identificado a transação que está causando o bloqueio e ela estiver em espera, pode ser necessário encerrá-la manualmente. No entanto, essa ação deve ser tomada com cautela, pois pode levar a perda de dados ou inconsistências. Consulte a equipe responsável ou siga as políticas de resolução de bloqueios estabelecidas na sua organização.
5. Otimizar consultas e transações: Analise as consultas envolvidas no bloqueio e verifique se é possível otimizá-las. Certifique-se de que as consultas estejam usando índices apropriados, evite bloqueios desnecessários e revise o projeto do banco de dados, se necessário. Otimizações adequadas podem ajudar a reduzir conflitos de bloqueio no futuro.
6. Ajustar o isolamento da transação: Considere ajustar o nível de isolamento da transação. Por exemplo, reduzir o nível de isolamento pode ajudar a minimizar bloqueios, mas isso também pode aumentar a possibilidade de leituras inconsistentes. Escolha o nível de isolamento com base nos requisitos de consistência e desempenho do seu sistema.
7. Monitorar e ajustar regularmente: O monitoramento contínuo do desempenho do SQL Server, o uso de ferramentas de monitoramento e a revisão regular das consultas e transações podem ajudar a identificar e resolver problemas de bloqueio antes que eles se tornem críticos.

Lembre-se de que cada situação de bloqueio pode ser única, e as ações a serem tomadas podem variar dependendo do contexto e da natureza do problema. Se você não tiver experiência em lidar com bloqueios no SQL Server, é recomendável envolver um administrador de banco de dados experiente ou buscar orientação junto à equipe de suporte.

É isso aí pessoal, qualquer dúvida é só me chamar.

Obrigado e abs.