Explicación de la escalada de bloqueos de SQL Server

Hola amigos,

Escribí una entrada de blog sobre el bloqueo a nivel de partición donde demostré cómo se escalan los bloqueos de fila a partición. La entrada del blog está aquí;

Algunos lectores me respondieron preguntándome cuándo SQL Server escala los bloqueos de fila a bloqueos de nivel de tabla o partición. Así que en este post, hablaré sobre la escalada de bloqueo de SQL Server.

Bueno, el motor de base de datos tiene un componente llamado Administrador de bloqueos, que escala los bloqueos de grano fino (filas & bloqueos de página) a bloqueos de grano más grueso (bloqueos de partición o tabla) en 2 circunstancias:

1. Cuando se alcanza el umbral de bloqueo que según BOL es 5000 (para una sola instancia de la objeción en cuestión)

2. Cuando se alcanza el umbral de memoria para el Administrador de bloqueos, incluso si no se alcanza el umbral de número de bloqueos de 5000.

Déjeme demostrarle rápidamente esto:

Escribí la siguiente consulta:

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

Puede ver que estoy colocando 5000 bloqueos DE FILA, pero cuando ejecuta el DMV sys.dm_tran_locks, puede ver que el bloqueo de tabla se ha adquirido.

select*fromsys.dm_tran_lock

Bueno, esto está perfectamente en línea con BOL. Sin embargo, estaba jugando con los números y cambié el valor a 4900. Antes de ejecutar la consulta de modificación cada vez, no olvide REVERTIR TRAN.

ROLLBACKTRAN BEGINTRANUPDATETOP (4900) Person.ContactSET EmailPromotion = 0

De nuevo corrí el DMV y esta vez también, vi un bloqueo de mesa. Probablemente, esta vez esperaba cerraduras de 4900 filas. Pero puede ser que se haya alcanzado el umbral de memoria, así que seguí jugando con los números hasta que descubrí que el umbral en mi sistema es 4707/4708; donde si el número de bloqueos de fila es 4708 o más, el motor de base de datos aumenta esto a un bloqueo de mesa, de lo contrario, cualquier cosa por debajo de ese número son esos muchos bloqueos de fila.

Así que si ejecuto lo siguiente (REVERTIR cualquier transacción anterior):

BEGINTRANUPDATETOP (4707) Person.ContactSET EmailPromotion = 0

Y luego revisa el DMV, verás esas muchas cerraduras de FILA. Puede ejecutar el siguiente:

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();

sin Embargo, cuando me cambie el valor a 4708, escalada sucede. (REVERTIR la transacción anterior)

BEGINTRANUPDATETOP (4708) Person.ContactSET EmailPromotion = 0

Ejecute el DMV de nuevo: