Guide du verrouillage des transactions et du versioning des lignes

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Dans une base de données, une mauvaise gestion des transactions conduit souvent à des problèmes de contention et de détérioration des performances dans les systèmes comprenant de nombreux utilisateurs. Plus le nombre d'utilisateurs qui ont accès aux données est grand, plus il est important que les applications utilisent les transactions de manière efficace. Ce guide présente les mécanismes de verrouillage et de contrôle de version de ligne utilisés par le moteur de base de données pour garantir l'intégrité de chaque transaction et contient des informations sur la façon dont les applications peuvent contrôler efficacement les transactions.

Principes de base sur les transactions

Une transaction est une suite d'opérations effectuées comme une seule unité logique de travail. Une unité logique de travail doit posséder quatre propriétés appelées propriétés ACID (Atomicité, Cohérence, Isolation et Durabilité), pour être considérée comme une transaction :

Atomicité
Une transaction doit être une unité de travail indivisible ; soit toutes les modifications de données sont effectuées, soit aucune ne l'est.

Cohérence
Lorsqu'elle est terminée, une transaction doit laisser les données dans un état cohérent. Dans une base de données relationnelle, toutes les règles doivent être appliquées aux modifications apportées par la transaction, afin de conserver l'intégrité de toutes les données. Toutes les structures de données internes, comme les index B-tree ou les listes à chaînage double, doivent être cohérentes à la fin de la transaction.

Isolement
Les modifications effectuées par des transactions concurrentes doivent être isolées transaction par transaction. Une transaction reconnaît les données dans l'état où elles se trouvaient avant d'être modifiées par une transaction simultanée, ou les reconnaît une fois que la deuxième transaction est terminée, mais ne reconnaît jamais un état intermédiaire. Cette propriété est nommée mise en série, car elle permet de recharger les données de départ et de répéter une suite de transactions dont le résultat sur les données sera identique à celui des transactions d'origine.

Durabilité
Lorsqu'une transaction durable est terminée, ses effets sur le système sont permanents. Les modifications sont conservées même en cas de défaillance du système. SQL Server 2014 (12.x) et versions ultérieures activent les transactions durables retardées. Les transactions durables retardées sont validées avant de consigner l'enregistrement du journal des transactions sur le disque. Pour plus d'informations sur la durabilité des transactions retardées, consultez l'article Durabilité des transactions.

Les applications sont chargées de commencer et de terminer les transactions à des points qui garantissent la cohérence logique des données. L'application doit définir la séquence des modifications de données qui laissent les données dans un état cohérent par rapport aux règles de gestion de l'organisation. L'application effectue ces modifications dans une transaction unique afin que le moteur de base de données puisse garantir l'intégrité de la transaction.

Un système de base de données d'entreprise, par exemple une instance du moteur de base de données se doit de fournir des mécanismes permettant de garantir l'intégrité de chaque transaction. Le moteur de base de données fournit :

• Des fonctionnalités de verrouillage permettant d'assurer l'isolement des transactions.

• Des fonctionnalités de consignation assurent la durabilité des transactions. Pour les transactions durables, l'enregistrement du journal est renforcé sur le disque avant les validations des transactions. Ainsi, en cas de défaillance du matériel serveur, du système d'exploitation ou de l'instance du moteur de base de données lui-même, l'instance utilise au redémarrage les journaux des transactions pour restaurer automatiquement toutes les transactions incomplètes jusqu'au moment de la défaillance du système. Les transactions durables retardées sont validées avant de renforcer l'enregistrement du journal des transactions sur le disque. Ces transactions peuvent être perdues en cas de défaillance du système avant que l'enregistrement du journal ne soit renforcé sur le disque. Pour plus d'informations sur la durabilité des transactions retardées, consultez l'article Durabilité des transactions.

• Des fonctionnalités de gestion des transactions qui assurent l'atomicité et la cohérence des transactions. Lorsqu'une transaction a débuté, elle doit se dérouler correctement jusqu'à la fin (validée), sans quoi l'instance du moteur de base de données annule toutes les modifications effectuées sur les données depuis le début de la transaction. Cette opération est appelée restauration d'une transaction, car elle retourne les données telles qu'elles étaient avant ces modifications.

Contrôle des transactions

Le contrôle des transactions par les applications consiste principalement à spécifier des points de début et de fin de chaque transaction. La spécification est effectuée à l'aide des instructions Transact-SQL ou des fonctions d'interface de programmation d'applications (API) de base de données. Le système doit aussi être capable de gérer les erreurs interrompant une transaction avant sa fin normale. Pour plus d'informations, consultez Transactions, Transactions dans ODBC et Transactions dans SQL Server Native Client.

Par défaut, les transactions sont gérées au niveau de la connexion. Lorsqu'une transaction est démarrée lors d'une connexion, toutes les instructions Transact-SQL exécutées lors de cette connexion font partie de la transaction jusqu'à la fin de celle-ci. Toutefois, dans une session MARS (Multiple Active Result Set), une transaction Transact-SQL explicite ou implicite devient une transaction dont l'étendue est définie par traitement gérée au niveau du lot. À la fin du traitement, si une transaction dont l'étendue est définie par traitement n'est pas validée ou restaurée, elle est automatiquement restaurée par le moteur de base de données. Pour plus d’informations, consultez Utilisation de MARS (Multiple Active Result Sets).

Démarrer des transactions

À l'aide des fonctions API et des instructions Transact-SQL, vous pouvez démarrer des transactions en mode explicite, implicite ou validation automatique.

Transactions explicites

Une transaction est explicite si vous définissez le début et la fin de la transaction de manière explicite à l'aide d'une fonction API ou en exécutant les instructions Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ou ROLLBACK WORK. À la fin de la transaction, la connexion revient au mode de transaction sélectionné avant le début de la transaction, qui peut être le mode implicite ou de validation automatique.

Vous pouvez utiliser toutes les instructions Transact-SQL dans une transaction explicite, à l'exception des instructions suivantes :

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• Procédures stockées système de recherche en texte intégral
• sp_dboption pour définir les options de base de données, ou une des procédures système qui modifient la base de données master à l'intérieur de transactions explicites ou implicites.

Transactions en mode de validation automatique

Le mode de validation automatique est le mode de gestion par défaut des transactions du moteur de base de données. Chaque instruction Transact-SQL est validée ou restaurée dès qu'elle se termine. Lorsqu'une instruction est exécutée avec succès, elle est validée ; si une erreur se produit, elle est restaurée. Une connexion à une instance du moteur de base de données fonctionne par défaut en mode de validation automatique si les modes explicite ou implicite n'ont pas été spécifiés pour une transaction. Le mode de validation automatique est également le mode par défaut pour SqlClient, ADO, OLE DB, et ODBC.

Transactions implicites

Lorsqu'une connexion fonctionne en mode de transaction implicite, l'instance du moteur de base de données démarre automatiquement une nouvelle transaction après la validation ou la restauration de la transaction en cours. Vous n'avez pas à définir le début d'une transaction, il vous suffit de valider ou de restaurer chaque transaction. Le mode de transaction implicite génère une succession continue de transactions. Activez le mode de transaction implicite en utilisant une fonction d'API ou l'instruction SET IMPLICIT_TRANSACTIONS ON. Ce mode est également appelé « Autocommit OFF ». Consultez Méthode setAutoCommit (SQLServerConnection).

Lorsque le mode de transaction implicite est activé pour une connexion, l'instance du moteur de base de données démarre automatiquement une transaction lorsqu'il exécute pour la première fois l'une des instructions suivantes :

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

Transactions délimitées au traitement

Uniquement applicable aux ensembles de résultats MARS (Multiple Active Result Set), une transaction Transact-SQL explicite ou implicite qui démarre sous une session MARS devient une transaction dont l’étendue est définie par traitement. Une transaction dont l'étendue est définie par traitement qui n'est pas validée ou restaurée à la fin du traitement est automatiquement restaurée par le moteur de base de données.

Transactions distribuées

Les transactions distribuées sont réparties sur plusieurs serveurs nommés gestionnaires de ressources. La gestion de la transaction doit être coordonnée entre les gestionnaires de ressources par un composant du serveur nommé gestionnaire de transactions. Chaque instance du moteur de base de données peut être utilisée comme gestionnaire de ressources dans les transactions distribuées coordonnées par un gestionnaire de transactions tel que Microsoft Distributed Transaction Coordinator (MS DTC), ou tout autre gestionnaire de transactions prenant en charge les spécifications Open Group XA pour le traitement des transactions distribuées. Pour plus d'informations, consultez la documentation MS DTC.

Une transaction exécutée sur une seule instance du moteur de base de données utilisant plusieurs bases de données, est une transaction distribuée. Cette instance gère la transaction distribuée de manière interne ; elle apparaît comme une transaction locale pour l'utilisateur.

Dans l’application, une transaction distribuée est gérée de manière comparable à une transaction locale. À la fin de la transaction, l'application requiert que la transaction soit validée ou restaurée. La validation d'une transaction distribuée doit être gérée de façon particulière par le gestionnaire de transaction pour minimiser les risques qu'une défaillance du réseau entraîne la validation de la transaction par certains gestionnaires de ressources, alors qu'elle sera restaurée par d'autres. Pour cela, le processus de validation est géré en deux phases, une phase de préparation et une phase de validation, d’où son nom de « validation en deux phases ».

• Phase de préparation

  Lorsque le gestionnaire de transactions reçoit une requête de validation, il envoie une commande de préparation à tous les gestionnaires de ressources concernés par la transaction. Chaque gestionnaire de ressources fait alors tout ce qui est nécessaire pour rendre la transaction durable, et tous les tampons du journal des transactions pour la transaction sont vidés sur le disque. Lorsque chaque gestionnaire de ressources a terminé la phase de préparation, il retourne un message de succès ou d'échec au gestionnaire de transactions. SQL Server 2014 (12.x) a introduit la durabilité différée des transactions. Les transactions durables retardées sont validées avant que les tampons du journal des transactions de chaque gestionnaire de ressources ne soient vidés sur le disque. Pour plus d'informations sur la durabilité des transactions retardées, consultez l'article Durabilité des transactions.

• Phase de validation

  Si le gestionnaire de transactions reçoit des messages de préparation réussie de tous les gestionnaires de ressources, il envoie une commande de validation à chacun d'entre eux. Les gestionnaires de ressources peuvent alors effectuer la validation. Si tous les gestionnaires de ressources signalent le succès de la validation, le gestionnaire de transactions envoie alors une notification de succès à l'application. Si l'un des gestionnaires de ressources indique un échec de la préparation, le gestionnaire de transactions envoie une commande de restauration à chaque gestionnaire de ressources et notifie l'échec de la validation à l'application.

  Les applications du moteur de base de données peuvent gérer les transactions distribuées à l'aide de Transact-SQL ou de l'API de base de données. Pour plus d’informations, consultez BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Terminer les transactions

Terminez les transactions avec une instruction COMMIT ou ROLLBACK, ou au moyen d'une fonction API correspondante.

• Commiter

  Si une transaction est réussie, validez-la. L'instruction COMMIT garantit que toutes les modifications effectuées sur la base de données au cours de la transaction sont permanentes. Cette validation libère également les ressources, telles que les verrous, qui ont été utilisées par la transaction.

• Restaurer

  Si une erreur se produit pendant une transaction ou si l'utilisateur décide de l'abandonner, restaurez-la. Une instruction ROLLBACK annule toutes les modifications effectuées par la transaction en rétablissant les données dans l'état où elles étaient avant le début de celle-ci. Restaurez également les ressources bloquées par la transaction.

Erreurs de traitement au cours d’une transaction

Si une erreur entrave le bon déroulement d'une transaction, le moteur de base de données restaure automatiquement et libère toutes les ressources bloquées par la transaction. Si la connexion réseau du client à une instance du moteur de base de données est interrompue, toutes les transactions en cours associées à cette connexion sont restaurées au moment de la notification de l'instance de l'interruption de connexion. En cas de défaillance de l'application cliente et de panne ou de redémarrage de l'ordinateur client, la connexion est interrompue. L'instance du moteur de base de données restaure toutes les transactions en cours au moment de la notification de la panne par le réseau. Si le client se déconnecte du moteur de base de données, toutes les transactions en attente sont restaurées.

Si une instruction génère une erreur à l'exécution (comme une violation de contrainte) dans un traitement, la réaction par défaut du moteur de base de données est de restaurer seulement l'instruction ayant généré l'erreur. Vous pouvez modifier ce comportement à l’aide de l’instruction SET XACT_ABORT ON. Après l’exécution de SET XACT_ABORT ON, toute erreur d’exécution causée par une instruction déclenche automatiquement la restauration de la transaction en cours. Les erreurs de compilation, comme les erreurs de syntaxe, ne sont pas affectées par SET XACT_ABORT. Pour plus d'informations, consultez SET XACT_ABORT (Transact-SQL).

Quand une erreur se produit, l’action appropriée (COMMIT ou ROLLBACK) doit être incluse dans le code de l’application. Pour gérer efficacement les erreurs, notamment celles qui surviennent au cours de transactions, utilisez la construction Transact-SQL TRY...CATCH. Pour plus d'informations et d'exemples portant sur les transactions, consultez TRY...CATCH (Transact-SQL). À partir de SQL Server 2012 (11.x), vous pouvez utiliser l'instruction THROW pour lever une exception et transférer l'exécution vers un bloc CATCH d'une construction TRY...CATCH. Pour plus d'informations, consultez THROW (Transact-SQL).

Erreurs de compilation et d'exécution en mode de validation automatique

En mode de validation automatique, il peut arriver qu'une instance du moteur de base de données semble restaurer un lot entier au lieu d'une instruction SQL unique. Ceci se produit en cas d'erreur de compilation et non en cas d'erreur d'exécution. Une erreur de compilation empêche le moteur de base de données de créer un plan d'exécution, de telle sorte qu'aucune instruction du traitement ne peut être executée. Bien qu'il semble que toutes les instructions précédant celle qui a produit l'erreur soient restaurées, en réalité l'erreur rend impossible l'exécution de toutes les instructions du lot. Dans l'exemple qui suit, une erreur de compilation empêche l'exécution de toutes les instructions INSERT du troisième lot. Les deux premières instructions INSERT semblent avoir été restaurées alors qu'elles n'ont en fait jamais été exécutées.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

Dans l'exemple ci-dessous, la troisième instruction INSERT génère une erreur d'exécution causée par une clé primaire en double. Les deux premières instructions INSERT étant correctes et validées, elles ne sont pas restaurées après l'erreur d'exécution.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Le moteur de base de données introduit la résolution de nom différée, où la résolution des noms d'objet est effectuée au moment de l'exécution seulement. Dans l'exemple suivant, les deux premières instructions INSERT sont exécutées et validées, et ces deux lignes restent dans la table TestBatch, même une fois que la référence à une table inexistante dans la troisième instruction INSERT a généré une erreur d'exécution.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Principes de base sur le verrouillage et le contrôle de version de ligne

Le moteur de base de données utilise les mécanismes suivants pour garantir l'intégrité des transactions et gérer la cohérence des bases de données lorsque plusieurs utilisateurs accèdent simultanément aux données :

• Verrouillage

  Chaque transaction demande des verrous de différents types sur les ressources dont elle dépend, telles que les lignes, les pages ou les tables. Les verrous demandés empêchent les autres transactions d'apporter aux ressources des modifications susceptibles de nuire à la transaction. Chaque transaction libère ses verrous lorsqu'elle ne dépend plus des ressources verrouillées.

• Gestion de versions de ligne

  Lorsqu'un niveau d'isolation basé sur la version des lignes est utilisé, le moteur de base de données conserve les versions de chaque ligne modifiée. Les applications peuvent, au lieu de protéger toutes les lectures avec des verrous, spécifier qu'une transaction utilise les versions de ligne pour consulter les données telles qu'elles existaient à son démarrage ou à l'exécution de l’instruction. L'utilisation du contrôle de version de ligne réduit sensiblement le risque qu'une opération de lecture bloque les autres transactions.

Le verrouillage et le contrôle de version de ligne empêchent les utilisateurs de lire les données non validées et plusieurs utilisateurs de modifier simultanément les mêmes données. Sans le verrouillage ou le contrôle de version de ligne, les requêtes exécutées sur ces données pourraient produire des résultats inattendus en retournant des données qui ne sont pas encore validées dans la base de données.

Les applications peuvent choisir les niveaux d'isolement de transaction, qui définissent le niveau de protection d'une transaction contre les modifications apportées par les autres transactions. Vous pouvez spécifier des indicateurs de table pour des instructions Transact-SQL spécifiques en fonction des besoins de l'application.

Gestion de l'accès concurrentiel aux données

Lorsque plusieurs utilisateurs accèdent à une ressource en même temps, on parle d'accès concurrentiel. L'accès concurrentiel aux données requiert certains mécanismes permettant de contrer les effets négatifs de la modification d'une ressource déjà en cours d'utilisation.

Effet des accès concurrentiels

Les utilisateurs qui modifient des données peuvent interférer avec d'autres utilisateurs en train de lire ou de modifier les mêmes données en même temps. On dit que ces utilisateurs accèdent aux données de manière concurrentielle. Si une base de données est dépourvue de contrôle des accès concurrentiels, les utilisateurs peuvent constater les effets secondaires suivants :

• Mises à jour perdues

  Les mises à jour perdues se produisent lorsque deux transactions ou plus sélectionnent la même ligne, puis la mettent à jour en fonction de la valeur qui s'y trouvait à l'origine. Aucune transaction n'a connaissance des autres transactions. La dernière mise à jour écrase les mises à jour effectuées par les autres transactions, ce qui entraîne une perte de données.

  Exemple : deux éditeurs font une copie électronique du même document. Chaque éditeur modifie son document et l'enregistre ensuite, en écrasant le document original. Le dernier éditeur à avoir enregistré le document écrase les modifications effectuées par l'autre éditeur. Le problème pourrait être évité en empêchant un éditeur d'accéder au fichier tant que l'autre éditeur n'a pas terminé et validé la transaction.

• Dépendance non validée (lecture erronée)

  Une dépendance non validée se produit lorsqu'une deuxième transaction lit une ligne qui est actuellement mise à jour par une autre transaction. La deuxième transaction lit les données qui n'ont pas encore été validées et qui peuvent être modifiées par la transaction de mise à jour de la ligne.

  Supposons par exemple qu'un éditeur effectue des modifications dans un document électronique. Pendant les modifications, un second éditeur fait une copie du document comprenant toutes les modifications effectuées jusqu'alors et distribue ce dernier aux destinataires concernés. Le premier éditeur décide alors que les modifications effectuées sont incorrectes, les supprime et enregistre le document. Le document qui a été distribué comprend donc des modifications qui n'existent plus et devraient être traitées comme si elles n'avaient jamais existé. Le problème pourrait être évité en interdisant la lecture du document modifié tant que le premier éditeur n'a pas effectué l'enregistrement final des modifications et validé la transaction.

• Analyse incohérente (lecture non reproductible)

  Une analyse incohérente se produit lorsqu'une deuxième transaction accède à la même ligne plusieurs fois et lit différentes données à chaque fois. Une analyse incohérente est similaire à une dépendance non validée en ce sens qu'une autre transaction change les données qu'une deuxième transaction est en train de lire. Cependant, dans une analyse incohérente, les données lues par la deuxième transaction sont validées par la transaction qui a effectué la modification. En outre, une analyse incohérente implique plusieurs lectures (deux ou plus) de la même ligne dont les informations sont systématiquement modifiées par une autre transaction, d'où l'expression de lecture non renouvelable.

  Par exemple, un éditeur relit le même document deux fois, mais l'auteur réécrit le document entre les relectures. Lorsque l'éditeur relit le document pour la seconde fois, le document a changé. La relecture initiale n'est donc pas renouvelable. Ce problème pourrait être évité si l'auteur ne pouvait pas modifier le document tant que l'éditeur n'a pas terminé la dernière lecture.

• Lectures fantômes

  Une lecture fantôme est une situation qui se produit lorsque deux requêtes identiques sont exécutées et que l’ensemble des lignes retournée par la deuxième requête est différent. L'exemple suivant montre comment cela peut arriver. Supposons que les deux transactions ci-dessous s'exécutent en même temps. Les deux instructions SELECT dans la première transaction peuvent retourner des résultats différents parce que l'instruction INSERT dans la deuxième transaction modifie les données utilisées par les deux transactions.

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• Lectures manquantes et en double provoquées par des mises à jour de ligne

  • Manquer une ligne mise à jour ou consulter une ligne mise à jour plusieurs fois

    Les transactions qui s'exécutent au niveau READ UNCOMMITTED (ou les instructions utilisant l'indice de table NOLOCK) n'émettent pas de verrous partagés pour empêcher d'autres transactions de modifier les données lues par la transaction en cours. Les transactions qui s'exécutent au niveau READ COMMITTED génèrent des verrous partagés, mais les verrous de ligne ou de page sont libérés une fois la ligne lue. Dans les deux cas, lorsque vous analysez un index, si un autre utilisateur modifie la colonne de clé d'index de la ligne pendant votre lecture, la ligne peut apparaître de nouveau si la modification apportée à la clé a déplacé la ligne à une position située en aval de votre analyse. De même, la ligne peut ne pas être lue du tout si le changement de clé a déplacé la ligne vers une position de l'index que vous aviez déjà lue. Pour éviter cela, utilisez l’indicateur SERIALIZABLE ou HOLDLOCK, ou le contrôle de version de ligne. Pour plus d’informations, consultez Indicateurs de table (Transact-SQL).

  • Manquer une ou plusieurs lignes qui n'étaient pas la cible de la mise à jour

    Quand vous utilisez READ UNCOMMITTED, si votre requête lit des lignes à l’aide d’une analyse d’ordre d’allocation (à l’aide de pages IAM), vous risquez de manquer des lignes si une autre transaction provoque un fractionnement de page. Cela ne se produit pas lorsque vous utilisez le niveau d’isolation READ COMMITTED.

Types de concurrence

Lorsque plusieurs transactions tentent de modifier des données dans une base de données en même temps, il convient d'implémenter un système de contrôle de manière à ce que les modifications apportées par une transaction n'en pénalisent pas une autre. Ce système s'appelle le contrôle de concurrence.

La théorie du contrôle de concurrence repose sur deux méthodes de classification :

• Contrôle de concurrence pessimiste

  Un système de verrouillage empêche les transactions de modifier des données d'une manière qui affecterait les autres transactions. Lorsqu'une transaction effectue une action qui entraîne l'application d'un verrou, les autres transactions ne peuvent pas effectuer d'actions qui entreraient en conflit avec le verrou jusqu'à ce que le propriétaire le libère. Cette méthode s'appelle le contrôle pessimiste car elle est principalement utilisée dans les environnements où les données sont très sollicitées et où le coût de protection des données par verrouillage est inférieur au coût de restauration des transactions en cas de contentions de concurrence.

• Contrôle de l’accès concurrentiel optimiste

  Dans le cas du contrôle de concurrence optimiste, les transactions ne verrouillent pas les données quand ils les lisent. Cependant, lors d'une mise à jour de transaction, le système vérifie si une autre transaction a modifié les données après leur lecture. Si une autre transaction a mis à jour les données, une erreur est soulevée. Généralement, la transaction recevant l'erreur la restaure et recommence. Cette méthode s'appelle le contrôle optimiste car elle est principalement utilisée dans les environnements où les données sont peu demandées et où le coût de la restauration occasionnelle d'une transaction est inférieur au coût du verrouillage des données lors de la lecture.

Le moteur de base de données prend en charge les deux méthodes de contrôle d’accès concurrentiel. Les utilisateurs spécifient le type de contrôle de concurrence lorsqu'ils choisissent les niveaux d'isolement des transactions pour les connexions et les options de concurrence sur les curseurs. Ces attributs peuvent être définis à l'aide des instructions Transact-SQL ou des propriétés et attributs des interfaces de programmation d'applications (API) de base de données telles que ADO, ADO.NET, OLE DB et ODBC.

Niveaux d'isolement du moteur de base de données

Les transactions spécifient un niveau d'isolation qui définit le degré auquel une transaction doit être isolée des modifications de ressources ou de données effectuées par d'autres transactions. Les niveaux d'isolation déterminent les effets secondaires de la concurrence (lectures incorrectes, lectures fantômes) qui sont autorisés.

Le niveau d'isolation d'une transaction régit les éléments suivants :

• l'acquisition de verrous lors de la lecture de données, et le type de verrous nécessaires ;
• la durée de vie des verrous de lecture ;
• la réaction d'une opération de lecture qui fait référence à des lignes modifiées par une autre transaction :
  • blocage jusqu'à ce que le verrou exclusif sur la ligne soit levé,
  • récupération de la version validée de la ligne telle qu'elle était au début de l'instruction ou de la transaction,
  • lecture de la modification des données non validées.

Un niveau d'isolation plus faible augmente la capacité de nombreuses transactions à accéder aux données en même temps, mais augmente également le nombre d'effets de concurrence (tels que les lectures sales ou les mises à jour perdues) auxquels les transactions peuvent être confrontées. Inversement, plus le niveau d'isolation est élevé, plus le nombre de types d'effets secondaires de la concurrence qu'une transaction est susceptible de rencontrer est réduit. Cependant, la quantité de ressources système nécessaires et la probabilité d'un blocage mutuel de transactions sont plus élevées. Le choix du niveau d'isolation adéquat dépend d'une mise en équilibre de l'espace réservé et des exigences en matière d'intégrité des données de l'application. Le niveau le plus élevé, SERIALIZABLE, garantit qu'une transaction récupère exactement les mêmes données à chaque fois qu'elle répète une opération de lecture, mais en utilisant un niveau de verrouillage susceptible de gêner les autres transactions dans les systèmes multi-utilisateurs. Le niveau d'isolation le plus bas, READ UNCOMMITTED, permet la récupération de données qui ont été modifiées mais non validées par d'autres transactions. Tous les effets secondaires de la concurrence peuvent se produire dans READ UNCOMMITTED, mais il n'y a pas de verrouillage en lecture ou de version, ce qui permet de minimiser les frais généraux.

Niveaux d'isolation du moteur de base de données

La norme ISO définit les niveaux d'isolation suivants, tous pris en charge par le moteur de base de données :

Niveau d’isolation	Définition
READ UNCOMMITTED	Le niveau d'isolation le plus bas et suffisant pour s'assurer que les données physiquement inconsistantes ne sont pas lues. À ce niveau, les lectures de modifications sont autorisées. Ainsi, une transaction peut afficher les modifications qui ne sont pas encore validées apportées par d'autres transactions.
READ COMMITTED	Permet à une transaction de lire des données lues auparavant (non modifiées) par une autre transaction, sans attendre la fin de la première transaction. Le moteur de base de données conserve les verrous d'écriture (acquis sur les données sélectionnées) jusqu'à la fin de la transaction, mais les verrous de lecture sont libérés dès que l'opération de lecture est effectuée. Il s'agit du niveau par défaut pour le moteur de base de données.
REPEATABLE READ	Le moteur de base de données conserve les verrous de lecture et d'écriture qui sont acquis sur les données sélectionnées jusqu'à la fin de la transaction. Cependant, étant donné que les verrous d'étendus ne sont pas gérés, des lectures fantômes peuvent se produire.
SERIALIZABLE	Niveau le plus élevé, dans lequel les transactions sont totalement isolées les unes des autres. Le moteur de base de données conserve les verrous de lecture et d'écriture acquis sur les données sélectionnées jusqu'à la fin de la transaction. Les verrouillages de plage sont acquis lorsqu'une opération SELECT utilise une clause WHERE de plage afin d'éviter les lectures fantômes. Remarque : les opérations DDL et les transactions sur les tables répliquées peuvent échouer quand le niveau d’isolation SERIALIZABLE est demandé. Cela est dû au fait que les requêtes de réplication utilisent des indicateurs qui peuvent être incompatibles avec le niveau d'isolation SERIALIZABLE.

Le moteur de base de données prend également en charge deux niveaux d’isolation de la transaction supplémentaires qui utilisent le contrôle de version de ligne. Il s’agit d’une implémentation du niveau d’isolation READ COMMITTED, et il s’agit du niveau d’isolation de la transaction SNAPSHOT.

Niveau d'isolement basé sur le contrôle de version de ligne	Définition
Read Committed Snapshot (RCSI)	Lorsque l’option de base de données READ_COMMITTED_SNAPSHOT est définie sur ON, qui est le paramètre par défaut dans Azure SQL Database, le niveau d’isolation READ COMMITTED utilise le contrôle de version de ligne pour fournir une cohérence de lecture au niveau de l’instruction. Les opérations de lecture ont besoin uniquement de verrous de table de schéma de stabilité (Sch-S), et pas de verrous de page ni de ligne. À savoir, le moteur de base de données utilise le contrôle de version de ligne pour présenter à chaque instruction un instantané cohérent des données (du point de vue transactionnel) telles qu'elles étaient au début de l'instruction. Les verrous ne sont pas utilisés pour protéger les données des mises à jour par d'autres transactions. Une fonction définie par l'utilisateur peut retourner des données qui ont été validées après l'heure de début de l'instruction contenant cette fonction. Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est définie sur OFF, ce qui est le paramètre par défaut dans SQL Server et Azure SQL Managed Instance, l'isolation READ COMMITTED utilise des verrous partagés pour empêcher d'autres transactions de modifier des lignes pendant que la transaction en cours exécute une opération de lecture. Les verrous partagés empêchent également l'instruction de lire des lignes modifiées par d'autres transactions, tant que celles-ci ne sont pas terminées. Les deux variantes sont conformes à la définition ISO de l'isolation READ COMMITTED.
SNAPSHOT	Le niveau d'isolation d'instantané utilise le contrôle de version de ligne pour assurer la cohérence des lectures au niveau de la transaction. Les opérations de lecture n'acquièrent aucun verrous de page ou de ligne ; seuls les verrous de table de stabilité du schéma (Sch-S) sont acquis. Lors de la lecture de lignes modifiées par une autre transaction, les opérations de lecture récupèrent la version de la ligne qui existait au moment du démarrage de la transaction. Vous ne pouvez utiliser l’isolation SNAPSHOT que lorsque l’option ALLOW_SNAPSHOT_ISOLATION de la base de données est définie sur ON. Par défaut, cette option est définie sur OFF pour les bases de données utilisateur dans SQL Server et Azure SQL Managed Instance, et définie sur ON les bases de données dans Azure SQL Database. Remarque : le moteur de base de données ne prend pas en charge le contrôle de version des métadonnées. Pour cette raison, il existe des restrictions sur les opérations DDL pouvant être effectuées dans une transaction explicite exécutée avec le niveau d'isolement d'instantané. Les instructions DDL suivantes ne sont pas autorisées sous isolation d’instantané après une instruction BEGIN TRANSACTION : ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, ou toute instruction CLR (Common Language Runtime) DDL. Ces instructions sont autorisées quand vous utilisez l’isolement d’instantané au sein de transactions implicites. Par définition, une transaction implicite est une instruction unique qui permet d'appliquer la sémantique de l'isolation d'instantané, même avec des instructions DDL. Tout manquement à ce principe peut provoquer l’erreur 3961 : Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

Le tableau suivant répertorie les effets secondaires de la concurrence provoqués par les différents niveaux d'isolation.

Niveau d'isolation	Lecture incorrecte	Lecture non renouvelable	Fantôme
READ UNCOMMITTED	Oui	Oui	Oui
READ COMMITTED	No	Oui	Oui
REPEATABLE READ	No	Non	Oui
SNAPSHOT	No	Non	Non
SERIALIZABLE	Non	Non	Non

Pour plus d'informations sur les types spécifiques de verrouillage ou de contrôle de version de ligne contrôlés par chaque niveau d'isolation de la transaction, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Les niveaux d'isolement des transactions peuvent être définis à l'aide de Transact-SQL ou d'une API de base de données.

Transact-SQL
Les scripts Transact-SQL utilisent l'instruction Transact-SQL SET TRANSACTION ISOLATION LEVEL.

ADO
Les applications ADO définissent la propriété IsolationLevel de l'objet Connection sur adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead ou adXactReadSerializable.

ADO.NET
ADO.NET applications utilisant l'espace de noms managé System.Data.SqlClient peut appeler la méthode SqlConnection.BeginTransaction et définir l'option IsolationLevel sur Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable ou Snapshot.

OLE DB
Lors du démarrage d'une transaction, les applications utilisant l'appel OLE DB ITransactionLocal::StartTransaction avec isoLevel défini sur ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT ou ISOLATIONLEVEL_SERIALIZABLE.

Lorsque vous spécifiez le niveau d'isolation des transactions en mode de validation automatique, les applications OLE DB peuvent définir la propriété DBPROP_SESS_AUTOCOMMITISOLEVELS de DBPROPSET_SESSION définie sur DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED ou DBPROPVAL_TI_SNAPSHOT.

ODBC
Appel d'applications ODBC SQLSetConnectAttr avec Attribute défini sur SQL_ATTR_TXN_ISOLATION et ValuePtr défini sur SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ ou SQL_TXN_SERIALIZABLE.

Pour les transactions d'instantanés, les applications appellent SQLSetConnectAttr avec l'attribut défini sur SQL_COPT_SS_TXN_ISOLATION et ValuePtr défini sur SQL_TXN_SS_SNAPSHOT. Une transaction d'instantané peut être récupérée en utilisant soit SQL_COPT_SS_TXN_ISOLATION soit SQL_ATTR_TXN_ISOLATION.

Verrouillage du moteur de base de données

Le verrouillage est un mécanisme utilisé par le moteur de base de données pour synchroniser l'accès simultané de plusieurs utilisateurs à la même donnée.

Avant qu'une transaction acquière une dépendance sur l'état actuel d'un élément de données, par exemple par sa lecture ou la modification d'une donnée, elle doit se protéger des effets d'une autre transaction qui modifie la même donnée. Pour ce faire, la transaction demande un verrou sur l'élément de données. Le verrou possède plusieurs modes, par exemple partagé (S) ou exclusif (X). Le mode de verrouillage définit le niveau de dépendance de la transaction sur les données. Aucune transaction ne peut obtenir un verrou qui entrerait en conflit avec le mode d'un verrou déjà accordé sur ces données à une autre transaction. Si une transaction demande un mode de verrouillage qui est en conflit avec un verrou déjà accordé aux mêmes données, le moteur de base de données suspend la transaction concernée jusqu'à ce que le premier verrou soit libéré.

Lorsqu'une transaction modifie une donnée, elle conserve le verrou qui protège la modification jusqu'à la fin de la transaction. La durée pendant laquelle une transaction conserve le verrou acquis pour protéger les opérations de lecture dépend de la configuration du niveau d'isolement de la transaction et du fait que le verrouillage optimisé est activé ou non.

• Lorsque le verrouillage optimisé n'est pas activé, les verrous de ligne et de page nécessaires pour les écritures sont conservés jusqu'à la fin de la transaction.

• Lorsque le verrouillage optimisé est activé, seul un verrou d'ID de transaction (TID) est maintenu jusqu'à la fin de la transaction. Sous le niveau d'isolation par défaut READ COMMITTED, les transactions ne contiennent pas les verrous de ligne et de page nécessaires pour les écritures jusqu'à la fin de la transaction. Cela réduit la mémoire de verrou requise et réduit la nécessité d'une escalade de verrous. En outre, lorsque le verrouillage optimisé est activé, l'optimisation du verrou après qualification (LAQ) é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.

Tous les verrous conservés par une transaction sont libérés lorsque cette dernière est terminée (validée ou restaurée).

En général, les applications ne demandent pas de verrous directement. Les verrous sont gérés en interne par une partie du moteur de base de données, nommée gestionnaire de verrous. Lorsqu'une instance du moteur de base de données traite une instruction Transact-SQL, le processeur de requêtes du moteur de base de données détermine les ressources à accéder. Le processeur de requêtes détermine les types de verrou nécessaires pour protéger chaque ressource, en fonction du type d'accès et de la configuration du niveau d'isolement de la transaction. Le processeur de requêtes demande ensuite les verrous appropriés auprès du gestionnaire de verrous. Le gestionnaire de verrous accorde les verrous s'il n'existe aucun verrou en conflit détenu par d'autres transactions.

Granularité et hiérarchie des verrous

Le moteur de base de données possède un verrouillage multigranulaire qui permet à différents types de ressources d'être verrouillés par une transaction. Pour minimiser le coût du verrouillage, le moteur de base de données verrouille automatiquement les ressources au niveau approprié pour la tâche. Le verrouillage à un faible niveau de granularité (tel que les lignes) augmente la simultanéité d'accès, mais à un coût plus élevé, puisqu'un grand nombre de verrous doit être maintenu si de nombreuses lignes sont verrouillées. Le verrouillage à un niveau de granularité élevé (tel que les tables) est coûteux en termes de simultanéité d'accès, car le verrouillage d'une table entière empêche les autres transactions d'accéder à d'autres parties de la table. Cependant, son coût est moindre puisque les verrous sont peu nombreux.

Le moteur de base de données doit souvent acquérir des verrous à plusieurs niveaux de granularité pour protéger intégralement une ressource. Ce groupe de verrous à plusieurs niveaux de granularité est appelé « hiérarchie des verrous ». Par exemple, pour protéger complètement la lecture d'un index, une instance du moteur de base de données devra peut-être acquérir des verrous partagés sur les lignes et des verrous partagés Intent sur les pages et la table.

Le tableau suivant répertorie les ressources que le moteur de base de données peut verrouiller.

Ressource	Description
RID	Identificateur de ligne utilisé pour verrouiller une seule ligne dans un segment de mémoire.
KEY	Verrou de ligne pour verrouiller une seule ligne dans un index B-tree.
PAGE	Page de 8 kilo-octets (Ko) dans une base de données, par exemple des pages de données ou d'index.
EXTENT	Groupe contigu de huit pages, par exemple des pages de données ou d'index.
HoBT 1	Segment de mémoire ou arbre B (B-tree). Verrou protégeant un arbre B (B-tree) (index) ou les pages de données de segment de mémoire d'une table ne possédant pas d'index cluster.
TABLE 1	Table complète comprenant tous les index et toutes les données.
FILE	Fichier de base de données.
APPLICATION	Ressource spécifiée par une application.
METADATA	Verrous des métadonnées.
ALLOCATION_UNIT	Unité d'allocation.
DATABASE	Base de données complète.
XACT 2	Verrou d'ID de transaction (TID) utilisé dans le verrouillage optimisé. Pour plus d’informations, consultez verrouillage de l’ID de transaction (TID).

¹ Les verrous HoBT et TABLE peuvent être affectés par l'option LOCK_ESCALATION de ALTER TABLE.

² ressources de verrouillage supplémentaires sont disponibles pour les ressources de verrouillage XACT, consultez Ajouts de diagnostic pour le verrouillage optimisé.

Modes de verrouillage

Le moteur de base de données verrouille les ressources en utilisant différents modes de verrouillage qui déterminent le mode d'accès aux ressources par des transactions simultanées.

Le tableau suivant illustre les modes de verrouillage des ressources utilisés par le moteur de base de données.

Mode de verrouillage	Description
Partagé (S)	Utilisé pour les opérations de lecture qui n’effectuent aucune modification ni mise à jour des données, par exemple une instruction SELECT.
Mise à jour (U)	Utilisé pour les ressources pouvant être mises à jour. Empêche une forme de blocage courante qui se produit lorsque plusieurs sessions lisent, verrouillent et mettent à jour des ressources ultérieurement.
Exclusif (X)	Utilisé pour les opérations de modification de données, telles que INSERT, UPDATE ou DELETE. Empêche des mises à jour multiples sur la même ressource au même moment.
Intention	Permet d'établir une hiérarchie de verrouillage. Les types de verrouillage intentionnels sont les suivants : les verrous de partage intentionnel (IS), d'exclusion intentionnelle (IX) et de partage intentionnel exclusif (SIX).
Schéma	Utilisé lors de l'exécution d'une opération associée au schéma d'une table. Les types de verrouillage de schéma sont les suivant : la stabilité du schéma (Sch-M) et la modification du schéma (Sch-S).
Mise à jour en bloc (BU)	Utilisé lors de la copie en bloc de données dans une table avec l’indicateur TABLOCK.
Groupe de clés	Protège la plage de lignes lue par une requête lorsque le niveau d'isolation des transactions SERIALIZABLE est utilisé. Garantit qu'aucune autre transaction ne peut insérer des lignes susceptibles de répondre aux requêtes de la transaction SERIALIZABLE si ces dernières étaient réexécutées.

Verrous partagés

Les verrous partagés (S) permettent à des transactions simultanées de lire une ressource dans des conditions de contrôle d'accès concurrentiel pessimiste. Aucune autre transaction ne peut modifier les données de la ressource tant que des verrous partagés (S) existent sur la ressource. Les verrous partagés (S) sur une ressource sont enlevés dès que l'opération de lecture est terminée, à moins que le niveau d'isolation de la transaction soit de type REPEATABLE READ ou plus élevé, ou qu'un indicateur de verrouillage conserve les verrous partagés (S) pendant toute la durée de la transaction.

Verrous de mise à jour

Le moteur de base de données place les verrous de mise à jour (U) lors de sa préparation à l'exécution d'une mise à jour. Les verrous U sont compatibles avec les verrous S, mais une seule transaction peut contenir un verrou U à la fois sur une ressource donnée. Il s'agit d'une clé : de nombreuses transactions simultanées peuvent contenir des verrous S, mais une seule transaction peut contenir un verrou U sur une ressource. Les verrous de mise à jour (U) sont finalement mis à niveau vers des verrous exclusifs (X) pour mettre à jour une ligne.

Les verrous de mise à jour (U) peuvent également être pris par des instructions autres que UPDATE, lorsque l’indicateur de table UPDLOCK est spécifié dans l’instruction.

• Certaines applications utilisent un modèle « sélectionner une ligne, puis mettre à jour la ligne », où la lecture et l'écriture sont explicitement séparées dans la transaction. Dans ce cas, si le niveau d’isolation est REPEATABLE READ ou SERIALIZABLE, les mises à jour simultanées peuvent provoquer un blocage, comme suit :

  Une transaction lit les données en obtenant un verrou partagé (S) sur la ressource, puis modifie ces données, ce qui nécessite une conversion du verrou en mode exclusif (X). Si deux transactions acquièrent des verrous partagés (S) sur une ressource et tentent ensuite de mettre à jour des données de manière simultanée, une transaction tente de convertir le verrou en verrou exclusif (X). La conversion du verrou partagé au mode de verrou exclusif (X) reste en attente, car le verrou exclusif de la première transaction n'est pas compatible avec le verrou partagé (S) de l'autre transaction. Une attente de verrouillage se produit alors. La deuxième transaction impliquée essaie d'acquérir un verrou exclusif (X) pour sa mise à jour. Puisque les deux transactions effectuant la conversion en verrous exclusifs (X) attendent que l'autre transaction libère son verrou partagé (S), un blocage se produit.

  Dans le niveau d'isolation READ COMMITTED par défaut, les verrous S sont de courte durée, libérés dès qu'ils sont utilisés. Bien que le blocage décrit ci-dessus soit toujours possible, il est beaucoup moins probable avec des verrous de courte durée.

  Pour éviter ce type de blocage, les applications peuvent suivre un modèle « sélectionner une ligne indicatrice UPDLOCK, puis mettre à jour la ligne ».

• Si l'indice UPDLOCK est utilisé dans une écriture alors que l'isolement SNAPSHOT est utilisé, la transaction doit avoir accès à la dernière version de la ligne. Si la dernière version n'est plus visible, il est prévu de recevoir Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict. Pour obtenir un exemple, consultez Utiliser l'isolation d'instantané.

Verrous exclusifs

Les verrous exclusifs (X) empêchent l'accès à une ressource par des transactions simultanées. Un verrou exclusif (X) empêche toute autre transaction de modifier les données protégées par le verrou ; les opérations de lecture ne peuvent avoir lieu qu'avec l'indicateur NOLOCK ou le niveau d'isolation READ UNCOMMITTED.

Les instructions qui modifient les données telles que INSERT, UPDATE, et DELETE combinent des opérations de modification et de lecture. Elles commencent par les opérations de lecture pour obtenir les données, puis elles effectuent les opérations de modification. Par conséquent, les instructions qui modifient les données demandent généralement à la fois des verrous partagés et des verrous exclusifs. Ainsi, une instruction UPDATE peut modifier les lignes d'une table en fonction d'une jointure avec une autre table. Dans ce cas, l'instruction UPDATE demande des verrous partagés sur les lignes lues dans la table jointe en plus des verrous exclusifs sur les lignes mises à jour.

Verrous intentionnels

Le moteur de base de données utilise des verrous intentionnels pour protéger le placement de verrous partagés (S) ou exclusifs (X) sur une ressource hiérarchiquement inférieure. Les verrous intentionnels sont appelés ainsi parce qu'ils sont obtenus avant un verrou de niveau inférieur et signalent par conséquent l'intention de placer des verrous à un niveau inférieur.

Les verrous intentionnels ont deux fonctions :

• Empêcher les autres transactions de modifier la ressource de niveau supérieur de façon à invalider le verrou au niveau inférieur.
• Améliorer l'efficacité du moteur de base de données en matière de détection de conflits de verrous au plus haut niveau de granularité.

Par exemple, un verrou de partage intentionnel est demandé au niveau table avant une demande de verrous partagés (S) sur les pages ou les lignes de cette table. Un verrou intentionnel placé au niveau de la table empêche une autre transaction d'acquérir un verrou exclusif (X) sur la table contenant cette page. Les verrous intentionnels améliorent les performances, car le moteur de base de données n'examine les verrous intentionnels qu'au niveau des tables afin de déterminer si une transaction peut acquérir un verrou en toute sécurité sur chaque table. Cela supprime la nécessité d'examiner chaque verrou de ligne ou page pour déterminer si une transaction peut verrouiller la table entière.

Les verrous intentionnels comprennent les verrous de partage intentionnel (IS), d'exclusion intentionnelle (IX) et de partage intentionnel exclusif (SIX).

Mode de verrouillage	Description
Partage intentionnel (IS)	Protège les verrous partagés demandés ou acquis sur certaines ressources (mais pas toutes) de niveau inférieur dans la hiérarchie.
Intentionnel exclusif (IX)	Protège les verrous exclusifs demandés ou acquis sur certaines ressources (mais pas toutes) de niveau inférieur dans la hiérarchie. IX est un sur-ensemble de IS qui protège également les demandes de verrou partagé sur des ressources de niveau inférieur.
Partagé avec intentionnel exclusif (SIX)	Protège les verrous partagés (S) demandés ou acquis sur toutes les ressources de niveau inférieur dans la hiérarchie et les verrous d'exclusion intentionnelle sur certaines ressources de niveau inférieur (mais pas toutes). Des verrous IS simultanés sur la ressource de niveau supérieur sont autorisés. Par exemple, l'obtention d'un verrou SIX sur une table permet également d'obtenir des verrous d'exclusion intentionnelle sur les pages modifiées et des verrous exclusifs sur les lignes modifiées. Il ne peut y avoir qu'un seul verrou SIX par ressource à la fois pour empêcher la mise à jour des ressources par une autre transaction, bien que cette dernière peut lire les ressources inférieures dans la hiérarchie en obtenant des verrous IS au niveau des tables.
Mise à jour intentionnelle (IU)	Protège les verrous de mise à jour demandés ou acquis sur toutes les ressources de niveau inférieur dans la hiérarchie. Les verrous IU sont utilisés uniquement sur les ressources de page. Les verrous IU sont convertis en verrous IX si une opération de mise à jour a lieu.
Mise à jour intentionnelle partagée (SIU)	Combinaison de verrous S et IU résultant de l'acquisition séparée de ces verrous et de leur gestion simultanée. Par exemple, une transaction peut exécuter une requête avec l'indicateur PAGLOCK, puis une opération de mise à jour. La requête contenant l'indicateur PAGLOCK obtient le verrou S et l'opération de mise à jour obtient le verrou IU.
Mise à jour intentionnelle exclusive (UIX)	Combinaison de verrous U et IX résultant de l'acquisition séparée de ces verrous et de leur gestion simultanée.

Verrous de schéma

Le moteur de base de données utilise les verrous de modification de schémas (Sch-M) quand une opération de langage de définition de données (DDL) est effectuée sur une table (ajout d'une colonne ou suppression d'une table, par exemple). Pendant le temps de sa détention, le verrou Sch-M empêche les accès simultanés à la table. Cela signifie que le verrou Sch-M bloque toutes les opérations externes jusqu'à ce que le verrou soit libéré.

Certaines opérations DML(langage de manipulation de données), comme la troncation de table, utilisent les verrous Sch-M pour empêcher l'accès aux tables affectées par des opérations simultanées.

Le moteur de base de données utilise les verrous de stabilité de schéma (Sch-S) lors de la compilation et l'exécution des requêtes. Les verrous Sch-S ne bloquent aucun verrou transactionnel, verrous exclusifs (X) y compris. Par conséquent, les autres transactions, y compris celles avec des verrous X sur une table, continuent à s'exécuter pendant la compilation d'une requête. Toutefois, les opérations DDL simultanées et les opérations DML simultanées qui acquièrent des verrous Sch-M sont bloquées par les verrous Sch-S.

Verrous de mise à jour en bloc (BU)

Les verrous de mise à jour en bloc (BU) permettent à plusieurs threads de charger simultanément des données en bloc dans la même table tout en empêchant les autres processus qui ne chargent pas de données en bloc d'accéder à la table. Le moteur de base de données utilise des verrous (BU) lorsque les deux conditions suivantes sont vraies.

• Vous utilisez l'instruction Transact-SQL BULK INSERT, ou la fonction OPENROWSET(BULK), ou vous utilisez une des commandes d'API Bulk Insert, telles que .NET SqlBulkCopy, les API OLEDB Fast Load, ou les API ODBC Bulk Copy pour copier en bloc des données dans une table.
• L'indice TABLOCK est spécifié ou l'option de table table lock on bulk load est définie à l'aide de sp_tableoption.

Verrous de clés

Les verrous d'étendues de clés protègent une plage de lignes implicitement incluses dans un jeu d'enregistrements lu par une instruction Transact-SQL lors de l'utilisation du niveau d'isolement des transactions SERIALIZABLE. Le verrouillage d'étendues de clés empêche les lectures fantômes. Les verrous d'étendues de clés couvrent des enregistrements individuels et les étendues entre les enregistrements, empêchant les insertions ou les suppressions fantômes dans un ensemble d'enregistrements auquel accède une transaction.

Compatibilité de verrouillage

La compatibilité de verrouillage détermine si plusieurs transactions peuvent simultanément acquérir des verrous sur la même ressource. Si une ressource est déjà verrouillée par une autre transaction, une demande de nouveau verrou ne peut être accordée que si le mode du verrou demandé est compatible avec celui du verrou existant. Si le mode du verrou demandé n'est pas compatible avec le verrou existant, la transaction qui demande le nouveau verrou attend que le verrou existant soit libéré ou que l'intervalle de délai de verrouillage ait expiré. Par exemple, aucun mode de verrou n'est compatible avec les verrous exclusifs. Lorsqu'un verrou exclusif (X) est posé, aucune autre transaction ne peut acquérir un verrou de quelque sorte que ce soit (partagé, mise à jour, exclusif) sur cette ressource tant que le verrou exclusif (X) n'a pas été libéré. Inversement, si un verrou partagé (S) a été appliqué à une ressource, les autres transactions peuvent aussi acquérir un verrou partagé ou de mise à jour (U) sur cet élément, même si la première transaction n'est pas terminée. Toutefois, les autres transactions ne peuvent pas acquérir un verrou exclusif tant que le verrou partagé n'a pas été libéré.

Le tableau suivant décrit la compatibilité des modes de verrouillage les plus courants.

Mode accordé existant	IS	S	U	IX	SIX	X
Mode requis
Partage intentionnel (IS)	Oui	Oui	Oui	Oui	Oui	Non
Partagé (S)	Oui	Oui	Oui	No	Non	Non
Mise à jour (U)	Oui	Oui	No	Non	Non	Non
Intentionnel exclusif (IX)	Oui	No	Non	Oui	No	Non
Partagé avec intentionnel exclusif (SIX)	Oui	No	Non	Non	Non	Non
Exclusif (X)	Non	Non	Non	Non	Non	N°

Utilisez le tableau suivant pour déterminer la compatibilité de tous les modes de verrouillage disponibles dans le moteur de base de données.

Clé	Description
N	Aucun conflit
I	Illegal
C	Conflit
NL	Aucun verrou
SCH-S	Verrou de stabilité du schéma
SCH-M	Verrou de modification de schéma
S	Partagé
U	Update
X	Exclusif
IS	Intention partagée
IU	Mise à jour de l’intention
IX	Intention exclusive
SIU	Partager avec la mise à jour de l’intention
SIX	Partager avec l’intention exclusive
UIX	Mettre à jour avec l’intention exclusive
BU	Mise à jour par lot
RS-S	Shared range-shared
RS-U	Mise à jour de plage partagée
RI-N	Insérer une plage null
RI-S	Insérer une plage partagée
RI-U	Insérer une mise à jour de plage
RI-X	Insérer une plage exclusive
RX-S	Plages exclusives partagées
RX-U	Mise à jour exclusive de plage
RX-X	Exclusive de gamme exclusive

Verrouillage d’étendues de clés

Les verrous d'étendues de clés protègent une plage de lignes implicitement incluses dans un jeu d'enregistrements lu par une instruction Transact-SQL lors de l'utilisation du niveau d'isolement des transactions SERIALIZABLE. Le niveau d'isolement SERIALIZABLE exige que toute requête exécutée pendant une transaction obtienne le même jeu de lignes à chaque exécution lors de la transaction. Un verrou de groupe de clés satisfait cette exigence en empêchant d'autres transactions d'insérer de nouvelles lignes dont les clés sont comprises dans la plage des clés lues par la transaction SERIALIZABLE.

Le verrouillage d'étendues de clés empêche les lectures fantômes. La protection des étendues de clés entre les lignes permet également d'empêcher les insertions fantômes dans un jeu d'enregistrements auquel une transaction accède.

Un verrou d'étendues de clés est placé sur un index, spécifiant une valeur de clé de début et de fin. Ce verrou bloque toute tentative d'insertion, de mise à jour ou de suppression de ligne possédant une valeur de clé comprise dans cette étendue, car ces opérations doivent d'abord acquérir un verrou sur l'index. Par exemple, une transaction SERIALIZABLE peut émettre une instruction SELECT qui lit toutes les lignes dont les valeurs de clés remplissent la condition BETWEEN 'AAA' AND 'CZZ'. Un verrou de groupes de clés sur les valeurs de clés comprises entre 'AAA' et 'CZZ' empêche les autres transactions d’insérer des lignes possédant des valeurs de clés comprises dans ce groupe, telles que 'ADG', 'BBD' ou 'CAL'.

Modes de verrouillage d'étendues de clés

Les verrous d'étendues de clés comprennent un composant étendue et un composant ligne, au format étendue-ligne :

• L'étendue représente le mode de verrouillage protégeant l'étendue entre deux entrées d'index successives.
• La ligne représente le mode de verrouillage protégeant l'entrée de l'index.
• Le mode représente la combinaison de modes de verrouillage utilisée. Les modes de verrouillage d'étendues de clés comportent deux parties. La première représente le type de verrou utilisé pour verrouiller l’étendue d’index (RangeT) et la deuxième représente le type de verrou utilisé pour verrouiller une clé spécifique (K). Les deux parties sont reliées par un tiret (-), par exemple RangeT-K.

Plage	Ligne	Mode	Description
RangeS	S	RangeS-S	Verrou de ressource partagé, étendue partagée ; analyse de plage SERIALIZABLE.
RangeS	U	RangeS-U	Verrou de mise à jour de ressource, étendue partagée ; analyse d'étendue SERIALIZABLE.
RangeI	Null	RangeI-N	Verrou de ressource NULL, étendue d'insertion ; utilisé pour tester les étendues avant l'insertion d'une nouvelle clé dans un index.
RangeX	X	RangeX-X	Verrou de ressource exclusif, étendue exclusive ; utilisé lors de la mise à jour d'une clé dans une étendue.

Les modes de verrouillage d'étendues de clés possèdent un tableau de compatibilité qui indique quels verrous sont compatibles avec les autres verrous obtenus sur les clés et étendues se chevauchant.

Mode accordé existant	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Mode requis
Partagé (S)	Oui	Oui	No	Oui	Oui	Oui	Non
Mise à jour (U)	Oui	No	Non	Oui	No	Oui	Non
Exclusif (X)	Non	Non	Non	Non	Non	Oui	No
RangeS-S	Oui	Oui	No	Oui	Oui	No	Non
RangeS-U	Oui	No	Non	Oui	No	Non	Non
RangeI-N	Oui	Oui	Oui	No	Non	Oui	No
RangeX-X	Non	Non	Non	Non	Non	Non	Non

Verrous de conversion

Les verrous de conversion sont créés lorsqu'un verrou d'étendue de clés chevauche un autre verrou.

Verrou 1	Verrou 2	Verrou de conversion
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

Les verrous de conversion peuvent être observés pendant une courte période dans différentes circonstances complexes, parfois lors de l'exécution de processus concurrents.

Analyse d'étendue sérialisable, extraction de singleton, suppression et insertion

Le verrouillage d'étendues de clés permet la sérialisation des opérations suivantes :

• Requête d'analyse d'étendue
• Extraction singleton de ligne inexistante
• Opération de suppression
• Opération d'insertion

Les conditions suivantes doivent être satisfaites pour qu'un verrouillage d'étendues de clés puisse se produire :

• Le niveau d'isolement de la transaction doit être défini sur SERIALIZABLE.
• Le processeur de requêtes doit utiliser un index pour implémenter le prédicat de filtre de l'étendue. Par exemple, la clause WHERE dans une instruction SELECT peut établir une condition d’étendue avec le prédicat suivant : ColumnX BETWEEN N'AAA' AND N'CZZ'. Un verrou de groupes de clés ne peut être acquis que si ColumnX est couvert par une clé d’index.

Exemples

La table et l'index suivants sont utilisés comme base pour les exemples de verrouillage d'étendues de clés ci-dessous.

Requête d'analyse d'étendue

Pour qu'une requête d'analyse d'étendue soit sérialisable, cette requête doit retourner les mêmes résultats chaque fois qu'elle est exécutée dans la même transaction. De nouvelles lignes ne doivent pas être insérées dans la requête d'analyse d'étendue par d'autres transactions, sinon celles-ci deviennent des insertions fantômes. Par exemple, la requête suivante utilise la table et l'index de l'illustration précédente :

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Des verrous de groupes de clés sont placés sur les entrées d’index correspondant à l’étendue de lignes dans laquelle le nom se trouve entre Adam et Dale, empêchant l’insertion ou la suppression de nouvelles lignes correspondant à la requête précédente. Bien que le premier nom de l’étendue soit Adam, le verrou de groupes de clés du mode RangeS-S sur cette entrée d’index veille à ce qu’aucun nouveau nom commençant par la lettre A ne soit ajouté avant Adam, comme Abigail. De manière similaire, le verrou de groupes de clés RangeS-S sur l’entrée d’index pour Dale fait en sorte qu’aucun nom commençant par C ne puisse être ajouté après Carlos, comme Clive.

Extraction d’un singleton de données non existantes

Si une requête à l'intérieur d'une transaction tente de sélectionner une ligne qui n'existe pas, l'exécution de la requête plus loin dans la même transaction doit retourner le même résultat. Aucune autre transaction ne peut être autorisée à insérer cette ligne inexistante. Supposons par exemple la requête suivante :

SELECT name
FROM mytable
WHERE name = 'Bill';

Un verrou d'étendues de clés est placé sur l'entrée d'index correspondant à l'étendue de noms se trouvant entre Ben et Bing, car le nom Bill serait inséré entre ces deux entrées d'index adjacentes. Le verrou d'étendues de clés du mode RangeS-S est placé sur l'entrée d'index Bing. Ceci empêche toute autre transaction d'insérer des valeurs, telles que Bill, entre les entrées d'index Ben et Bing.

Opération de suppression sans verrouillage optimisé

Lors de la suppression d'une ligne dans une transaction, l'étendue dans laquelle la ligne se trouve ne doit pas nécessairement être verrouillée pendant toute la durée de la transaction effectuant l'opération de suppression. Le verrouillage de la valeur de clé supprimée jusqu'à la fin de la transaction est suffisant pour assurer la sérialisation. Par exemple, pour l'instruction DELETE suivante :

DELETE mytable
WHERE name = 'Bob';

Un verrou exclusif (X) est placé sur l'entrée d'index correspondant au nom Bob. D'autres transactions peuvent insérer ou supprimer des valeurs avant ou après la ligne contenant la valeur Bob qui est supprimée. Toutefois, toute transaction qui tente de lire, d'insérer ou de supprimer des lignes correspondant à la valeur Bob sera bloquée jusqu'à ce que la transaction de suppression soit validée ou annulée. (L'option de base de données READ_COMMITTED_SNAPSHOT et le niveau d'isolation SNAPSHOT autorisent également les lectures à partir d'une version de ligne de l'état précédemment validé.)

La suppression d'étendues peut être exécutée à l'aide de trois modes de verrouillage de base : verrouillage de ligne, de page ou de table. La stratégie de verrouillage de ligne, de page ou de table est décidée par l’optimiseur de requête, ou peut être spécifiée par l’utilisateur via des options d’optimiseur de requête comme ROWLOCK, PAGLOCK, ou TABLOCK. Lorsque l'option PAGLOCK ou TABLOCK est utilisée, le moteur de base de données SQL Server désalloue immédiatement une page d'index page si toutes les lignes qu'elle contient sont supprimées. En revanche, lorsque l'option ROWLOCK est utilisée, toutes les lignes supprimées sont uniquement marquées en tant que telles ; elles sont effectivement retirées de la page d'index ultérieurement, à l'aide d'une tâche en arrière-plan.

Supprimer l’opération avec verrouillage optimisé

Lors de la suppression d'une ligne dans une transaction, les verrous de ligne et de page sont acquis et libérés de manière incrémentielle et ne sont pas conservés pendant la durée de la transaction. Par exemple, pour l'instruction DELETE suivante :

DELETE mytable
WHERE name = 'Bob';

Un verrou TID est placé sur toutes les lignes modifiées pendant la durée de la transaction. Un verrou est acquis sur le TID des lignes d'index correspondant à la valeur Bob. Avec le verrouillage optimisé, 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 verrou TID protège les lignes d'être mises à jour jusqu'à la fin de la transaction. Toute transaction tentant de lire, insérer ou supprimer la ligne avec la valeur Bob sera bloquée jusqu'à ce que la transaction effectuant la suppression soit validée ou restaurée. (L'option de base de données READ_COMMITTED_SNAPSHOT et le niveau d'isolation SNAPSHOT autorisent également les lectures à partir d'une version de ligne de l'état précédemment validé.)

Dans le cas contraire, la mécanique de verrouillage d'une opération de suppression est la même que sans verrouillage optimisé.

Opération d'insertion sans verrouillage optimisé

Lors de l'insertion d'une ligne à l'intérieur d'une transaction, l'étendue dans laquelle la ligne se trouve ne doit pas nécessairement être verrouillée pendant la durée de l'opération effectuant l'opération d'insertion. Le verrouillage de la valeur de clé jusqu'à la fin de la transaction suffit pour assurer la sérialisation. Par exemple, étant donné l'instruction INSERT suivante :

INSERT mytable VALUES ('Dan');

Le verrou d'étendues de clés du mode RangeI-N est placé sur la ligne d'index correspondant au nom David pour le test de l'étendue. Si le verrou est accordé, une ligne avec la valeur Dan est insérée et un verrou exclusif (X) est placé sur la ligne insérée. Le verrou d'étendues de clés du mode RangeI-N est uniquement nécessaire pour le test de l'étendue et n'est pas maintenu pendant la durée de la transaction effectuant l'opération d'insertion. D'autres transactions peuvent insérer ou supprimer des valeurs avant ou après la ligne avec la valeur Dan insérée. Toutefois, toute transaction tentant de lire, d'insérer ou de supprimer la ligne contenant la valeur Dan sera bloquée jusqu'à ce que la transaction d'insertion soit validée ou annulée.

Opération d'insertion avec verrouillage optimisé

Lors de l'insertion d'une ligne à l'intérieur d'une transaction, l'étendue dans laquelle la ligne se trouve ne doit pas nécessairement être verrouillée pendant la durée de l'opération effectuant l'opération d'insertion. Les verrous de ligne et de page sont rarement acquis, uniquement lorsqu'une reconstruction d'index en ligne est en cours ou lorsqu'il y a des transactions SERIALIZABLE simultanées. Si les verrous de ligne et de page sont acquis, ils sont libérés rapidement et ne sont pas conservés pendant la durée de la transaction. Le fait de placer un verrou TID exclusif sur la valeur de clé insérée jusqu'à la fin de la transaction est suffisant pour maintenir la sérialisation. Par exemple, pour l'instruction INSERT suivante :

INSERT mytable VALUES ('Dan');

Avec le verrouillage optimisé, un verrou RangeI-N n'est acquis que s'il existe au moins une transaction qui utilise le niveau d'isolation SERIALIZABLE dans l'instance. Le verrou d'étendues de clés du mode RangeI-N est placé sur la ligne d'index correspondant au nom David pour le test de l'étendue. Si le verrou est accordé, une ligne avec la valeur Dan est insérée et un verrou exclusif (X) est placé sur la ligne insérée. Le verrou d'étendues de clés du mode RangeI-N est uniquement nécessaire pour le test de l'étendue et n'est pas maintenu pendant la durée de la transaction effectuant l'opération d'insertion. D'autres transactions peuvent insérer ou supprimer des valeurs avant ou après la ligne avec la valeur Dan insérée. Toutefois, toute transaction tentant de lire, d'insérer ou de supprimer la ligne contenant la valeur Dan sera bloquée jusqu'à ce que la transaction d'insertion soit validée ou annulée.

Escalade de verrous

L'escalade des verrous est le processus qui consiste à convertir de nombreux verrous précis en un plus petit nombre de verrous grossiers, ce qui réduit la surcharge du système tout en augmentant la probabilité de contention de concurrence.

L'escalade de verrous se comporte différemment selon que le verrouillage optimisé est activé ou non.

Escalade de verrous sans verrouillage optimisé

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

• Lors du verrouillage de lignes ou de 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.
• Lors du verrouillage de pages, le moteur de base de données place un verrou intentionnel sur les objets de niveau supérieur qui contiennent ces pages. En plus du verrou d'intention sur l'objet, les verrous de page d'intention 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 verrouiller à la fois les lignes et les pages pour la même instruction afin de minimiser le nombre de verrous et de réduire la probabilité que l'escalade des verrous soit nécessaire. 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 l'index groupé ou le tas.

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 (IX) à un verrou d'exclusion intentionnelle (X) ou un verrou partagé (IS) à un verrou de partage intentionnel (S). Si la tentative d'escalade de verrous réussit et que le verrou de table complet est acquis, tous les HoBT, page (PAGE) ou RID (RID, KEY) détenus par la transaction sur le tas ou 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 toujours réaffectés aux verrous de table. 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 de niveau HoBT ne verrouille pas nécessairement les HoBT alignés pour la partition.

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 effectuée pour élever les verrous de cette table. Si l'escalade de verrous réussit, les verrous acquis par la transaction dans une instruction précédente et conservés au moment où l'événement démarre seront réaffectés si la table est référencée par l'instruction active et est incluse dans l'événement d'escalade.

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

• Commence une transaction.
• Met à jour TableA. Cela génère des verrous de ligne exclusifs dans TableA qui sont conservés jusqu'à la fin de la transaction.
• Met à jour TableB. Cela génère des verrous de ligne exclusifs dans TableB qui sont conservés jusqu'à la fin de la transaction.
• Exécute une 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 de l'instruction SELECT et les verrous exclusifs de l'instruction UPDATE précédente. 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 TableB ne comportait aucune référence active à SELECT. 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.

Escalade de verrous avec verrouillage optimisé

Le verrouillage optimisé permet de réduire la mémoire des verrous, car très peu de verrous sont conservés pendant la durée de la transaction. À mesure que le moteur de base de données acquiert des verrous de ligne et de page, l'escalade de verrous peut se produire de la même façon, mais beaucoup moins fréquemment. Le verrouillage optimisé réussit généralement à éviter les escalades de verrous, en réduisant le nombre de verrous et la quantité de mémoire de verrou nécessaires.

Lorsque le verrouillage optimisé est activé, et au niveau d'isolation READ COMMITTED par défaut, le moteur de base de données libère les verrous de ligne et de page dès que la ligne est modifiée. Aucun verrou de ligne ou de page n'est maintenu pendant la durée de la transaction, à l'exception d'un seul verrou d'ID de transaction (TID). Cela réduit la probabilité d'escalade de verrous.

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'une 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

Quand le moteur de base de données vérifie les escalades possibles chaque fois que 1 250 verrous ont été acquis, une escalade de verrous se produit si et seulement si une instruction Transact-SQL a acquis au moins 5 000 verrous sur une référence unique d'une table. L'escalade de verrous est déclenchée quand une instruction Transact-SQL acquiert au moins 5 000 verrous sur une seule référence d'une table. 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 a une jointure automatique sur une table, et chaque référence à la table n'acquiert que 3 000 verrous dans la table.

L'escalade de verrous se produit uniquement pour les tables auxquelles l'accès a été effectué au moment où l'escalade est déclenchée. 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 inférieur à 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 a une longueur d'environ 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 verrous avec des types de verrous mixtes

Lorsque l'escalade de verrous se produit, le verrou sélectionné pour le segment de mémoire ou l'index est suffisamment fort pour répondre aux exigences du verrou de niveau inférieur le plus restrictif.

Par exemple, supposons qu'une session :

• Commence une transaction.
• Met à jour une table contenant un index cluster.
• Émet une instruction SELECT qui fait référence à la même table.

L'instruction UPDATE acquiert les verrous suivants :

• Verrous exclusifs (X) sur les lignes de données mises à jour.
• Verrous d'exclusion intentionnelle (IX) sur les pages d'index en cluster contenant ces lignes.
• Verrou IX sur l'index cluster et un autre sur la table.

L'instruction SELECT acquiert les verrous suivants :

• Verrous partagés (S) sur toutes les lignes de données qu'il lit, sauf si la ligne est déjà protégée par un verrou X de l'instruction UPDATE.
• Verrous de partage intentionnel (IS) sur toutes les pages d'index en grappe contenant ces lignes, à moins que la page ne soit déjà protégée par un verrou IX.
• Aucun verrou sur la table ou 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 des verrous et que l'escalade réussit, le verrou IX de la table est converti en verrou X, et toutes les lignes, pages et verrous d'index sont libérés. Les mises à jour et les lectures sont protégées par le verrou X sur la table.

Réduction du verrouillage et escalade de verrous

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.

• Bénéficiez du verrouillage optimisé.

  • Le verrouillage optimisé offre un mécanisme de verrouillage amélioré réduisant la consommation de mémoire de verrouillage et le blocage au cours des transactions simultanées. L'escalade de verrous est beaucoup moins susceptible de se produire lorsque le verrouillage optimisé est activé.
  • Évitez d'utiliser des indicateurs de table avec verrouillage optimisé. Les indicateurs de table peuvent réduire l'efficacité du verrouillage optimisé.
  • Activez l’option READ_COMMITTED_SNAPSHOT dans la base de données pour tirer le meilleur parti du verrouillage optimisé. C'est celui par défaut dans Azure SQL Database.
  • 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.

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 :

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

  • Niveau d’isolation READ COMMITTED lorsque l’option de base de données READ_COMMITTED_SNAPSHOT est ON.
  • Niveau d'isolement SNAPSHOT.
  • Niveau d'isolement READ UNCOMMITTED. Cela ne peut être utilisé que pour les systèmes qui peuvent fonctionner avec la lecture erronée.

• Utilise 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, l'utilisation de cette option augmente les problèmes des utilisateurs bloquant d'autres utilisateurs qui tentent d'accéder aux mêmes données et ne doivent pas être utilisés dans les systèmes avec plus de quelques utilisateurs simultanés.

• Lorsque le verrouillage optimisé n'est pas activé, pour les tables partitionnées, utilisez l'optionLOCK_ESCALATION de ALTER TABLE pour escalader les verrous au niveau de la partition au lieu de la table ou pour désactiver l'escalade de verrous.

• Divisez les opérations de traitement par lots volumineuses en plusieurs opérations plus petites. Par exemple, supposons que vous avez exécuté la requête suivante pour supprimer plusieurs centaines de milliers de lignes d’une table d’audit, et que vous avez découvert que cela a entraîné une escalade de verrous à l’origine du blocage d’autres utilisateurs :

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  En supprimant ces lignes par plusieurs centaines à la fois, vous pouvez réduire considérablement le nombre de verrous qui s’accumulent par transaction et empêcher l’escalade de verrous. Par exemple :

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• Réduisez l’empreinte des verrous d’une requête en rendant la requête aussi efficace que possible. Les analyses de grande envergure ou les nombres élevés de recherches par clés peuvent augmenter le risque d’escalade de verrous ; de plus, cela augmente le risque de blocages et dégrade généralement la concurrence et les performances. Une fois que vous avez trouvé la requête à l’origine de l’escalade de verrous, recherchez les opportunités de créer des index ou d’ajouter des colonnes à un index complet existant pour supprimer les analyses d’index ou de table et maximiser l’efficacité des recherches d’index. Envisagez d’utiliser l’Assistant Paramétrage du moteur de base de données pour effectuer une analyse d’index automatique sur la requête. Pour plus d'informations, consultez Tutoriel : Assistant Paramétrage du moteur de base de données. L’un des objectifs de cette optimisation est de faire en sorte que les recherches d’index renvoient le moins de lignes possible pour réduire le coût des recherches par clés (maximiser la sélectivité de l’index pour une requête particulière). Si le moteur de base de données estime qu'un opérateur logique de recherche de clé peut renvoyer de nombreuses lignes, il peut utiliser une optimisation de la recherche préalable pour effectuer la recherche. Si le moteur de base de données utilise la recherche anticipée pour une consultation, il doit augmenter le niveau d’isolation de la transaction d'une partie de la requête à REPEATABLE READ. Cela signifie que ce qui peut ressembler à une instruction SELECT à un niveau d’isolation READ COMMITTED peut acquérir plusieurs milliers de verrous de clé (à la fois sur l’index cluster et sur un index non cluster), ce qui peut entraîner un dépassement des seuils d’escalade de verrous pour une telle requête. Cela s'avère particulièrement important si vous constatez que le verrou escaladé est un verrou de table partagé, qui, en revanche, n'est pas communément visible au niveau d'isolation READ COMMITTED par défaut.

  Si une recherche par clés est à l’origine de l’escalade de verrous, envisagez d’ajouter des colonnes supplémentaires à l’index non cluster qui apparaît dans l’opérateur logique Recherche d’index ou Analyse d’index sous l’opérateur logique Recherche par clés dans le plan de requête. Il peut être possible de créer un index de couverture (un index qui inclut toutes les colonnes d’une table qui ont été utilisées dans la requête), ou au moins un index qui couvre les colonnes utilisées pour les critères de jointure ou dans la clause WHERE si l’inclusion de tous les éléments dans la liste de sélection de colonnes SELECT n’est pas pratique. Une jointure de boucle imbriquée peut également utiliser l'optimisation de préchargement, ce qui entraîne le même comportement de verrouillage.

• L’escalade de verrous ne peut pas se produire si un autre SPID contient actuellement un verrou de table incompatible. L’escalade de verrous passe toujours à un verrou de table et jamais à des verrous de page. De plus, si une tentative d'escalade de verrous échoue parce qu'un autre SPID contient un verrou de table incompatible, la requête qui a tenté l'escalade n'est pas bloquée pendant l'attente d'un verrou de table. Elle continue plutôt d’acquérir des verrous à son niveau d’origine, plus précis (ligne, clé ou page), en effectuant régulièrement des tentatives d’escalade supplémentaires. Ainsi, une méthode pour empêcher l'escalade de verrous sur une table particulière consiste à acquérir et à conserver un verrou sur une autre connexion qui n'est pas compatible avec le type de verrou escaladé. Un verrou d’exclusion intentionnelle (IX) au niveau de la table ne verrouille aucune ligne ni aucune page, mais il n'est quand même pas compatible avec un verrou de table partagé (S) ou exclusif (X) remonté. Par exemple, supposons que vous devez exécuter un programme de traitement par lots qui modifie un grand nombre de lignes dans la table mytable et qui a entraîné un blocage en raison d’une escalade de verrous. Si ce programme s'exécute toujours en moins d'une heure, vous pouvez créer un travail Transact-SQL qui contient le code suivant et planifier le démarrage du nouveau travail plusieurs minutes avant l'heure de début d'exécution du programme de traitement par lots :

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  Cette requête acquiert et conserve un verrou IX sur mytable pendant une heure, ce qui empêche l’escalade de verrous sur la table pendant cette période. Ce lot ne modifie aucune donnée et ne bloque pas les autres requêtes (à moins que l'autre requête ne force un verrou de table avec l'indice TABLOCK ou qu'un administrateur n'ait désactivé les verrous de page ou de ligne sur un index sur mytable).

• Vous pouvez également utiliser les indicateurs de trace 1211 et 1224 pour désactiver toutes ou certaines escalades de verrous. Toutefois, ces indicateurs de trace désactivent toutes les escalades de verrous globalement pour toute l’instance du moteur de base de données. Une escalade de verrous joue un rôle très utile dans le moteur de base de données en optimisant l'efficacité des requêtes qui, sinon, sont ralenties par la surcharge due à l'acquisition et à la libération de plusieurs milliers de verrous. Une escalade de verrous permet également de réduire la mémoire nécessaire pour effectuer le suivi des verrous. La mémoire que le moteur de base de données peut allouer dynamiquement pour les structures de verrous est limitée. Par conséquent, si vous désactivez l'escalade de verrous et que la mémoire de verrous augmente de façon suffisante, toute tentative d'allocation de verrous supplémentaires pour une requête risque d'échouer et l'erreur suivante se produit : Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Remarque

  Quand l'erreur MSSQLSERVER_1204 se produit, le traitement de l'instruction en cours est interrompu et la transaction active est annulée. La restauration elle-même risque de bloquer les utilisateurs ou d’entraîner une longue durée de récupération de base de données si vous redémarrez le service de base de données.

  Remarque

  L’utilisation d’un indicateur de verrou comme ROWLOCK modifie uniquement l’acquisition de verrouillage initial. Les indicateurs de verrou n’empêchent pas l’escalade de verrous.

À compter de SQL Server 2008 (10.0.x), le comportement d'escalade de verrous a changé avec l'introduction de l'option de table LOCK_ESCALATION. Pour plus d’informations, consultez l’option LOCK_ESCALATION de l’instruction ALTER TABLE.

Surveiller l'escalade de verrous

Surveillez l'escalade de verrous en utilisant l'événement étendu lock_escalation, comme dans l'exemple suivant :

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

Verrouillage dynamique

L'utilisation de verrous de bas niveau, comme les verrous de ligne, augmente la concurrence car elle diminue la probabilité d'avoir deux transactions qui demandent des verrous sur les mêmes données en même temps. L'utilisation de verrous de bas niveau augmente également le nombre de verrous et les ressources nécessaires à leur gestion. Les verrous de table ou de page de haut niveau réduisent la charge mais au détriment de la concurrence.

Le moteur de base de données utilise une stratégie de verrouillage dynamique pour déterminer les verrous les plus rentables. Lorsqu'une requête est exécutée, le moteur de base de données détermine automatiquement les verrous les plus appropriés sur la base des caractéristiques du schéma et de la requête. Par exemple, pour réduire l'utilisation des verrous, l'optimiseur peut choisir d'utiliser des verrous de page dans un index lors de l'analyse de l'index.

Partitionnement de verrous

Pour les gros systèmes informatiques, des verrous sur des objets souvent référencés peuvent affaiblir les performances, car l'acquisition et la libération des verrous provoque une contention sur les ressources des verrous internes. Le partitionnement de verrous améliore les performances du verrouillage en fractionnant une ressource de verrou en plusieurs. Cette fonctionnalité n'est disponible que pour les systèmes dotés d'au moins 16 processeurs logiques ; elle est activée automatiquement et ne peut pas être désactivée. Seuls les verrous d'objet peuvent être partitionnés. Les verrous d'objet qui ont un sous-type ne sont pas partitionnés. Pour plus d'informations, consultez sys.dm_tran_locks (Transact-SQL).

Comprendre le partitionnement de verrous

Les opérations de verrouillage accèdent à plusieurs ressources partagées, dont deux sont optimisées par le partitionnement de verrous :

• Spinlock

  Contrôle l'accès à une ressource de verrou, par exemple une ligne ou une table.

  Sans partitionnement de verrous, un verrouillage spinlock gère toutes les demandes de verrouillage pour une seule ressource de verrou. Sur des systèmes qui connaissent une activité intense, une contention peut se produire pendant que les demandes de verrouillage attendent que le verrouillage spinlock devienne disponible. Dans ce cas, l'acquisition de verrous peut causer un goulet d'étranglement et détériorer les performances.

  Pour réduire la contention sur une ressource de verrou, le partitionnement de verrous fractionne une ressource de verrou en plusieurs ressources, afin de répartir la charge sur plusieurs verrouillages spinlock.

• Mémoire

  Permet de stocker les structures des ressources de verrous.

  Une fois le verrouillage spinlock acquis, les structures des verrous sont stockées dans la mémoire, puis utilisées et éventuellement modifiées. La répartition de l'accès aux verrous entre plusieurs ressources permet d'éliminer la nécessité de transférer des blocs de mémoire entre les UC, ce qui améliore les performances.

Implémenter et surveiller le partitionnement de verrous

Le partitionnement de verrous est activé par défaut pour les systèmes comportant 16 UC ou plus. Lorsque le partitionnement de verrous est activé, un message d'informations est inscrit dans le journal des erreurs de SQL Server.

Lors de l'acquisition de verrous sur une ressource partitionnée :

• Seuls les modes de verrouillage NL, Sch-S, IS, IU, et IX sont acquis sur une seule partition.

• Les verrous partagés (S), exclusifs (X) et autres dans les modes autres que NL, Sch-S, IS, IU, et IX doivent être acquis sur toutes les partitions à partir de la partition ID 0, et ensuite dans l'ordre selon l'ID. Ces verrous situés sur une ressource partitionnée utiliseront plus de mémoire que des verrous dans le même mode sur une ressource non partitionnée, puisque chaque partition constitue en fait un verrou distinct. La quantité de mémoire en plus est déterminée par le nombre de partitions. Les compteurs de performance des verrous SQL Server affichent des informations sur la mémoire utilisée par les verrous partitionnés et non partitionnés.

Une transaction est affectée à une partition au début de la transaction. Pour la transaction, toutes les demandes de verrou qui peuvent être partitionnés utilisent la partition attribuée à cette transaction. Avec cette méthode, l'accès aux ressources de verrous du même objet par différentes transactions est réparti sur plusieurs partitions.

La colonne resource_lock_partition de la vue de gestion dynamique sys.dm_tran_locks fournit l’ID de partition de verrou pour une ressource partitionnée de verrou. Pour plus d'informations, consultez sys.dm_tran_locks (Transact-SQL).

Utilisation du partitionnement de verrous

Les exemples de code suivants illustrent le partitionnement de verrous : dans ces exemples, deux transactions sont exécutées dans deux sessions différentes pour montrer le comportement du partitionnement de verrous sur un système informatique doté de 16 UC.

Ces instructions Transact-SQL créent des objets de test logiques qui sont utilisés dans les exemples qui suivent.

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

Exemple A

Session 1 :

Une instruction SELECT est exécutée sous une transaction. À cause de l'indicateur de verrou HOLDLOCK, cette instruction va acquérir et conserver un verrou partagé (IS) sur la table (pour cette illustration, les verrous de ligne et de page sont ignorés). Le verrou IS sera acquis uniquement sur la partition attribuée à la transaction. Pour cet exemple, il est supposé que le verrou IS est acquis sur l'ID de partition 7.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Session 2 :

Une transaction a démarré et l'instruction SELECT qui s'exécute sous cette transaction va acquérir et conserver un verrou partagé (S) sur la table. Le verrou S sera acquis sur toutes les partitions, ce qui aboutit à plusieurs verrous de table, un pour chaque partition. Par exemple, sur un système à 16 UC, 16 verrous S seront créés sur les ID de partition de 0 à 15. Comme le verrou S est compatible avec le verrou IS qui se trouve sur la partition ID 7 du fait de la transaction de la session 1, il n'y a pas de blocages entre les transactions.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Session 1 :

L'instruction SELECT suivante est exécutée sous la transaction qui est encore active sous la session 1. En raison de l'indicateur de verrou de table exclusif (X), la transaction va essayer d'acquérir un verrou exclusif X sur la table. Toutefois, le verrou S qui est maintenu par la transaction de la session 2 bloquera le verrou X au niveau de la partition ID 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Exemple B

Session 1 :

Une instruction SELECT est exécutée sous une transaction. À cause de l'indicateur de verrou HOLDLOCK, cette instruction va acquérir et conserver un verrou partagé (IS) sur la table (pour cette illustration, les verrous de ligne et de page sont ignorés). Le verrou IS sera acquis uniquement sur la partition attribuée à la transaction. Pour cet exemple, il est supposé que le verrou IS est acquis sur l'ID de partition 6.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Session 2 :

Une instruction SELECT est exécutée sous une transaction. En raison de l'indicateur de verrou TABLOCKX, la transaction essaie d'acquérir un verrou exclusif (X) sur la table. Souvenez-vous que le verrou X doit être acquis sur toutes les partitions à partir de la partition ID 0. Le verrou X sera acquis sur toutes les partitions, de l'ID 0 à 5, mais il sera bloqué par le verrou IS acquis sur la partition ID 6.

Sur les ID de partition de 7 à 15 que le verrou X n'a pas encore atteint, d'autres transactions peuvent continuer d'acquérir des verrous.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Niveaux d’isolation basés sur le contrôle de version de ligne dans le moteur de base de données

À partir de SQL Server 2005 (9.x), le moteur de base de données introduit une implémentation d'un niveau d'isolement de la transaction existant, READ COMMITTED, qui fournit un instantané au niveau des instructions basé sur le contrôle de version de ligne. Le moteur de base de données offre également un niveau d'isolement de la transaction, SNAPSHOT, qui fournit un instantané au niveau des transactions basé sur le contrôle de version de ligne.

Le contrôle de version de ligne est un cadre général de SQL Server qui appelle un mécanisme de copie sur écriture lorsqu'une ligne est modifiée ou supprimée. Il exige que l'ancienne version de la ligne soit disponible pendant l'exécution de la transaction pour les transactions qui nécessitent un état antérieur cohérent d'un point de vue transactionnel. Le contrôle de version de ligne est utilisé pour implémenter les fonctionnalités suivantes :

• Générez les tables inserted et deleted dans des déclencheurs. Toutes les lignes modifiées par le déclencheur reçoivent une version, y compris celles modifiées par l'instruction qui a lancé le déclencheur, de même que toute modification de données effectuée par le déclencheur.
• Prise en charge des ensembles de résultats actifs multiples (MARS, Multiple Active Result Sets). Si une session MARS émet une instruction de modification de données (par exemple, INSERT, UPDATE ou DELETE) à un moment où il existe un jeu de résultats actif, les lignes concernées par l’instruction de modification sont avec contrôle de version.
• Prise en charge des opérations d'index qui spécifient l'option ONLINE.
• Prise en charge des niveaux d'isolation de la transaction basés sur le contrôle de version de ligne :
  • Une nouvelle implémentation du niveau d'isolement READ COMMITTED qui utilise le contrôle de version de ligne pour assurer la cohérence de la lecture au niveau de l'instruction.
  • Un nouveau niveau d'isolement, SNAPSHOT, pour assurer la cohérence de la lecture au niveau de la transaction.

Les versions de ligne sont stockées dans un magasin de versions. Si la récupération de base de données accélérée est activée sur une base de données, le magasin de versions est créé dans cette base de données. Sinon, le magasin de versions est créé dans la base de données tempdb.

La base de données doit avoir suffisamment d’espace pour contenir la banque des versions. Lorsque le magasin de versions se trouve dans tempdb et que la base de données tempdb est pleine, les opérations de mise à jour cesseront de générer des versions mais continueront d'aboutir, mais les opérations de lecture risquent d'échouer parce que la version d'une ligne particulière nécessaire n'existe pas. Ceci a des conséquences sur les opérations comme les déclencheurs, MARS et l'indexation en ligne.

Lorsque la récupération de base de données accélérée est utilisée et que le magasin de versions est plein, les opérations de lecture continuent de réussir, mais les opérations d’écriture qui génèrent des versions, telles que UPDATE et DELETE échouent. Les opérations INSERT continuent de réussir si la base de données a suffisamment d’espace.

L'utilisation du contrôle de version de ligne pour les transactions READ COMMITTED et SNAPSHOT se fait en deux étapes :

1. Activez les options de base de données READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION sur ON.

2. Définissez le niveau d'isolement des transactions approprié dans une application :

   • Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est ON, les transactions qui définissent le niveau d'isolation READ COMMITTED utilisent le contrôle de version de ligne.
   • Quand l’option de base de données ALLOW_SNAPSHOT_ISOLATION est ON, les transactions peuvent définir le niveau d’isolation SNAPSHOT.

Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION est définie sur ON, le moteur de base de données affecte un numéro de séquence de transaction (XSN) à chaque transaction qui manipule des données à l'aide du contrôle de version de ligne. Les transactions démarrent au moment où une instruction BEGIN TRANSACTION est exécutée. En revanche, le numéro de séquence de la transaction commence à la première opération de lecture ou d'écriture suivant l'instruction BEGIN TRANSACTION. Ce numéro augmente de 1 à chaque fois qu'il est attribué.

Quand une seule des deux options de base de données (READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION) est définie sur ON, des copies logiques (versions) sont maintenues pour toutes les modifications de données effectuées dans la base de données. Chaque fois qu'une ligne est modifiée par une transaction, l'instance du moteur de base de données enregistre une version de l'image précédemment validée de la ligne dans le magasin de versions. Chaque version porte le numéro de séquence de la transaction responsable de la modification. Les versions des lignes modifiées sont enchaînées au moyen d'une liste de liens. La valeur de ligne la plus récente est toujours stockée dans la base de données active et enchaînée aux lignes avec contrôle de version dans la banque de versions.

Les versions de lignes sont conservées suffisamment longtemps pour satisfaire aux besoins des transactions qui s'exécutent sous le régime d'isolement « contrôle de version de ligne ». Le moteur de base de données recherche le plus ancien numéro de séquence de transaction et supprime de façon périodique toutes les versions de lignes dont le numéro de séquence de transaction est inférieur à celui-ci.

Lorsque les deux options de base de données sont définies sur OFF, seules les lignes modifiées par des déclencheurs ou des sessions MARS, ou lues par des opérations d'index en ligne, sont avec version. Ces versions de lignes sont libérées lorsqu'elles ne sont plus nécessaires. Un processus en arrière-plan supprime les versions de lignes obsolètes.

Comportement lors de la lecture de données

Lorsque des transactions exécutées dans le cadre d'un contrôle version de ligne lisent des données, les opérations de lecture n'acquièrent pas de verrous partagés (S) sur les données lues et ne bloquent donc pas les transactions qui modifient les données. De plus, la charge liée au verrouillage des ressources est minimisée en raison de la réduction du nombre de verrous acquis. L'isolement READ COMMITTED avec contrôle de version de ligne et l'isolement SNAPSHOT sont conçus pour garantir la cohérence des données avec version au niveau de l'instruction ou de la transaction.

Toutes les requêtes, y compris les transactions qui s'exécutent sous les niveaux d'isolement basés sur le contrôle de version de ligne, acquièrent des verrous de stabilité du schéma (Sch-S) au cours de la compilation et de l'exécution. Pour cette raison, les requêtes sont bloquées lorsqu'une transaction concurrente détient un verrou de modification de schéma (Sch-M) sur la table. Par exemple, une opération DDL (langage de définition de données) acquiert un verrou Sch-M avant de modifier les informations de schéma de la table. Les transactions, y compris celles qui s'exécutent sous un niveau d'isolement basé sur le contrôle de version de ligne, sont bloquées lors d'une tentative visant à acquérir un verrou Sch-S. Inversement, une requête qui détient un verrou Sch-S bloque une transaction simultanée qui tente d'acquérir un verrou Sch-M.

Lorsqu'une transaction avec niveau d'isolement SNAPSHOT est lancée, l'instance de moteur de base de données enregistre toutes les transactions en cours. Lorsque la transaction SNAPSHOT lit une ligne comportant une chaîne de versions, le moteur de base de données suit la chaîne et récupère la ligne où se trouve le numéro de séquence de la transaction :

• se rapproche le plus, sans le dépasser, du numéro de séquence de la transaction qui lit la ligne ;

• ne figure pas dans la liste de transactions actives au moment de la création de la transaction.

Les opérations de lecture effectuées par une transaction SNAPSHOT récupèrent la dernière version de chaque ligne validée au début de la transaction SNAPSHOT. Ceci permet de disposer d'un instantané cohérent de manière transactionnelle des données présentes au début de la transaction.

Les transactions READ COMMITTED utilisant le contrôle de version de ligne fonctionnement pratiquement de la même manière. La différence est que ces transactions READ COMMITTED n'utilisent pas leurs propres numéros de séquence de transaction lors du choix des versions de lignes. Chaque fois qu'une instruction est lancée, la transaction READ COMMITTED lit le dernier numéro de séquence de transaction émis pour cette instance du moteur de base de données. C'est ce numéro qui servira à sélectionner les versions de lignes pour cette instruction. Ceci permet aux transactions READ COMMITTED de voir un instantané des données telles qu'elles existaient au début de chaque instruction.

Comportement lors de la modification de données

Le comportement de l'écriture des données est sensiblement différent en présence ou non d'un verrouillage activé.

Modification des données sans verrouillage optimisé

Dans une transaction READ COMMITTED avec contrôle de version de ligne, le choix des lignes à mettre à jour se fait au moyen d'une analyse bloquante. Au cours de celle-ci, un verrou de mise à jour (U) est acquis sur la ligne de données au fur et à mesure que les valeurs de données sont lues. La même chose se produit avec une transaction READ COMMITTED qui n'utilise pas le contrôle de version de ligne. Si la ligne de données ne répond pas aux critères de mise à jour, le verrou de mise à jour est déplacé sur la ligne suivante, qui est analysée.

Les transactions s'exécutant avec isolement SNAPSHOT adoptent une approche optimiste en matière de modification de données car elles ne verrouillent les lignes que lorsque les données qui s'y trouvent doivent être modifiées. Sinon, les verrous ne sont pas placés sur les données tant que celles-ci doivent être modifiées. Lorsqu'une ligne de données répond aux critères de mise à jour, la transaction SNAPSHOT vérifie que la ligne n'a pas été modifiée par une transaction simultanée effectuée après le début de la transaction SNAPSHOT. Si la ligne de données a été modifiée en dehors de la transaction SNAPSHOT, un conflit de mise à jour se produit et la transaction SNAPSHOT est arrêtée. Le conflit de mise à jour est géré par le moteur de base de données. Il n'y a aucun moyen de désactiver la détection de conflit de mise à jour.

Modification des données avec verrouillage optimisé

Lorsque le verrouillage optimisé est activé, que l'option de base de données READ_COMMITTED_SNAPSHOT (RCSI) est activée et que le niveau d'isolation READ COMMITTED par défaut est utilisé, les lecteurs n'acquièrent aucun verrou et les enregistreurs acquièrent des verrous de bas niveau de courte durée, au lieu de verrous qui expirent à la fin de la transaction.

L'activation du RCSI est recommandée pour une efficacité maximale avec un verrouillage optimisé. 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, tant pour les lecteurs que pour les écrivains, ce qui entraîne une augmentation des blocages et de la mémoire des verrous.

Lorsque le RCSI est activé et que l'on utilise le niveau d'isolation READ COMMITTED par défaut, 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. Une requête n'attend que si la ligne remplit les conditions requises et qu'il existe une autre transaction d'écriture active sur cette ligne ou cette page. 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.

Si des conflits de mise à jour sont détectés avec RCSI et dans le niveau d'isolation READ COMMITTED par défaut, ils sont gérés et retentés automatiquement sans impact sur les charges de travail client.

Une fois le verrouillage optimisé activé, à l'aide du niveau d'isolation SNAPSHOT, le comportement des conflits de mise à jour est le même. Les conflits de mise à jour doivent être gérés et retentés par l'application.

Comportements lors de résumé

Le tableau suivant résume les différences entre l'isolement SNAPSHOT et l'isolement READ COMMITTED avec contrôle de version de ligne.

Propriété	Niveau d’isolement READ COMMITTED à l’aide du contrôle de version de ligne	Niveau d'isolement SNAPSHOT
L'option de base de données doit être défini sur ON pour assurer la prise en charge nécessaire.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Manière dont une session demande le type spécifique de contrôle de version de ligne.	Utilisez le niveau d’isolation par défaut READ COMMITTED ou exécutez l’instruction SET TRANSACTION ISOLATION LEVEL pour spécifier le niveau d’isolation READ COMMITTED. Ceci peut se faire après le début de la transaction.	Requiert l'exécution de SET TRANSACTION ISOLATION LEVEL pour spécifier le niveau d'isolement SNAPSHOT avant le début de la transaction.
La version des données lue par les instructions.	Toutes les données qui ont été validées avant le début de chaque instruction.	Toutes les données qui ont été validées avant le début de chaque transaction.
Manière dont les mises à jour sont gérées.	Sans verrouillage optimisé : revient des versions de lignes aux données réelles pour sélectionner les lignes à mettre à jour et utilise des verrous de mise à jour sur les lignes de données sélectionnées. Acquiert des verrous exclusifs sur les lignes à modifier réellement. Pas de détection de conflit de mise à jour. Avec verrouillage optimisé : les lignes sont sélectionnées en fonction de la dernière version validée sans verrous acquis. Si les lignes sont éligibles pour la mise à jour, les verrous de ligne ou de page exclusifs sont acquis. Si des conflits de mise à jour sont détectés, ils sont gérés et retentés automatiquement.	Utilise les versions de lignes pour sélectionner les lignes à mettre à jour. Essaie d'acquérir un verrou exclusif sur les lignes à modifier réellement et, si les données ont été modifiées par une autre transaction, génère un conflit de mise à jour qui entraîne l'arrêt de la transaction.
Détection d'un conflit de mise à jour	Sans verrouillage optimisé : aucun. Avec le verrouillage optimisé : si des conflits de mise à jour sont détectés, ils sont gérés et retentés automatiquement.	Prise en charge intégrée. Ne peut être désactivée.

Utilisation de la ressource de contrôle de version de ligne

L'infrastructure de contrôle de version de ligne prend en charge les fonctionnalités suivantes dans le moteur de base de données :

• Déclencheurs
• MARS (Multiple Active Results Sets)
• Indexation en ligne

La structure de contrôle de version de ligne prend également en charge les niveaux d’isolement des transactions basé sur le contrôle de version de ligne :

• Quand l’option de base de données READ_COMMITTED_SNAPSHOT est définie sur ON, les transactions READ_COMMITTED permettent une lecture cohérente au niveau des instructions grâce au contrôle de version de ligne.
• Quand l’option de base de données ALLOW_SNAPSHOT_ISOLATION est définie sur ON, les transactions SNAPSHOT permettent une lecture cohérente au niveau des transactions grâce au contrôle de version de ligne.

Les niveaux d'isolement basé sur le contrôle de version de ligne réduisent le nombre de verrous obtenus par la transaction en supprimant l'utilisation des verrous partagés dans les opérations de lecture. Les performances système sont ainsi accrues et les ressources nécessaires à la gestion des verrous diminuées. La réduction des blocages d'une transaction par des verrous obtenus par d'autres transactions permet également d'augmenter les performances.

Les niveaux d'isolement basé sur le contrôle de version de ligne augmentent les ressources nécessaires pour la modification de données. L'activation de ces options induit automatiquement le contrôle de version de toutes les modifications apportées aux données de la base de données. Une copie des données avant modification est stockée dans le magasin de versions, même s'il n'existe aucune transaction active utilisant l'isolement basé sur le contrôle de version de ligne. Les données modifiées contiennent un pointeur vers les données de version dans le magasin de versions. En ce qui concerne les objets volumineux, seule la partie de l'objet ayant été modifiée est copiée dans le magasin de versions.

Espace occupé dans tempdb

Pour chaque instance du moteur de base de données, le magasin de versions doit disposer de suffisamment d'espace pour contenir les versions des lignes. L’administrateur de base de données doit s’assurer que tempdb et d’autres bases de données (si la récupération de base de données accélérée est activée) disposent d’un espace suffisant pour prendre en charge le magasin de versions. Il existe deux types de magasins de versions :

• Le magasin de versions de construction d'index en ligne, utilisée pour les constructions d'index en ligne.
• La banque des versions commune, utilisée pour toutes les autres opérations de modification des données.

Les versions de ligne doivent être stockées pour toute la durée au cours de laquelle une transaction active doit être accessible. De manière périodique, un thread d'arrière-plan supprime les versions de ligne qui ne sont plus nécessaires et libère de l'espace dans la banque de versions. Une transaction longue empêche la libération d'espace dans une banque des versions si l'une des conditions suivantes est remplie :

• elle utilise l'isolement basé sur le contrôle de version de ligne ;
• elle utilise des déclencheurs, des jeux MARS ou des opérations de construction d'index en ligne ;
• elle génère des versions de ligne.

Si le magasin de versions se trouve dans tempdb, et que tempdb manque d'espace, le moteur de base de données oblige les magasins de versions à se réduire. Lors de ce processus de réduction, les transactions les plus longues n'ayant pas encore généré de versions de ligne sont marquées comme victimes. Le message 3967 est inscrit dans le journal d'erreurs pour chaque transaction victime. Toute transaction marquée comme victime ne peut plus lire les versions de ligne de la banque des versions. En cas de tentative de lecture des versions de ligne, le message 3966 est généré et la transaction est restaurée. En cas de réussite du processus de réduction, l'espace est disponible dans tempdb. Dans le cas contraire, l'espace de tempdb devient insuffisant, avec les conséquences suivantes :

• L'exécution des opérations d'écriture se poursuit, mais sans génération de versions. Un message d'information (3959) apparaît dans le journal d'erreurs. La transaction d'écriture des données n'en est pas affectée.

• Les transactions qui tentent d'accéder aux versions de ligne n'ayant pas été générées à cause d'une restauration complète dans tempdb se terminent sur l'erreur 3958.

Espace occupé dans les lignes de données

Chaque ligne de base de données peut, à des fins d'informations sur le contrôle de version de ligne, utiliser un maximum de 14 octets en fin de ligne. Les informations sur le contrôle de version de ligne contiennent le numéro de séquence de la transaction ayant validé la version et le pointeur vers la ligne avec version. Ces 14 octets sont ajoutés lors de la première modification de la ligne ou lors de l'insertion d'une nouvelle ligne, pour autant que l'une des conditions suivantes soit remplie :

• Les options READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION sont définies sur ON.
• la table comporte un déclencheur ;
• des jeux MARS (Multiple Active Results Sets) sont en cours d'utilisation ;
• des opérations de construction d'index en ligne sont en cours d'exécution dans la table.

Si la banque de versions est en tempdb, ces 14 octets sont supprimés de la ligne de la base de données la première fois que la ligne est modifiée dans toutes ces conditions :

• Les options READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION sont définies sur OFF.
• le déclencheur n'existe plus dans la table ;
• les jeux MARS ne sont pas en cours d'utilisation ;
• aucune opération de construction d'index en ligne n'est en cours d'exécution.

Les 14 octets sont également supprimés lorsqu’une ligne est modifiée si la récupération de base de données accélérée n’est plus activée et que les conditions ci-dessus sont satisfaites.

En cas d'utilisation de l'une des fonctionnalités de contrôle de version de ligne, vous devrez peut-être allouer un espace disque suffisant pour permettre les 14 octets nécessaires par ligne de base de données. L'ajout d'informations sur le contrôle de version de ligne peut entraîner le fractionnement des pages d'index ou l'allocation d'une nouvelle page de données en cas d'insuffisance d'espace disponible sur la page actuelle. Par exemple, si la longueur de ligne moyenne est de 100 octets, les 14 octets supplémentaires peuvent provoquer une augmentation de 14 pour cent de la table existante.

La réduction du facteur de remplissage peut permettre d’empêcher ou de réduire la fragmentation des pages d’index. Pour afficher les informations relatives à la densité de page actuelle pour les données et les index d'une table ou d'une vue, vous pouvez utiliser sys.dm_db_index_physical_stats.

Espace occupé dans les objets volumineux

Le moteur de base de données prend en charge plusieurs types de données pouvant contenir des chaînes volumineuses d'une longueur de 2 gigaoctets (Go) au maximum, tels que : nvarchar(max), varchar(max), varbinary(max), ntext, text et image. Les données volumineuses stockées à l'aide de ces types de données sont stockées dans une série de fragments de données associés à la ligne de données. Les informations sur le contrôle de version de ligne sont stockées dans chaque fragment utilisé pour le stockage des chaînes volumineuses. Les fragments de données sont stockés dans un ensemble de pages dédiées aux objets volumineux d'une table.

Lorsque des valeurs importantes sont ajoutées dans une base de données, elles sont allouées avec un maximum de 8 040 octets de données par fragment. Les versions antérieures du moteur de base de données pouvaient stocker jusqu'à 8 080 octets de données ntext, text ou image par fragment.

Les données des objets volumineux (LOB) ntext, text et image existants ne sont pas mises à jour pour libérer de l'espace pour les informations sur le contrôle de version de ligne lorsqu'une base de données est mise à niveau vers SQL Server à partir d'une version antérieure de SQL Server. Cependant, lors de leur première modification, les données LOB sont mises à niveau de manière dynamique pour permettre le stockage des informations sur le contrôle de version, même si des versions de lignes sont générées. Une fois la mise à niveau des données LOB terminée, le nombre maximum d'octets stockés par fragment passe de 8 080 à 8 040. Le processus de mise à niveau équivaut à supprimer la valeur LOB et à réinsérer la même valeur. Les données LOB sont mises à niveau même en cas de modification d'un seul octet. Cette opération est unique pour chaque colonne ntext, text ou image Chaque opération peut néanmoins générer une quantité importante d'allocations de pages et d'activité E/S selon la taille des données LOB, ainsi qu'une activité importante d'écriture dans le journal si la modification doit être écrite en entier dans le journal. Les opérations WRITETEXT et UPDATETEXT sont écrites dans le journal de façon minimale si le mode de récupération de la base de données n'est pas défini sur FULL.

Un espace disque suffisant doit être alloué pour satisfaire à cette exigence.

Contrôle du contrôle de version de ligne et du magasin de versions

Le moteur de base de données fournit des outils sous la forme de vues dynamiques de gestion (DMV) et de compteurs de performances pour surveiller les performances et les problèmes des processus de versionnement des rangées, de stockage des versions et d'isolation des instantanés.

DMV

Les vues DMV suivantes fournissent des informations sur l'état système actuel de tempdb et du magasin de versions, ainsi que sur les transactions utilisant le contrôle de version de ligne.

• sys.dm_db_file_space_usage. Retourne des informations sur l'utilisation de l'espace pour chaque fichier de la base de données. Pour plus d'informations, consultez sys.dm_db_file_space_usage (Transact-SQL).

• sys.dm_db_session_space_usage. Renvoie les activités d'allocation ou de désallocation des pages par session de la base de données. Pour plus d'informations, consultez sys.dm_db_session_space_usage (Transact-SQL).

• sys.dm_db_task_space_usage. Renvoie l'activité d'allocation/désallocation des pages par tâche pour la base de données. Pour plus d'informations, consultez sys.dm_db_task_space_usage (Transact-SQL).

• sys.dm_tran_top_version_generators. Renvoie une table virtuelle pour les objets générant la majorité des versions d'un magasin de versions. Agrégation des 256 premières longueurs d'enregistrement selon database_id et rowset_id. Utilisez cette fonction pour rechercher les clients les plus volumineux de la banque de versions. S’applique uniquement au magasin de versions dans tempdb. Pour plus d'informations, consultez sys.dm_tran_top_version_generators (Transact-SQL).

• sys.dm_tran_version_store. Renvoie une table virtuelle qui affiche tous les enregistrements de version du magasin de versions commun. S’applique uniquement au magasin de versions dans tempdb. Pour plus d'informations, consultez sys.dm_tran_version_store (Transact-SQL).

• sys.dm_tran_version_store_space_usage. Retourne une table virtuelle qui affiche l'espace total dans tempdb utilisé par les enregistrements du magasin de versions pour chaque base de données. S’applique uniquement au magasin de versions dans tempdb. Pour plus d'informations, consultez sys.dm_tran_version_store_space_usage (Transact-SQL).

  Remarque

  Les objets système sys.dm_tran_top_version_generators et sys.dm_tran_version_store sont potentiellement très coûteuses à exécuter, car les deux interrogent l'intégralité du magasin de versions, qui peut être volumineux. sys.dm_tran_version_store_space_usage est efficace et peu coûteuse à exécuter, car elle ne parcourt pas les enregistrements du magasin de versions individuels et retourne un espace du magasin de versions agrégé consommé dans tempdb par base de données.

• sys.dm_tran_active_snapshot_database_transactions. Retourne une table virtuelle pour toutes les transactions actives dans l'ensemble des bases de données d'une instance de SQL Server utilisant le contrôle de version de ligne. Les transactions système n'apparaissent pas dans cette vue DMV. Pour plus d'informations, consultez sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

• sys.dm_tran_transactions_snapshot. Renvoie une table virtuelle qui affiche les instantanés pris par chaque transaction. L'instantané contient le numéro de séquence des transactions actives utilisant le contrôle de version de ligne. Pour plus d'informations, consultez sys.dm_tran_transactions_snapshot (Transact-SQL).

• sys.dm_tran_current_transaction. Renvoie une ligne unique affichant des informations sur l'état du contrôle de version de ligne pour la transaction de la session en cours. Pour plus d'informations, consultez sys.dm_tran_current_transaction (Transact-SQL).

• sys.dm_tran_current_snapshot. Retourne une table virtuelle affichant toutes les transactions actives au début de la transaction d'isolement d'instantané. Si la transaction actuelle utilise l'isolement d'instantané, cette fonction ne retourne aucune ligne. La vue DMV sys.dm_tran_current_snapshot est similaire à sys.dm_tran_transactions_snapshot, sauf qu'elle ne retourne que les transactions actives pour l'instantané actuel. Pour plus d'informations, consultez sys.dm_tran_current_snapshot (Transact-SQL).

• sys.dm_tran_persistent_version_store_stats. Retourne des statistiques pour le magasin de versions persistants dans chaque base de données utilisée lorsque la récupération de base de données accélérée est activée. Pour plus d’informations, consultez sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Compteurs de performance

Les compteurs de performances suivants surveillent la mémoire des versions dans tempdb, ainsi que les transactions utilisant le contrôle de version de ligne. Les compteurs de performances se trouvent dans l'objet de performances SQLServer:Transactions.

• Espace disponible dans tempdb (Ko). Contrôle la quantité, en kilooctets (Ko), d'espace libre dans la base de données tempdb. tempdb doit disposer d'un espace libre suffisant pour gérer le magasin de versions prenant en charge l'isolation d'instantané.

  La formule ci-dessous vous donne une estimation grossière de la taille du magasin de versions. Pour estimer la taille du magasin de versions en ce qui concerne les transactions longues, il peut s'avérer utile de contrôler les taux de génération et de nettoyage.

  [taille du magasin de versions commune] = 2 * [données du magasin de versions générées par minute] * [délai d'exécution le plus long (en minutes) de la transaction]

  Le délai le plus long d'exécution de transaction ne doit pas inclure les constructions d'un index en ligne. Étant donné que ces dernières opérations peuvent prendre un certain temps pour les tables volumineuses, elles utilisent un autre magasin de versions. La taille approximative du magasin de versions utilisé pour les constructions d'un index en ligne équivaut à la quantité de données modifiées dans la table, y compris tous les index, pendant toute la durée d'activité de la construction de l'index en ligne.

• Taille de la banque des versions (Ko). Contrôle la taille en Ko de tous les magasins de versions dans tempdb. Cette information permet de déterminer la quantité d'espace nécessaire dans la base de données tempdb pour le magasin de versions. Le contrôle de ce compteur sur une période de temps fournit une estimation utile de l'espace supplémentaire requis pour tempdb.

• Taux de génération de version (Ko/s). Contrôle le taux de génération de version en Ko par seconde pour tous les magasins de versions dans tempdb.

• Taux de nettoyage de version (Ko/s). Contrôle le taux de nettoyage de version en Ko par seconde pour tous les magasins de versions dans tempdb.

  Remarque

  Les informations obtenues à l'aide des compteurs Taux de génération de version (Ko/s) et Taux de nettoyage de version (Ko/s) permettent de prévoir l'espace nécessaire pour tempdb.

• Nombre d’unités dans la banque des versions. Contrôle le nombre d'unités dans le magasin de versions.

• Création d’unité dans la banque des versions. Contrôle le nombre total d'unités créées dans le magasin de versions pour le stockage des versions de lignes depuis le démarrage de l'instance.

• Troncation d’unité dans la banque des versions. Contrôle le nombre total d'unités tronquées dans le magasin de versions depuis le démarrage de l'instance. Une unité de magasin de versions est tronquée lorsque SQL Server spécifie qu'aucune des lignes de versions stockées dans l'unité du magasin de versions n'est requise pour l'exécution des transactions actives.

• Proportion de conflits de mise à jour. Contrôle la proportion de transactions de mise à jour d'instantanés présentant des conflits de mise à jour par rapport au nombre total de transactions de mise à jour d'instantanés.

• Délai le plus long d’exécution de transaction. Contrôle le délai le plus long (en secondes) d'exécution de toute transaction utilisant le contrôle de version de ligne. Ce compteur permet de déterminer si l'exécution de l'une des transactions est trop longue.

• Transactions. Contrôle le nombre total de transactions actives. Les transactions système ne sont pas prises en compte.

• Transactions d'instantanés. Contrôle le nombre total de transactions d'instantanés actives.

• Transactions d'instantanés de mise à jour. Contrôle le nombre total de transactions d'instantanés effectuant des opérations de mise à jour.

• Transactions de versions non liées à des instantanés. Contrôle le nombre total de transactions actives non liées à des instantanés générant des enregistrements de versions.

  Remarque

  La somme des compteurs Transactions d'instantanés de mise à jour et Transactions de versions non liées à des instantanés représente le nombre total de transactions participant à la génération d'une version. La différence entre les compteurs Transactions d'instantanés et Transactions d'instantanés de mise à jour représente le nombre de transactions d'instantanés en lecture seule.

Exemple de niveau d’isolement basé sur le contrôle de version de ligne

Les exemples ci-dessous illustrent les différences de comportement entre les transactions d'isolement SNAPSHOT et les transactions READ COMMITTED qui utilisent le contrôle de version de ligne.

R : Utilisation du niveau d'isolation d'INSTANTANÉ

Dans cet exemple, une transaction exécutée sous isolement SNAPSHOT lit des données qui sont ensuite modifiées par une autre transaction. La transaction SNAPSHOT ne bloque pas l'opération de mise à jour exécutée par l'autre transaction et continue de lire les données à partir de la ligne avec version, en ignorant la modification apportée aux données. Toutefois, lorsque la transaction SNAPSHOT tente de modifier des données qui ont déjà été modifiées par l'autre transaction, la transaction SNAPSHOT génère une erreur et est terminée.

Sur la session 1 :

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Sur la session 2 :

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Sur la session 1 :

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Sur la session 2 :

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

Sur la session 1 :

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. Utilisation d'une isolation READ COMMITTED à l'aide du contrôle de version de ligne

Dans cet exemple, une transaction READ COMMITTED à l'aide du contrôle de version de ligne est exécutée en même temps qu'une autre transaction. La transaction READ COMMITTED se comporte différemment de la transaction SNAPSHOT. À l'instar d'une transaction SNAPSHOT, la transaction READ COMMITTED lit les lignes avec version même après la modification des données effectuée par l'autre transaction. Toutefois, contrairement à une transaction SNAPSHOT, la transaction READ COMMITTED :

• Lit les données modifiées une fois que l'autre transaction a validé les modifications de données.
• Est capable de mettre à jour les données modifiées par l'autre transaction, alors que cette opération n'est pas possible avec la transaction SNAPSHOT.

Sur la session 1 :

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Sur la session 2 :

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Sur la session 1 :

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Sur la session 2 :

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Sur la session 1 :

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Activer les niveaux d'isolation basés sur le contrôle de version de ligne

Les administrateurs de bases de données déterminent les paramètres de contrôle de version de ligne définis au niveau de la base de données à l’aide des options de base de données READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION de l’instruction ALTER DATABASE.

Quand l’option de base de données READ_COMMITTED_SNAPSHOT est définie sur ON, les mécanismes de prise en charge de l’option sont immédiatement activés. Lors du paramétrage de l’option READ_COMMITTED_SNAPSHOT, seule la connexion exécutant la commande ALTER DATABASE est autorisée dans la base de données. La base de données ne peut contenir aucune autre connexion ouverte avant la fin de l'exécution de la commande ALTER DATABASE. Il n’est pas nécessaire que la base de données soit en mode mono-utilisateur.

L'instruction Transact-SQL suivante active READ_COMMITTED_SNAPSHOT :

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

Quand l'option de base de données ALLOW_SNAPSHOT_ISOLATION est définie sur ON, l'instance du moteur de base de données ne commence pas à générer le contrôle de version de ligne pour les données modifiées que quand l'exécution de toutes les transactions actives modifiant les données de la base de données est terminée. Si des transactions de modification sont en cours, le moteur de base de données attribue la valeur PENDING_ON à l'option. Une fois l'exécution des transactions de modification terminées, l'état de l'option passe sur ON. Les utilisateurs ne peuvent lancer une transaction SNAPSHOT que quand l'option a la valeur ON. De même, la base de données passe par un état PENDING_OFF lorsque l'administrateur de bases de données définit l'option ALLOW_SNAPSHOT_ISOLATION sur OFF.

L'instruction Transact-SQL suivante activera ALLOW_SNAPSHOT_ISOLATION :

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

Le tableau suivant répertorie et décrit les différents états de l'option ALLOW_SNAPSHOT_ISOLATION. L'utilisation de la commande ALTER DATABASE avec l'option ALLOW_SNAPSHOT_ISOLATION ne bloque pas les utilisateurs qui sont en cours d'accès aux données de la base de données.

État de l'isolement SNAPSHOT pour la base de données actuelle	Description
OFF	La prise en charge des transactions d'isolement SNAPSHOT n'est pas activée. Aucune transaction d'isolement SNAPSHOT n'est autorisée.
PENDING_ON	La prise en charge des transactions d'isolement SNAPSHOT est en état de transition (de OFF à ON). L'exécution de toutes les transactions ouvertes doit être terminée. Aucune transaction d'isolement SNAPSHOT n'est autorisée.
ON	La prise en charge des transactions d'isolement SNAPSHOT est activée. SNAPSHOT transactions sont autorisées.
PENDING_OFF	La prise en charge des transactions d'isolement SNAPSHOT est en état de transition (de ON à OFF). Les transactions SNAPSHOT lancées dès ce moment ne peuvent accéder à la base de données. Les transactions SNAPSHOT existantes peuvent toujours accéder à la base de données. Les transactions d’écriture existantes utilisent toujours le contrôle de version dans cette base de données. L’état PENDING_OFF ne devient pas OFF tant que toutes les transactions SNAPSHOT démarrées lorsque l’état d’isolation de la base de données SNAPSHOT a été ON terminé.

Utilisez l'affichage catalogue sys.databases pour déterminer l'état des deux options de contrôle de version de ligne de la base de données.

Toutes les mises à jour des tables utilisateur et de certaines tables système stockées dans master et msdb génère le contrôle de version de ligne.

L'option ALLOW_SNAPSHOT_ISOLATION est automatiquement définie sur ON dans les bases de données master et msdb. Elle ne peut être désactivée.

Les utilisateurs ne peuvent pas activer l’option READ_COMMITTED_SNAPSHOT sur ON dans master, tempdb, ou msdb.

Utilisation de niveaux d'isolation basés sur le contrôle de version de ligne

L'infrastructure de contrôle de version de ligne est toujours activée et est utilisée par plusieurs fonctionnalités. En plus de fournir des niveaux d'isolement basés sur le contrôle de version de ligne, elle permet la prise en charge des modifications apportées aux déclencheurs et aux sessions MARS (Multiple Active Result Sets), ainsi que la prise en charge des lectures de données pour les opérations d'index en ligne.

Les niveaux d'isolement basés sur le contrôle de version de ligne sont activés au niveau de la base de données. Toute application accédant à des objets de bases de données activées peut exécuter des requêtes en utilisant les niveaux d'isolement suivants :

• READ COMMITTED qui utilise contrôle de version de ligne par l'activation de l'option de base de données READ_COMMITTED_SNAPSHOT sur ON, comme illustré dans l'exemple de code suivant :

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  Quand l’option READ_COMMITTED_SNAPSHOT est activée pour la base de données, toutes les requêtes s’exécutant sous le niveau d’isolation READ COMMITTED utilisent le contrôle de version de ligne, ce qui signifie que les opérations de lecture ne bloquent pas les opérations de mise à jour.

• Isolement SNAPSHOT en définissant l'option de base de données ALLOW_SNAPSHOT_ISOLATION sur ON comme illustré dans l'exemple de code suivant :

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Lorsque vous utilisez des requêtes inter-bases de données, une transaction exécutée sous l’isolation SNAPSHOT peut accéder aux tables dans la ou les bases de données sur lesquelles l’option de base de données ALLOW_SNAPSHOT_ISOLATION est définie sur ON. Pour accéder aux tables des bases de données qui n’ont pas l’option de base de données ALLOW_SNAPSHOT_ISOLATION définie sur ON, le niveau d’isolation doit être modifié. Ainsi, dans l'exemple de code suivant, une instruction SELECT exécutée dans le cadre d'une transaction SNAPSHOT joint deux tables. Une table appartient à une base de données dans laquelle le niveau d'isolation SNAPSHOT n'est pas activé. Lorsque l'instruction SELECT s'exécute sous le niveau d'isolement SNAPSHOT, elle échoue.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

  Dans l'exemple de code suivant, la même instruction SELECT a été modifiée pour faire passer le niveau d'isolation de la transaction à READ COMMITTED lors de l'accès à un tableau spécifique. Grâce à cette modification, l'exécution de l'instruction SELECT aboutit.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1 WITH (READCOMMITTED)
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

Limites liées aux transactions utilisant les niveaux d’isolement basés sur le contrôle de version de ligne

Tenez compte des limites suivantes lors de l'utilisation des niveaux d'isolement basés sur le contrôle de version de ligne :

• READ_COMMITTED_SNAPSHOT ne peut pas être activé dans tempdb, msdb ou master.

• Les tables temporaires globales sont stockées dans tempdb. Si une transaction SNAPSHOT implique l'accès à des tables temporaires globales, vous devez effectuer l'une des opérations suivantes :

  • Activez l'option de base de données ALLOW_SNAPSHOT_ISOLATION sur ON dans tempdb.
  • Utilisez un indicateur d'isolement afin de modifier le niveau d'isolement pour l'instruction.

• Les transactions SNAPSHOT échouent quand :

  • Une base de données est passée en lecture seule après que la transaction SNAPSHOT ait démarré, mais avant que la transaction SNAPSHOT ait accédé à la base de données.
  • S'il y a eu accès à des objets de plusieurs bases de données, l'état d'une base de données a été modifié au point qu'une récupération de base de données a eu lieu après que la transaction SNAPSHOT ait démarré, mais avant que la transaction SNAPSHOT ait accédé à la base de données. Par exemple : la base de données a été définie sur OFFLINE puis sur ONLINE, la base de données a été automatiquement fermée et rouverte en raison de l’option AUTO_CLOSE définie sur ON, ou la base de données a été détachée et attachée.

• Les transactions distribuées, notamment les requêtes dans les bases de données partitionnées distribuées, ne sont pas prises en charge sous le niveau d'isolement SNAPSHOT.

• Le moteur de base de données ne conserve pas plusieurs versions des métadonnées système. Les instructions DDL (Data Definition Language) portant sur des tables et autres objets de base de données (index, vues, types de données, procédures stockées et fonctions CLR (Common Language Runtime)) modifient les métadonnées. Si une instruction DDL modifie un objet, toute référence simultanée à l'objet sous le niveau d'isolement SNAPSHOT entraînera l'échec de la transaction SNAPSHOT. Les transactions READ COMMITTED n’ont pas cette limitation lorsque l’option de base de données READ_COMMITTED_SNAPSHOT est définie sur ON.

  Supposons, par exemple, qu'un administrateur de base de données exécute l'instruction ALTER INDEX suivante.

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
  GO
  

  Toute transaction d'instantané qui est active au moment de l'exécution de l'instruction ALTER INDEX recevra une erreur si elle tente de faire référence à la table HumanResources.Employee après l'exécution de l'instruction ALTER INDEX. Les transactions READ COMMITTED utilisant le contrôle de version de ligne ne sont pas affectées.

  Remarque

  Les opérations BULK INSERT peuvent entraîner des modifications au niveau des métadonnées de la table cible (par exemple, lors de la désactivation des vérifications de contraintes). Dans ce cas, les transactions simultanées d'isolement SNAPSHOT qui accèdent à des tables faisant l'objet d'insertion en bloc échouent.

Personnalisation du verrouillage et du contrôle de version de ligne

Personnalisation du délai d'expiration des verrous

Quand une instance du moteur de base de données ne peut pas accorder un verrou à une transaction, car une autre transaction a déjà un verrou en conflit sur la ressource, la première transaction se bloque, dans l'attente de la libération du verrou existant. Par défaut, il n’existe aucun délai d’attente de verrouillage. Par conséquent, une transaction peut potentiellement être bloquée indéfiniment.

Le paramètre LOCK_TIMEOUT permet à une application de définir la durée maximale pendant laquelle une instruction reste en attente sur une ressource bloquée. Lorsqu'une instruction a attendu plus longtemps que le paramètre LOCK_TIMEOUT, le relevé bloqué est automatiquement annulé et le message d'erreur 1222 (Lock request time-out period exceeded) est renvoyé. Cependant, toute transaction contenant l'instruction n'est pas restaurée. L'application doit donc posséder un gestionnaire d'erreurs capable d'intercepter le message d'erreur 1222. Si une application n’intercepte pas l’erreur, l’application peut continuer à ignorer qu’une instruction individuelle au sein d’une transaction a été annulée, mais la transaction reste active. Des erreurs peuvent se produire, car les instructions ultérieures dans la transaction peuvent dépendre de l’instruction qui n’a jamais été exécutée.

L'implémentation d'un gestionnaire d'erreurs qui intercepte le message d'erreur 1222 permet à une application de prendre les mesures conséquentes au délai d'expiration, par exemple soumettre à nouveau l'instruction qui été bloquée ou restaurer toute la transaction.

Pour déterminer le paramètre LOCK_TIMEOUT actuel, exécutez la fonction @@LOCK_TIMEOUT :

SELECT @@LOCK_TIMEOUT;
GO

Personnalisation du niveau d'isolation de la transaction

READ COMMITTED est le niveau d’isolation par défaut du moteur de base de données. Si une application doit fonctionner à un niveau d'isolation différent, elle peut le définir selon plusieurs méthodes :

• Exécuter l’instruction SET TRANSACTION ISOLATION LEVEL.
• Les applications ADO.NET qui utilisent l'espace de noms System.Data.SqlClient peuvent spécifier une option IsolationLevel à l'aide de la méthode SqlConnection.BeginTransaction.
• Les applications qui utilisent ADO peuvent définir la propriété Autocommit Isolation Levels.
• Lors du lancement d'une transaction, les applications utilisant OLE DB peuvent appeler ITransactionLocal::StartTransaction avec le paramètre isoLevel défini sur le niveau d'isolation de la transaction souhaité. Lorsque vous spécifiez le niveau d'isolation en mode de validation automatique, les applications utilisant OLE DB peuvent affecter à la propriété DBPROP_SESS_AUTOCOMMITISOLEVELS de DBPROPSET_SESSION la valeur de niveau d'isolation de la transaction souhaitée.
• Les applications qui utilisent ODBC peuvent définir l'attribut SQL_COPT_SS_TXN_ISOLATION à l'aide de SQLSetConnectAttr.

Lorsque le niveau d'isolation est spécifié, le verrouillage s'applique à ce niveau d'isolation, à toutes les requêtes et instructions DML de la session . Le niveau d'isolation reste en vigueur jusqu'à la fin de la session ou jusqu'à ce qu'il soit modifié.

L'exemple suivant montre comment définir le niveau d'isolation SERIALIZABLE :

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

Le niveau d'isolation peut être remplacé si nécessaire pour des requêtes ou des instructions DML individuelles, en spécifiant un indicateur de niveau table. Un indicateur de niveau table n'affecte pas les autres instructions de la session.

Pour déterminer le niveau d'isolation des transactions en cours, utilisez l'instruction DBCC USEROPTIONS comme dans l'exemple qui suit. Le jeu de résultats peut être différent sur votre système.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Voici le jeu de résultats.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Indicateurs de verrouillage

Il est possible de spécifier des indicateurs de verrouillage pour des références de table individuelles dans les instructions SELECT, INSERT, UPDATE, DELETE et MERGE. Ces indicateurs déterminent le type de verrouillage ou de contrôle de version de ligne qu'utilise l'instance du moteur de base de données pour les données de la table. Les indicateurs de verrouillage au niveau des tables peuvent être utilisés pour un contrôle plus fin des types de verrous acquis sur un objet. Ces options de verrouillage remplacent le niveau d'isolement courant de la transaction pour la session.

Pour plus d'informations sur les indicateurs de verrouillage spécifiques et leurs comportements, consultez Indicateurs de table (Transact-SQL).

Il se peut que le moteur de base de données doive obtenir des verrous lors de la lecture de métadonnées, même lors du traitement d'une instruction avec un indicateur de verrouillage qui empêche les demandes de verrous de partage lors de la lecture de données. Par exemple, une instruction SELECT exécutée sous le niveau d'isolation READ UNCOMMITTED ou utilisant l'indice NOLOCK n'acquiert pas de verrous de partage lors de la lecture de données, mais peut parfois demander des verrous lors de la lecture d'une vue du catalogue du système. Cela signifie qu’il est possible qu’une telle instruction SELECT soit bloquée lorsqu’une transaction simultanée modifie les métadonnées de la table.

Comme l'illustre l'exemple suivant, si le niveau d'isolation de la transaction est SERIALIZABLE et que l'indicateur de verrouillage au niveau des tables NOLOCK est utilisé avec l'instruction SELECT, les verrous de clé habituellement utilisés pour préserver des transactions SERIALIZABLE ne sont pas appliqués.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

Le seul verrou appliqué faisant référence à HumanResources.Employee est le verrou de stabilité de schéma (Sch-S). Dans ce cas, la possibilité de sérialisation n'est plus garantie.

L’option LOCK_ESCALATION de ALTER TABLE évite les verrous de table pendant l’escalade de verrous et active les verrous HoBT (partition) sur les tables partitionnées. Cette option n'est pas un indice de verrouillage, et peut être utilisée pour réduire l'escalade de verrous. Pour plus d’informations, consultez ALTER TABLE (Transact-SQL).

Personnalisation du verrouillage pour un index

Dans la plupart des cas, le moteur de base de données utilise une stratégie de verrouillage dynamique qui choisit automatiquement la granularité de verrouillage la plus appropriée pour les requêtes. Nous vous recommandons de ne pas remplacer les niveaux de verrouillage par défaut, sauf si les modèles d'accès à la table ou à l'index sont bien assimilés et cohérents et s'il existe une contention de ressources à résoudre. Le remplacement d’un niveau de verrouillage peut affecter considérablement les accès simultanés à une table ou un index. Par exemple, la spécification de verrous de niveau table uniquement sur une table de grande taille à laquelle les utilisateurs accèdent fréquemment peut provoquer des goulets d’étranglement, car les utilisateurs doivent attendre que le verrou de niveau table soit libéré avant de pouvoir accéder à la table.

Il existe quelques cas où l'interdiction du verrouillage de page ou de ligne peut être avantageuse, à condition que les modèles d'accès soient bien assimilés et cohérents. Par exemple, une application de base de données utilise une table de recherche mise à jour chaque semaine via un processus par lot. Les lecteurs simultanés accèdent à la table avec un verrou partagé (S) et la mise à jour par lot hebdomadaire accède à la table avec un verrou exclusif (X). La désactivation du verrouillage de page et de ligne sur la table réduit la charge de traitement liée au verrouillage tout au long de la semaine en permettant aux lecteurs d'accéder simultanément à la table via des verrous de table partagés. Lorsque le programme de traitement par lot s'exécute, il peut effectuer la mise à jour efficacement, car il obtient un verrou de table exclusif.

La désactivation du verrouillage de page et de ligne peut parfois être acceptable, car la mise à jour par lot hebdomadaire empêche les lecteurs simultanés d'accéder à la table pendant l'exécution de la mise à jour. Si le programme de traitement par lot modifie seulement quelques lignes ou pages, vous pouvez changer le niveau de verrouillage afin d'autoriser le verrouillage de ligne ou de page ; cela permet à la table d'être lue sans blocage dans d'autres sessions. Si le programme de traitement par lot doit effectuer un grand nombre de mises à jour, l'obtention d'un verrou exclusif sur la table peut représenter la meilleure solution pour garantir l'efficacité et la totalité de son exécution.

Dans certaines charges de travail, un type d’interblocage peut se produire lorsque deux opérations simultanées obtiennent des verrous de ligne sur la même table, puis bloquent les uns les autres, car ils doivent tous deux verrouiller la page. L'interdiction des verrous de ligne force l'une des opérations à attendre, ce qui évite le blocage. Pour plus d'informations sur les interblocages, consultez le Guide des interblocages.

La granularité de verrouillage utilisée sur un index peut être définie via les instructions CREATE INDEX et ALTER INDEX. De plus, les instructions CREATE TABLE et ALTER TABLE peuvent être utilisées pour définir la granularité de verrouillage sur les contraintes PRIMARY KEY et UNIQUE. À des fins de compatibilité descendante, la procédure stockée système sp_indexoption peut également définir la granularité. Pour afficher l’option de verrouillage en cours pour un index donné, utilisez la fonction INDEXPROPERTY. Les verrous au niveau des pages, des lignes, ou une combinaison de ces derniers peuvent être refusés pour un index donné.

Verrous refusés	Accès à l'index par
Niveau page	Verrous au niveau des lignes et des tables
Niveau ligne	Verrous au niveau des pages et des tables
Niveau page et niveau ligne	Verrous au niveau des tables

Informations sur les transactions avancées

Transactions imbriquées

Les transactions explicites peuvent être imbriquées. Cette fonctionnalité est avant tout destinée à la prise en charge des transactions dans les procédures stockées appelées par un processus faisant partie d'une transaction, ou par des processus ne disposant pas de transaction active.

L'exemple suivant illustre cette utilisation des transactions imbriquées. Si TransProc est appelée lorsqu’une transaction est active, le résultat de la transaction imbriquée dans TransProc est contrôlé par la transaction externe et ses instructions INSERT sont validées ou restaurées en fonction de la validation ou de la restauration de la transaction externe. Si TransProc est exécutée par un processus pour lequel aucune transaction n'est en cours, l'instruction COMMIT TRANSACTION qui se trouve à la fin de la procédure déclenche la validation des instructions INSERT.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

La validation des transactions internes est ignorée par l’Moteur de base de données lorsqu’une transaction externe est active. La transaction est validée ou annulée en fonction de la validation ou de l'annulation à la fin de la transaction la plus éloignée. Si la transaction externe est validée, les transactions imbriquées dans celle-ci le sont aussi. Si la transaction externe est restaurée, toutes les transactions internes le sont aussi, qu'elles aient été validées individuellement ou non.

Chaque appel à COMMIT TRANSACTION ou COMMIT WORK s’applique à la dernière instruction BEGIN TRANSACTION exécutée. Si les instructions BEGIN TRANSACTION sont imbriquées, une instruction COMMIT s’applique uniquement à la dernière transaction imbriquée, qui est la transaction la plus intérieure. Même si une instruction COMMIT TRANSACTION transaction_name d'une transaction imbriquée fait référence à la transaction externe, seule la transaction la plus intérieure est validée.

Il n'est pas permis que le paramètre transaction_name d'une instruction ROLLBACK TRANSACTION fasse référence à la transaction interne d'un ensemble de transactions imbriquées nommées. transaction_name ne peut faire référence qu’au nom de la transaction la plus extérieure. Si une instruction ROLLBACK TRANSACTION transaction_name transaction_name utilisant le nom de la transaction externe est exécutée à n’importe quel niveau d’un ensemble de transactions imbriquées, toutes les transactions imbriquées sont restaurées. Si une instruction ROLLBACK WORK ou ROLLBACK TRANSACTION sans un paramètre transaction_name est exécutée à n’importe quel niveau d’un ensemble de transactions imbriquées, toutes les transactions imbriquées, la plus extérieure comprise, sont restaurées.

La fonction @@TRANCOUNT enregistre le niveau d’imbrication de la transaction en cours. Chaque instruction BEGIN TRANSACTION incrémente @@TRANCOUNT d’une unité. Chaque instruction COMMIT TRANSACTION ou COMMIT WORK décrémente @@TRANCOUNT d’une unité. Une instruction ROLLBACK WORK ou ROLLBACK TRANSACTION pour laquelle aucun nom de transaction n'est spécifié restaure toutes les transactions imbriquées et remet @@TRANCOUNT à 0. Une instruction ROLLBACK TRANSACTION dans laquelle le nom de la transaction la plus extérieure d’un groupe de transactions imbriquées est spécifié restaure toutes les transactions imbriquées et remet @@TRANCOUNT à 0. Pour savoir si vous êtes déjà dans une transaction, SELECT @@TRANCOUNT pour consulter si sa valeur est supérieure ou égale à 1. Si la valeur de @@TRANCOUNT est égale à 0, vous n’êtes pas dans une transaction.

Utilisation de sessions associées

Les sessions associées facilitent la coordination des actions entre plusieurs sessions exécutées sur le même serveur. Les sessions associées permettent à plusieurs sessions de partager la même transaction et les mêmes verrous. Elles peuvent opérer sur les mêmes données sans conflits de verrous. Les sessions associées peuvent être créées à partir de plusieurs sessions de la même application ou à partir de sessions distinctes de plusieurs applications.

Pour participer à une session liée, une session doit appeler sp_getbindtoken ou srv_getbindtoken (via Open Data Services) pour obtenir un jeton de liaison. Un jeton d'association est une chaîne de caractères qui identifie de manière unique chaque transaction associée. Le jeton d'association est ensuite transmis aux autres sessions à associer à la session en cours. Les autres sessions se lient à la transaction en appelant sp_bindsession avec le jeton de liaison reçu de la première session.

Les jetons d'association doivent être transmis par le code de l'application qui crée la première session au code des applications qui lient leurs sessions à la première. Il n'existe aucune instruction Transact-SQL ni fonction d'API qui permette à une application d'obtenir le jeton d'association pour une transaction démarrée par un autre processus. Les méthodes suivantes peuvent être utilisées pour transmettre un jeton d'association :

• Si toutes les sessions sont ouvertes à partir du même processus d'application, les jetons d'association peuvent être stockés en mémoire globale ou passés comme paramètres à des fonctions.

• Si les sessions sont créées à partir de processus d'application différents, les jetons d'association peuvent être transmis à l'aide d'un système de communication entre processus (IPC), comme l'appel de procédure distante (RPC) ou l'échange dynamique de données (DDE).

• Les jetons d'association peuvent être stockés dans une instance du moteur de base de données dans une table qui peut être lue par les processus voulant s'associer à la première session.

Dans un ensemble de sessions associées, une seule session peut être active à la fois. Si une session exécute une instruction sur l'instance ou si elle attend des résultats de l'instance, aucune des autres sessions associées ne peut accéder à l'instance tant que la session active n'a pas terminé ou annulé l'exécution de l'instruction en cours. Si l'instance est occupée à traiter une instruction provenant d'une autre des sessions associée, un message d'erreur s'affiche pour indiquer que l'espace de transaction est utilisé et que la session doit renouveler la tentative ultérieurement.

Chacune des sessions associées conserve son niveau d'isolation propre. Si vous utilisez SET TRANSACTION ISOLATION LEVEL pour changer le niveau d'isolation d'une session, cela n'affecte pas celui des autres sessions associées au même jeton.

Types de sessions associées

Les sessions associées peuvent être locales ou distribuées.

• Session associées locale Permet aux sessions associées locales de partager l'espace de transaction d'une transaction unique dans une seule instance du moteur de base de données.

• Session associées distribuée Permet aux sessions associées de partager la même transaction entre deux ou plusieurs instances jusqu'à ce que la transaction entière soit validée ou annulée à l'aide du Microsoft Distributed Transaction Coordinator (MS DTC).

Les sessions associées distribuées ne sont pas identifiées par la chaîne de caractères d'un jeton de liaison, mais par des numéros d'identification de transaction distribuée. Si une session liée est impliquée dans une transaction locale et exécute un appel de procédure distante (RPC) sur un serveur distant avec SET REMOTE_PROC_TRANSACTIONS ON, la transaction liée locale est automatiquement promue au rang de transaction liée distribuée par MS DTC et une session MS DTC est lancée.

Quand utiliser des sessions associées

Dans les versions précédentes de SQL Server, les sessions associées étaient principalement utilisées pour développer des procédures stockées étendues devant exécuter des instructions Transact-SQL pour le processus qui les appelle. Le passage par le processus appelant d'un jeton d'association comme paramètre de la procédure stockée permet à celle-ci d'accéder à l'espace de transaction du processus appelant, et d'intégrer ainsi la procédure stockée étendue à ce dernier.

Dans le moteur de base de données, les procédures stockées écrites à l'aide de CLR sont supérieures aux procédures stockées étendues en termes de sécurité, d'évolutivité et de stabilité. Les procédures stockées CLR utilisent l’objet SqlContext et non sp_bindsession pour joindre le contexte de la session appelante.

Les sessions associées peuvent être utilisées pour développer des applications à trois niveaux où la logique de gestion est incluse dans des programmes distincts qui peuvent travailler en coopération sur une seule transaction commerciale. Ces programmes doivent être codés de manière à coordonner soigneusement leur accès à une base de données. Comme les deux sessions partagent les mêmes verrous, les deux programmes ne doivent pas essayer de modifier simultanément les mêmes données. A tout moment, une seule session peut participer à la transaction ; aucune exécution en parallèle n'est possible. La transaction ne peut passer d'une session à l'autre que lors de points d'interruption bien définis, notamment lorsque l'exécution de toutes les instructions DML est terminée et que les résultats correspondants ont été extraits.

Transactions efficaces en termes de code

Il est important de réduire la durée des transactions au minimum. Au démarrage d'une transaction, le SGBD, autrement dit le système de gestion de base de données, doit utiliser de nombreuses ressources pour toute la durée de la transaction afin de préserver les propriétés ACID (atomicité, cohérence, isolement et durabilité) de la transaction. En cas de modification des données, les lignes modifiées doivent être protégées par des verrous exclusifs qui empêchent les autres transactions de lire ces lignes, et ces verrous doivent être maintenus jusqu'à ce que la transaction soit validée ou restaurée. En fonction des paramètres de niveau d’isolation de la transaction, les instructions SELECT peuvent acquérir des verrous qui doivent être maintenus jusqu’à la restauration ou la validation de la transaction. Dans le cas de systèmes comprenant de nombreux utilisateurs, les transactions doivent être aussi courtes que possible afin de limiter la contention des ressources par les verrous pour des connexions concurrentes. Des transactions longues et peu performantes peuvent ne pas poser de problème pour un nombre réduit d'utilisateurs, mais elles sont très problématiques dans le cas d'un système comprenant plusieurs milliers d'utilisateurs. À compter de SQL Server 2014 (12.x), le moteur de base de données prend en charge les transactions durables retardées. Les transactions durables retardées peuvent améliorer la scalabilité et les performances, mais elles ne garantissent pas la durabilité. Pour plus d’informations, consultez Contrôler la durabilité d’une transaction.

Recommandations de code

Vous trouverez ci-dessous des directives vous permettant de coder des transactions performantes :

• Évitez l'entrée de données par l'utilisateur au cours d'une transaction. Effectuez toutes les entrées de données par l'utilisateur avant le début d'une transaction. Si d'autres données doivent être entrées par l'utilisateur au cours d'une transaction, restaurez la transaction en cours et redémarrez-la après l'entrée des données. Même si les utilisateurs répondent immédiatement, le temps de réaction d'un être humain est incomparablement plus lent que la vitesse d'un ordinateur. Toutes les ressources utilisées par la transaction sont verrouillées pendant un temps extrêmement long, ce qui peut entraîner des problèmes de blocage du système. Si les utilisateurs ne répondent pas, la transaction reste active et verrouille les ressources critiques jusqu'à ce qu'ils répondent, ce qui peut prendre plusieurs minutes, voire des heures.

• Si possible, n'ouvrez pas une transaction alors que vous êtes en train de consulter des données. Ne démarrez pas de transaction avant que l'analyse préliminaire des données soit terminée.

• Limitez la durée de la transaction autant que possible. Lorsque vous connaissez les modifications effectuées, démarrez une transaction, exécutez les instructions de modification, puis validez-les ou restaurez-les immédiatement. N'ouvrez pas la transaction tant que ce n'est pas nécessaire.

• Pour réduire les blocages, envisagez d'utiliser un niveau d'isolement basé sur le contrôle de version de ligne pour les requêtes en lecture seule.

• Utilisez avec discernement les niveaux d'isolement de transaction inférieurs. De nombreuses applications peuvent être codées pour utiliser le niveau d’isolation de la transaction READ COMMITTED. Peu de transactions nécessitent le niveau d’isolation de la transaction SERIALIZABLE.

• Utilisez intelligemment les options d’accès concurrentiel optimiste. Dans un système pour lequel la probabilité de modifications concurrentes est faible, le temps supplémentaire nécessaire pour traiter une erreur causée par la modification de vos données par un autre utilisateur après que vous les ayez lues peut être inférieur à celui qu'entraîne le verrouillage systématique des lignes au moment de leur lecture.

• Limitez autant que possible le volume de données auxquelles accède votre transaction. Le nombre de lignes verrouillées est ainsi limité, ce qui limite également la contention des transactions.

• Évitez autant que possible les indicateurs de verrouillage pessimiste tels que HOLDLOCK. Des indicateurs tels que HOLDLOCK ou le niveau d’isolation SERIALIZABLE peuvent entraîner l’attente des processus même sur des verrous partagés et réduire la concurrence.

• Évitez d’utiliser des transactions implicites lorsque cela est possible. Les transactions implicites peuvent introduire un comportement imprévisible en raison de leur nature. Consultez Transactions implicites et problèmes de concurrence.

Transactions implicites et prévention des problèmes de concurrence et de ressources

Pour prévenir les problèmes de concurrence et de ressources, soyez minutieux dans la gestion des transactions implicites. Dans les transactions implicites, l'instruction Transact-SQL qui suit une instruction COMMIT ou ROLLBACK démarre automatiquement une nouvelle transaction. Une nouvelle transaction risque ainsi d'être ouverte alors que l'application consulte des données, ou qu'elle attend une entrée de données par l'utilisateur. Après avoir terminé la dernière transaction nécessaire à la protection des modifications, désactivez les transactions implicites jusqu'à ce qu'une transaction doive à nouveau protéger les modifications de données. Cette procédure permet au moteur de base de données d'utiliser le mode autocommit lorsque l'application consulte des données ou attend une entrée de données par l'utilisateur.

De plus, quand le niveau d’isolation SNAPSHOT est activé, même si une nouvelle transaction ne contient pas de verrous, une exécution longue empêche la suppression des anciennes versions dans le magasin de versions.

Gestion des transactions durables

Une transaction durable est une transaction active qui n'a pas été validée ou annulée en temps voulu. Par exemple, si le début et la fin d'une transaction sont contrôlés par l'utilisateur, une raison classique de l'existence d'une transaction de longue durée est qu'un utilisateur a commencé une transaction puis est parti alors que la transaction attend une réponse de l'utilisateur.

Une transaction longue peut entraîner de graves problèmes pour une base de données, comme suit :

• Si une instance de serveur est arrêtée après qu’une transaction active a effectué de nombreuses modifications non validées, la phase de récupération du redémarrage suivant peut prendre plus de temps que le temps indiqué par l’option de configuration du serveur recovery interval ou par l’option ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Ces options contrôlent la fréquence des points de contrôle actifs et indirects, respectivement. Pour plus d'informations sur les types de points de contrôle, consultez Points de contrôle de base de données (SQL Server).

• Plus important, bien qu'une transaction en attente génère très peu d'entrées de journal, elle empêche la troncation du journal et entraîne ainsi sa croissance et son remplissage. Si le journal des transactions est rempli, la base de données ne peut plus effectuer d’écritures. Pour plus d'informations, consultez Guide d'architecture et gestion du journal des transactions SQL Server, Résoudre les problèmes liés à un journal des transactions saturé (erreur SQL Server 9002) et Journal des transactions (SQL Server).

Découvrir des transactions de longue durée

Pour rechercher des transactions de longue durée, appliquez une des procédures suivantes :

• sys.dm_tran_database_transactions

  Cet affichage de gestion dynamique retourne des informations sur les transactions au niveau de la base de données. Pour une transaction durable, les colonnes intéressantes incluent l'heure du premier enregistrement de journal (database_transaction_begin_time), l'état actuel de la transaction (database_transaction_state) et le numéro séquentiel dans le journal (LSN) de l'enregistrement initial dans le journal des transactions (database_transaction_begin_lsn).

  Pour plus d'informations, consultez sys.dm_tran_aborted_transactions (Transact-SQL).

• DBCC OPENTRAN

  Cette déclaration vous permet d'identifier l'ID utilisateur du propriétaire de la transaction, afin que vous puissiez éventuellement retrouver la source de la transaction pour y mettre fin de manière appropriée (la valider au lieu de la restaurer). Pour plus d'informations, consultez DBCC OPENTRAN (Transact-SQL).

Terminer une transaction

Pour terminer une transaction sur une session spécifique, utilisez l’instruction KILL. Utilisez cette instruction avec précaution, particulièrement lorsque des processus critiques sont en cours d'exécution. Pour plus d’informations, consultez KILL (Transact-SQL).

Interblocages

Les interblocages sont une rubrique complexe liée au verrouillage, mais différente du blocage.

• Pour plus d'informations sur les interblocages, notamment la surveillance, le diagnostic et les exemples, consultez le Guide des interblocages.
• Pour plus d'informations sur les interblocages spécifiques à Azure SQL Database, consultez Analyser et empêcher les interblocages dans Azure SQL Database.

Contenu connexe

• Compréhension et résolution des problèmes bloquants dans SQL Server
• Comprendre et résoudre les problèmes de blocage d’Azure SQL Database
• Fonctions et vues de gestion dynamique relatives aux transactions (Transact-SQL)
• Charge du contrôle de version de ligne
• sys.dm_tran_locks (Transact-SQL)