Partager via

Verrouillage optimisé

  • Article

S’applique à : base de données Azure SQL

Cet article présente le verrouillage optimisé, une nouvelle fonctionnalité du Moteur de base de données qui offre un mécanisme de verrouillage de transaction amélioré réduisant la consommation de mémoire de verrouillage et le blocage pour les transactions simultanées.

Qu’est-ce que le verrouillage optimisé ?

Le verrouillage optimisé permet de réduire la mémoire des verrous, car très peu de verrous sont conservés pour les grandes transactions. En outre, le verrouillage optimisé évite également les escalades de verrous. Cela permet un accès plus simultané à la table.

Le verrouillage optimisé se compose de deux composants principaux : verrouillage d’ID de transaction (TID) et verrouillage après qualification (LAQ).

  • Un ID de transaction (TID) est un identificateur unique d’une transaction. Chaque ligne est étiquetée avec le dernier TID qui l’a modifié. Au lieu d’avoir plusieurs verrous d’identificateur de clé ou de ligne, un seul verrou sur le TID est utilisé. Pour plus d’informations, consultez la section sur le verrouillage de l’ID de transaction (TID).
  • Verrou après qualification (LAQ) est une optimisation qui évalue les prédicats d'une requête sur la dernière version validée de la ligne sans acquérir de verrou, améliorant ainsi la concurrence. Pour plus d’informations, consultez la section sur le verrouillage après qualification (LAQ).

Par exemple :

  • Sans verrouillage optimisé, la mise à jour d’1 million de lignes dans une table peut nécessiter 1 million de verrous de ligne exclusifs (X) conservés jusqu’à la fin de la transaction.
  • Avec le verrouillage optimisé, la mise à jour d’1 million de lignes dans une table peut nécessiter 1 million de verrous de ligne X, mais chaque verrou est libéré dès que chaque ligne est mise à jour, et un seul verrou TID est conservé jusqu’à la fin de la transaction.

Cet article décrit ces deux concepts fondamentaux du verrouillage optimisé en détails.

Disponibilité

Pour l'instant, le verrouillage optimisé n'est disponible que dans Azure SQL Database. Pour plus d’informations, consultez Où le verrouillage optimisé est-il actuellement disponible ?

Le verrouillage optimisé est-il activé ?

Le verrouillage optimisé est activé par base de données utilisateur. Connectez-vous à votre base de données, puis utilisez la requête suivante pour vérifier si le verrouillage optimisé est activé sur votre base de données :

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('testdb', 'IsOptimizedLockingOn');

Si vous n’êtes pas connecté à la base de données spécifiée dans DATABASEPROPERTYEX, le résultat sera NULL. Vous devez recevoir 0 (le verrouillage optimisé est désactivé) ou 1 (activé).

Le verrouillage optimisé s’appuie sur d’autres fonctionnalités de la base de données :

ADR et RCSI sont activés par défaut dans base de données Azure SQL. Pour vérifier que ces options sont activées pour votre base de données actuelle, utilisez la requête T-SQL suivante :

SELECT name
, is_read_committed_snapshot_on
, is_accelerated_database_recovery_on
FROM  sys.databases
WHERE name = db_name();

Vue d’ensemble du verrouillage

Il s’agit d’un bref résumé du comportement lorsque le verrouillage optimisé n’est pas activé. Pour plus d’informations, consultez le Guide du verrouillage des transactions et de la gestion de versions de ligne.

Dans le moteur de base de données, le verrouillage est un mécanisme qui empêche plusieurs transactions de mettre à jour les mêmes données simultanément, afin de protéger l’intégrité et la cohérence des données.

Lorsqu’une transaction doit modifier des données, elle peut demander un verrou sur les données. Le verrou est accordé si aucun autre verrou en conflit n’est conservé sur les données, et la transaction peut poursuivre la modification. Si un autre verrou en conflit est conservé sur les données, la transaction doit attendre que le verrou soit libéré avant de pouvoir continuer.

Lorsque plusieurs transactions sont autorisées à accéder simultanément aux mêmes données, le moteur de base de données doit résoudre des conflits potentiellement complexes avec des lectures et des écritures simultanées. Le verrouillage est l’un des mécanismes par lesquels le moteur de base de données peut fournir la sémantique pour les niveaux d’isolation des transactions ANSI SQL. Bien que le verrouillage dans les bases de données soit essentiel, la concurrence réduite, les blocages, la complexité et la surcharge de verrouillage peuvent avoir un impact sur les performances et l’extensibilité.

Verrouillage optimisé et verrouillage d'ID de transaction (TID).

Chaque ligne du moteur de base de données contient en interne un ID de transaction (TID) lors de l’utilisation du contrôle de version de ligne. Ce TID est conservé sur le disque. Chaque transaction modifiant une ligne marque cette ligne avec son TID.

Avec le verrouillage TID, au lieu de prendre le verrou sur la clé de la ligne, un verrou est pris sur le TID de la ligne. La transaction de modification contient un verrou X sur son TID. D’autres transactions acquièrent un verrou S sur le TID pour vérifier si la première transaction est toujours active. Avec le verrouillage TID, les verrous de page et de ligne continuent d'être acquis pour les mises à jour, mais chaque page et verrou de ligne est libéré dès que chaque ligne est mise à jour. Le seul verrou conservé jusqu’à la fin de la transaction est le verrou X sur la ressource TID, qui remplace les verrous de page et de ligne (clé), comme illustré dans la démonstration suivante. (Les autres verrous de base de données et d’objets standard ne sont pas affectés par le verrouillage optimisé.)

Le verrouillage optimisé permet de réduire la mémoire des verrous, car très peu de verrous sont conservés pour les grandes transactions. En outre, le verrouillage optimisé évite également les escalades de verrous. Cela permet à d’autres transactions simultanées d’accéder à la table.

Étudiez l’exemple de scénario T-SQL suivant qui recherche des verrous sur la session active de l’utilisateur :

CREATE TABLE t0
(a int PRIMARY KEY not null
,b int null);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRAN
UPDATE t0
SET b=b+10;

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID
AND resource_type in ('PAGE','RID','KEY','XACT');

COMMIT TRAN
GO
DROP TABLE IF EXISTS t0;

Une capture d’écran du jeu de résultats d’une requête sur sys.dm_tran_locks pour une seule session affiche un seul verrou lorsque le verrouillage optimisé est activé.

La même requête sans l’avantage du verrouillage optimisé crée quatre verrous :

Une capture d’écran du jeu de résultats d’une requête sur sys.dm_tran_locks pour une session unique montre trois verrous lorsque le verrouillage optimisé n’est pas activé.

La vue de gestion dynamique (DMV) sys.dm_tran_locks peut être utile pour examiner ou résoudre les problèmes de verrouillage, notamment l’observation du verrouillage optimisé en action.

Verrouillage optimisé et verrouillage après qualification (LAQ)

En utilisant l’infrastructure de TID, le verrouillage optimisé change la façon dont les prédicats de requête sécurisent les verrous.

Sans verrouillage optimisé, les prédicats des requêtes sont vérifiés ligne par ligne dans une analyse en prenant d’abord un verrou de ligne (U) de mise à jour. Si le prédicat est satisfait, un verrou de ligne X est pris avant de mettre à jour la ligne.

Avec le verrouillage optimisé et lorsque le niveau d’isolation d’instantané validé en lecture (RCSI) est activé, les prédicats sont appliqués à la dernière version validée sans prendre de verrous de ligne. Si le prédicat n’est pas rempli, la requête passe à la ligne suivante de l’analyse. Si le prédicat est rempli, un verrou de ligne X est pris pour mettre à jour la ligne. Le verrou de ligne X est libéré dès que la mise à jour de ligne est terminée, avant la fin de la transaction.

Étant donné que l’évaluation du prédicat est effectuée sans acquérir de verrous, les requêtes simultanées modifiant différentes lignes ne se bloquent pas les unes les autres.

Exemple :

CREATE TABLE t1
(a int not null
,b int null);

INSERT INTO t1 VALUES (1,10),(2,20),(3,30);
GO
Session 1 Session 2
BEGIN TRAN
UPDATE t1
SET b=b+10
WHERE a=1;
BEGIN TRAN
UPDATE t1
SET b=b+10
WHERE a=2;
COMMIT TRAN
COMMIT TRAN

Comportement de blocage des modifications avec verrouillage optimisé dans l’exemple précédent. Sans verrouillage optimisé, la session 2 est bloquée.

Toutefois, avec le verrouillage optimisé, la session 2 ne sera pas bloquée, car la dernière version validée de la ligne 1 contient a=1, ce qui ne remplit pas le prédicat de session 2.

Si le prédicat est rempli, nous attendons la fin de toute transaction active sur la ligne. Si nous avons dû attendre le verrou TID S, la ligne a peut-être changé et la dernière version validée a peut-être changé. Dans ce cas, au lieu d’arrêter la transaction en raison d’un conflit de mise à jour, le moteur de base de données retente l’évaluation du prédicat sur la même ligne. Si le prédicat se qualifie lors de la nouvelle tentative, la ligne est mise à jour.

Prenons l’exemple suivant lorsqu’une modification de prédicat est automatiquement retentée :

CREATE TABLE t2
(a int not null
,b int null);

INSERT INTO t2 VALUES (1,10),(2,20),(3,30);
GO
Session 1 Session 2
BEGIN TRAN
UPDATE t2
SET b=b+10
WHERE a=1;
BEGIN TRAN
UPDATE t2
SET b=b+10
WHERE a=1;
COMMIT TRAN
COMMIT TRAN

Changements de comportement des requêtes avec verrouillage optimisé et RCSI

Les systèmes simultanés sous le niveau d’isolation de capture instantanée en lecture validée (RCSI) avec des charges de travail qui s’appuient sur un ordre d’exécution strict des transactions, peuvent rencontrer un comportement de requête différent lorsque le verrouillage optimisé est activé.

Prenons l’exemple suivant où la transaction T2 met à jour la table t1 en fonction de la colonne b mise à jour pendant la transaction T1.

CREATE TABLE t1 (a int not null, b int null);

INSERT INTO t1 VALUES (1,1);
GO
Session 1 Session 2
BEGIN TRAN T1
UPDATE t1
SET b=2
WHERE a=1;
BEGIN TRAN T2
UPDATE t1
SET b=3
WHERE b=2;
COMMIT TRAN
COMMIT TRAN

Nous allons évaluer le résultat du scénario ci-dessus avec et sans verrou après qualification (LAQ), partie intégrante du verrouillage optimisé.

Sans LAQ

Sans LAQ, la transaction T2 est bloquée et attend la fin de la transaction T1.

Une fois les deux transactions validées, la table t1 contient les lignes suivantes :

 a | b
 1 | 3

Avec LAQ

Avec LAQ, la transaction T2 utilise la dernière version validée de la ligne b (b=1 dans le magasin de versions) pour évaluer son prédicat (b=2). Cette ligne ne se qualifie pas ; elle est donc ignorée et T2 passe à la ligne suivante sans avoir été bloquée par la transaction T1. Dans cet exemple, LAQ supprime le blocage, mais entraîne des résultats différents.

Une fois les deux transactions validées, la table t1 contient les lignes suivantes :

 a | b
 1 | 2

Important

Même sans LAQ, les applications ne doivent pas supposer que SQL Server (sous niveaux d’isolation de contrôle de version) garantit un ordre strict, sans utiliser d’indicateurs de verrouillage. Notre recommandation générale pour les clients sur les systèmes simultanés sous RCSI avec des charges de travail qui s’appuient sur un ordre d’exécution strict des transactions (comme indiqué dans l’exercice précédent) consiste à utiliser des niveaux d’isolation plus stricts.

Ajouts de diagnostic pour le verrouillage optimisé

Pour prendre en charge la surveillance et la résolution des problèmes de blocage et de blocage avec verrouillage optimisé, recherchez les ajouts suivants :

  • Types d’attente pour le verrouillage optimisé
    • Types d’attente XACT et descriptions de ressources dans sys.dm_os_wait_stats (Transact-SQL) :
      • LCK_M_S_XACT_READ - Se produit lorsqu’une tâche attend un verrou partagé sur un type XACT wait_resource, avec l’intention de lire.
      • LCK_M_S_XACT_MODIFY - Se produit lorsqu’une tâche attend un verrou partagé sur un type XACT wait_resource, avec l’intention de modifier.
      • LCK_M_S_XACT - Se produit lorsqu’une tâche attend un verrou partagé sur un type XACT wait_resource, avec une intention qui ne peut pas être déduite. Rare.
  • Visibilité des ressources de verrouillage
  • Visibilité des ressources d’attente
  • Graphique de blocage
    • Sous chaque ressource du rapport de blocage <resource-list>, chaque élément <xactlock> signale les ressources sous-jacentes et des informations spécifiques pour les verrous de chaque membre d’un blocage. Pour plus d’informations et un exemple, consultez Verrouillage optimisé et blocages.

Meilleures pratiques avec verrouillage optimisé

Activer l'isolement de capture instantanée à lecture validée (RCSI)

Pour optimiser les avantages du verrouillage optimisé, il est recommandé d’activer l’isolation de capture instantanée validée en lecture (RCSI) sur la base de données et d’utiliser l’isolation validée en lecture comme niveau d’isolation par défaut. Si elle n’est pas activée, activez RCSI à l’aide de l’exemple suivant :

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

Dans base de données Azure SQL, RCSI est activé par défaut et la lecture validée est le niveau d’isolation par défaut. Avec RCSI activé et lors de l’utilisation du niveau d’isolation validé en lecture, les lecteurs ne bloquent pas les writers et les writers ne bloquent pas les lecteurs. Les lecteurs lisent une version de la ligne à partir de l’instantané pris au début de la requête. Avec LAQ, les writers qualifient les lignes par prédicat en fonction de la dernière version validée de la ligne sans acquérir de verrous U. Avec LAQ, une requête n'attend que si la ligne remplit les conditions requises et qu'il existe une transaction d'écriture active sur cette ligne. La qualification sur la base de la dernière version validée et le verrouillage des seules lignes qualifiées réduisent les blocages et augmentent la concurrence.

En plus du blocage réduit, la mémoire de verrou requise sera réduite. Cela est dû au fait que les lecteurs ne prennent pas de verrous et que les writers ne prennent que des verrous de courte durée, au lieu de verrous qui expirent à la fin de la transaction. Lorsque vous utilisez des niveaux d'isolation plus stricts comme la lecture reproductible ou sérialisable, le moteur de base de données est forcé de conserver les verrous de ligne et de page jusqu'à la fin de la transaction, pour les lecteurs et les enregistreurs, ce qui entraîne un blocage accru et une mémoire de verrou.

Éviter les indicateurs de verrouillage

Bien que les indicateurs de table et de requête soient respectés, ils réduisent les avantages du verrouillage optimisé. Les indicateurs de verrouillage tels que UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, etc. dans vos requêtes réduisent les avantages complets du verrouillage optimisé. L’utilisation de ces indicateurs de verrou dans les requêtes force le Moteur de base de données à prendre des verrous de ligne/page et à les conserver jusqu’à la fin de la transaction, pour respecter l’intention des indicateurs de verrou. Certaines applications ont une logique où des indicateurs de verrou sont nécessaires, par exemple lors de la lecture d’une ligne avec la sélection UPDLOCK, puis en la mettant à jour ultérieurement. Nous vous recommandons d’utiliser des indicateurs de verrou uniquement si nécessaire.

Avec le verrouillage optimisé, il n’existe aucune restriction sur les requêtes existantes et les requêtes n’ont pas besoin d’être réécrites. Les requêtes qui n’utilisent pas d’indicateurs bénéficient le plus du verrouillage optimisé.

Un indicateur de table sur une table d’une requête ne désactive pas le verrouillage optimisé pour les autres tables de la même requête. En outre, le verrouillage optimisé affecte uniquement le comportement de verrouillage des tables mises à jour par une instruction UPDATE. Par exemple :

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

Dans l’exemple de requête précédent, seule la table t4 sera affectée par l’indicateur de verrouillage, tandis que t3 peut toujours bénéficier d’un verrouillage optimisé.

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

Dans l’exemple de requête précédent, seule la table t3 utilise le niveau d’isolation de lecture reproductible et conserve les verrous jusqu’à la fin de la transaction. D’autres mises à jour vers t3 peuvent toujours bénéficier d’un verrouillage optimisé. Il en va de même pour l’indicateur HOLDLOCK.

Forum aux questions (FAQ)

Où le verrouillage optimisé est-il actuellement disponible ?

Pour l'instant, le verrouillage optimisé est disponible dans base de données Azure SQL.

Le verrouillage optimisé est disponible dans les niveaux de service suivants :

  • tous les niveaux de service DTU
  • tous les niveaux de service vCore, y compris provisionné et serverless

Le verrouillage optimisé n’est actuellement pas disponible dans :

  • Azure SQL Managed Instance
  • SQL Server 2022 (16.x)

Le verrouillage optimisé est-il activé par défaut dans les bases de données nouvelles et existantes ?

Dans base de données Azure SQL, oui.

Comment puis-je détecter si le verrouillage optimisé est activé ?

Consultez Le verrouillage optimisé est-il activé ?

Que se passe-t-il lorsque la récupération accélérée de base de données (ADR) n’est pas activée sur ma base de données ?

Si ADR est désactivée, le verrouillage optimisé est automatiquement désactivé.

Que se passe-t-il si je veux forcer les requêtes à bloquer malgré le verrouillage optimisé ?

Pour les clients qui utilisent RCSI, pour forcer le blocage entre deux requêtes lorsque le verrouillage optimisé est activé, utilisez l’indicateur de requête READCOMMITTEDLOCK.

Contenu connexe