Escalonamento de bloqueio do SQL Server explicado

Oi amigos,

eu escrevi um post no blog sobre Bloqueio de nível de partição onde demonstrei como os bloqueios são escalados de linha para partição. A postagem do blog está aqui;

alguns leitores escreveram de volta para mim perguntando quando o SQL Server aumenta os bloqueios de linha para bloqueios de nível de tabela ou partição. Então, neste post, vou falar sobre o escalonamento de bloqueio do SQL Server.

bem, o motor DB tem um componente chamado Gerenciador de bloqueio, que aumenta bloqueios de granulação fina (linhas & bloqueios de página) para bloqueios de granulação mais grosseiros (bloqueios de partição ou tabela) em 2 circunstâncias:

1. Quando o limite de bloqueio é atingido, que de acordo com BOL é 5000 (para uma única instância da objeção em questão)

2. Quando o limite de memória é atingido para o Gerenciador de bloqueio, mesmo que o número de limites de bloqueios de 5000 não seja atingido.

deixe-me demonstrar rapidamente isso para você:

escrevi a seguinte consulta:

USE AdventureWorksGO BEGINTRANUPDATETOP (5000) Person.ContactSET EmailPromotion = 0

você pode ver que estou colocando 5000 bloqueios de linha, mas quando você executa o sys.dm_tran_locks DMV, você pode ver que o bloqueio de tabela foi adquirido.

select*fromsys.dm_tran_lock

Bem, isso perfeitamente em linha com BOL. No entanto, eu estava brincando com os números e mudei o valor para 4900. Antes de executar a consulta de modificação a cada vez, não se esqueça de reverter TRAN.

ROLLBACKTRAN BEGINTRANUPDATETOP (4900) Person.ContactSET EmailPromotion = 0

eu novamente executei o DMV e, desta vez, também, vi um bloqueio de tabela. Provavelmente, desta vez eu esperava 4900 bloqueios de linha. Mas pode ser o limite de memória foi atingido, por isso continuei a brincar com os números, até que eu descobri que o limite no meu sistema é 4707/4708; onde se o número de bloqueios de LINHA são 4708 ou mais, o mecanismo de banco de dados aumenta este para um bloqueio de tabela, mais qualquer coisa abaixo desse número é os muitos bloqueios de linha.

então, se eu executar o seguinte (reverter qualquer transação anterior):

BEGINTRANUPDATETOP (4707) Person.ContactSET EmailPromotion = 0

e então verifique o DMV, você verá aqueles muitos bloqueios de linha. Você pode executar o seguinte:

select resource_type,resource_database_id,resource_description,resource_associated_entity_id,request_mode,request_type,request_statusfrom sys.dm_tran_lockswhere resource_database_id = DB_ID();

no entanto, quando altero o valor para 4708, a escalada acontece. (Reverter a transação anterior)

BEGINTRANUPDATETOP (4708) Person.ContactSET EmailPromotion = 0

execute o DMV novamente: