SQL Server vu par Christian Robert

SQL Server : Identifier les enregistrements verrouillés dans une table

Question posée sur un forum : « Y a-t-il un moyen de savoir si un enregistrement est actuellement verrouillé dans ma table ? Ainsi il serait possible d'attendre qu'il soit disponible pour le modifier »

Pour répondre à cela, il y a plusieurs possibilités. Comment fonctionne SQL Server au niveau du verrouillage des données ? Il dispose pour simplifier d'un verrou en écriture (type X qu'il essaye d'acquérir avant de réaliser une écriture) et d'un autre en lecture (type S qu'il essaye d'acquérir avant de réaliser une lecture). Il est donc impossible d'écrire dans un enregistrement quand celui-ci est en cours de lecture ou d'écriture, c'est-à-dire quand un verrou X ou S est posé dessus.

1^ère méthode pour connaître l'état de verrouillage d'un enregistrement.

SET LOCK_TIMEOUT 0

UPDATE MaTable
SET MonChamp = 'MaValeur'
WHERE MonClef = 'MaClef'

Si cet enregistrement est verrouillé SQL Server renverra directement une erreur. La commande SET LOCK_TIMEOUT ayant pour effet de renvoyer une erreur si la requête ne peut acquérir un verrou au bout de x millisecondes (ici 0 ms) pour s'exécuter. Ici si l'update ne peut acquérir un verrou X immédiatement l'erreur 1222 est renvoyé, ce cas se produit si l'enregistrement est actuellement lu ou modifié.

A noter, l'erreur 1222 ne pourra être intercepté que dans le code TRY CATCH d'une procédure stockée parente ou dans votre code .net (ou autre), car elle met fin à la transaction et à l'execution du batch.

2^ème méthode

-- Variante pour SQL Server 2005

SELECT * FROM MaTable WITH(NOLOCK)
EXCEPT
SELECT * FROM MaTable WITH(READPAST)

-- Variante pour SQL Server 2000 & 7

SELECT * FROM MaTable AS A WITH(NOLOCK)
WHERE NOT EXISTS(SELECT * FROM MaTable AS B WITH(READPAST) WHERE A.Id = B.Id)

Cette requête renvoie la liste des enregistrements actuellement actuellement verrouillés dans la table. Si le votre apparaît dans la liste c'est qu'il est verrouillé par un processus qui le modifie. Le NOLOCK permet la lecture de tous les enregistrements de la table (lecture qui ne pose pas de verrou, déconseillé dans la pluparts des cas) et le READPAST permet de lire uniquement les enregistrements non verrouillés (lecture filtrée). Il est possible d'inverser la logique si vous le souhaitez.

Cette méthode permet de vérifier la disponibilité d'un enregistrement et éventuellement de boucler pour détecter sa disponibilité ou non dans le code du programme.

L'avantage est que vous n'avez pas de retour d'erreur le désavantage est que vous avez une requête supplémentaire à gérer… De plus rien ne vous garantie qu'entre le SELECT et la mise à jour l'enregistrement sera toujours libre.

La 1^ère méthode et la 2^ème fonctionnent de manière différente, la 1^ère détectera tout type de verrous, tandis que la seconde seulement les verrous de modification de données.

Bon code…