Verrouillage optimisé
S’applique à :
Azure SQL Database
Cet article présente la fonctionnalité de verrouillage optimisée, une nouvelle fonctionnalité SQL Server Moteur de base de données qui offre un mécanisme de verrouillage des transactions amélioré qui réduit la consommation de mémoire de verrouillage et le blocage des verrous pour les transactions simultanées.
Qu’est-ce que le verrouillage optimisé ?
Le verrouillage optimisé permet de réduire la mémoire de verrou, car très peu de verrous sont conservés pour les transactions volumineuses. 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 et verrouillage ID detransaction (TID) 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 de 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).
- Lock after 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 Lock after qualification (LAQ).
Par exemple :
- Sans verrouillage optimisé, la mise à jour de 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 de 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étail.
Disponibilité
Actuellement, le verrouillage optimisé est disponible uniquement 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. Connecter à votre base de données, puis utilisez la requête suivante pour case activée 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 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 rcSI (Read Commit instantané isolation) doit être activée pour la base de données.
Adr et RCSI sont activés par défaut dans Azure SQL Database. Pour vérifier que ces options sont activées pour votre base de données active, 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 de verrouillage des transactions et de contrôle de version des lignes.
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 que 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 interblocages, 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 de la 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. Cette TID est conservée 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 la TID de la ligne. La transaction de modification contiendra un verrou X sur son TID. D’autres transactions acquiert un verrou S sur le TID pour case activée si la première transaction est toujours active. Avec le verrouillage TID, les verrous de page et de ligne continuent d’être pris 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, en remplaçant 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 de verrou, car très peu de verrous sont conservés pour les transactions volumineuses. En outre, le verrouillage optimisé évite également les escalades de verrous. Cela permet à d’autres transactions simultanées d’accéder à la table.
Considérez 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;
La même requête sans l’avantage du verrouillage optimisé crée quatre verrous :
L’sys.dm_tran_locks vue de gestion dynamique (DMV) 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 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 case activée ligne par ligne dans une analyse en prenant d’abord un verrou de ligne de mise à jour (U). 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 rcSI (Read Commit instantané) est activé, les prédicats sont appliqués à la dernière version validée sans prendre de verrous de ligne. Si le prédicat ne répond pas, la requête passe à la ligne suivante de l’analyse. Si le prédicat est satisfait, 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 TRANUPDATE t1SET b=b+10WHERE a=1; |
|
BEGIN TRANUPDATE t1SET b=b+10WHERE 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 satisfait pas le prédicat de session 2.
Si le prédicat est satisfait, nous attendons la fin d’une transaction active sur la ligne. Si nous avons dû attendre le verrou S TID, 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, l’Moteur de base de données réessayera 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 TRANUPDATE t2SET b=b+10WHERE a=1; |
|
BEGIN TRANUPDATE t2SET b=b+10WHERE a=1; |
|
COMMIT TRAN |
|
COMMIT TRAN |
Changements de comportement des requêtes avec verrouillage optimisé et RCSI
Les systèmes simultanés en lecture validée instantané niveau d’isolation (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 ; il est donc ignoré et T2 passe à la ligne suivante sans avoir été bloqué 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é
XACTtypes d’attente 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 XACTwait_resource, avec l’intention de lire.LCK_M_S_XACT_MODIFY- Se produit lorsqu’une tâche attend un verrou partagé sur un type XACTwait_resource, avec une intention de modification.LCK_M_S_XACT- Se produit lorsqu’une tâche attend un verrou partagé sur un type XACTwait_resource, où l’intention ne peut pas être déduite. Rare.
- Visibilité des ressources de verrouillage
XACTverrouillage des ressources. Pour plus d’informations, consultezresource_descriptionsys.dm_tran_locks (Transact-SQL).
- Visibilité des ressources d’attente
XACTattendez les ressources. Pour plus d’informations, consultezwait_resourcesys.dm_exec_requests (Transact-SQL).
- Graphique d’interblocage
- Sous chaque ressource du rapport d’interblocage
<resource-list>, chaque<xactlock>élément 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 interblocages.
- Sous chaque ressource du rapport d’interblocage
Meilleures pratiques avec verrouillage optimisé
Activer l’isolation des instantané validées en lecture (RCSI)
Pour optimiser les avantages du verrouillage optimisé, il est recommandé d’activer l’isolation de lecture validée instantané (RCSI) sur la base de données et d’utiliser l’isolation validée en lecture comme niveau d’isolation par défaut. Si ce n’est pas activé, activez RCSI à l’aide de l’exemple suivant :
ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;
Dans Azure SQL Database, 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 enregistreurs et les enregistreurs ne bloquent pas les lecteurs. Les lecteurs lisent une version de la ligne à partir de la instantané prise au début de la requête. Avec LAQ, les enregistreurs 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 attend uniquement si la ligne se qualifie et qu’il existe une transaction d’écriture active sur cette ligne. La qualification basée sur la dernière version validée et le verrouillage uniquement des lignes qualifiées réduit le blocage et augmente 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 enregistreurs 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, la Moteur de base de données est forcée 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 l’avantage 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 l’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ù les indicateurs de verrou sont nécessaires, par exemple lors de la lecture d’une ligne avec upDLOCK, puis la mise à 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, tout en t3 bénéficiant toujours 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 t3 peuvent toujours bénéficier d’un verrouillage optimisé. La même chose s’applique à l’indicateur HOLDLOCK.
Forum aux questions (FAQ)
Où le verrouillage optimisé est-il actuellement disponible ?
Actuellement, le verrouillage optimisé est disponible dans Azure SQL Database.
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 Azure SQL Database, oui.
Comment puis-je détecter si le verrouillage optimisé est activé ?
Voir 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é, 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.
Puis-je désactiver le verrouillage optimisé ?
Actuellement, les clients peuvent créer une demande de support pour désactiver le verrouillage optimisé.
Procédez comme suit pour créer une demande de support à partir du Portail Azure pour Azure SQL Database.
Tout d’abord, vérifiez que le verrouillage optimisé est activé pour votre base de données.
Dans le menu du portail Azure, sélectionnez Aide + support.
Dans Aide + support, sélectionnez Créer une demande de support.
Pour Type de problème, sélectionnez Technique.
Pour l’abonnement, le service et la ressource, sélectionnez la base de données SQL souhaitée.
Dans Résumé, tapez « Désactiver le verrouillage optimisé ».
Pour le type de problème, choisissez Performances et exécution de requête.
Pour le sous-type problème, choisissez Blocage et interblocages.
Dans des détails supplémentaires, fournissez autant d’informations que possible pour la raison pour laquelle vous souhaitez désactiver le verrouillage optimisé. Nous sommes intéressés à examiner les raisons et les cas d’usage pour désactiver le verrouillage optimisé avec vous.
Contenu associé
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour