Free Press

Le verrouillage est essentiel au succès du traitement des transactions de SQL Server et il est conçu pour permettre à SQL Server de fonctionner de manière transparente dans un environnement multi-utilisateurs. Le verrouillage est la manière dont SQL Server gère la concurrence des transactions. Essentiellement, les verrous sont des structures en mémoire qui ont des propriétaires, des types et le hachage de la ressource qu’ils doivent protéger. Un verrou en tant que structure en mémoire a une taille de 96 octets.

Pour mieux comprendre le verrouillage dans SQL Server, il est important de comprendre que le verrouillage est conçu pour assurer l’intégrité des données dans la base de données, car il oblige chaque transaction SQL Server à passer le test ACID.

Le test ACID se compose de 4 exigences que chaque transaction doit passer avec succès :

• Atomicité – exige qu’une transaction qui implique deux ou plusieurs parties discrètes d’informations doit commettre toutes les parties ou aucune
• Cohérence – exige qu’une transaction doit créer un état valide de nouvelles données, ou elle doit ramener toutes les données à l’état qui existait avant l’exécution de la transaction
• Isolation – exige qu’une transaction qui est toujours en cours d’exécution et n’a pas encore commis toutes les données, doit rester isolée de toutes les autres transactions
• Durabilité – exige que les données commises soient stockées en utilisant une méthode qui préservera toutes les données dans un état correct et disponible pour un utilisateur, même en cas d’échec

Le verrouillage du serveur SQL est la partie essentielle de l’exigence d’isolation et il sert à verrouiller les objets affectés par une transaction. Pendant que les objets sont verrouillés, SQL Server empêchera les autres transactions d’effectuer toute modification des données stockées dans les objets affectés par le verrou imposé. Une fois le verrou libéré par la validation des modifications ou par le retour des modifications à l’état initial, les autres transactions seront autorisées à effectuer les modifications de données requises.

Traduit dans le langage SQL Server, cela signifie que lorsqu’une transaction impose le verrou sur un objet, toutes les autres transactions qui nécessitent l’accès à cet objet seront forcées d’attendre jusqu’à ce que le verrou soit libéré et cette attente sera enregistrée avec le type d’attente adéquat

Les verrous de SQL Server peuvent être spécifiés via les modes de verrouillage et la granularité de verrouillage

Modes de verrouillage

Le mode de verrouillage considère divers types de verrouillage qui peuvent être appliqués à une ressource qui doit être verrouillée :

• Exclusif (X)
• Partagé (S)
• Mise à jour (U)
• Intention (I)
• Schéma (Sch)
• Mise à jour en masse (BU)

Verrouillage exclusif (X) – Ce type de verrouillage, lorsqu’il est imposé, garantit qu’une page ou une ligne sera réservée exclusivement à la transaction qui a imposé le verrou exclusif, tant que la transaction détient le verrou.

Le verrou exclusif sera imposé par la transaction lorsqu’elle voudra modifier les données de la page ou de la ligne, ce qui est le cas des instructions DML DELETE, INSERT et UPDATE. Un verrou exclusif peut être imposé à une page ou une ligne seulement s’il n’y a pas d’autre verrou partagé ou exclusif déjà imposé sur la cible. Cela signifie pratiquement qu’un seul verrou exclusif peut être imposé à une page ou à une ligne, et qu’une fois imposé, aucun autre verrou ne peut être imposé aux ressources verrouillées

Verrouillage partagé (S) – ce type de verrou, lorsqu’il est imposé, réserve une page ou une ligne pour qu’elle soit disponible uniquement en lecture, ce qui signifie que toute autre transaction sera empêchée de modifier l’enregistrement verrouillé tant que le verrou est actif. Cependant, un verrou partagé peut être imposé par plusieurs transactions en même temps sur la même page ou ligne et, de cette façon, plusieurs transactions peuvent partager la capacité de lecture des données puisque le processus de lecture lui-même n’affectera en aucun cas les données réelles de la page ou de la ligne. En outre, un verrou partagé permettra des opérations d’écriture, mais aucune modification DDL ne sera autorisée

Verrouillage de mise à jour (U) – ce verrou est similaire à un verrou exclusif mais est conçu pour être plus flexible d’une certaine manière. Un verrou de mise à jour peut être imposé à un enregistrement qui possède déjà un verrou partagé. Dans ce cas, le verrou de mise à jour imposera un autre verrou partagé sur la ligne cible. Une fois que la transaction qui détient le verrou de mise à jour est prête à modifier les données, le verrou de mise à jour (U) sera transformé en verrou exclusif (X). Il est important de comprendre que le verrou de mise à jour est asymétrique par rapport aux verrous partagés. Alors que le verrou de mise à jour peut être imposé à un enregistrement qui possède le verrou partagé, le verrou partagé ne peut pas être imposé à l’enregistrement qui possède déjà le verrou de mise à jour

Verrous d’intention (I) – ce verrou est un moyen utilisé par une transaction pour informer une autre transaction de son intention d’acquérir un verrou. L’objectif d’un tel verrou est d’assurer la modification des données à exécuter correctement en empêchant une autre transaction d’acquérir un verrou sur l’objet suivant dans la hiérarchie. En pratique, lorsqu’une transaction souhaite acquérir un verrou sur une ligne, elle acquiert un verrou intentionnel sur une table, qui est un objet de hiérarchie supérieure. En acquérant le verrou intentionnel, la transaction ne permettra pas aux autres transactions d’acquérir le verrou exclusif sur cette table (sinon, le verrou exclusif imposé par une autre transaction annulerait le verrou de la ligne).

Il s’agit d’un type de verrou important du point de vue des performances, car le moteur de base de données de SQL Server inspectera les verrous d’intention uniquement au niveau de la table pour vérifier s’il est possible pour la transaction d’acquérir un verrou de manière sûre dans cette table, et donc le verrou d’intention élimine le besoin d’inspecter chaque verrou de ligne/page dans une table pour s’assurer que la transaction peut acquérir un verrou sur la table entière

Il existe trois verrous d’intention réguliers et trois verrous dits de conversion :

Verrous d’intention réguliers :

Verrous d’intention exclusifs (IX) – lorsqu’un verrou d’intention exclusif (IX) est acquis, il indique à SQL Server que la transaction a l’intention de modifier certaines des ressources de la hiérarchie inférieure en acquérant des verrous exclusifs (X) individuellement sur ces ressources de la hiérarchie inférieure

Verrous d’intention partagés (IS) – lorsqu’un verrou d’intention partagé (IS) est acquis, il indique à SQL Server que la transaction a . l’intention de lire certaines ressources de hiérarchie inférieure en acquérant des verrous partagés (S) individuellement sur ces ressources inférieures dans la hiérarchie

Intention de mise à jour (IU) – lorsqu’un verrou partagé d’intention (IS) est acquis, il indique à SQL Server que la transaction a l’intention de lire certaines des ressources de hiérarchie inférieure en acquérant des verrous partagés (S) individuellement sur ces ressources inférieures dans la hiérarchie. Le verrou intentionnel de mise à jour (IU) ne peut être acquis qu’au niveau de la page et dès que l’opération de mise à jour a lieu, il se convertit en verrou intentionnel exclusif (IX)

Verrous de conversion:

Partagé avec intention exclusive (SIX) – lorsqu’il est acquis, ce verrou indique que la transaction a l’intention de lire toutes les ressources d’une hiérarchie inférieure et donc d’acquérir le verrou partagé sur toutes les ressources qui sont plus basses dans la hiérarchie, et à son tour, de modifier une partie de celles-ci, mais pas toutes. Ce faisant, elle acquerra un verrou exclusif d’intention (IX) sur les ressources de la hiérarchie inférieure qui doivent être modifiées. En pratique, cela signifie qu’une fois que la transaction a acquis un verrou SIX sur la table, elle va acquérir un verrou exclusif d’intention (IX) sur les pages modifiées et un verrou exclusif (X) sur les lignes modifiées.

Un seul verrou partagé avec intention exclusive (SIX) peut être acquis sur une table à la fois et il bloquera les autres transactions pour faire des mises à jour, mais il n’empêchera pas les autres transactions de lire les ressources de la hiérarchie inférieure qu’elles peuvent acquérir le verrou partagé avec intention (IS) sur la table

Partagé avec intention mise à jour (SIU) – c’est un verrou un peu plus spécifique car il est une combinaison des verrous partagés (S) et intention mise à jour (IU). Un exemple typique de ce verrou est lorsqu’une transaction utilise une requête exécutée avec l’indice et la requête PAGELOCK, puis la requête de mise à jour. Après que la transaction acquiert un verrou SIU sur la table, la requête avec l’indice PAGELOCK acquerra le verrou partagé (S) tandis que la requête de mise à jour acquerra le verrou d’intention de mise à jour (IU)

Mise à jour avec intention exclusive (UIX) – lorsque les verrous de mise à jour (U) et d’intention exclusive (IX) sont acquis à des ressources de hiérarchie inférieure dans la table simultanément, le verrou exclusif de mise à jour avec intention sera acquis au niveau de la table en conséquence

Verrous de schéma (Sch) – Le moteur de base de données SQL Server reconnaît deux types de verrous de schéma : Verrouillage de modification de schéma (Sch-M) et Verrouillage de stabilité de schéma (Sch-S)

• Un verrou de modification de schéma (Sch-M) sera acquis lorsqu’une instruction DDL est exécutée, et il empêchera l’accès aux données de l’objet verrouillé pendant que la structure de l’objet est modifiée. Le serveur SQL autorise un seul verrou de modification de schéma (Sch-M) sur tout objet verrouillé. Afin de modifier une table, une transaction doit attendre d’acquérir un verrou Sch-M sur l’objet cible. Une fois qu’elle a acquis le verrou de modification de schéma (Sch-M), la transaction peut modifier l’objet et une fois la modification terminée, le verrou sera libéré. Un exemple typique de verrou Sch-M est la reconstruction d’un index, car la reconstruction d’un index est un processus de modification de table. Une fois que l’ID de reconstruction d’index est émis, un verrou de modification de schéma (Sch-M) sera acquis sur cette table et ne sera libéré qu’après la fin du processus de reconstruction d’index (lorsqu’il est utilisé avec l’option ONLINE, la reconstruction d’index acquerra le verrou Sch-M peu de temps après la fin du processus)
• Un verrou de stabilité de schéma (Sch-S) sera acquis pendant la compilation et l’exécution d’une requête dépendant du schéma et le plan d’exécution est généré. Ce verrou particulier ne bloquera pas les autres transactions pour accéder aux données de l’objet et il est compatible avec tous les modes de verrouillage, sauf avec le verrou de modification de schéma (Sch-M). Essentiellement, les verrous de stabilité de schéma seront acquis par chaque requête DML et select pour garantir l’intégrité de la structure de la table (s’assurer que la table ne change pas pendant l’exécution des requêtes).

Verrous de mise à jour en bloc (BU) – ce verrou est conçu pour être utilisé par les opérations d’importation en bloc lorsqu’il est émis avec un argument/intérêt TABLOCK. Lorsqu’un verrou de mise à jour en bloc est acquis, les autres processus ne pourront pas accéder à une table pendant l’exécution du chargement en bloc. Toutefois, un verrou de mise à jour en masse n’empêchera pas le traitement en parallèle d’un autre chargement en masse. Mais gardez à l’esprit que l’utilisation de TABLOCK sur une table d’index clusterisée ne permettra pas l’importation en masse parallèle. Plus de détails à ce sujet sont disponibles dans Guidelines for Optimizing Bulk Import

Hiérarchie de verrouillage

SQL Server a introduit la hiérarchie de verrouillage qui est appliquée lorsque la lecture ou la modification des données est effectuée. La hiérarchie de verrouillage commence par la base de données au niveau hiérarchique le plus élevé et descend via la table et la page jusqu’à la ligne au niveau le plus bas

Essentiellement, il y a toujours un verrou partagé au niveau de la base de données qui est imposé chaque fois qu’une transaction est connectée à une base de données. Le verrou partagé au niveau de la base de données est imposé pour empêcher l’abandon de la base de données ou la restauration d’une sauvegarde de la base de données sur la base de données en cours d’utilisation. Par exemple, lorsqu’une instruction SELECT est émise pour lire certaines données, un verrou partagé (S) sera imposé au niveau de la base de données, un verrou partagé intentionnel (IS) sera imposé au niveau de la table et de la page, et un verrou partagé (S) sur la ligne elle-même

Dans le cas d’une instruction DML (c’est-à-dire. insérer, mettre à jour, supprimer) un verrou partagé (S) sera imposé au niveau de la base de données, un verrou exclusif d’intention (IX) ou un verrou de mise à jour d’intention (IU) sera imposé au niveau de la table et de la page, et un verrou exclusif ou de mise à jour (X ou U) sur la ligne

Les verrous seront toujours acquis du haut vers le bas, car de cette façon, SQL Server empêche une condition dite de course de se produire.

Maintenant que les modes de verrouillage et la hiérarchie des verrous ont été expliqués, développons davantage les modes de verrouillage et comment ceux-ci se traduisent par une hiérarchie des verrous.

Tous les modes de verrouillage ne peuvent pas être appliqués à tous les niveaux.

Au niveau de la ligne, les trois modes de verrouillage suivants peuvent être appliqués :

• Exclusif (X)
• Partagé (S)
• Mise à jour (U)

Pour comprendre la compatibilité de ces modes, veuillez vous référer au tableau suivant :

✓ – Compatible ✗ – Incompatible

Au niveau de la table, il existe cinq types de verrous différents :

• Exclusif (X)
• Partagé (S)
• Intent exclusif (IX)
• Intent partagé (IS)
• Partagé avec intention exclusive (SIX)

La compatibilité de ces modes peut être vue dans le tableau ci-dessous

.

✓ – Compatible ✗ – Incompatible

Un verrouillage de schéma (Sch) est également un verrouillage au niveau de la table, mais ce n’est pas un verrou lié aux données

Pour mieux comprendre la compatibilité entre ces types de verrou, veuillez vous référer à ce tableau :

Lock escalation

Afin d’éviter une situation où le verrouillage utilise trop de ressources, SQL Server a introduit la fonctionnalité d’escalade de verrou.

Sans escalade, les verrouillages pourraient nécessiter une quantité importante de ressources mémoire. Prenons un exemple où un verrou doit être imposé sur les 30 000 lignes de données, où chaque ligne a une taille de 500 octets, pour effectuer l’opération de suppression. Sans escalade, un verrou partagé (S) sera imposé sur la base de données, 1 verrou exclusif d’intention (IX) sur la table, 1,875 verrous exclusifs d’intention (IX) sur les pages (une page de 8KB contient 16 lignes de 500 octets, ce qui fait 1,875 pages qui contiennent 30,000 lignes) et 30,000 verrous exclusifs (X) sur les lignes elles-mêmes. Comme chaque verrou a une taille de 96 octets, 31 877 verrous occupent environ 3 Mo de mémoire pour une seule opération de suppression. L’exécution d’un grand nombre d’opérations en parallèle pourrait nécessiter des ressources importantes, juste pour s’assurer que le gestionnaire de verrouillage peut effectuer l’opération en douceur

Pour éviter une telle situation, SQL Server utilise l’escalade de verrouillage. Cela signifie que dans une situation où plus de 5 000 verrous sont acquis sur un seul niveau, SQL Server escaladera ces verrous vers un seul verrou de niveau table. Par défaut, le serveur SQL remontera toujours directement au niveau de la table, ce qui signifie que la remontée au niveau de la page ne se produit jamais. Au lieu d’acquérir de nombreux verrous de lignes et de pages, SQL Server escaladera vers le verrou exclusif (X) au niveau de la table

Bien que cela réduise le besoin de ressources, les verrous exclusifs (X) dans une table signifient qu’aucune autre transaction ne pourra accéder à la table verrouillée et que toutes les requêtes essayant d’accéder à cette table seront bloquées. Par conséquent, cela réduira la surcharge du système mais augmentera la probabilité de contention de la concurrence

Afin de fournir un contrôle sur l’escalade, à partir de SQL Server 2008 R2, l’option LOCK_EXCALATION est introduite dans le cadre de l’instruction ALTER TABLE

Chacune de ces options est définie pour permettre un contrôle spécifique sur le processus d’escalade des verrous :

Table – C’est l’option par défaut pour toute table nouvellement créée, car par défaut SQL Server exécutera toujours l’escalade de verrous au niveau de la table, ce qui inclut également les tables partitionnées

Auto – Cette option permet l’escalade de verrous à un niveau de partition lorsqu’une table est partitionnée. Lorsque 5 000 verrous sont acquis dans une seule partition, l’escalade de verrouillage acquerra un verrou exclusif (X) sur cette partition tandis que la table acquerra un verrou exclusif d’intention (IX). Dans le cas où cette table n’est pas partitionnée, l’escalade de verrous acquerra le verrou au niveau de la table (égal à l’option Table).

Bien que cette option semble très utile, elle doit être utilisée très prudemment car elle peut facilement provoquer un blocage. Dans une situation où nous avons deux transactions sur deux partitions où le verrou exclusif (X) est acquis, et les transactions essaient d’accéder à la date de la partition utilisée par l’autre transaction, un deadlock sera rencontré

Donc, il est très important de contrôler soigneusement le modèle d’accès aux données, si cette option est activée, ce qui n’est pas facile à réaliser, et c’est pourquoi cette option n’est pas les paramètres par défaut dans SQL Server

Disable – Cette option désactivera complètement l’escalade de verrou pour une table. Encore une fois, cette option doit être utilisée avec précaution pour éviter que le gestionnaire de verrouillage de SQL Server ne soit obligé d’utiliser une quantité excessive de mémoire

Comme on peut le voir, l’escalade de verrouillage pourrait être un défi pour les DBA. Si la conception de l’application nécessite la suppression ou la mise à jour de plus de 5 000 lignes à la fois, une solution pour éviter l’escalade des verrous, et les effets qui en résultent, est de diviser la transaction unique en deux ou plusieurs transactions où chacune traitera moins de 5 000 lignes, car de cette façon l’escalade des verrous pourrait être éludée

Avoir des infos sur les verrous actifs de SQL Server

SQL Server fournit la vue de gestion dynamique (DMV) sys.dm_tran_locks qui renvoie des informations sur les ressources du gestionnaire de verrous qui sont actuellement utilisées, ce qui signifie qu’il affichera tous les verrous « vivants » acquis par les transactions. Plus de détails sur cette DMV peuvent être trouvés dans l’article sys.dm_tran_locks (Transact-SQL).

La colonne la plus importante utilisée pour l’identification du verrou sont resource_type, request_mode, et resource_description. Si nécessaire, d’autres colonnes comme ressource supplémentaire pour les infos peuvent être incluses lors du dépannage

Voici l’exemple de la requête

La clause where de cette requête est utilisée comme filtre sur le resource_type à éliminer. des résultats, ceux généralement des verrous partagés acquis sur la base de données puisque ceux-ci sont toujours présents au niveau de la base de données

Une brève explication des trois colonnes présentées ici :

type_ressource – Affiche une ressource de base de données où les verrous sont acquis. La colonne peut afficher l’une des valeurs suivantes : ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID

request_mode – affiche le mode de verrouillage qui est acquis sur la ressource

resource_description – affiche une courte description de la ressource et n’est pas remplie pour tous les modes de verrouillage. Le plus souvent, la colonne contient l’id de la ligne, de la page, de l’objet, du fichier, etc

• Auteur
• Postages récents

Nikola est un fou d’informatique depuis 1981 et un passionné de SQL avec l’intention de devenir un fou. Spécialisé dans l’audit de SQL Server, la conformité et la surveillance des performances.
Dévot de l’aviation militaire et modéliste d’avions à l’échelle hard core. Fan de sports extrêmes ; parachutiste et instructeur de saut à l’élastique. Autrefois sérieux, maintenant juste un photographe de temps libre
Voir tous les messages de Nikola Dimitrijevic

Derniers messages de Nikola Dimitrijevic (voir tous)

• SQL Server trace flags guide ; de -1 à 840 – 4 mars 2019
• Comment gérer le type d’attente WRITELOG de SQL Server – 13 juin 2018
• Compteurs de performance de SQL Server (Requêtes batch/sec ou Transactions/sec) : quoi surveiller et pourquoi – 5 juin 2018

.