Estágios de bloqueio de consulta de atualização do servidor SQL?

Existem dois estágios para o bloqueio de dados atualizados. O primeiro é um bloqueio de atualização (U) e o segundo, desde que haja dados que precisam ser modificados, é um bloqueio exclusivo (X). É uma operação de dois estágios no sentido de que há dados que precisam ser pesquisados ​​para determinar quais/se os dados precisam ser modificados. O bloqueio de atualização existirá para isso (ou tentará ser obtido durante a operação). Então, quando os dados precisarem ser modificados, o bloqueio de atualização será convertido em um bloqueio exclusivo.

A razão por trás desse mecanismo de bloqueio é evitar o bloqueio do cenário de atualização de dados. Por favor, veja a postagem de Kalen Delaney sobre mais detalhes sobre isso.

Veja abaixo como exemplo:

Execute isso para a conexão 1...

-- connection 1, leaves an open tran to prevent connection 2 from progressing

use AdventureWorks2012;
go


begin tran

    update HumanResources.Department
    set name = 'This is a new name'
    where DepartmentID = 11;

--commit tran

Em seguida, em outra sessão, tente a seguinte atualização:

use AdventureWorks2012;
go

update HumanResources.Department
set name = 'This is a conflicting tran'
where DepartmentID = 11;

O que temos aqui é um bloqueio de atualização que está esperando porque já existe um bloqueio exclusivo no recurso que o bloqueio de atualização está tentando obter (um bloqueio de atualização não é compatível com um bloqueio exclusivo). O cenário acima é usado para "interromper" a conversão do bloqueio de atualização em bloqueio exclusivo.

Você pode ver isso olhando para o sys.dm_tran_locksDMV:

use AdventureWorks2012;
go

select
    resource_type,
    resource_description,
    resource_associated_entity_id,
    request_mode,
    request_status
from sys.dm_tran_locks
where resource_database_id = db_id();

Você deve ter resultados semelhantes para a saída da consulta acima: