SQL Server vu par Christian Robert

SQL Server : SQL Server sait verrouiller des enregistrements et pas seulement des pages !!!

Dans la série des mythes sur SQL Server, en voici un de taille : « SQL Server ne sait pas verrouiller les enregistrements, il verrouille les pages (blocs de 8 ko) ou même la table  »… Evidement c'est faux, il sait gérer le verrouillage au niveau enregistrement depuis SQL Server 7.0.

Pour ceux qui ne croient que ce qu'ils voient en voici la démonstration. Avant une petite explication du système de verrouillage.

Un verrou occupe 96 octets il peut être spécifié sur un enregistrement (RID), une clef d'index (KEY), une page (PAGE), une extension (EXTENT), un objet (OBJECT), une base de données (DATABASE) et d'autres… Vous avez 2 types de verrous principaux : X (Exclusive) que le serveur demande avant une écriture et S (Shared) que le serveur demande avant une lecture. A quoi cela sert ? A vous éviter de lire des données fausses, ou de perdre des valeurs.

L'intérêt de verrouiller finement (au niveau enregistrement) c'est d'améliorer la concurrence d'accès aux données, mais on consomme plus de ressources, dont beaucoup de mémoire. A l'opposé le verrouillage grossier consomme peu de mémoire mais diminue fortement la concurrence d'accès aux données. Ci-dessous dans le cas de gauche on verrouille les enregistrements, résultat les 3 requêtes peuvent s'exécuter simultanément. Dans le cas à droite, chaque requête verrouille la table, ce qui provoque une exécution des requêtes décalée dans le temps.

Comment SQL Server verrouille une la table dont il a besoin dans une requête ? SQL Server suivant la requête à exécutée (suivant le nombre d'enregistrements affectés dans la table) essaye tout d'abord un verrouillage de niveau table ou page. Si celui-ci échoue, on essaye alors un verrouillage au niveau enregistrements. Au-delà de 5000 enregistrements verrouillés on escalade, c'est-à-dire que l'on essaye de verrouiller des pages. A nouveau cela peut échouer et on peut se retrouver à escalader partiellement, avec un verrouillage mixte (enregistrement + pages). Au-delà de 5000 pages on essaye l'escalade à la table. (Voir le petit schéma ci-dessous).

Comment tester le phénomène ? Exécutez 2 requêtes suivantes dans 2 sessions différentes, j'ai utilisé la base de données exemple de SQL Server 2005 (AdventureWorks) qui est disponible en téléchargement ou avec le CD d'installation de SQL Server 2005. Ne mettez pas de COMMIT à la fin de chacune de requêtes, le but étant de garder les verrous posés le plus longtemps possible (Au pire faites un ROLLBACK une fois le test terminé).

-- Première connexion
BEGIN TRANSACTION

UPDATE Person.Contact
SET FirstName = 'A'
WHERE ContactId = 8

-- Seconde connexion
BEGIN TRANSACTION

UPDATE Person.Contact
SET FirstName = 'A'
WHERE ContactId = 7

Comment savoir quel verrou a été posé ? Exécutez la requêtes suivante (elle est équivalente à ce que vous renvoie sp_lock sous SQL 7 et 2000) :

SELECT * FROM sys.dm_tran_locks

Vous obtiendrez quelque chose de similaire à ce j'ai eu :

Il y a 2 verrous de clefs (donc d'enregistrement, comme nous sommes dans un index), 2 verrous de pages, et 2 verrous de tables. Ils correspondent à la même opération exécutée sur les 2 sessions, mais sur un enregistrement différent.

D'où viennent les verrous IX au niveau page et au niveau table ? Ce sont des verrous « intent » il interdise toute opération incompatible aux niveaux supérieurs des enregistrements. Cela permet par exemple d'interdire un SELECT * au niveau de la table sans clause WHERE, qui aurait besoin d'un verrou S de type table, mais qui averti par le verrou IX de la table sait qu'il ne peut l'obtenir pour le moment.

Je pense que je vais faire un tag mythe et légende sur mon blog ;o)

Bon verrous…