Partager via

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), base de données SQL dans Microsoft Fabric

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 décrit les mécanismes de verrouillage et de versionnement des lignes utilisés par le moteur de base de données pour garantir l'intégrité de chaque transaction et fournit des informations sur la manière dont les applications peuvent contrôler efficacement les transactions.

Note

Le verrouillage optimisé est une fonctionnalité du moteur de base de données introduite en 2023 qui réduit considérablement la mémoire de verrouillage et le nombre de verrous requis pour les écritures simultanées. Cet article est mis à jour pour décrire le comportement du moteur de base de données avec et sans verrouillage optimisé.

Le verrouillage optimisé introduit des modifications importantes apportées à certaines sections de cet article, notamment :

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, telles que les index B-tree ou les listes doublement liées, doivent être correctes à la fin de la transaction.

Note

La documentation utilise généralement le terme B-tree pour faire référence aux index. Dans les index Rowstore, le moteur de base de données implémente un arbre B+. Cela ne s'applique pas aux index de stockage en colonnes ni aux index sur des tables à mémoire optimisée. Pour plus d'informations, reportez-vous au Guide de conception et d'architecture des index SQL Server et Azure SQL.

Isolation
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 qu'une autre transaction simultanée ne les modifie, ou elle reconnaît les données après la fin de la deuxième transaction, mais elle ne reconnaît pas 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 différé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 différées, consultez l'article Contrôler la durabilité des transactions.

Les applications sont responsables du démarrage et de la fin des transactions aux points qui imposent la cohérence logique des données. L'application doit définir la séquence de modifications des données qui laissent les données dans un état cohérent par rapport aux règles métier de l'organisation. L'application effectue ces modifications dans une seule transaction afin que le moteur de base de données puisse garantir l'intégrité de la transaction.

Il incombe à un système de base de données d'entreprise, tel qu'une instance du moteur de base de données, de fournir des mécanismes garantissant 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 journalisation pour garantir la durabilité des transactions. Pour les transactions durables, l'enregistrement du journal est renforcé sur le disque avant les validations des transactions. Ainsi, même 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 elle-même, l'instance utilise les journaux d'activité lors du redémarrage pour restaurer automatiquement toutes les transactions incomplètes jusqu'au point de 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 différées, consultez l'article Contrôler la durabilité des transactions.

  • Des fonctionnalités de gestion des transactions qui assurent l'atomicité et la cohérence des transactions. Une fois qu'une transaction a démarré, elle doit être terminée avec succès (validée), sinon le moteur de base de données annule toutes les modifications apportées aux 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. Vous pouvez spécifier cela à l'aide d'instructions Transact-SQL ou de fonctions d'interface de programmation d'application (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, Exécution de 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 sur une connexion, toutes les instructions Transact-SQL exécutées sur 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 de portée batch qui est gérée au niveau du batch. Lorsque le batch est terminé, si la transaction de portée batch 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 tant que transactions explicites, à validation automatique ou implicites.

Transactions explicites

Une transaction explicite est une transaction dans laquelle vous définissez explicitement le début et la fin de la transaction à l'aide d'une fonction API ou en émettant les instructions Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION ou ROLLBACK WORK. Lorsque la transaction se termine, la connexion revient au mode de transaction dans lequel elle se trouvait avant le démarrage de la transaction explicite, qui peut être le mode implicite ou autocommit.

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 des options de base de données ou toute procédure système qui modifie la base de données master à l'intérieur de transactions explicites ou implicites.

Note

UPDATE STATISTICS peut être utilisé dans une transaction explicite Cependant, UPDATE STATISTICS s'exécute indépendamment de la transaction qui l'encapsule et ne peut pas être restauré.

Transactions avec validation automatique

Le mode de validation automatique est le mode de gestion des transactions par défaut 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 en mode de validation automatique tant que ce mode par défaut n'a pas été remplacé par des transactions explicites ou implicites. Le mode Autocommit 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 à l'aide d'une fonction API ou de l'instruction SET IMPLICIT_TRANSACTIONS ONTransact-SQL. Ce mode est également appelé Autocommit OFF, voir Méthode setAutoCommit (SQLServerConnection).

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

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

Transactions au niveau du lot

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 de portée batch qui n'est ni validée ni restaurée à la fin d'un batch 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 fonctionner comme un gestionnaire de ressources dans des transactions distribuées coordonnées par des gestionnaires de transactions, tels que Microsoft Distributed Transaction Coordinator (MS DTC) ou d'autres gestionnaires de transactions qui prennent en charge la spécification Open Group XA pour le traitement des transactions distribuées. Pour plus d'informations, consultez la documentation MS DTC.

Une transaction au sein d'une instance unique du moteur de base de données qui s'étend sur deux bases de données ou plus 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 la même manière qu'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 effectue ensuite toutes les opérations nécessaires pour rendre la transaction durable, et tous les tampons de journal des transactions pour la transaction sont vidés sur le disque. Lorsque chaque gestionnaire de ressources achève la phase de préparation, il renvoie le succès ou l'échec de la phase au gestionnaire de transactions. SQL Server 2014 (12.x) a introduit la durabilité des transactions différées. Les transactions durables différées sont validées avant que les tampons de journaux de transactions de chaque gestionnaire de ressources ne soient vidés sur le disque. Pour plus d'informations sur la durabilité des transactions différées, consultez l'article Contrôler la 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 via Transact-SQL ou via l'API de base de données. Pour plus d’informations, consultez BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Fin des transactions

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

  • Commit

    Si une transaction est réussie, validez-la. Une instruction COMMIT garantit que toutes les modifications apportées à la transaction sont enregistrées de manière permanente dans la base de données. Un commit libère également les ressources utilisées par la transaction, telles que les verrous.

  • Restaurer

    Si une erreur se produit dans une transaction ou si l'utilisateur décide d'annuler la transaction, restaurez la transaction. Une instruction ROLLBACK annule toutes les modifications apportées à la transaction en rétablissant l'état des données tel qu'il était au début de la transaction. La restauration libère également les ressources occupées par la transaction.

Note

Dans les sessions MARS (Multiple Active Result Sets), une transaction explicite démarrée via une fonction API ne peut pas être validée tant qu'il existe des requêtes en attente d'exécution. Toute tentative de validation de ce type de transaction pendant l’exécution des requêtes entraîne une erreur.

Erreurs de traitement au cours d’une transaction

Si une erreur empêche la réussite d'une transaction, le moteur de base de données restaure automatiquement la transaction et libère toutes les ressources occupées par celle-ci. Si la connexion réseau du client à une instance du moteur de base de données est interrompue, toutes les transactions en attente pour la connexion sont restaurées lorsque le réseau signale l'interruption de la connexion à l'instance. Si l'application cliente échoue ou si l'ordinateur client tombe en panne ou redémarre, la connexion est également interrompue et l'instance du moteur de base de données restaure toutes les transactions en attente lorsque le réseau lui signale l'interruption de la connexion. Si le client se déconnecte du moteur de base de données, toutes les transactions en attente sont restaurées.

Si une erreur d'instruction d'exécution (telle qu'une violation de contrainte) se produit dans un lot, le comportement par défaut du moteur de base de données consiste à restaurer uniquement l'instruction qui a 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'instruction d'exécution entraîne la restauration automatique 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).

Lorsque des erreurs se produisent, l'action appropriée (COMMIT ou ROLLBACK) doit être incluse dans le code de l'application. La construction Transact-SQL TRY...CATCH est un outil efficace pour gérer les erreurs, y compris celles qui se produisent dans les transactions. Pour plus d'informations et des exemples incluant des transactions, consultez TRY...CATCH (Transact-SQL). À partir de SQL Server 2012 (11.x), vous pouvez utiliser l'instruction THROW pour déclencher 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 autocommit, il semble parfois qu'une instance du moteur de base de données a restauré un lot entier au lieu d'une seule instruction SQL. 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, ce qui rend impossible l'exécution du lot. 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 utilise la résolution de nom différée, dans laquelle les noms d'objet sont résolus au moment de l'exécution, et non au moment de la compilation. 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 après que la troisième instruction INSERT a généré une erreur d'exécution en faisant référence à une table qui n'existe pas.

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 maintenir la cohérence des bases de données lorsque plusieurs utilisateurs accèdent aux données en même temps :

  • 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.

  • Versionnement des lignes

    Lorsqu'un niveau d'isolation basé sur le versionnement des lignes est utilisé, le moteur de base de données conserve les versions de chaque ligne modifiée. Les applications peuvent spécifier qu'une transaction utilise les versions des lignes pour afficher les données telles qu'elles existaient au début de la transaction ou de l'instruction, au lieu de protéger toutes les lectures à l'aide de verrous. Avec le versionnage de ligne, la probabilité qu’une opération de lecture bloque d’autres transactions est considérablement réduite.

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 verrouillage ni versionnement des lignes, les requêtes exécutées sur ces données pourraient produire des résultats inattendus en renvoyant des données qui n'ont pas encore été 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. Des indications au niveau de la table peuvent être spécifiées pour des instructions Transact-SQL individuelles afin d'adapter davantage le comportement aux exigences de l'application.

Gérer l'accès simultané 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 ne dispose d'aucun contrôle de la concurrence, 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 sale)

    Une dépendance non validée se produit lorsqu'une deuxième transaction lit une ligne en cours de mise à jour par une autre transaction. La deuxième transaction lit des données qui n'ont pas encore été validées et qui peuvent être modifiées par la transaction qui met à jour 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 renvoyées par la deuxième requête est différent. L'exemple suivant montre comment cela peut se produire. Supposons que les deux transactions 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 doubles causé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'astuce de table NOLOCK) ne posent 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 posent des verrous partagés, mais les verrous de ligne ou de page sont libérés après la lecture de la ligne 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 dans 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 simultanément des données dans une base de données, un système de contrôle doit être mis en place afin que les modifications apportées par une transaction n'affectent pas celles d'une autre transaction. 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 pessimiste de la concurrence

    Un système de verrous empêche les transactions de modifier les données d'une manière qui affecte d'autres transactions. Lorsqu'une transaction a effectué une action qui applique un verrouillage, les autres transactions ne peuvent plus effectuer d'actions qui entreraient en conflit avec ce verrouillage tant que celui-ci n'est pas libéré par le propriétaire. Il s'agit d'un contrôle pessimiste, car il est généralement utilisé dans les systèmes où les données font l'objet d'une forte concurrence et où le coût de la protection des données par des verrous est inférieur au coût de l'annulation des transactions en cas de conflits de concurrence.

  • Contrôle optimiste de la concurrence

    Dans le contrôle de concurrence optimiste, les transactions ne verrouillent pas les données lorsqu'elles les lisent. Toutefois, lorsqu'une transaction met à jour des données, 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 générée. En règle générale, la transaction qui reçoit l'erreur est restaurée et recommence. Cette méthode est dite optimiste parce qu'elle est généralement utilisée dans les systèmes où la concurrence pour les données est faible et où le coût d'un retour occasionnel sur une transaction est inférieur au coût du verrouillage des données lorsqu'elles sont lues.

Le moteur de base de données prend en charge les deux méthodes de contrôle de concurrence. 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 d'instructions Transact-SQL ou via les 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'isolation dans le 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 apportées aux ressources ou aux donné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 :

  • Si des verrous sont acquis lors de la lecture des données et quel type de verrous est demandé.
  • la durée de vie des verrous de lecture ;
  • Si une opération de lecture fait référence à des lignes modifiées par une autre transaction : XXX
    • 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.

Importante

Le choix d'un niveau d'isolation n'a aucune influence sur les verrous acquis pour protéger les modifications de données. Une transaction détient toujours un verrou exclusif pour effectuer la modification des données et conserve ce verrou jusqu'à la fin de la transaction, quel que soit le niveau d'isolation défini pour cette transaction. Dans le cas des opérations de lecture, le niveau d'isolation d'une transaction définit principalement son niveau de protection contre les effets des modifications apportées par les autres transactions.

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) que les transactions peuvent rencontrer. Inversement, un niveau d'isolation plus élevé réduit les types d'effets de concurrence que les transactions peuvent rencontrer, mais nécessite davantage de ressources système et augmente les risques qu'une transaction en bloque une autre. 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 d'isolation 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 elle le fait en effectuant un niveau de verrouillage susceptible d'avoir un impact sur d'autres transactions dans des systèmes multi-utilisateurs. Le niveau d'isolation le plus bas, READ UNCOMMITTED, peut récupérer des 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 ni de gestion des versions, ce qui minimise la surcharge.

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

La norme ISO définit les niveaux d'isolation suivants, qui sont 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, dans lequel les transactions sont isolées uniquement dans la mesure nécessaire pour garantir que les données physiquement incohérentes 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 (acquises 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 du moteur de base de données.
REPEATABLE READ Le moteur de base de données conserve les verrous en lecture et en écriture 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 en lecture et en écriture acquis sur les données sélectionnées jusqu'à la fin de la transaction. Les verrous de plage sont acquis lorsqu'une opération SELECT utilise une clause WHERE de plage pour éviter les lectures fantômes.

Remarque : les opérations DDL et les transactions sur des tables répliquées peuvent échouer lorsque le niveau d'isolation SERIALIZABLE est demandé En effet, les requêtes de réplication utilisent des indications 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 transaction supplémentaires qui utilisent le versionnement des lignes. L'un est une implémentation du niveau d'isolation READ COMMITTED, et l'autre est le niveau d'isolation de 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 versionnement des lignes pour assurer la cohérence de lecture au niveau des instructions. Les opérations de lecture nécessitent uniquement des verrous de niveau de table de stabilité du schéma (Sch-S) et aucun verrou de page ou 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, 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 les 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 implémentations 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'acquiert aucun verrou de page ou de ligne ; seuls les verrous 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 début de la transaction. Vous ne pouvez utiliser l'isolation SNAPSHOT que lorsque l'option de base de données ALLOW_SNAPSHOT_ISOLATION 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 sur ON pour les bases de données dans Azure SQL Database.

Remarque : le moteur de base de données ne prend pas en charge le versionnage 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 l'isolation de type 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 DDL CLR (Common Language Runtime). 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 Non Oui Oui
REPEATABLE READ Non Non Oui
SNAPSHOT Non Non Non
SERIALIZABLE Non Non Non

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

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

Transact-SQL
Les scripts Transact-SQL utilisent l'instruction 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
Les applications ADO.NET qui utilisent l'espace de noms géré System.Data.SqlClient peuvent 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 OLE DB appellent 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 de transaction en mode autocommit, les applications OLE DB peuvent définir la propriété DBPROPSET_SESSION DBPROP_SESS_AUTOCOMMITISOLEVELS 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
Les applications ODBC appellent 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 instantanées, 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 instantanée peut être récupérée à l'aide de SQL_COPT_SS_TXN_ISOLATION ou 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. Les verrous ont différents modes, tels que 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 requête un mode de verrouillage qui entre en conflit avec un verrou déjà accordé sur les mêmes données, le moteur de base de données suspend la transaction demandeuse jusqu'à ce que le premier verrou soit libéré.

Lorsqu'une transaction modifie une donnée, elle maintient certains verrous protégeant la modification jusqu'à la fin de la transaction. La durée pendant laquelle une transaction maintient les verrous acquis pour protéger les opérations de lecture dépend du paramètre de niveau d'isolation des transactions et de l'activation ou non du verrouillage optimisé.

  • Lorsque le verrouillage optimisé n'est pas activé, les verrous de ligne et de page nécessaires aux é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 conservé jusqu'à la fin de la transaction. Au niveau d'isolation READ COMMITTED par défaut, les transactions ne conservent pas les verrous de ligne et de page nécessaires aux écritures jusqu'à la fin de la transaction. Cela réduit la mémoire de verrouillage requise et diminue le besoin d'escalade des verrous. En outre, lorsque le verrouillage optimisé est activé, l'optimisation LAQ (Lock After Qualification) évalue les prédicats d'une requête sur la dernière version validée de la ligne sans acquérir de verrou, ce qui améliore 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 appelé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 auxquelles il doit 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 dispose d'un verrouillage multigranulaire qui permet à une transaction de verrouiller différents types de ressources. Pour minimiser le coût du verrouillage, le moteur de base de données verrouille automatiquement les ressources à un niveau approprié à 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 entièrement une ressource. Ce groupe de verrous à plusieurs niveaux de granularité est appelé « hiérarchie des verrous ». Par exemple, pour protéger entièrement la lecture d'un index, une instance du moteur de base de données peut devoir acquérir des verrous partagés sur les lignes et des verrous partagés d'intention sur les pages et la table.

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

Ressource Descriptif
RID Identificateur de ligne utilisé pour verrouiller une seule ligne dans un segment de mémoire.
KEY Un 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.
HoBT1 Segment de mémoire ou arbre B (B-tree). Un verrou protégeant un arbre B (index) ou les pages de données de tas dans une table qui ne possède pas d'index cluster.
TABLE1 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.
XACT2 Verrou d'ID de transaction (TID) utilisé dans le verrouillage optimisé. Pour plus d'informations, consultez Verrouillage d'ID de transaction (TID).

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

2 ressources de verrouillage supplémentaires sont disponibles pour les ressources de verrouillage XACT. Pour plus d'informations, consultez Ajouts de diagnostic pour un verrouillage optimisé.

Modes de verrouillage

Le moteur de base de données verrouille les ressources à l'aide de différents modes de verrouillage qui déterminent comment les ressources peuvent être accessibles par des transactions simultanées.

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

Mode de verrouillage Descriptif
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 verrous d'intention sont les suivants : intention partagée (IS), intention exclusive (IX) et partagée avec intention exclusive (SIX).
Schema Utilisé lors de l'exécution d'une opération associée au schéma d'une table. Les types de verrous de schéma sont les suivants : modification du schéma (Sch-M) et stabilité du schéma (Sch-S).
Mise à jour en masse (BU) Utilisé lors de la copie en masse de données dans une table avec l'indication TABLOCK.
Plage de clés Protège la plage de lignes lues par une requête lors de l'utilisation du niveau d'isolation de transaction SERIALIZABLE. Garantit que d'autres transactions ne peuvent pas insérer de lignes qui seraient éligibles pour les requêtes de la transaction SERIALIZABLE si les requêtes étaient exécutées à nouveau.

Verrous partagés

Les verrous partagés (S) permettent aux transactions simultanées de lire une ressource sous contrôle de concurrence pessimiste. Aucune autre transaction ne peut modifier les données tant que des verrous partagés (S) existent sur la ressource. Les verrous partagés (S) sur une ressource sont libérés dès que l'opération de lecture est terminée, sauf si le niveau d'isolation de transaction est défini sur REPEATABLE READ ou supérieur, ou si une indication de verrouillage est utilisée pour conserver 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 des verrous de mise à jour (U) lorsqu'il se prépare à exécuter une mise à jour. Les verrous U sont compatibles avec les verrous S, mais une seule transaction peut détenir un verrou U à la fois sur une ressource donnée Ceci est essentiel : plusieurs transactions simultanées peuvent détenir des verrous S, mais une seule transaction peut détenir un verrou U sur une ressource Les verrous de mise à jour (U) sont finalement mis à niveau vers des verrous exclusifs (X) afin de mettre à jour une ligne

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

  • Certaines applications utilisent le modèle « sélectionner une ligne, puis mettre à jour la ligne », dans lequel la lecture et l'écriture sont explicitement séparées au sein de 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 des données, acquiert un verrou partagé (S) sur la ressource, puis modifie les données, ce qui nécessite la conversion du verrou en un verrou exclusif (X). Si deux transactions acquièrent des verrous partagés (S) sur une ressource, puis tentent de mettre à jour les données simultanément, une transaction tente la conversion du verrou en un verrou exclusif (X). La conversion du verrou partagé en verrou exclusif doit attendre, car le verrou exclusif (X) d'une transaction n'est pas compatible avec le verrou partagé (S) de l'autre transaction ; une attente de verrou se produit. La deuxième transaction tente d'acquérir un verrou exclusif (X) pour sa mise à jour. Comme les deux transactions se convertissent en verrous exclusifs (X) et qu'elles attendent chacune que l'autre transaction libère son verrou partagé (S), un blocage se produit.

    Au niveau d'isolation READ COMMITTED par défaut, les verrous S sont de courte durée et sont 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 le modèle « sélectionner une ligne avec l'indication UPDLOCK, puis mettre à jour la ligne ».

  • Si l'indication UPDLOCK est utilisée dans une écriture lorsque l'isolation SNAPSHOT est utilisée, la transaction doit avoir accès à la dernière version de la ligne. Si la dernière version n'est plus visible, il est possible de recevoir Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict. Pour un exemple, consultez Utilisation de l'isolation par instantané.

Verrous exclusifs

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

Les instructions de modification des données, telles que INSERT, UPDATE et DELETE, combinent des opérations de lecture et de modification. 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. Par exemple, 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 requête des verrous partagés sur les lignes lues dans la table de jointure en plus de requêter des verrous exclusifs sur les lignes mises à jour.

Verrous intentionnels

Le moteur de base de données utilise des verrous d'intention pour protéger le placement d'un verrou partagé (S) ou exclusif (X) sur une ressource située plus bas dans la hiérarchie des verrous. Les verrous d'intention sont appelés ainsi car ils sont acquis avant un verrou de niveau inférieur et signalent donc 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.
  • Pour améliorer l'efficacité du moteur de base de données dans la détection des conflits de verrous au niveau de granularité supérieur.

Par exemple, un verrou d'intention partagé est demandé au niveau de la table avant que des verrous partagés (S) ne soient demandés sur les pages ou les lignes de cette table. La définition d'un verrou d'intention au niveau de la table empêche une autre transaction d'acquérir ultérieurement un verrou exclusif (X) sur la table contenant cette page. Les verrous d'intention améliorent les performances car le moteur de base de données examine uniquement les verrous d'intention au niveau de la table pour déterminer si une transaction peut acquérir en toute sécurité un verrou sur cette 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 d'intention incluent les verrous d'intention partagés (IS), les verrous d'intention exclusifs (IX) et les verrous partagés avec intention exclusive (SIX).

Mode de verrouillage Descriptif
Intention partagée (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.
Intention exclusive (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 et protège également les verrous partagés demandés sur les ressources de niveau inférieur.
Partagé avec intention exclusive (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). Les verrous IS simultanés au niveau de la ressource de niveau supérieur sont autorisés. Par exemple, l'acquisition d'un verrou SIX sur une table acquiert également des verrous d'intention exclusive sur les pages en cours de modification et des verrous exclusifs sur les lignes modifiées. Il ne peut y avoir qu'un seul verrou SIX par ressource à la fois, ce qui empêche les autres transactions de mettre à jour la ressource, bien que celles-ci puissent lire les ressources situées à un niveau hiérarchique inférieur en obtenant des verrous IS au niveau de la table.
Verrous d'intention de mise à jour (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.
Verrous d'intention de mise à jour partagée (SIU) Combinaison de verrous S et IU, résultant de l'acquisition séparée et simultanée de ces deux verrous. Par exemple, une transaction exécute une requête avec l'indication PAGLOCK, puis exécute une opération de mise à jour. La requête avec l'indication PAGLOCK acquiert le verrou S, et l'opération de mise à jour acquiert le verrou IU.
Intention de mise à jour exclusive (UIX) Combinaison de verrous U et IX, résultant de l'acquisition séparée de ces verrous et du maintien simultané des deux verrous.

Verrous de schéma

Le moteur de base de données utilise des verrous de modification de schéma (Sch-M) pendant une opération de langage de définition de données de table (DDL), telle que l'ajout d'une colonne ou la suppression d'une table. Pendant qu'il est maintenu, le verrou Sch-M empêche l'accès simultané à la table. Cela signifie que le verrou Sch-M bloque toutes les opérations externes jusqu'à ce qu'il soit libéré.

Certaines opérations du langage de manipulation de données (DML), telles que la troncature de table, utilisent des 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 des verrous de stabilité de schéma (Sch-S) lors de la compilation et de l'exécution des requêtes. Les verrous Sch-S ne bloquent aucun verrou transactionnel, y compris les verrous exclusifs (X). 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 masse (BU) permettent à plusieurs threads de charger en masse des données simultanément dans la même table tout en empêchant les autres processus qui ne chargent pas de données en masse d'accéder à la table. Le moteur de base de données utilise des verrous de mise à jour en masse (BU) lorsque les deux conditions suivantes sont remplies.

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

Conseil

Contrairement à l'instruction BULK INSERT, qui maintient un verrou de mise à jour en bloc (BU) moins restrictif, INSERT INTO...SELECT avec l'indication TABLOCK maintient un verrou d'intention exclusive (IX) sur la table. Cela signifie que vous ne pouvez pas insérer de lignes à l'aide d'opérations d'insertion parallèles.

Verrous de clés

Les verrous de plage de clés protègent implicitement une plage de lignes incluses dans un ensemble d'enregistrements lu par une instruction Transact-SQL lors de l'utilisation du niveau d'isolation de transaction 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 le délai d'expiration du verrou expire. Par exemple, aucun mode de verrou n'est compatible avec les verrous exclusifs. Lorsqu'un verrou exclusif (X) est détenu, aucune autre transaction ne peut acquérir un verrou de quelque type que ce soit (partagé, de mise à jour ou exclusif) sur cette ressource tant que le verrou exclusif (X) n'est pas libéré. Inversement, si un verrou partagé (S) a été appliqué à une ressource, d'autres transactions peuvent également acquérir un verrou partagé ou un verrou de mise à jour (U) sur cette ressource, 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 présente la compatibilité des modes de verrouillage les plus courants.

Mode accordé existant IS S U IX SIX X
Mode demandé
Intention partagée (IS) Oui Oui Oui Oui Oui Non
Partagé (S) Oui Oui Oui Non Non Non
Mise à jour (U) Oui Oui Non Non Non Non
Intention exclusive (IX) Oui Non Non Oui Non Non
Partagé avec intention exclusive (SIX) Oui Non Non Non Non Non
Exclusif (X) Non Non Non Non Non Non

Note

Un verrou d'intention exclusive (IX) est compatible avec un mode de verrou IX, car IX signifie que l'intention est de mettre à jour uniquement certaines lignes plutôt que toutes. Les autres transactions qui essaient de lire ou de mettre à jour certaines lignes sont aussi autorisées si elles ne mettent pas à jour les lignes en cours de mise à jour par les autres transactions. En outre, si deux transactions tentent de mettre à jour la même ligne, elles bénéficient toutes deux d'un verrou IX au niveau de la table et de la page. Toutefois, une transaction se voit accorder un verrou X au niveau de la ligne. L'autre transaction doit attendre jusqu'à ce que le verrouillage au niveau de la ligne soit supprimé.

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

Diagramme illustrant une matrice des conflits et de la compatibilité des verrous.

Clé Descriptif
N Aucun conflit
I Illégal
C Conflict
NL Aucun verrou
SCH-S Verrou de stabilité du schéma
SCH-M Verrou de modification du schéma
S Partagé
U Mise à jour
X Exclusif
Système d'information État islamique Intention partagée
IU Intention de mise à jour
IX Intention exclusive
SIU Partage avec intention de mise à jour
SIX Partage avec intention exclusive
UIX Mise à jour avec intention exclusive
Bibliothèque Universitaire Mise à jour par lot
RS-S Plage partagée-partagée
RS-U Plage partagée-mise à jour
RI-N Insertion de plage nulle
RI-S Insertion de plage partagée
RI-U Insertion de plage mise à jour
RI-X Insertion de plage exclusive
RX-S Plage exclusive partagée
RX-U Plage exclusive mise à jour
RX-X Plage exclusive exclusive

Verrouillage d’étendues de clés

Les verrous de plage de clés protègent implicitement une plage de lignes incluses dans un ensemble d'enregistrements lu par une instruction Transact-SQL lors de l'utilisation du niveau d'isolation de transaction SERIALIZABLE. Le niveau d'isolation SERIALIZABLE exige que toute requête exécutée au cours d'une transaction obtienne le même ensemble de lignes à chaque fois qu'elle est exécutée pendant la transaction. Un verrouillage de plage de clés répond à cette exigence en empêchant d'autres transactions d'insérer de nouvelles lignes dont les clés se trouveraient dans la plage de 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 pourrait émettre une instruction SELECT qui lit toutes les lignes dont les valeurs de clé correspondent à 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 trait d'union (-), par exemple RangeT-K.
Plage Ligne Mode Descriptif
RangeS S RangeS-S Plage partagée, verrouillage de ressource partagée ; analyse de plage SERIALIZABLE.
RangeS U RangeS-U Plage partagée, verrouillage de mise à jour de ressource ; analyse de mise à jour 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.

Note

Le mode de verrouillage Null interne est compatible avec tous les autres modes de verrouillage.

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 demandé
Partagé (S) Oui Oui Non Oui Oui Oui Non
Mise à jour (U) Oui Non Non Oui Non Oui Non
Exclusif (X) Non Non Non Non Non Oui Non
RangeS-S Oui Oui Non Oui Oui Non Non
RangeS-U Oui Non Non Oui Non Non Non
RangeI-N Oui Oui Oui Non Non Oui Non
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'isolation de 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 pourrait établir une condition de plage avec ce prédicat : ColumnX BETWEEN N'AAA' AND N'CZZ'. Un verrouillage de plage 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.

Diagramme d'un exemple de Btree.

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

Les verrous de plage de clés sont placés sur les entrées d'index correspondant à la plage de lignes où le nom se trouve entre les valeurs Adam et Dale, empêchant ainsi l'ajout ou la suppression de nouvelles lignes correspondant à la requête précédente. Bien que le premier nom de cette plage soit Adam, le verrou de plage de clés en mode RangeS-S sur cette entrée d'index garantit qu'aucun nouveau nom commençant par la lettre A ne peut être ajouté avant Adam, tel que Abigail. De même, le verrouillage de plage de clés RangeS-S sur l'entrée d'index pour Dale garantit qu'aucun nouveau nom commençant par la lettre C ne peut être ajouté après Carlos, tel que Clive.

Note

Le nombre de verrous RangeS-S détenus est n+1, où nest le nombre de lignes qui satisfont la requête.

Extraction d’un singleton de données non existantes

Si une requête dans une transaction tente de sélectionner une ligne qui n'existe pas, le fait d'émettre la requête à un moment ultérieur dans la même transaction doit renvoyer 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 verrouillage de plage de clés en 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, la plage dans laquelle se trouve la ligne n'a pas besoin d'être verrouillée pendant 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, considérez 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 dont la valeur Bob est en cours de suppression. Cependant, toute transaction qui tente de lire, d'insérer ou de supprimer des lignes correspondant à la valeur Bob est bloquée jusqu'à ce que la transaction de suppression soit validée ou restaurée. (L'option de base de données READ_COMMITTED_SNAPSHOT et le niveau d'isolation SNAPSHOT permettent également la lecture à 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 des lignes, des pages ou des tables est déterminée par l'optimiseur de requêtes ou peut être spécifiée par l'utilisateur à l'aide d'indications de l'optimiseur de requêtes telles que ROWLOCK, PAGLOCK ou TABLOCK. Lorsque PAGLOCK ou TABLOCK est utilisé, le moteur de base de données désalloue immédiatement une page d'index si toutes les lignes sont supprimées de cette page. En revanche, lorsque ROWLOCK est utilisé, toutes les lignes supprimées sont uniquement marquées comme supprimées ; elles sont supprimées de la page d'index ultérieurement à l'aide d'une tâche d'arrière-plan.

Opération de suppression 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 toute 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 toute 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 verrou de page et de ligne est libéré dès que chaque ligne est mise à jour. Le verrou TID empêche les lignes d'être mises à jour jusqu'à ce que la transaction soit terminée. Toute transaction qui tente de lire, d’insérer ou de supprimer des lignes avec la valeur Bob est bloquée jusqu’à ce que la transaction qui supprime confirme ou annule. (L'option de base de données READ_COMMITTED_SNAPSHOT et le niveau d'isolation SNAPSHOT permettent également la lecture à partir d'une version de ligne de l'état précédemment validé.

Sinon, le mécanisme de verrouillage d'une opération de suppression est le même que sans verrouillage optimisé.

Opération d'insertion sans verrouillage optimisé

Lors de l'insertion d'une ligne dans une transaction, la plage dans laquelle se trouve la ligne n'a pas besoin d'être verrouillée pendant toute la durée de la transaction 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 verrouillage de plage de clés en mode RangeI-N est placé sur la ligne d'index correspondant au nom David afin de tester la plage. Si le verrouillage est accordé, une ligne avec la valeur Dan est insérée et un verrouillage exclusif (X) est placé sur la ligne insérée. Le verrouillage de plage de clés en mode RangeI-N est nécessaire uniquement pour tester la plage et n'est pas maintenu pendant toute la durée de la transaction qui effectue l'opération d'insertion. D'autres transactions peuvent insérer ou supprimer des valeurs avant ou après la ligne insérée avec la valeur Dan. Toutefois, toute transaction tentant de lire, insérer ou supprimer la ligne avec la valeur Dan est bloquée jusqu’à ce que la transaction d’insertion se confirme ou s'annule.

Opération d'insertion avec verrouillage optimisé

Lors de l'insertion d'une ligne dans une transaction, la plage dans laquelle se trouve la ligne n'a pas besoin d'être verrouillée pendant toute la durée de la transaction 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 existe des transactions SERIALIZABLE simultanées. Si des verrous de ligne et de page sont acquis, ils sont rapidement libérés et ne sont pas maintenus pendant toute la durée de la transaction. Il suffit de placer un verrou TID exclusif sur la valeur de clé insérée jusqu'à la fin de la transaction pour maintenir la sérialisabilité. Par exemple, considérez 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 utilisant le niveau d'isolation SERIALIZABLE dans l'instance. Le verrouillage de plage de clés en mode RangeI-N est placé sur la ligne d'index correspondant au nom David afin de tester la plage. Si le verrouillage est accordé, une ligne avec la valeur Dan est insérée et un verrouillage exclusif (X) est placé sur la ligne insérée. Le verrouillage de plage de clés en mode RangeI-N est nécessaire uniquement pour tester la plage et n'est pas maintenu pendant toute la durée de la transaction qui effectue l'opération d'insertion. D'autres transactions peuvent insérer ou supprimer des valeurs avant ou après la ligne insérée avec la valeur Dan. Toutefois, toute transaction tentant de lire, insérer ou supprimer la ligne avec la valeur Dan est bloquée jusqu’à ce que la transaction d’insertion se confirme ou s'annule.

Escalade de verrous

L'escalade de verrouillage est le processus qui consiste à convertir de nombreux verrous fins en verrous moins fins, ce qui réduit la charge du système tout en augmentant la probabilité de conflits de concurrence.

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

Escalade de verrouillage sans verrouillage optimisé

Lorsque le moteur de base de données acquiert des verrous de bas niveau, il place également des verrous d'intention 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 d'intention 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 d'intention sur les objets de niveau supérieur qui contiennent les pages. En plus du verrou d'intention sur l'objet, des verrous de page d'intention sont requis sur les objets suivants :
    • Pages de niveau feuille des index non clusterisés
    • Pages de données des index clusterisés
    • Pages de données de tas

Le moteur de base de données peut effectuer un verrouillage de ligne et un verrouillage de page pour la même instruction afin de réduire au minimum le nombre de verrous et la probabilité qu’il faille en escalader. Par exemple, le moteur de base de données peut placer des verrous de page sur un index non clusterisé (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 clusterisé ou le tas.

Pour faire monter les verrous, le moteur de base de données tente de remplacer le verrou d'intention sur la table par le verrou complet correspondant, par exemple en remplaçant un verrou d'intention exclusif (IX) par un verrou exclusif (X) ou un verrou d'intention partagé (IS) par un verrou partagé (S). Si la tentative d'escalade du verrou aboutit et que le verrou complet de la table est acquis, tous les verrous HoBT, de page (PAGEXXX) ou de ligne (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 alors 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 vers des verrous de page, mais les escalade directement vers des verrous de table. De même, les verrous de page sont toujours élevés à des 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.

Note

Les verrous de niveau HoBT augmentent généralement la concurrence, mais introduisent un risque de blocage lorsque des transactions qui verrouillent différentes partitions veulent étendre leurs verrous exclusifs aux autres partitions. Dans de rares cas, la granularité de verrouillage TABLE peut offrir de meilleures performances.

Si une tentative d'escalade de verrouillage échoue en raison de verrous conflictuels détenus par des transactions simultanées, le moteur de base de données réessaie l'escalade de verrouillage pour chaque 1 250 verrous supplémentaires acquis par la transaction.

Chaque événement d'escalade opère principalement au niveau d'une seule instruction Transact-SQL. Lorsque l'événement démarre, le moteur de base de données tente d'escalader tous les verrous détenus par la transaction en cours dans toutes les tables référencées par l'instruction active, à condition qu'elle respecte les exigences de seuil d'escalade. Si l'événement d'escalade démarre avant que l'instruction n'ait accédé à une table, aucune tentative d'escalade des verrous sur cette table n'est effectuée. Si l'escalade de verrouillage réussit, tous les verrous acquis par la transaction dans une instruction précédente et toujours détenus au moment où l'événement commence sont escaladés si la table est référencée par l'instruction actuelle et incluse dans l'événement d'escalade.

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

  • Commence une transaction.
  • Met à jour TableA. Cela génère des verrous exclusifs sur les lignes dans TableA qui sont conservés jusqu'à la fin de la transaction.
  • Met à jour TableB. Cela génère des verrous exclusifs sur les lignes dans TableB qui sont conservés jusqu'à la fin de la transaction.
  • Exécute un 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 l'escalade des verrous pendant qu'elle récupère des lignes à partir 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 à la fois les verrous partagés de l'instruction SELECT et les verrous exclusifs de l'instruction UPDATE précédente. Bien que seuls les verrous acquis par la session dans TableA pour l'instruction SELECT soient pris en compte pour déterminer si l'escalade de verrous doit être effectuée, une fois l'escalade réussie, tous les verrous détenus par la session dans TableA sont convertis en verrous exclusifs sur la table, et tous les autres verrous de granularité inférieure, y compris les verrous d'intention, sur TableA sont libérés.

Aucune tentative d'escalade des verrous sur TableB n'est effectuée, car il n'y avait aucune référence active à TableB dans l'instruction 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.

Élévation de verrouillage avec verrouillage optimisé

Le verrouillage optimisé permet de réduire la mémoire de verrouillage, car très peu de verrous sont conservés pendant la durée de la transaction. Lorsque le moteur de base de données acquiert des verrous de ligne et de page, l'élévation de verrouillage peut se produire de manière similaire, mais beaucoup moins fréquemment. Le verrouillage optimisé permet généralement d'éviter les élévations de verrouillage, ce qui réduit le nombre de verrous et la quantité de mémoire de verrouillage nécessaire.

Lorsque le verrouillage optimisé est activé et que le niveau d'isolation READ COMMITTED est défini 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 conservé pendant la durée de la transaction, à l'exception d'un seul verrou d'identifiant de transaction (TID). Cela réduit le risque d'escalade des verrous.

Seuils d’escalade de verrous

L'escalade de verrouillage est déclenchée lorsque l'option d'escalade de verrouillage n'est pas désactivée sur la table à l'aide de l'option ALTER TABLE SET LOCK_ESCALATION et lorsque l'une des conditions suivantes est remplie :

  • Une seule instruction Transact-SQL acquiert au moins 5 000 verrous sur une seule table ou un seul index non partitionné.
  • Une seule instruction Transact-SQL acquiert au moins 5 000 verrous sur une seule partition d'une table partitionnée et l'option ALTER TABLE SET LOCK_ESCALATION est définie sur 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 les verrous ne peuvent pas être escaladés en raison de conflits de verrous, le moteur de base de données déclenche périodiquement l'escalade des verrous tous les 1 250 nouveaux verrous acquis.

Seuil d’escalade pour une instruction Transact-SQL

Lorsque le moteur de base de données vérifie les escalades possibles tous les 1 250 nouveaux verrous acquis, une escalade de verrous se produit si et seulement si une instruction Transact-SQL a acquis au moins 5 000 verrous sur une seule référence d'une table. L'escalade de verrous est déclenchée lorsqu'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 comporte une jointure sur elle-même dans une table et que chaque référence à la table n'acquiert que 3 000 verrous dans la table.

L'escalade de verrous ne se produit que pour les tables auxquelles l'accès a été effectué au moment où l'escalade est déclenchée. Supposons qu'une seule instruction SELECT soit une jointure qui accède à trois tables dans cet ordre : TableA, TableB et TableC. L'instruction acquiert 3 000 verrous de ligne dans l'index clusterisé pour TableA et au moins 5 000 verrous de ligne dans l'index clusterisé pour TableB, mais n'a pas encore 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 tente d'escalader tous les verrous détenus par la transaction en cours sur 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

Le moteur de base de données déclenche l'escalade des verrous chaque fois que le nombre de verrous est supérieur au seuil de mémoire. Le seuil de mémoire dépend du paramètre locks de l'option de configuration :

  • Si l'option locks est définie sur sa valeur par défaut 0, le seuil d'escalade des verrous est atteint lorsque la mémoire utilisée par les objets verrouillés 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 est différente de 0, le seuil d'escalade des verrous est de 40 % (ou moins en cas de pression sur la mémoire) de la valeur de l'option locks.

Le moteur de base de données peut choisir n'importe quelle instruction active dans n'importe quelle session pour l'escalade, et pour chaque 1 250 nouveaux verrous, il choisira des instructions pour l'escalade tant que la mémoire de verrouillage utilisée dans l'instance reste supérieure au seuil.

Échappement des verrous avec des types de verrous mixtes

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

Par exemple, considérons une session :

  • Commence une transaction.
  • - mise à jour d'une table contenant un index clusterisé.
  • Émission d'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'intention exclusive (IX) sur les pages d'index groupé contenant ces lignes.
  • Un verrou IX sur l'index groupé 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'elle lit, sauf si ces dernières sont déjà protégées par un verrou X de l'instruction UPDATE.
  • Verrous de partage (IS) sur toutes les pages d'index groupé contenant ces lignes, sauf si la page est déjà protégée par un verrou IX.
  • Aucun verrou n'est placé sur l'index groupé ou la table, 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 celle-ci réussit, le verrou IX sur la table est converti en verrou X et tous les verrous de ligne, de page et d'index sont libérés. Les mises à jour et les lectures sont protégées par le verrou X sur la table.

Réduire le verrouillage et l'escalade des verrous

Dans la plupart des cas, le moteur de base de données offre les meilleures performances lorsqu'il fonctionne avec ses paramètres par défaut pour le verrouillage et l'escalade des verrous.

Si une instance du moteur de base de données génère de nombreux verrous et rencontre fréquemment des escalades de verrous, envisagez de réduire le nombre de verrous à l'aide des stratégies suivantes :

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

    • READ COMMITTED niveau d'isolation lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est activéeON.
    • SNAPSHOT niveau d'isolation.
    • READ UNCOMMITTED niveau d'isolation. Cette option ne peut être utilisée que pour les systèmes pouvant fonctionner avec des lectures sales.

  • Utilisez les conseils de table PAGLOCK ou TABLOCK pour que le moteur de base de données utilise des verrous de page, de tas ou d'index au lieu de verrous de bas niveau. Cependant, l'utilisation de cette option augmente les problèmes de blocage des utilisateurs qui tentent d'accéder aux mêmes données et ne doit pas être utilisée dans les systèmes comportant plus de quelques utilisateurs simultanés.

  • Si le verrouillage optimisé n'est pas disponible, pour les tables partitionnées, utilisez l'option LOCK_ESCALATION de ALTER TABLE pour faire passer les verrous à la partition au lieu de la table, ou pour désactiver l'escalade des verrous pour une table.

  • Divisez les opérations de traitement par lots volumineuses en plusieurs opérations plus petites. Par exemple, supposons que vous ayez exécuté la requête suivante pour supprimer plusieurs centaines de milliers d'anciennes lignes d'une table d'audit, puis que vous ayez constaté que cela provoquait une escalade de verrouillage qui bloquait d'autres utilisateurs :

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

    En supprimant ces lignes par quelques centaines à la fois, vous pouvez réduire considérablement le nombre de verrous qui s'accumulent par transaction et éviter l'escalade des 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 d'un verrou de requête en rendant la requête aussi efficace que possible. Les analyses volumineuses ou les recherches de clés en grand nombre peuvent augmenter le risque d'escalade des verrous ; en outre, cela augmente le risque de blocages et affecte généralement la concurrence et les performances. Après avoir identifié la requête qui provoque l'escalade des verrous, recherchez les possibilités de créer de nouveaux index ou d'ajouter des colonnes à un index existant afin de supprimer les analyses complètes d'index ou de tables et d'optimiser 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 : Conseiller de réglage 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 afin de minimiser le coût des recherches de clés (maximiser la sélectivité de l'index pour la 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 prélecture pour effectuer la recherche. Si le moteur de base de données utilise la prélecture pour une recherche, il doit augmenter le niveau d'isolation de 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 groupé et sur un index non groupé), ce qui peut entraîner le dépassement des seuils d'escalade de verrouillage pour une telle requête. Ceci est particulièrement important si vous constatez que le verrou évalué est un verrou de table partagé, ce qui n'est toutefois pas courant au niveau d'isolation READ COMMITTED par défaut.

    Si une recherche de clé avec l'optimisation de prélecture provoque une évaluation de verrou, envisagez d'ajouter des colonnes supplémentaires à l'index non clusterisé qui apparaît dans l'opérateur logique Index Seek ou Index Scan sous l'opérateur logique de recherche de clé dans le plan de requête. Il est possible de créer un index de couverture (un index qui inclut toutes les colonnes d'une table 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 tout le contenu de la liste de colonnes SELECT n'est pas pratique. Une jointure en boucle imbriquée peut également utiliser l'optimisation de prélecture, 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. En outre, si une tentative d'escalade de verrouillage échoue parce qu'un autre SPID détient un verrou de table incompatible, la requête qui a tenté l'escalade ne se bloque pas en attendant 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. Par conséquent, une méthode pour empêcher l'escalade de verrouillage sur une table particulière consiste à acquérir et à conserver un verrou sur une connexion différente qui n'est pas compatible avec le type de verrou escaladé. Un verrou d'intention exclusive (IX) au niveau de la table ne verrouille aucune ligne ni aucune page, mais il n'est toujours pas compatible avec un verrou de table partagé (S) ou exclusif (X) évalué. Par exemple, supposez que vous devez exécuter un travail par lots qui modifie un grand nombre de lignes dans la table mytable et qui a provoqué un blocage dû à l'escalade de verrou. Si ce travail se termine toujours en moins d'une heure, vous pouvez créer un travail Transact-SQL contenant le code suivant et planifier le nouveau travail pour qu'il démarre quelques minutes avant l'heure de début du travail 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 maintient un verrou IX sur mytable pendant une heure, ce qui empêche l'escalade des verrous sur la table pendant cette période. Ce lot ne modifie aucune donnée et ne bloque aucune autre requête (à moins que l'autre requête ne force un verrou de table avec l'indicateur TABLOCK ou qu'un administrateur 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 tout ou partie des escalades de verrouillage. Toutefois, ces indicateurs de trace désactivent globalement toutes les escalades de verrouillage pour l'ensemble de l'instance du moteur de base de données. Une escalade de verrous joue un rôle 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. L'escalade de verrouillage permet également de réduire la mémoire requise pour suivre les verrous. La mémoire que le moteur de base de données peut allouer dynamiquement aux structures de verrouillage est limitée. Par conséquent, si vous désactivez l'escalade de verrouillage et que la mémoire de verrouillage devient trop importante, les tentatives d'allocation de verrous supplémentaires pour une requête peuvent é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.

    Note

    Lorsque l'erreur MSSQLSERVER_1204 se produit, elle arrête le traitement de l'instruction en cours et provoque une restauration de la transaction active. 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.

    Note

    L'utilisation d'une indication de verrouillage telle que ROWLOCK modifie uniquement l'acquisition initiale du verrou. Les indicateurs de verrou n’empêchent pas l’escalade de verrous.

À partir de SQL Server 2008 (10.0.x), le comportement de l'escalade des 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 des verrous

Surveillez l'escalade des verrous à l'aide de 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.

Graphique représentant le coût du verrouillage par rapport au coût de la concurrence.

Le moteur de base de données utilise une stratégie de verrouillage dynamique pour déterminer les verrous les plus efficaces. Le moteur de base de données détermine automatiquement les verrous les plus appropriés lors de l'exécution de la requête, en fonction des caractéristiques du schéma et de la requête. Par exemple, pour réduire la surcharge liée au verrouillage, l'optimiseur peut choisir des verrous de page dans un index lors de l'analyse d'un 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é est uniquement disponible pour les systèmes équipés d'au moins 16 processeurs logiques. Elle est automatiquement activée 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 des 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 des verrous

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

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

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

  • Les verrous partagés (S), exclusifs (X) et autres verrous dans des modes autres que NL, Sch-S, IS, IU et IX doivent être acquis sur toutes les partitions commençant par l'ID de partition 0 et suivant dans l'ordre des ID de partition. Ces verrous situés sur une ressource partitionnée utilisent 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 performances de verrou 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 des 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 utilisés dans les exemples suivants.

-- 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. En raison de l'indication de verrouillage HOLDLOCK, cette instruction acquiert et conserve un verrou partagé d'intention (IS) sur la table (pour cette illustration, les verrous de ligne et de page sont ignorés). Le verrou IS est obtenu uniquement sur la partition assignée à la transaction. Dans cet exemple, nous supposons 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 est démarrée et l'instruction SELECT exécutée dans cette transaction acquiert et conserve un verrou partagé (S) sur la table. Le verrou S est acquis sur toutes les partitions, ce qui entraîne plusieurs verrous de table, un pour chaque partition. Par exemple, sur un système à 16 processeurs, 16 verrous S seront émis sur les ID de partition de verrou 0 à 15. Comme le verrou S est compatible avec le verrou IS détenu sur l'ID de partition 7 par la transaction de la session 1, il n'y a pas de blocage 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'indication de verrouillage exclusif (X) de la table, la transaction tente d'acquérir un verrou X sur la table. Toutefois, le verrou S qui est maintenu par la transaction de la session 2 bloque 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. En raison de l'indication de verrouillage HOLDLOCK, cette instruction acquiert et conserve un verrou partagé d'intention (IS) sur la table (pour cette illustration, les verrous de ligne et de page sont ignorés). Le verrou IS est obtenu uniquement sur la partition assignée à la transaction. Dans cet exemple, nous supposons 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'indication de verrouillage TABLOCKX, la transaction tente d'acquérir un verrou exclusif (X) sur la table. N'oubliez pas que le verrou X doit être acquis sur toutes les partitions commençant par l'ID de partition 0. Le verrou X sera acquis sur toutes les partitions, de l'ID 0 à 5, mais il est bloqué par le verrou IS acquis sur la partition ID 6.

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

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Niveaux d'isolation basés sur le versionnement des lignes dans le moteur de base de données

À partir de SQL Server 2005 (9.x), le moteur de base de données offre une implémentation d'un niveau d'isolation de transaction existant, READ COMMITTED, qui fournit un instantané au niveau de l'instruction à l'aide du versionnement des lignes. Le moteur de base de données offre également un niveau d'isolation des transactions, SNAPSHOT, qui fournit un instantané au niveau de la transaction en utilisant également le versionnement des lignes.

Le versionnement des lignes est un framework général dans SQL Server qui invoque un mécanisme de copie à l'é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 versionnement des lignes est utilisé pour implémenter les fonctionnalités suivantes :

  • Créer 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'indexation qui spécifient l'option ONLINE.
  • Prise en charge des niveaux d'isolation des transactions basés sur le versionnement des lignes :
    • Une nouvelle implémentation du niveau d'isolation READ COMMITTED qui utilise le versionnement des lignes pour fournir une cohérence de lecture au niveau des instructions.
    • Un nouveau niveau d'isolation, SNAPSHOT, pour fournir une cohérence de lecture au niveau des transactions.

Les versions des lignes sont stockées dans un magasin de versions. Si la récupération accélérée de la base de données (ADR) 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 disposer de suffisamment d'espace pour le magasin de versions. Lorsque le magasin de versions se trouve dans tempdbet que la base de données tempdb est complète, les opérations de mise à jour arrêtent de générer des versions, mais continuent de réussir, mais les opérations de lecture peuvent échouer, car une version de 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 adr est utilisé et que le magasin de versions est complet, 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. INSERT Les opérations continuent de réussir si la base de données dispose de suffisamment d'espace.

L'utilisation du versionnement de lignes pour les transactions READ COMMITTED et SNAPSHOT est un processus en deux étapes :

  1. Définissez l'option de base de données READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION, ou les deux, 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 définie sur ON, les transactions qui définissent le niveau d'isolation READ COMMITTED utilisent le versionnement de lignes.
    • Lorsque l'option de base de données ALLOW_SNAPSHOT_ISOLATION est définie sur 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 attribue un numéro de séquence de transaction (XSN) à chaque transaction qui manipule des données à l'aide du versionnement de lignes. Les transactions démarrent au moment où une instruction BEGIN TRANSACTION est exécutée. Toutefois, le numéro de séquence de transaction commence par la première opération de lecture ou d'écriture après l'instruction BEGIN TRANSACTION. Ce numéro augmente de 1 à chaque fois qu'il est attribué.

Lorsque les options de base de données READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION sont définies sur ON, des copies logiques (versions) sont conservées 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 spécifique, l'instance du moteur de base de données stocke 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 actuelle et chaînée aux lignes versionnées dans le magasin de versions.

Note

Pour la modification d'objets volumineux (LOB), seul le fragment modifié est copié dans le magasin 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 suit le numéro de séquence de transaction utile le plus ancien et supprime périodiquement toutes les versions de ligne marquées avec des numéros de séquence de transaction inférieurs au numéro de séquence utile le plus ancien.

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 versionnées. Ces versions de lignes sont libérées lorsqu'elles ne sont plus nécessaires. Un processus d'arrière-plan supprime les versions de lignes obsolètes.

Note

Pour les transactions de courte durée, une version d'une ligne modifiée peut être mise en cache dans le pool de tampons sans être écrite dans le magasin de versions. Si la ligne versionnée n'est nécessaire que pendant une courte période, elle est supprimée du pool de tampons et n'entraîne pas de surcharge d'E/S.

Comportement lors de la lecture de données

Lorsque des transactions s'exécutant sous un niveau d'isolation basé sur le versionnement des lignes lisent des données, les opérations de lecture n'acquiert 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. READ COMMITTED L'isolation basée sur le versionnement des lignes et l'isolation SNAPSHOT sont conçues pour fournir une cohérence de lecture au niveau des instructions ou des transactions pour les données versionnées.

Toutes les requêtes, y compris les transactions s'exécutant sous des niveaux d'isolation basés sur le versionnement des lignes, acquièrent des verrous de stabilité du schéma (Sch-S) pendant la compilation et l'exécution. Pour cette raison, les requêtes sont bloquées lorsqu'une transaction concurrente détient un verrou de modification du schéma (Sch-M) sur la table. Par exemple, une opération de langage de définition de données (DDL) 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'isolation basé sur le versionnement des lignes, sont bloquées lors d'une tentative d'acquisition d'un verrou Sch-S. Inversement, une requête détenant un verrou Sch-S bloque une transaction simultanée qui tente d'acquérir un verrou Sch-M.

Lorsqu'une transaction utilisant le niveau d'isolation SNAPSHOT démarre, l'instance du moteur de base de données enregistre toutes les transactions actuellement actives. Lorsque la transaction SNAPSHOT lit une ligne qui possède 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 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 qui avait été validée au moment où la transaction SNAPSHOT a démarré. 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 qui utilisent le versionnement de lignes fonctionnent de manière très similaire. La différence réside dans le fait que la transaction READ COMMITTED n'utilise pas son propre numéro de séquence de transaction lors de la sélection 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. Il s'agit du numéro de séquence de transaction utilisé pour sélectionner les versions de ligne pour cette instruction. Cela permet aux transactions READ COMMITTED de voir un instantané des données telles qu'elles existent au début de chaque instruction.

Note

Même si les transactions READ COMMITTED utilisant le versionnement de lignes fournissent une vue cohérente des données au niveau de l'instruction, les versions de lignes générées ou accessibles par ce type de transaction sont conservées jusqu'à la fin de la transaction.

Comportement lors de la modification de données

Le comportement des écritures de données est différent avec et sans verrouillage optimisé activé.

Modifier les données sans verrouillage optimisé

Dans une transaction READ COMMITTED utilisant le versionnement de lignes, la sélection des lignes à mettre à jour s'effectue à l'aide d'une analyse bloquante où un verrou de mise à jour (U) est acquis sur la ligne de données lors de la lecture des valeurs de données. Ceci est identique à une transaction READ COMMITTED qui n'utilise pas le versionnement de lignes. Si la ligne de données ne répond pas aux critères de mise à jour, le verrou de mise à jour est libéré sur cette ligne et la ligne suivante est verrouillée et analysée.

Les transactions s'exécutant sous l'isolation SNAPSHOT adoptent une approche optimiste de la modification des données en acquérant des verrous sur les données avant d'effectuer la modification, uniquement pour appliquer les contraintes. 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 de données n'a pas été modifiée par une transaction simultanée qui s'est engagé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 interrompue. Le conflit de mise à jour est géré par le moteur de base de données et il n'existe aucun moyen de désactiver la détection des conflits de mise à jour.

Note

Les opérations de mise à jour s'exécutant sous l'isolation SNAPSHOT s'exécutent en interne sous l'isolation READ COMMITTED lorsque la transaction SNAPSHOT accède à l'un des éléments suivants :

Une table avec une contrainte de clé étrangère.

Une table référencée dans la contrainte de clé étrangère d'une autre table.

une vue indexée faisant référence à plusieurs tables.

Cependant, même sous ces conditions, l'opération de mise à jour continue à vérifier que les données n'ont pas été modifiées par une autre transaction. Si les données ont été modifiées par une autre transaction, la transaction SNAPSHOT rencontre un conflit de mise à jour et est interrompue. Les conflits de mise à jour doivent être gérés et réessayés par l'application.

Modifier les données avec un 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'obtiennent aucun verrou et les rédacteurs obtiennent des verrous de bas niveau de courte durée, au lieu de verrous qui expirent à la fin de la transaction.

Il est recommandé d'activer RCSI pour obtenir une efficacité optimale avec le verrouillage optimisé. Lorsque vous utilisez des 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 rédacteurs, ce qui entraîne une augmentation du blocage et de la mémoire de verrouillage.

Lorsque RCSI est activé et que vous utilisez le niveau d'isolation READ COMMITTED par défaut, les rédacteurs qualifient les lignes en fonction du prédicat basé sur la dernière version validée de la ligne, sans acquérir de verrous U. Une requête attend uniquement si la ligne se qualifie et qu’il existe une autre transaction d’écriture active sur cette ligne ou cette page. La qualification basée sur la dernière version validée et le verrouillage des seules lignes qualifiées réduisent le blocage et augmentent la concurrence.

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

Lorsque le verrouillage optimisé est activé et que le niveau d'isolation SNAPSHOT est utilisé, le comportement des conflits de mise à jour est identique à celui observé sans verrouillage optimisé. Les conflits de mise à jour doivent être gérés et réessayés par l'application.

Note

Pour plus d'informations sur les changements de comportement liés à la fonctionnalité de verrouillage après qualification (LAQ) dans le cadre du verrouillage optimisé, consultez Changements de comportement des requêtes avec verrouillage optimisé et RCSI.

Synthèse des comportements

Le tableau suivant résume les différences entre l'isolation SNAPSHOT et l'isolation READ COMMITTED utilisant le versionnement des lignes.

Propriété Niveau d'isolation READ COMMITTED à l'aide du versionnage des lignes SNAPSHOT Niveau d'isolation
Option de base de données qui doit être définie sur ON pour activer la prise en charge requise. 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 READ COMMITTED par défaut 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. Nécessite l'exécution de SET TRANSACTION ISOLATION LEVEL pour spécifier le niveau d'isolation 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 ligne 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 acquérir de verrous. Si les lignes sont éligibles pour la mise à jour, des verrous exclusifs de ligne ou de page sont acquis. Si des conflits de mise à jour sont détectés, ils sont gérés et réessayé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 des conflits de mise à jour Sans verrouillage optimisé : Aucune.

Avec verrouillage optimisé : si des conflits de mise à jour sont détectés, ils sont gérés et réessayé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

Le framework de versionnement des lignes prend en charge les fonctionnalités suivantes du moteur de base de données :

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

Le framework de versionnement des lignes prend également en charge les niveaux d'isolation des transactions basés sur le versionnement des lignes suivants :

  • Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est définie sur ON, les transactions READ_COMMITTED assurent la cohérence de lecture au niveau des instructions à l'aide du versionnement des lignes.
  • Lorsque l'option de base de données ALLOW_SNAPSHOT_ISOLATION est définie sur ON, les transactions SNAPSHOT assurent la cohérence de lecture au niveau des transactions à l'aide du versionnement des lignes.

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 lorsqu'aucune transaction active n'utilise l'isolation basée sur le versionnement des lignes. Les données après modification incluent un pointeur vers les données versionnées dans le magasin de versions. Pour les objets volumineux, seule la partie de l'objet qui a été modifiée est stocké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 d'un espace suffisant pour contenir les versions de lignes. L’administrateur de base de données doit s’assurer que tempdb et d’autres bases de données (si ADR est activé) 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 création d'index en ligne est utilisé pour les créations d'index en ligne.
  • Le magasin de versions commun est utilisé pour toutes les autres opérations de modification des données.

Les versions de lignes doivent être stockées aussi longtemps qu'une transaction active a besoin d'y accéder. Périodiquement, un thread d'arrière-plan supprime les versions de ligne qui ne sont plus nécessaires et libère de l'espace dans le magasin 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.

Note

Quand un déclencheur est appelé au sein d'une transaction, les versions de ligne créées par le déclencheur sont conservées jusqu'à la fin de la transaction, même si les versions de ligne ne sont plus nécessaires après l'exécution du déclencheur. Cela s'applique également aux transactions READ COMMITTED qui utilisent le versionnement de ligne. Dans ce type de transaction, une vue cohérente sur le plan transactionnel de la base de données n'est nécessaire que pour chaque instruction de la transaction. Cela signifie que les versions de ligne créées pour une instruction de la transaction ne sont plus nécessaires une fois l'instruction exécutée. Cependant, les versions de ligne créées par chaque instruction de la transaction sont conservées jusqu'à la fin de la transaction.

Si le magasin de versions se trouve dans tempdb et que tempdb manque d'espace, le moteur de base de données force 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. Si le processus de réduction aboutit, de l'espace devient disponible dans tempdb. Sinon, tempdb manque d'espace et les événements suivants se produisent :

  • 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, mais la transaction qui écrit les données n'est pas affectée.

  • Les transactions qui tentent d'accéder à des versions de ligne qui n'ont pas été générées en raison d'une restauration complète de tempdb se terminent avec une 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 :

  • 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.
  • La récupération de base de données accélérée (ADR) est activée.

Ces 14 octets sont supprimés de la ligne de base de données lors de la première modification de la ligne, pour autant que toutes les conditions suivantes soient remplies :

  • READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION sont définies sur OFF.
  • le déclencheur n'existe plus dans la table ;
  • MARS n'est pas utilisé.
  • aucune opération de construction d'index en ligne n'est en cours d'exécution.
  • La récupération de base de données accélérée (ADR) est désactivée.

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 des informations de versionnement des lignes peut entraîner le fractionnement des pages d'index ou l'allocation d'une nouvelle page de données si l'espace disponible sur la page actuelle est insuffisant. 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 actuelles sur la densité de la page pour les données et les index d'une table ou d'une vue, vous pouvez utiliser sys.dm_db_index_physical_stats.

Espace utilisé par le magasin de versions persistantes (PVS)

Lorsque l’ADR est activé, les versions de ligne peuvent être stockées dans le magasin de versions persistantes (PVS) de l’une des manières suivantes, en fonction de la taille de la ligne avant la modification :

  • Si la taille est petite, l’ancienne version de ligne entière est stockée dans le cadre de la ligne modifiée.
  • Si la taille est intermédiaire, la différence entre l’ancienne version de ligne et la ligne modifiée est stockée dans le cadre de la ligne modifiée. La différence est construite de manière à permettre au moteur de base de données de reconstruire entièrement l’ancienne version de ligne si nécessaire.
  • Si la taille est importante, l'ancienne version de la ligne est stockée dans une table interne distincte.

Les deux premières méthodes sont appelées stockage de version dans la ligne. La dernière méthode est appelée stockage de versions en dehors de la rangée. Lorsque les versions dans les lignes ne sont plus nécessaires, elles sont supprimées pour libérer de l’espace sur les pages. De même, les pages de la table interne contenant des versions hors-ligne qui ne sont plus nécessaires sont supprimées par le nettoyeur de versions.

L'intégration des versions de lignes au sein même des lignes optimise la récupération des données pour les transactions nécessitant la lecture de ces versions. Si une version est stockée dans la ligne, il n'est pas nécessaire de lire séparément une page PVS hors ligne.

Le DMV sys.dm_db_index_physical_stats fournit le nombre et le type de versions stockées dans la ligne et hors ligne pour une partition d'un index. La taille totale des données de version stockées dans la ligne est indiquée dans la colonne total_inrow_version_payload_size_in_bytes.

La taille du stockage de version hors ligne est indiquée dans la colonne persistent_version_store_size_kb du DMV sys.dm_tran_persistent_version_store_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 de grande taille, jusqu'à 2 gigaoctets (Go), 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 lié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 dans 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 stockaient jusqu'à 8 080 octets de données ntext, text ou image par fragment.

Les données d'objets volumineux (LOB) ntext, text et image existantes ne sont pas mises à jour pour libérer de l'espace pour les informations de versionnement des lignes 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, Cela se produit même si les versions de ligne ne sont pas 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 si un seul octet est modifié. 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 consignées de manière minimale si le modèle 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.

Surveiller le versionnement des lignes et le magasin de versions

Pour surveiller les processus de versionnement des lignes, du magasin de versions et de l'isolation des instantanés afin de vérifier les performances et de détecter les problèmes, le moteur de base de données fournit des outils sous la forme de vues de gestion dynamique (DMV) et de compteurs de performances.

DMV

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

  • 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. Renvoie 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).

    Note

    L'interrogation de sys.dm_tran_top_version_generators et sys.dm_tran_version_store peut s'avérer coûteuse, car ces deux commandes analysent l'intégralité du magasin de versions, qui peut être volumineux. sys.dm_tran_version_store_space_usage est efficace et peu coûteux à exécuter, car il ne parcourt pas les enregistrements individuels du magasin de versions, mais renvoie l'espace total du magasin de versions utilisé dans tempdb par base de données.

  • sys.dm_tran_active_snapshot_database_transactions. Renvoie une table virtuelle pour toutes les transactions actives dans toutes les bases de données de l'instance SQL Server qui utilisent le versionnement de lignes. 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. Le DMV sys.dm_tran_current_snapshot est similaire à sys.dm_tran_transactions_snapshot, sauf qu'il renvoie uniquement 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 accélérée de la base de données 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 le magasin de versions dans tempdb, ainsi que les transactions utilisant le versionnement des lignes. Les compteurs de performances sont contenus dans l'objet de performances SQLServer:Transactions.

  • Espace libre dans tempdb (Ko). Surveille la quantité, en kilo-octets (Ko), d'espace libre dans la base de données tempdb. Il doit y avoir suffisamment d'espace libre dans tempdb pour gérer le magasin de versions qui prend en charge l'isolation des instantanés.

    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 commun] = 2 * [données du magasin de versions générées par minute] * [durée d'exécution la plus longue (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 du magasin de versions (Ko). Surveille la taille en Ko de tous les magasins de versions dans tempdb. Ces informations permettent de déterminer l'espace nécessaire dans la base de données tempdb pour le magasin de versions. La surveillance de ce compteur sur une période donnée fournit une estimation utile de l'espace supplémentaire nécessaire pour tempdb.

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

  • Taux de nettoyage des versions (Ko/s). Surveille le taux de nettoyage des versions en Ko par seconde dans tous les magasins de versions dans tempdb.

    Note

    Les informations provenant du taux de génération des versions (Ko/s) et du taux de nettoyage des versions (Ko/s) peuvent être utilisées pour prévoir les besoins en espace de tempdb.

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

  • Création d'unités de magasin de 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.

  • Troncature d'unités de magasin de 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 détermine qu'aucune des lignes de version stockées dans l'unité de magasin de versions n'est nécessaire pour exécuter des transactions actives.

  • Taux de conflit de mise à jour. Surveille le rapport entre le nombre de transactions de mise à jour instantanée présentant des conflits de mise à jour et le nombre total de transactions de mise à jour instantanée.

  • Durée d'exécution la plus longue d'une 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. Cette option permet de déterminer si une transaction s'exécute pendant une durée inhabituelle.

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

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

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

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

    Note

    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 transactions instantanées et les transactions de mise à jour d'instantané représente le nombre de transactions instantanées en lecture seule.

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

Les exemples suivants illustrent les différences de comportement entre les transactions d'isolation SNAPSHOT et les transactions READ COMMITTED qui utilisent le versionnement de ligne.

A. Utilisation de l'isolation SNAPSHOT

Dans cet exemple, une transaction s'exécutant sous isolation 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 à lire les données de la ligne versionnée, en ignorant la modification des données. Toutefois, lorsque la transaction SNAPSHOT tente de modifier les données qui ont déjà été modifiées par l'autre transaction, la transaction SNAPSHOT génère une erreur et est interrompue.

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 de l'isolation READ COMMITTED avec le versionnement de lignes

Dans cet exemple, une transaction READ COMMITTED utilisant le versionnement de lignes s'exécute simultanément avec une autre transaction. La transaction READ COMMITTED se comporte différemment d'une transaction SNAPSHOT. Comme une transaction SNAPSHOT, la transaction READ COMMITTED lit les lignes versionnées même après que l'autre transaction a modifié les données. Cependant, contrairement à une transaction SNAPSHOT, la transaction READ COMMITTED :

  • Lit les données modifiées après que l'autre transaction a validé les modifications apportées aux données.
  • Peut mettre à jour les données modifiées par l'autre transaction là où la transaction SNAPSHOT ne le pouvait pas.

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 versionnement des lignes

Les administrateurs de base de données contrôlent les paramètres au niveau de la base de données pour le versionnement des lignes à l'aide des options de base de données READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION dans l'instruction ALTER DATABASE.

Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est définie sur ON, les mécanismes utilisés pour prendre en charge l'option sont immédiatement activés. Lorsque vous définissez l'option READ_COMMITTED_SNAPSHOT, seule la connexion qui exécute la commande ALTER DATABASE est autorisée dans la base de données. Il ne doit y avoir aucune autre connexion ouverte dans la base de données jusqu'à ce que ALTER DATABASE soit terminé. 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;

When the ALLOW_SNAPSHOT_ISOLATION database option is set to ON, the instance of the Database Engine doesn't start generating row versions for modified data until all active transactions that have modified data in the database complete. Si des transactions de modification sont actives, le moteur de base de données définit l'état de l'option sur PENDING_ON. Une fois toutes les transactions de modification terminées, l'état de l'option passe à ON. Les utilisateurs ne peuvent pas démarrer une transaction SNAPSHOT dans la base de données tant que l'option n'est pas ON. De même, la base de données passe par un état PENDING_OFF lorsque l'administrateur de base de données définit l'option ALLOW_SNAPSHOT_ISOLATION sur OFF.

L'instruction Transact-SQL suivante active ALLOW_SNAPSHOT_ISOLATION :

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

Le tableau suivant répertorie et décrit les états de l'option ALLOW_SNAPSHOT_ISOLATION. L'utilisation de ALTER DATABASE avec l'option ALLOW_SNAPSHOT_ISOLATION ne bloque pas les utilisateurs qui accèdent actuellement aux données de la base de données.

État de l'isolation SNAPSHOT pour la base de données actuelle Descriptif
OFF La prise en charge des transactions d'isolation SNAPSHOT n'est pas activée. Aucune transaction d'isolation SNAPSHOT n'est autorisée.
PENDING_ON La prise en charge des transactions d'isolation SNAPSHOT est en état de transition (de OFF à ON). L'exécution de toutes les transactions ouvertes doit être terminée.

Aucune transaction d'isolation SNAPSHOT n'est autorisée.
ON La prise en charge des transactions d'isolation SNAPSHOT est activée.

Les transactions SNAPSHOT sont autorisées.
PENDING_OFF La prise en charge des transactions d'isolation SNAPSHOT est en état de transition (de ON à OFF).

Les transactions SNAPSHOT démarrées après ce moment ne peuvent pas accéder à cette base de données. Les transactions SNAPSHOT existantes peuvent toujours accéder à cette 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 OFF que lorsque toutes les transactions SNAPSHOT démarrées lorsque l'état d'isolation SNAPSHOT de la base de données était ON sont terminées.

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èrent des versions de ligne.

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

Les utilisateurs ne peuvent pas définir l'option READ_COMMITTED_SNAPSHOT sur ON dans master, tempdb ou msdb.

Utiliser les niveaux d'isolation basés sur le versionnement des lignes

Le framework de versionnement des lignes est toujours activé et utilisé par plusieurs fonctionnalités. Outre la fourniture de niveaux d'isolation basés sur le versionnement des lignes, il est utilisé pour prendre en charge les modifications apportées dans les déclencheurs et les sessions MARS (Multiple Active Result Sets), ainsi que pour prendre en charge la lecture des données pour les opérations d'indexation 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 le versionnement des lignes en définissant l'option de base de données READ_COMMITTED_SNAPSHOT sur ON, comme indiqué dans l'exemple de code suivant :

    ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

    Lorsque la base de données est activée pour READ_COMMITTED_SNAPSHOT, toutes les requêtes s'exécutant sous le niveau d'isolation READ COMMITTED utilisent le versionnement des lignes, ce qui signifie que les opérations de lecture ne bloquent pas les opérations de mise à jour.

  • Isolation SNAPSHOT en définissant l'option de base de données ALLOW_SNAPSHOT_ISOLATION sur ON, comme indiqué 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 s'exécutant sous l'isolation SNAPSHOT peut accéder aux tables de la ou des bases de données pour 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 pour lesquelles l'option de base de données ALLOW_SNAPSHOT_ISOLATION n'est pas définie sur ON le niveau d'isolation doit être modifié. Par exemple, l'exemple de code suivant montre une instruction SELECT qui joint deux tables lors de l'exécution d'une transaction SNAPSHOT. Une table appartient à une base de données dans laquelle l'isolation SNAPSHOT n'est pas activée. Lorsque l'instruction SELECT s'exécute sous l'isolation 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;

    L'exemple de code suivant montre la même instruction SELECT qui a été modifiée pour changer le niveau d'isolation de transaction en READ COMMITTED lors de l'accès à une table 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_SNAPSHOTne peut pas être activé dans tempdb, msdb ou master.

  • Les tables temporaires globales sont stockées dans tempdb. Lors de l'accès à des tables temporaires globales dans une transaction SNAPSHOT, l'une des actions suivantes doit se produire :

    • Définissez 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 lorsque :

    • Une base de données est mise en lecture seule après le démarrage de la transaction SNAPSHOT, mais avant que la transaction SNAPSHOT n'accède à la base de données.
    • Si vous accédez à des objets à partir de plusieurs bases de données, l'état d'une base de données a été modifié de telle sorte que la récupération de la base de données s'est produite après le démarrage d'une transaction SNAPSHOT, mais avant que la transaction SNAPSHOT n'accède à 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 rattachée.

  • Les transactions distribuées, y compris les requêtes dans des bases de données partitionnées distribuées, ne sont pas prises en charge sous l'isolation 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 isolation SNAPSHOT entraîne l'échec de la transaction SNAPSHOT. Les transactions READ COMMITTED ne présentent 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 versionnement des lignes ne sont pas affectées.

    Note

    Les opérations BULK INSERT peuvent entraîner des modifications des métadonnées de la table cible (par exemple, lors de la désactivation des vérifications de contrainte). Dans ce cas, les transactions d'isolation SNAPSHOT simultanées accédant aux tables insérées en masse échouent.

Personnaliser le verrouillage et le versionnement des lignes

Personnaliser le délai d'expiration du verrou

Lorsqu'une instance du moteur de base de données ne peut pas accorder un verrou à une transaction parce qu'une autre transaction possède déjà un verrou en conflit sur la ressource, la première transaction est bloquée en attente de la libération du verrou existant. Par défaut, il n'y a pas de délai d'expiration pour les attentes de verrou, par conséquent, une transaction peut être bloquée indéfiniment.

Note

Utilisez la vue de gestion dynamique sys.dm_os_waiting_tasks pour déterminer si une tâche est bloquée et ce qui la bloque. Pour plus d'informations et des exemples, consultez Comprendre et résoudre les problèmes de blocage de SQL Server.

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 la valeur LOCK_TIMEOUT définie, l'instruction bloquée est automatiquement annulée et le message d'erreur 1222 (Lock request time-out period exceeded) est renvoyé. Toutefois, aucune transaction contenant l'instruction n'est restaurée. L'application doit donc posséder un gestionnaire d'erreurs capable d'intercepter le message d'erreur 1222. Si une application ne détecte pas l'erreur, elle peut continuer sans savoir qu'une instruction individuelle d'une transaction a été annulée, mais la transaction reste active. Des erreurs peuvent se produire car les instructions suivantes de 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.

Importante

Les applications qui utilisent des transactions explicites et qui exigent que la transaction se termine lors de la réception de l'erreur 1222 doivent restaurer explicitement la transaction dans le cadre de la gestion des erreurs. Sans cela, d'autres instructions peuvent s'exécuter involontairement sur la même session pendant que la transaction reste active, ce qui entraîne une croissance illimitée du journal des transactions et une perte de données si la transaction est restaurée ultérieurement.

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

SELECT @@LOCK_TIMEOUT;
GO

Personnaliser le niveau d'isolation des transactions

READ COMMITTED est le niveau d'isolation par défaut pour le 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 démarrage d'une transaction, les applications qui utilisent OLE DB peuvent appeler ITransactionLocal::StartTransaction avec isoLevel défini sur le niveau d'isolation des transactions souhaité. Lorsque vous spécifiez le niveau d'isolation en mode autocommit, les applications qui utilisent OLE DB peuvent définir la propriété DBPROPSET_SESSION DBPROP_SESS_AUTOCOMMITISOLEVELS sur le niveau d'isolation des transactions souhaité.
  • 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 comportement de verrouillage pour toutes les requêtes et instructions DML (langage de manipulation des données) de la session fonctionne à ce niveau d'isolation. 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. La spécification d'une astuce au niveau de la 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

Les astuces de verrouillage peuvent être spécifiées pour des références de table individuelles dans les instructions SELECT, INSERT, UPDATE, DELETE et MERGE. Les indications spécifient le type de verrouillage ou de versionnement des lignes que l'instance du moteur de base de données utilise 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.

Note

Il n'est pas recommandé d'utiliser des indications de verrouillage lorsque le verrouillage optimisé est activé. Bien que les indications de table et de requête soient respectées, elles réduisent les avantages du verrouillage optimisé. Pour plus d'informations, consultez Éviter les indications de verrouillage avec le verrouillage optimisé.

Pour plus d'informations sur les indications de verrouillage spécifiques et leur comportement, consultez Indications de table (Transact-SQL).

Note

Nous vous recommandons d'utiliser les indicateurs de verrouillage au niveau des tables à la place du verrouillage par défaut seulement lorsque cela est nécessaire. Forcer un niveau de verrouillage peut nuire à la concurrence.

Le moteur de base de données peut être amené à acquérir des verrous lors de la lecture de métadonnées, même lors du traitement d'une instruction avec une instruction de verrouillage qui empêche les requêtes de verrous partagés lors de la lecture de données. Par exemple, une instruction SELECT s'exécutant sous le niveau d'isolation READ UNCOMMITTED ou utilisant l'instruction NOLOCK n'acquiert pas de verrous partagés lors de la lecture de données, mais peut parfois requérir des verrous lors de la lecture d'une vue de catalogue système. Cela signifie qu'une instruction SELECT de ce type peut être bloquée lorsqu'une transaction simultanée modifie les métadonnées de la table.

Comme le montre l'exemple suivant, si le niveau d'isolation de transaction est défini sur SERIALIZABLE et que l'instruction NOLOCK est utilisée avec l'instruction SELECT, les verrous de plage de clés généralement utilisés pour maintenir les transactions SERIALIZABLE ne sont pas acquis.

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 acquis qui fait référence à HumanResources.Employee est un 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 des verrous et active les verrous HoBT (partition) sur les tables partitionnées. Cette option n'est pas une indication de verrouillage et peut être utilisée pour réduire l'escalade des verrouillages. Pour plus d’informations, consultez ALTER TABLE (Transact-SQL).

Personnaliser le verrouillage d'un index

Le moteur de base de données utilise une stratégie de verrouillage dynamique qui choisit automatiquement la granularité de verrouillage la plus adaptée aux requêtes dans la plupart des cas. Nous vous recommandons de ne pas remplacer les niveaux de verrouillage par défaut, sauf si les modèles d'accès aux tables ou aux index sont bien compris et cohérents, et s'il existe un problème de contention des 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 hebdomadaire par lots 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 travail par lots comporte un grand nombre de mises à jour, l'obtention d'un verrou exclusif sur la table peut être le meilleur moyen de garantir l'exécution efficace du travail par lots.

Dans certaines charges de travail, un type de blocage peut se produire lorsque deux opérations simultanées acquièrent des verrous de ligne sur la même table, puis se bloquent mutuellement car elles doivent toutes 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 blocages, consultez le guide sur les blocages.

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. Pour des raisons de compatibilité ascendante, 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 de la page, au niveau de la ligne ou les deux peuvent être interdits 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 l'utilisation de transactions imbriquées. Si TransProc est appelé 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é par un processus qui n'a pas de transaction en attente, le COMMIT TRANSACTION à la fin de la procédure valide les 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 le moteur de base de données lorsqu'une transaction externe est active. La transaction est validée ou restaurée en fonction de la validation ou de la restauration à la fin de la transaction la plus externe. 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 dans une transaction imbriquée fait référence au nom de la transaction externe, la validation s'applique uniquement à la transaction la plus interne.

Le paramètre transaction_name d'une instruction ROLLBACK TRANSACTION ne peut pas faire référence à la transaction interne dans un ensemble de transactions imbriquées nommées. transaction_name ne peut faire référence qu'au nom de la transaction la plus externe. Si une instruction ROLLBACK TRANSACTION 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 paramètre transaction_name est exécutée à n'importe quel niveau d'un ensemble de transactions imbriquées, toutes les transactions imbriquées sont restaurées, y compris la transaction la plus externe.

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 qui ne possède pas de nom de transaction restaure toutes les transactions imbriquées et décrémente @@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 déterminer si vous êtes déjà dans une transaction, utilisez SELECT @@TRANCOUNT pour voir si elle est égale ou supérieure à 1. Si la valeur de @@TRANCOUNT est égale à 0, vous n’êtes pas dans une transaction.

Utiliser des sessions lié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 appelle 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, à l'aide du jeton de liaison reçu de la première session.

Note

Une session doit avoir une transaction utilisateur active pour que l’exécution de sp_getbindtoken ou srv_getbindtoken aboutisse.

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 aucune fonction API qu'une application peut utiliser pour obtenir le jeton de liaison d'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 de liaison peuvent être stockés dans une table d'une instance du moteur de base de données qui peut être lue par les processus souhaitant se lier à 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 a des résultats en attente provenant de l'instance, aucune autre session liée au même jeton ne peut accéder à l'instance tant que la session en cours n'a pas terminé le traitement ou annulé 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. L'utilisation de SET TRANSACTION ISOLATION LEVEL pour modifier le paramètre de niveau d'isolation d'une session n'affecte pas le paramètre des autres sessions liées au même jeton.

Types de sessions associées

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

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

  • Session liée distribuée Permet aux sessions liées de partager la même transaction entre deux instances ou plus jusqu'à ce que la transaction entière soit validée ou restaurée à l'aide du Coordinateur de transactions distribuées Microsoft (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 antérieures de SQL Server, les sessions liées étaient principalement utilisées dans le développement de procédures stockées étendues qui devaient exécuter des instructions Transact-SQL au nom du processus qui les appelait. 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 plus sécurisées, plus évolutives et plus stables que les procédures stockées étendues. Les procédures stockées CLR utilisent l'objet SqlContext pour rejoindre le contexte de la session d'appel, et non sp_bindsession.

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

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. Les transactions longues et inefficaces peuvent ne pas poser de problème avec un petit nombre d'utilisateurs, mais elles sont très problématiques dans un système comptant des milliers d'utilisateurs. À partir de SQL Server 2014 (12.x), le moteur de base de données prend en charge les transactions durables différées. Les transactions durables différées peuvent améliorer l'évolutivité et les performances, mais elles ne garantissent pas la durabilité. Pour plus d’informations, consultez Contrôler la durabilité d’une transaction.

Directives de code

Voici les directives à suivre pour coder des transactions efficaces :

  • É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 transaction READ COMMITTED. Peu de transactions nécessitent le niveau d'isolation de transaction SERIALIZABLE.

  • Utilisez intelligemment les options de concurrence 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 conseils de verrouillage pessimiste tels que HOLDLOCK. Les conseils 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 dans la mesure du possible. En raison de leur nature, les transactions implicites peuvent entraîner un comportement imprévisible. 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. Lorsque vous utilisez des transactions implicites, l'instruction Transact-SQL suivante après 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. Ce processus permet au moteur de base de données d'utiliser le mode de validation automatique pendant que l'application parcourt les données et obtient des entrées de l'utilisateur.

En outre, lorsque le niveau d'isolation SNAPSHOT est activé, bien qu'une nouvelle transaction ne conserve pas les verrous, une transaction longue empêche les anciennes versions d'être supprimées du magasin de versions.

Gérer les transactions longues

Une transaction longue est une transaction active qui n'a pas été validée ou restaurée en temps opportun. 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 :

Importante

Dans Azure SQL Database, les transactions inactives (transactions qui n'ont pas écrit dans le journal des transactions pendant six heures) sont automatiquement terminées afin de libérer des ressources.

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 de longue durée, les colonnes particulièrement intéressantes sont l'heure du premier enregistrement du journal (database_transaction_begin_time), l'état actuel de la transaction (database_transaction_state) et le numéro de séquence du journal (LSN) de l'enregistrement de début dans le journal des transactions (database_transaction_begin_lsn).

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

  • DBCC OPENTRAN

    Cette instruction vous permet d'identifier l'ID utilisateur du propriétaire de la transaction, afin que vous puissiez éventuellement rechercher la source de la transaction pour la terminer de manière appropriée (validation ou restauration). 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).

Blocages

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

Contenu associé