• Article

Escalade de verrous (moteur de base de données)

L'escalade de verrous est le processus de conversion d'un grand nombre de verrous détaillés en verrous moins détaillés, tout en réduisant la charge du système et en augmentant la probabilité de conflit de concurrence.

Lorsque le Moteur de base de données SQL Server acquiert des verrous de bas niveau, il place également des verrous intentionnels sur les objets qui contiennent les objets de niveau inférieur :

  • Lorsqu'il verrouille des lignes ou des plages de clés d'index, le Moteur de base de données place un verrou intentionnel sur les pages qui contiennent les lignes ou les clés.

  • Lorsqu'il verrouille des pages, le Moteur de base de données place un verrou intentionnel sur les objets de niveau supérieur qui contiennent ces pages. Outre le verrou intentionnel sur l'objet, des verrous de page intentionnels sont demandés sur les objets suivants :

    • Pages de niveau feuille d'index non-cluster

    • Pages de données d'index cluster

    • Pages de données de segment de mémoire

Le Moteur de base de données peut effectuer un verrouillage de ligne et un verrouillage de page pour la même instruction afin de réduire au minimum le nombre de verrous et la probabilité qu'il faille en escalader. Par exemple, le Moteur de base de données peut placer des verrous de page sur un index non-cluster (si suffisamment de clés contiguës dans le nœud d'index sont sélectionnées pour satisfaire à la requête) et des verrous de ligne sur les données.

Pour escalader des verrous, le Moteur de base de données essaie de remplacer le verrou intentionnel sur la table par le verrou complet correspondant, par exemple, en substituant un verrou exclusif (X) à un verrou intentionnel exclusif (IX) ou un verrou partagé (S) à un verrou intentionnel partagé (IS). Si la tentative d'escalade de verrous réussit et que le verrou de table complet est acquis, tous les verrous de page (PAGE), de niveau ligne (RID), de segment ou d'arbre B (B-tree) détenus par la transaction sur le segment ou sur l'index sont libérés. Si le verrou complet ne peut pas être acquis, aucune escalade de verrous ne se produit à ce stade et le Moteur de base de données continue d'acquérir des verrous de ligne, de clé ou de page.

Le Moteur de base de données n'escalade pas les verrous de ligne ou de plage de clés en verrous de page, mais il les escalade directement en verrous de table. De même, les verrous de page sont systématiquement escaladés en verrous de table. Dans SQL Server 2008, le verrouillage des tables partitionnées peut être escaladé au niveau HoBT de la partition associée plutôt qu'au verrou de la table. Un verrou au niveau du HoBT ne verrouille pas nécessairement les HoBT alignés pour la partition.

[!REMARQUE]

Les verrous au niveau du HoBT augmentent habituellement la concurrence, mais introduisent le potentiel des blocages lorsque les transactions qui verrouillent différentes partitions souhaitent étendre leurs verrous exclusifs aux autres partitions. Dans de rares instances, la granularité de verrouillage de TABLE peut s'avérer plus efficace.

Si une tentative d'escalade de verrous échoue en raison de verrous en conflit détenus par des transactions simultanées, le Moteur de base de données renouvelle la tentative d'escalade de verrous chaque fois que la quantité de verrous supplémentaires acquis par la transaction atteint 1 250.

Chaque événement d'escalade fonctionne essentiellement au niveau d'une instruction Transact-SQL unique. Lorsque l'événement démarre, le Moteur de base de données essaie d'escalader tous les verrous détenus par la transaction en cours dans toutes les tables ayant été référencées par l'instruction active, sous réserve que celle-ci satisfasse aux contraintes de seuil d'escalade. Si l'événement d'escalade démarre avant que l'instruction n'ait accédé à une table, aucune tentative n'est réalisée pour escalader les verrous sur cette table. Si l'escalade de verrous réussit, tous les verrous acquis par la transaction dans une instruction antérieure et toujours détenus au moment du démarrage de l'événement sont escaladés si la table est référencée par l'instruction en cours et qu'elle figure dans l'événement d'escalade.

Par exemple, supposons qu'une session effectue les opérations suivantes :

  • Commence une transaction.

  • Met à jour TableA. Cette opération génère dans TableA des verrous de ligne exclusifs détenus jusqu'à la fin de la transaction.

  • Met à jour TableB. Cette opération génère dans TableB des verrous de ligne exclusifs détenus jusqu'à la fin de la transaction.

  • Effectue une opération SELECT qui joint TableA à TableC. Le plan d'exécution de requête demande à ce que les lignes soient extraites de TableA avant d'être extraites de TableC.

  • L'instruction SELECT déclenche une escalade de verrous pendant qu'elle extrait les lignes de TableA et avant d'accéder à TableC.

Si l'escalade de verrous réussit, seuls les verrous détenus par la session sur TableA sont escaladés. Cela inclut les verrous partagés acquis par l'instruction SELECT et les verrous exclusifs acquis par l'instruction UPDATE antérieure Alors que seuls les verrous acquis par la session dans TableA pour l'instruction SELECT sont décomptés pour déterminer si l'escalade de verrous doit être réalisée, une fois que celle-ci a réussi, tous les verrous détenus par la session dans TableA sont escaladés en verrou exclusif sur la table et tous les autres verrous de granularité inférieure de TableA, y compris les verrous intentionnels, sont libérés.

Aucune tentative n'est réalisée pour escalader les verrous de TableB car l'instruction SELECT ne comportait aucune référence active à TableB. De même, aucune tentative n'est réalisée pour escalader les verrous de TableC car l'instruction n'avait toujours pas accédé à cette table au moment de l'escalade.

Seuils d'escalade de verrous

L'escalade de verrous est déclenchée lorsqu'elle n'est pas désactivée sur la table à l'aide de l'option ALTER TABLE SET LOCK_ESCALATION et lorsque l'un ou l'autre des conditions suivantes existe :

  • une instruction Transact-SQL unique acquiert au moins 5 000 verrous sur une table ou un index unique et non partitionné ;

  • une instruction Transact-SQL unique acquiert au moins 5 000 verrous sur une partition unique d'une table partitionnée et l'option ALTER TABLE SET LOCK_ESCALATION a la valeur AUTO ;

  • le nombre de verrous dans une instance du Moteur de base de données dépasse les seuils de mémoire ou de configuration.

Si des verrous ne peuvent pas être escaladés en raison de verrous en conflit, le Moteur de base de données déclenche régulièrement une escalade de verrous chaque fois que le nombre de nouveaux verrous acquis atteint 1 250.

Seuil d'escalade pour une instruction Transact-SQL

L'escalade de verrous est déclenchée lorsqu'une instruction Transact-SQL acquiert au moins 5 000 verrous sur une référence unique d'une table ou d'un index, ou, si la table est partitionnée, une référence unique d'une partition de table ou d'index. Par exemple, l'escalade de verrous n'est pas déclenchée si une instruction acquiert 3 000 verrous dans un index et 3 000 verrous dans un autre index de la même table. De même, l'escalade de verrous n'est pas déclenchée si une instruction possède une jointure réflexive sur une table et que chaque référence à la table n'acquiert que 3 000 verrous dans celle-ci.

L'escalade de verrous ne se produit que pour les tables auxquelles l'instruction a accédé au moment du déclenchement de l'escalade. Supposons une instruction SELECT unique représentant une jointure qui accède successivement à trois tables : TableA, TableB et TableC. L'instruction acquiert 3 000 verrous de ligne dans l'index cluster de TableA et au moins 5 000 verrous de ligne dans l'index cluster de TableB, mais n'a toujours pas accédé à TableC. Lorsque le Moteur de base de données détecte que l'instruction a acquis au moins 5 000 verrous de ligne dans TableB, il essaie d'escalader tous les verrous détenus par la transaction en cours dans TableB. Il essaie également d'escalader tous les verrous détenus par la transaction en cours dans TableA mais, dans la mesure où le nombre de verrous dans TableA est < 5 000, l'escalade échoue. Aucune tentative d'escalade de verrous n'est réalisée pour TableC car l'instruction n'y avait toujours pas accédé lorsque l'escalade s'est produite.

Seuil d'escalade pour une instance du moteur de base de données

Chaque fois que le nombre de verrous est supérieur au seuil de mémoire pour l'escalade de verrous, le Moteur de base de données déclenche l'escalade de verrous. Le seuil de mémoire dépend du paramétrage de l'option de configuration locks :

  • Si l'option locks est paramétrée sur sa valeur par défaut 0, le seuil d'escalade de verrous est atteint lorsque la mémoire utilisée par les objets de verrou représente 24 % de la mémoire utilisée par le Moteur de base de données, à l'exclusion de la mémoire AWE. La structure de données utilisée pour représenter un verrou occupe approximativement 100 octets. Ce seuil est dynamique, car le moteur de base de données acquiert et libère dynamiquement la mémoire en fonction de l'importance des charges de travail.

  • Si l'option locks n'a pas pour valeur 0, le seuil d'escalade de verrous représente 40 % de la valeur de l'option (ou moins en cas d'insuffisance de mémoire).

Le Moteur de base de données peut choisir pour l'escalade n'importe quelle instruction active depuis n'importe quelle session et, chaque fois que 1 250 nouveaux verrous sont acquis, il choisit des instructions pour l'escalade, sous réserve que la mémoire des verrous utilisée dans l'instance demeure au-dessus du seuil.

Escalade de types de verrous mixtes

Lorsque l'escalade de verrous se produit, le verrou sélectionné pour le segment ou pour l'index est en mesure de satisfaire aux conditions du verrou de niveau inférieur le plus contraignant.

Par exemple, supposons la session suivante :

  • Commence une transaction.

  • Met à jour une table contenant un index cluster.

  • Émet une instruction SELECT qui référence la même table.

L'instruction UPDATE acquiert les verrous suivants :

  • Des verrous exclusifs (X) sur les lignes de données mises à jour

  • Des verrous intentionnels exclusifs (IX) sur les pages d'index cluster contenant ces lignes

  • Un verrou IX sur l'index cluster et un autre sur la table

L'instruction SELECT acquiert les verrous suivants :

  • Des verrous partagés (S) sur toutes les lignes de données qu'elle lit, sauf si la ligne est déjà protégée par un verrou X acquis par l'instruction UPDATE

  • Des verrous intentionnels partagés sur toutes les pages d'index cluster contenant ces lignes, sauf si la page est déjà protégée par un verrou IX

  • Aucun verrou sur la table ou sur l'index cluster car ils sont déjà protégés par des verrous IX

Si l'instruction SELECT acquiert suffisamment de verrous pour déclencher l'escalade de verrous et que celle-ci réussit, le verrou IX de la table est converti en verrou X et tous les verrous de ligne, de page et d'index sont libérés. Les mises à jour et les lectures sont protégées par le verrou X de la table.

Réduction du verrouillage et de l'escalade

Dans la plupart des cas, le Moteur de base de données offre les meilleures performances lorsqu'il utilise ses paramètres par défaut de verrouillage et d'escalade de verrous. Si une instance du Moteur de base de données génère beaucoup de verrous et donne lieu à de fréquentes escalades de verrous, pensez à réduire la quantité de verrouillage à l'aide :

  • d'un niveau d'isolement qui ne génère pas de verrous partagés pour les opérations de lecture ;

    • d'un niveau d'isolement READ COMMITTED lorsque l'option de base de données READ_COMMITTED_SNAPSHOT a pour valeur ON ;

    • d'un niveau d'isolement SNAPSHOT ;

    • d'un niveau d'isolement READ UNCOMMITTED. Celui-ci ne peut être utilisé que pour les systèmes qui prennent en charge les lectures incorrectes ;

[!REMARQUE]

La modification du niveau d'isolement affecte toutes les tables sur l'instance du Moteur de base de données.

  • des indicateurs de table PAGLOCK ou TABLOCK afin que le Moteur de base de données utilise des verrous de page, d'index ou de segment au lieu de verrous de ligne. Toutefois, cette option augmente le risque pour un utilisateur de bloquer un autre utilisateur essayant d'accéder aux mêmes données et ne peut être utilisée que dans les systèmes prenant en charge une faible quantité d'utilisateurs simultanés.

  • Pour les tables partitionnées, utilisez l'option LOCK_ESCALATION de ALTER TABLE pour escalader des verrous au niveau du HoBT au lieu de la table ou désactiver l'escalade de verrous.

En outre, vous pouvez utiliser les indicateurs de trace 1211 et 1224 pour désactiver la totalité ou une partie des escalades de verrous. Pour plus d'informations, consultez Indicateurs de trace (Transact-SQL). Par ailleurs, vous pouvez surveiller l'escalade de verrous à l'aide de l'événement Lock:Escalation du SQL Server Profiler et consulter Utilisation du Générateur de profils SQL Server.