Dans un précédent article (voir ici), j’ai présenté une première approche d’un usage de la vue sys.dm_tran_locks afin d’identifier dans le cas des verrous de type PAGE les données concernées par le verrouillage.
Maintenant, attachons-nous à l’identification plus précise des lignes de données verrouillées, c’est-à-dire aux verrous de type KEY.
Pour rappel, le niveau de verrouillage des ressources dans SQL Server se fait suivant plusieurs niveaux hiérarchiques, détaillés ici : https://docs.microsoft.com/fr-fr/previous-versions/sql/sql-server-2008-r2/ms189849(v=sql.105).
Les verrous de ligne sont identifiés dans la vue sys.dm_tran_locks par l’identifiant de la partition concernées (de même que les verrous de niveau PAGE) mais surtout par une valeur de hachage (KeyHashValue) qui permet au système d’identifier rapidement la ligne sur laquelle porte le verrou.
Par exemple, posons un verrou sur une ligne donnée dans la table Sales.SalesOrderDetail (la transaction restant ouverte, le verrou n’est pas libéré).
/*————————
begin transaction
select top 1 SalesOrderDetailID,rowguid
from Sales.SalesOrderDetail with (rowlock, xlock)
order by rowguid desc
————————*/
SalesOrderDetailID rowguid
—————— ————————————
18519 7762B051-2A02-4488-A42D-FFFF46488486
Cherchons, depuis cette connexion (et donc à l’intérieur de cette transaction) à vérifier qu’un verrou est bien en place sur la ligne :

Nous constatons notamment un verrou exclusif (request_mode=’X’) sur une ligne (resource_type=’KEY’). Les valeurs nous permettant d’identifier la ressource concernée sont dans un premier temps resource_associated_entity_id, qui correspond à l’identifiant HoBT (Heap or B-Tree) dans la table sys.partitions.

Nous constatons bien que nous somme dans la bonne table (Sales.SalesOrderDetail), et qui plus est que nous avons interrogé cette table à travers l’index portant sur rowguid.
Reste maintenant à identifier LA ligne concernée par ce verrou. Cela se fait à partir de la valeur de hachage (3e6e808d7855). Nous voyons déjà, à travers le verrou de page posé, que nous sommes dans la page 1:17648. Nous pouvons donc déjà, comme évoqué dans mon précédent article, utiliser la commande DBCC PAGE.

La dernière ligne correspond bien à l’enregistrement que nous avons verrouillé.
A noter toutefois que la recherche du KeyHashValue manuellement dans le résultat de DBCC PAGE n’est pas très « industrielle ». Plusieurs autres méthodes de recherche existent, mais la plus simple est l’utilisation d’une colonne cachée nommée %%lockres%%, qui contient ligne par ligne la clé de hachage correspondante.
/*————————
select rowguid,SalesOrderDetailID,%%lockres%% as HashKeyValue
from sales.SalesOrderDetail with (nolock,index(AK_SalesOrderDetail_rowguid))
where (%%lockres%%)='(3e6e808d7855)’
————————*/
rowguid SalesOrderDetailID HashKeyValue
———————————— —————— ——————————–
7762B051-2A02-4488-A42D-FFFF46488486 18519 (3e6e808d7855)
Remarque importante à bien prendre en compte, la clé de hachage dépend de l’index utilisé. Ainsi, la requête suivante donnera une autre clé de hachage car elle s’appuiera sur un autre index :
/*————————
select rowguid,SalesOrderDetailID,%%lockres%% as HashKeyValue
from sales.SalesOrderDetail with (nolock)
where SalesOrderDetailID=18519
————————*/
rowguid SalesOrderDetailID HashKeyValue
———————————— —————— ——————————–
7762B051-2A02-4488-A42D-FFFF46488486 18519 (e2f0aa5e3af2)

Voilà pour un bref exemple présentant la recherche des enregistrements partie prenante dans un verrou. N’hésitez pas à me donner votre avis sur cet article où à poser un commentaire pour toute question complémentaire…