Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

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 :

Niveau

Desc

Type

Session

OBJECT

 

IX

52

OBJECT

 

IX

54

KEY

(08000c080f1b)

X

52

PAGE

1:9698

IX

52

PAGE

1:9698

IX

54

KEY

(07005a186c43)

X

54

 

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…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié mercredi 9 mai 2007 19:27 par christian
Classé sous : ,

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01