Verrouillage optimisé

Article
10/16/2024

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, même 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 verrouillage de l’ID de transaction (TID).
Verrou après qualification (LAQ) est une optimisation qui évalue les prédicats de requête en utilisant la dernière version validée de la ligne sans acquérir de verrou, ce qui améliore la concurrence. Pour plus d’informations, consultez verrouillage après qualification (LAQ).

Par exemple :

Sans verrouillage optimisé, la mise à jour d’un million de lignes dans une table peut nécessiter un million de verrous de ligne exclusifs (X) conservés jusqu’à la fin de la transaction.
Avec le verrouillage optimisé, la mise à jour d’un million de lignes dans une table peut nécessiter un 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. Étant donné que les verrous sont libérés rapidement, l’utilisation de la mémoire des verrous est réduite et l’escalade de verrous est beaucoup moins susceptible de se produire, ce qui améliore la concurrence des charges de travail.

Remarque

L’activation du verrouillage optimisé réduit ou élimine les verrous de ligne et de page acquis par les instructions DML (Data Modification Language) telles que INSERT, UPDATE, DELETE, MERGE. Il n’a aucun effet sur d’autres types de verrous de base de données et d’objets, tels que les verrous de schéma.

Disponibilité

Le verrouillage optimisé est disponible uniquement dans Azure SQL Database, dans tous les niveaux de service et tailles de calcul.

Le verrouillage optimisé n’est actuellement pas disponible dans Azure SQL Managed Instance ou dans SQL Server.

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é :

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

Résultats	Description
`0`	Le verrouillage optimisé est désactivé.
`1`	Le verrouillage optimisé est activé.
`NULL`	Le verrouillage optimisé n’est pas disponible.

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

Le verrouillage optimisé nécessite l'activation de la récupération de base de données accélérée (ADR) sur la base de données.
Pour tirer le meilleur parti du verrouillage optimisé, l’isolation d’instantané validée en lecture (RCSI) doit être activée pour la base de données. Le composant LAQ du verrouillage optimisé est en vigueur uniquement si RCSI est activé.

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, connectez la base de données et exécutez 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 garantir les propriétés ACID des transactions.

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 tentent d’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 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).

Lorsque des niveaux d'isolation basés sur le contrôle de version de ligne sont utilisés ou lorsque l'ADR est activé, chaque ligne de la base de données contient en interne un identifiant de transaction (TID). 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 obtiennent un verrou S sur le TID pour attendre la fin de la première transaction. Avec le verrouillage TID, les verrous de page et de ligne continuent d'être acquis pour les modifications, mais chaque page et verrou de ligne est libéré dès que chaque ligne est modifiée. Le seul verrou conservé jusqu’à la fin de la transaction est le verrou X unique sur la ressource TID, qui remplace les verrous de page et de ligne (clé).

Prenons l’exemple suivant qui montre les verrous de la session active pendant qu’une transaction d’écriture est active :

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

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

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

BEGIN TRANSACTION;

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 TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Si le verrouillage optimisé est activé, la requête ne contient qu’un seul verrou X sur la ressource XACT (transaction).

Si le verrouillage optimisé n’est pas activé, la même requête contient quatre verrous : trois verrous clés X sur chaque ligne et un verrou IX (exclusif de l’intention) sur la page contenant les lignes :

La vue de gestion dynamique (DMV) sys.dm_tran_locks est 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 se basant sur l’infrastructure TID, le verrouillage optimisé change la façon dont les instructions DML telles que INSERT, UPDATE, DELETE et MERGE acquièrent des verrous.

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

Avec le verrouillage optimisé et lorsque le niveau d'isolement de capture instantanée READ COMMITTED (RCSI) est activé, les prédicats sont vérifiés à la dernière version de la ligne validée sans prendre de verrous. 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.

Par 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 TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

Sans verrouillage optimisé, la session 2 est bloquée, car la session 1 contient un verrou U sur la session de ligne 2 doit être mise à jour. Cependant, avec le verrouillage optimisé, la session 2 n'est pas bloquée parce que les verrous U ne sont pas pris, et parce que dans la dernière version validée de la ligne 1, la colonne a est égale à 1, ce qui ne satisfait pas le prédicat de la session 2.

Étant donné que les verrous LAQ U ne sont pas pris, une transaction simultanée peut modifier la ligne une fois que le prédicat a été évalué. Si le prédicat est satisfait et qu’il n’existe aucune autre transaction active sur la ligne (aucun verrou TID X), la ligne est modifiée. S’il existe une transaction active, le moteur de base de données attend qu’elle se termine et réévalue le prédicat au moment de la modification, car l’autre transaction a peut-être modifié la ligne. Si le prédicat est toujours satisfait, la ligne est modifiée.

Prenons l’exemple suivant où l’évaluation du prédicat est automatiquement retentée, car une autre transaction a changé la ligne :

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

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

Session 1	Session 2
`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

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

Les charges de travail simultanées sous RCSI (Isolation de l'instantané en lecture et en engagement) qui s'appuient sur un ordre d'exécution strict des transactions peuvent présenter des différences dans le comportement des requêtes lorsque le verrouillage optimisé est activé.

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

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

Session 1	Session 2
`BEGIN TRANSACTION T1;` `UPDATE t4` `SET b = 2` `WHERE a = 1;`
	`BEGIN TRANSACTION T2;` `UPDATE t4` `SET b = 3` `WHERE b = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

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

Sans LAQ

Sans LAQ, l'instruction UPDATE de la transaction T2 est bloquée, attendant la fin de la transaction T1. Une fois la T1 terminée, la T2 met à jour la ligne définissant la colonne b en 3, car son prédicat est satisfait.

Une fois les deux transactions validées, la table t4 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 où la colonne b est égale à 1 pour évaluer son prédicat (b = 2). La ligne ne remplit pas les conditions requises ; elle est donc ignorée et l'instruction se termine 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 t4 contient les lignes suivantes :

 a | b
 1 | 2

Important

Même sans LAQ, les applications ne doivent pas supposer que le moteur de base de données garantit un ordre strict sans utiliser de conseils de verrouillage lorsque des niveaux d'isolation basés sur la version des lignes sont utilisés. Nous recommandons généralement aux clients qui exécutent des charges de travail concurrentes sous RCSI et qui s'appuient sur un ordre d'exécution strict des transactions (comme dans l'exemple précédent) d'utiliser des niveaux d'isolation plus stricts, tels que REPEATABLE READ et SERIALIZABLE.

Ajouts de diagnostic pour le verrouillage optimisé

Les améliorations suivantes vous aident à surveiller et résoudre les blocages lorsque le verrouillage optimisé est activé :

Types d’attente pour le verrouillage optimisé
- XACT types d’attente pour le verrou S sur le TID et les descriptions des 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. ce qui n’est pas courant.
Visibilité des ressources de verrouillage
- Verrouiller des ressources XACT. Pour plus d'informations, consultez resource_description sys.dm_tran_locks (Transact-SQL).
Visibilité des ressources d’attente
- Ressources d’attente XACT Pour plus d’informations, consultez wait_resource dans sys.dm_exec_requests (Transact-SQL).
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 interblocage. 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 READ COMMITTED comme niveau d’isolation par défaut. S’il n’est pas déjà activé, activez RCSI en vous connectant à la master base de données et en exécutant l’instruction suivante :

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

Dans base de données Azure SQL, RCSI est activé par défaut et READ COMMITTED est le niveau d’isolation par défaut. Avec RCSI activé et lorsque vous utilisez le niveau d’isolation READ COMMITTED, les lecteurs lisent une version de la ligne à partir de l’instantané pris au début de l’instruction. 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 est 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 maintenus jusqu'à la fin de la transaction. Lors de l'utilisation de niveaux d'isolation plus stricts, tels que REPEATABLE READ ou SERIALIZABLE, le moteur de base de données conserve les verrous de ligne et de page jusqu'à la fin de la transaction, même si le verrouillage optimisé est activé, tant pour les lecteurs que pour les écrivains, ce qui entraîne une augmentation du blocage et de l'utilisation de la mémoire de verrouillage.

Éviter les indicateurs de verrouillage

Bien que les indicateurs de table et de requête tels que UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK, etc. soient respectés lorsque le verrouillage optimisé est activé, ils réduisent l’avantage du verrouillage optimisé. Les indications de verrouillage obligent le moteur de base de données à prendre des verrous de ligne ou de page et à les conserver jusqu'à la fin de la transaction, afin de respecter l'intention des indications de verrouillage. Certaines applications ont une logique où des indicateurs de verrou sont nécessaires, par exemple lors de la lecture d’une ligne avec l’indicateur 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'indices sont celles qui bénéficient le plus d'un 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é n'affecte que le comportement de verrouillage des tables mises à jour par une instruction DML telle que INSERT, UPDATE, DELETE, ou MERGE. Par exemple :

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

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

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

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

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

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

Forum aux questions (FAQ)

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.

Le verrouillage optimisé est-il utilisé sur les réplicas secondaires en lecture seule ?

Non, car les instructions DML ne peuvent pas s’exécuter sur des réplicas en lecture seule, et les verrous de ligne et de page correspondants ne sont pas pris.

Le verrouillage optimisé est-il utilisé lors de la modification des données dans tempdb et dans des tables temporaires ?

Pas pour l'instant.

Partage via

Verrouillage optimisé

Qu’est-ce que le verrouillage optimisé ?

Disponibilité

Le verrouillage optimisé est-il activé ?

Vue d’ensemble du verrouillage

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

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

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

Ajouts de diagnostic pour le verrouillage optimisé

Meilleures pratiques avec verrouillage optimisé

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

Éviter les indicateurs de verrouillage

Forum aux questions (FAQ)

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

Comment puis-je détecter si le verrouillage optimisé est 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 ?

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

Le verrouillage optimisé est-il utilisé sur les réplicas secondaires en lecture seule ?

Le verrouillage optimisé est-il utilisé lors de la modification des données dans tempdb et dans des tables temporaires ?

Commentaires

Ressources supplémentaires

Partage via

Verrouillage optimisé

Qu’est-ce que le verrouillage optimisé ?

Disponibilité

Le verrouillage optimisé est-il activé ?

Vue d’ensemble du verrouillage

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

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

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

Ajouts de diagnostic pour le verrouillage optimisé

Meilleures pratiques avec verrouillage optimisé

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

Éviter les indicateurs de verrouillage

Forum aux questions (FAQ)

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

Comment puis-je détecter si le verrouillage optimisé est 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 ?

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

Le verrouillage optimisé est-il utilisé sur les réplicas secondaires en lecture seule ?

Le verrouillage optimisé est-il utilisé lors de la modification des données dans tempdb et dans des tables temporaires ?

Contenu connexe

Commentaires

Ressources supplémentaires