Promotion de verrous (moteur de base de données)
La promotion 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 simultanéité.
Lorsque le Microsoft Moteur de base de données SQL Server acquiert des verrous de bas niveau, il place également des verrous intentionnels sur les objets contenant 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 contenant 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 contenant ces pages. Outre un verrou d'objet (OBJECT) intentionnel sur la table, des verrous intentionnels de segment ou d'arbre binaire (HOBT) sont requis sur :
- l'index non-cluster si les pages sont des pages d'index non-cluster ;
- l'index cluster si les pages sont des pages d'index cluster. Cela inclut les pages de données des tables possédant un index cluster ;
- le segment des pages de données si celles-ci appartiennent à une table dépourvue d'index cluster.
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 promouvoir. 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 promouvoir 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 de promotion de verrous réussit et que le verrou de table complet est acquis, tous les verrous de page (PAGE), de plage de clés (KEY), de niveau ligne (RID), de segment ou d'arbre binaire 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 promotion 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 ne promeut pas les verrous de ligne ou de plage de clés en verrous de page, mais il les promeut directement en verrous de table. De même, les verrous de page sont systématiquement promus en verrous de table.
Si une tentative de promotion 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 de promotion de verrous chaque fois que la quantité de verrous supplémentaires acquis par la transaction atteint 1 250.
Chaque événement de promotion fonctionne essentiellement au niveau d'une instruction Transact-SQL unique. Lorsque l'événement démarre, le Moteur de base de données essaie de promouvoir 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 de promotion. Si l'événement de promotion démarre avant que l'instruction n'ait accédé à une table, aucune tentative n'est réalisée pour promouvoir les verrous sur cette table. Si la promotion 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 promus si la table est référencée par l'instruction en cours et qu'elle figure dans l'événement de promotion.
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 promotion de verrous pendant qu'elle extrait les lignes de TableA et avant d'accéder à TableC.
Si la promotion de verrous réussit, seuls les verrous détenus par la session sur TableA sont promus. 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 la promotion 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 promus 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 promouvoir 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 promouvoir les verrous de TableC car l'instruction n'avait toujours pas accédé à cette table au moment de la promotion.
Seuils de promotion de verrous
La promotion de verrous est déclenchée à l'un des moments suivants :
- lorsqu'une instruction Transact-SQL unique acquiert au moins 5 000 verrous sur une table ou un index unique ;
- lorsque 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 promus en raison de verrous en conflit, le Moteur de base de données déclenche régulièrement une promotion de verrous chaque fois que le nombre de nouveaux verrous acquis atteint 1 250.
Seuil de promotion pour une instruction Transact-SQL
La promotion 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, la promotion 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, la promotion 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.
La promotion de verrous ne se produit que pour les tables auxquelles l'instruction a accédé au moment du déclenchement de la promotion. 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 de promouvoir tous les verrous détenus par la transaction en cours dans TableB. Il essaie également de promouvoir 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, la promotion échoue. Aucune tentative de promotion de verrous n'est réalisée pour TableC car l'instruction n'y avait toujours pas accédé lorsque la promotion s'est produite.
Seuil de promotion 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 la promotion de verrous, le Moteur de base de données déclenche la promotion 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 de promotion 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 de promotion 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 la promotion, 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 la promotion, sous réserve que la mémoire des verrous utilisée dans l'instance demeure au-dessus du seuil.
Promotion de types de verrous mixtes
Lorsque la promotion 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 la promotion 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 la promotion
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 de promotion de verrous. Si une instance du Moteur de base de données génère beaucoup de verrous et donne lieu à de fréquentes promotions 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 ;
- 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.
En outre, vous pouvez utiliser les indicateurs de trace 1211 et 1224 pour désactiver la totalité ou une partie des promotions de verrous. Pour plus d'informations, consultez Indicateurs de trace (Transact-SQL). Par ailleurs, vous pouvez surveiller la promotion de verrous à l'aide de l'événement Lock:Escalation du Générateur de profils SQL Server ; voir Utilisation du Générateur de profils SQL Server.
Voir aussi
Concepts
Niveaux d'isolement du moteur de base de données
Granularité et hiérarchie des verrous
Modes de verrouillage
Compatibilité de verrouillage (moteur de base de données)
Option locks
Autres ressources
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Indicateur de table (T-SQL)
Architecture des tables et des index