Le moteur de base de données SQL Server de Microsoft verrouille

Le moteur de base de données SQL Server de Microsoft verrouille les ressources en utilisant différents modes
de verrouillage qui déterminent le mode d'accès aux ressources par des transactions simultanées.
Le tableau suivant illustre les modes de verrouillage des ressources utilisés par le moteur de base de données.
Partagé (S)
Utilisé pour les opérations de lecture qui n'effectuent aucune modification ou mise à jour des données, par
exemple une instruction SELECT.
Mise à jour (U)
Utilisé pour les ressources pouvant être mises à jour. Empêche une forme de blocage courante qui se produit
lorsque plusieurs sessions lisent, verrouillent et mettent à jour des ressources ultérieurement.
Exclusif (X)
Utilisé par les opérations de modification de données, telles que INSERT, UPDATE ou DELETE. Empêche des
mises à jour multiples sur la même ressource au même moment.
Intentionnel
Permet d'établir une hiérarchie de verrouillage. Les types de verrouillage intentionnels sont les suivants : partage
intentionnel (IS), exclusion intentionnelle (IX) et partage intentionnel exclusif (SIX).
Schéma
Utilisé lors de l'exécution d'une opération associée au schéma d'une table. Les types de verrouillage de schéma
sont les suivants : modification de schéma (Sch-M). et stabilité de schéma (Sch-S)
Mise à jour en bloc (BU)
Utilisé lors de la copie en bloc de données dans une table avec l'indicateur TABLOCK spécifié.
Verrou de clé
Protège la plage de lignes lue par une requête lorsque le niveau d'isolation des transactions SERIALIZABLE est
utilisé. Garantit qu'aucune autre transaction ne peut insérer des lignes susceptibles de répondre aux requêtes de la
transaction sérialisable si ces dernières étaient réexécutées.
Verrous partagés
Les verrous partagés (S) permettent à des transactions simultanées de lire (SELECT) une ressource dans des
conditions de contrôle d'accès concurrentiel pessimiste. Pour plus d'informations, consultez Types de contrôles
de concurrence. Aucune autre transaction ne peut modifier les données de la ressource tant que des verrous
partagés (S) existent sur la ressource. Les verrous partagés (S) sur une ressource sont enlevés dès que l'opération
de lecture est terminée, à moins que le niveau d'isolation de la transaction soit de type lecture renouvelable ou
plus élevé, ou qu'un indicateur de verrouillage conserve les verrous partagés (S) pendant toute la durée de la
transaction.
Verrous de mise à jour
Les verrous de mise à jour (U) empêchent une forme fréquente de blocage. Une transaction isolée avec le niveau
sérialisable ou de lecture renouvelable lit les données en obtenant un verrou partagé (S) sur la ressource (page ou
ligne), puis modifie ces données, ce qui nécessite une conversion du verrou en mode exclusif (X). Si deux
transactions acquièrent des verrous partagés sur une ressource et tentent ensuite de mettre à jour des données de
manière simultanée, une transaction tente de convertir le verrou en verrou exclusif (X). La conversion du verrou
partagé au mode de verrou exclusif reste en attente, car le verrou exclusif de la première transaction n'est pas
compatible avec le verrou partagé de l'autre transaction. Une attente de verrouillage se produit alors. La
deuxième transaction impliquée essaie d'acquérir un verrou exclusif (X) pour sa mise à jour. Puisque les deux
transactions effectuant la conversion en verrous exclusifs (X) attendent que l'autre transaction libère son verrou
partagé, un blocage se produit.
Les verrous de mise à jour (U) permettent de résoudre les problèmes de blocage. Une seule transaction à la fois
peut obtenir un verrou de mise à jour (U) pour une ressource. Si une transaction modifie une ressource, le verrou
de mise à jour (U) est converti en verrou exclusif (X).

Verrous exclusifs
Les verrous exclusifs (X) empêchent l'accès à une ressource par des transactions simultanées. Un verrou exclusif
(X) empêche toute autre transaction de modifier les données ; les opérations de lecture ne peuvent avoir lieu
qu'avec l'indicateur NOLOCK ou le niveau d'isolation « lecture non validée ».
Les instructions qui modifient les données telles que INSERT, UPDATE et DELETE combinent des opérations
de modification et de lecture. Elles commencent par les opérations de lecture pour obtenir les données, puis elles
effectuent les opérations de modification. Par conséquent, les instructions qui modifient les données demandent
généralement à la fois des verrous partagés et des verrous exclusifs. Ainsi, une instruction UPDATE peut
modifier les lignes d'une table en fonction d'une jointure avec une autre table. Dans ce cas, l'instruction
UPDATE demande des verrous partagés sur les lignes lues dans la table jointe en plus des verrous exclusifs sur
les lignes mises à jour.
Verrous intentionnels
Le moteur de base de données utilise des verrous intentionnels pour protéger le placement de verrous partagés
(S) ou exclusifs (X) sur une ressource hiérarchiquement inférieure. Les verrous intentionnels sont appelés ainsi
parce qu'ils sont obtenus avant un verrou de niveau inférieur et signalent par conséquent l'intention de placer des
verrous à un niveau inférieur.
Les verrous intentionnels ont deux fonctions :
1 - Empêcher les autres transactions de modifier la ressource de niveau supérieur de façon à invalider le verrou
au niveau inférieur.
2 - Améliorer l'efficacité du moteur de base de données en matière de détection de conflits de verrous au plus
haut niveau de granularité.
Par exemple, un verrou de partage intentionnel est demandé au niveau table avant une demande de verrous
partagés (S) sur les pages ou les lignes de cette table. Un verrou intentionnel placé au niveau de la table empêche
une autre transaction d'acquérir un verrou exclusif (X) sur la table contenant cette page. Les verrous intentionnels
améliorent les performances, car le moteur de base de données n'examine les verrous intentionnels qu'au niveau
des tables afin de déterminer si une transaction peut acquérir un verrou en toute sécurité sur chaque table. Cela
supprime la nécessité d'examiner chaque verrou de ligne ou page pour déterminer si une transaction peut
verrouiller la table entière.
Les verrous intentionnels comprennent les verrous de partage intentionnel (IS), d'exclusion intentionnelle (IX),
et de partage intentionnel exclusif (SIX).
Partage intentionnel (IS)
Protège les verrous partagés demandés ou acquis sur certaines ressources (mais pas toutes) de niveau inférieur
dans la hiérarchie.
Exclusion intentionnelle (IX)
Protège les verrous exclusifs demandés ou acquis sur certaines ressources (mais pas toutes) de niveau inférieur
dans la hiérarchie. IX est un sur-ensemble du mode IS qui protège également les demandes de verrou partagé sur
des ressources de niveau inférieur.
Partage d'exclusion intentionnelle (SIX)
Protège les verrous partagés (S) demandés ou acquis sur toutes les ressources de niveau inférieur dans la
hiérarchie et les verrous d'exclusion intentionnelle sur certaines ressources de niveau inférieur (mais pas toutes).
Les lectures simultanées sur les ressources de niveau supérieur sont autorisées. Par exemple, l'obtention d'un
verrou SIX sur une table permet également d'obtenir des verrous d'exclusion intentionnelle sur les pages
modifiées et des verrous exclusifs sur les lignes modifiées. Il ne peut y avoir qu'un seul verrou SIX par ressource
à la fois pour empêcher la mise à jour des ressources par une autre transaction, bien que cette dernière peut lire
les ressources inférieures dans la hiérarchie en obtenant des verrous IS au niveau des tables.

Mise à jour intentionnelle (IU)
Protège les verrous de mise à jour demandés ou acquis sur toutes les ressources de niveau inférieur dans la
hiérarchie. Les verrous IU sont utilisés uniquement sur les ressources de page. Les verrous IU sont convertis en
verrous IX si une opération de mise à jour a lieu.
Mise à jour intentionnelle partagée (SIU)
Combinaison de verrous S et IU résultant de l'acquisition séparée de ces verrous et de leur gestion simultanée.
Par exemple, une transaction peut exécuter une requête avec l'indicateur PAGLOCK, puis une opération de mise
à jour. La requête contenant l'indicateur PAGLOCK obtient le verrou S et l'opération de mise à jour obtient le
verrou IU.
Mise à jour intentionnelle exclusive (UIX)
Combinaison de verrous U et IX résultant de l'acquisition séparée de ces verrous et de leur gestion simultanée.
Verrous de schéma
Le moteur de base de données utilise les verrous de modification de schémas (Sch-M) lorsqu'une opération DDL
(langage de définition de données) est effectuée sur une table (ajout d'une colonne ou suppression d'une table,
par exemple). Pendant le temps de sa détention, le verrou Sch-M empêche les accès simultanés à la table. Cela
signifie que le verrou Sch-M bloque toutes les opérations externes jusqu'à ce que le verrou soit libéré.
Certaines opérations DML(langage de manipulation de données), comme la troncation de table, utilisent les
verrous SCH-M pour empêcher l'accès aux tables affectées par des opérations simultanées.
Le moteur de base de données utilise les verrous de stabilité du schéma (Sch-S) lors de la compilation et
l'exécution des requêtes. Les verrous Sch-S ne bloquent aucun verrou transactionnel, verrous exclusifs (X) y
compris. Par conséquent, les autres transactions, y compris celles avec des verrous exclusifs (X) sur une table,
continuent à s'exécuter pendant la compilation d'une requête. Toutefois, les opérations DDL simultanées, ainsi
que les opérations DML simultanées qui définissent des verrous Sch-M, ne peuvent pas être exécutées sur la
table.
Verrous de mise à jour en bloc (BU)
Le moteur de base de données utilise les verrous de mise à jour en bloc (BU) lors de la copie en bloc de données
dans une table et que l'indicateur TABLOCK est spécifié ou que l'option table lock on bulk load est définie à
l'aide de sp_tableoption. Les verrous BU permettent à plusieurs threads de copier simultanément des données
dans la même table tout en empêchant les processus qui n'effectuent pas de copie de données en bloc d'accéder à
cette table.