Guide du verrouillage des transactions et du versioning des lignes

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

Remarque

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, ainsi que le nombre de verrous requis pour les écritures simultanées. Cet article a été mis à jour pour décrire le moteur de base de données SQL Server avec et sans verrouillage optimisé. Pour l'instant, le verrouillage optimisé n'est disponible que dans Azure SQL Database.

Le verrouillage optimisé a considérablement mis à jour certaines sections de cet article, notamment en ce qui concerne :

Principes de base sur les transactions

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

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

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

Remarque

De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, SQL Server implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux magasins de données en mémoire. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

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

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

Les programmeurs SQL doivent concevoir des transactions dont les points de début et de fin permettent de maintenir la cohérence logique des données. La séquence de modifications des données qu'ils définissent doivent laisser les données dans un état cohérent par rapport aux règles d'entreprise définies par leur société. Ces instructions de modification des données doivent par conséquent être contenues dans une seule transaction pour que le moteur de base de données SQL Server puisse garantir l'intégrité physique de la transaction.

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

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

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

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

Contrôle des transactions

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

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

Démarrer des transactions

À l'aide des fonctions API et des instructions Transact-SQL, vous pouvez démarrer des transactions en mode explicite, implicite ou validation automatique dans les instances du moteur de base de données SQL Server.

Transactions explicites
Une transaction est explicite si vous définissez le début et la fin de la transaction de manière explicite à l'aide d'une fonction API ou en exécutant les instructions Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION ou ROLLBACK WORK. À la fin de la transaction, la connexion revient au mode de transaction sélectionné avant le début de la transaction, c'est-à-dire 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 les options de base de données, ou une des procédures système qui modifient la base de données master à l'intérieur de transactions explicites ou implicites.

Remarque

UPDATE STATISTICS peut être utilisée à l'intérieur d'une transaction explicite. Toutefois, UPDATE STATISTICS est validée indépendamment de la transaction qui la contient et ne peut pas être restaurée.

Transactions en mode de validation automatique
Le mode de validation automatique (autocommit) est le mode de gestion par défaut des transactions du moteur de base de données SQL Server. 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 SQL Server fonctionne par défaut en mode de validation automatique si les modes explicite ou implicite n'ont pas été spécifiés pour une transaction. Le mode autocommit est également le mode par défaut pour ADO, OLE DB, ODBC et DB-Library.

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

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

  • ALTER TABLE

  • CREATE

  • DELETE

  • DROP

  • FETCH

  • GRANT

  • INSERT

  • OPEN

  • REVOKE

  • SELECT

  • TRUNCATE TABLE

  • UPDATE

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

  • 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 SQL Server peut être utilisée comme gestionnaire de ressources dans les transactions distribuées coordonnées par un gestionnaire de transactions tel que Microsoft Distributed Transaction Coordinator (MS DTC), ou tout autre gestionnaire de transactions prenant en charge les spécifications Open Group XA pour le traitement des transactions distribuées. Pour plus d'informations, consultez la documentation MS DTC.

    Une transaction exécutée sur une seule instance du moteur de base de données SQL Server, mais utilisant plusieurs bases de données, est en réalité 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 une application, une transaction distribuée est gérée de manière comparable à une transaction locale. À la fin de la transaction, l'application requiert que la transaction soit validée ou restaurée. La validation d'une transaction distribuée doit être gérée de façon particulière par le gestionnaire de transaction pour minimiser les risques qu'une défaillance du réseau entraîne la validation de la transaction par certains gestionnaires de ressources, alors qu'elle sera restaurée par d'autres. Pour cela, le processus de validation est géré en deux phases, une phase de préparation et une phase de validation, d'où son nom de validation à deux phases (2PC).

    • 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 alors toutes les opérations nécessaires à l'enregistrement de la transaction, et tous les tampons contenant les images du journal de la transaction sont vidés sur le disque. Lorsque chaque gestionnaire de ressources a terminé la phase de préparation, il retourne un message de succès ou d'échec au gestionnaire de transactions. SQL Server 2014 (12.x) a introduit la durabilité différée des transactions. Les transactions durables retardées sont validées avant de vider les images de journal pour la transaction sur le disque. Pour plus d'informations sur la durabilité des transactions retardées, consultez l'article Durabilité des transactions.

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

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

Terminer les transactions

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

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

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

Remarque

Dans le cas des connexions prenant en charge les ensembles de résultats MARS (Multiple Active Result Sets), une transaction explicite démarrée par le biais d'une fonction API ne peut pas être validée alors que des demandes sont en attente d'exécution. Toute tentative de validation d'une transaction de ce type entraîne une erreur si des opérations sont toujours en attente.

Erreurs de traitement au cours d’une transaction

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

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

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

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

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

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

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

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

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

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

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

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

  • Verrouillage

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

  • Gestion de versions de ligne

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

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

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

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

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

Effet des accès concurrentiels

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

  • Mises à jour perdues

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

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

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

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

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

  • Analyse incohérente (lecture non reproductible)

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

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

  • Lectures fantômes

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

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

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

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

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

      Les transactions qui s’exécutent au niveau READ UNCOMMITTED ne génèrent pas de verrous partagés pour empêcher d’autres transactions de modifier des données lues par la transaction en cours. Les transactions qui s'exécutent au niveau READ COMMITTED génèrent des verrous partagés, mais les verrous de ligne ou de page sont libérés une fois la ligne lue. Dans les deux cas, lorsque vous analysez un index, si un autre utilisateur modifie la colonne de clé d'index de la ligne pendant votre lecture, la ligne peut apparaître de nouveau si la modification apportée à la clé a déplacé la ligne à une position située en aval de votre analyse. De même, la ligne peut ne pas apparaître si la modification apportée à la clé a déplacé la ligne à 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 peut pas se produire lorsque vous utilisez la lecture validée, car un verrou de table est maintenu pendant un fractionnement de page et ne se produit pas si la table n'a pas d'index cluster, car les mises à jour ne provoquent pas de fractionnements de page.

Types de concurrence

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

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

  • Contrôle de concurrence pessimiste

    Un système de verrouillage empêche les utilisateurs de modifier des données d'une manière qui affecterait les autres utilisateurs. Lorsqu'un utilisateur a effectué une action qui applique un verrouillage, les autres utilisateurs 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. Cette méthode s'appelle le contrôle pessimiste car elle est principalement utilisée dans les environnements où les données sont très sollicitées et où le coût de protection des données par verrouillage est inférieur au coût de restauration des transactions en cas de contentions de concurrence.

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

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

SQL Server prend en charge plusieurs niveaux 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 des instructions Transact-SQL ou des propriétés et attributs des interfaces de programmation d'applications (API) de base de données telles que ADO, ADO.NET, OLE DB et ODBC.

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

Les transactions spécifient un niveau d'isolement. Ce niveau définit le degré d'isolement d'une transaction par rapport aux modifications de ressource ou de données apportées par d'autres transactions. Les niveaux d'isolation déterminent les effets secondaires de la concurrence (lectures incorrectes, lectures fantômes) qui sont autorisés.

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

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

Important

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 acquiert toujours un verrou exclusif sur les données qu'elle modifie et garde celui-ci jusqu'à ce qu'elle ait terminé son travail, indépendamment du niveau d'isolation défini pour elle. 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.

Plus le niveau d'isolation est faible, plus le nombre de personnes susceptibles d'accéder aux données en même temps est élevé, et plus les effets secondaires de la concurrence (lectures incorrectes, mises à jour perdues) sont nombreux. Inversement, plus le niveau d'isolation est élevé, plus le nombre de types d'effets secondaires de la concurrence qu'un utilisateur est susceptible de rencontrer est réduit. Cependant, la quantité de ressources système nécessaires et la probabilité d'un blocage mutuel de transactions sont plus élevées. Le choix du niveau d'isolation adéquat dépend d'une mise en équilibre de l'espace réservé et des exigences en matière d'intégrité des données de l'application. Le niveau le plus élevé, sérialisable, garantit qu'une transaction récupère exactement les mêmes données à chaque fois qu'elle répète une opération de lecture, mais en utilisant un niveau de verrouillage susceptible de gêner les autres utilisateurs dans les systèmes multi-utilisateurs. Le niveau le plus bas, lecture non validée, permet la récupération de données qui ont été modifiées mais non validées par d'autres transactions. Ce niveau permet l'apparition de tous les effets secondaires de la concurrence, mais la charge du système est réduite puisqu'il n'y a ni verrouillage de lecture, ni contrôle de version de ligne.

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

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

Niveau d’isolation Définition
Lecture non validée Le niveau d'isolement le plus bas et suffisant pour s'assurer que les données physiquement corrompues 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.
Lecture validée 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 SQL Server conserve les verrous d'écriture (acquis sur les données sélectionnées) jusqu'à la fin de la transaction, mais les verrous le lecture sont libérés dès que l'opération SELECT est terminée. Il s'agit du niveau par défaut pour le moteur de base de données SQL Server.
Lecture renouvelée Le moteur de base de données SQL Server conserve les verrous de lecture et d'é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.
Sérialisable Niveau le plus élevé, dans lequel les transactions sont totalement isolées les unes des autres. Le moteur de base de données SQL Server conserve les verrous de lecture et d'écriture acquis sur les données sélectionnées de façon à les libérer à la fin de la transaction. Les verrous d'étendues sont acquis lorsqu'une opération SELECT utilise une clause WHERE, plus particulièrement pour éviter les lectures fantômes.

Remarque : Les opérations DDL et les transactions sur les tables répliquées peuvent échouer quand le niveau d’isolation sérialisable est demandé. Cela est dû au fait que les requêtes de réplication utilisent des indicateurs qui peuvent être incompatibles avec le niveau d'isolation sérialisable.

SQL Server prend également en charge deux niveaux d'isolation de la transaction supplémentaires qui utilisent le contrôle de version de ligne. Le premier est une implémentation de l'isolement READ COMMITTED, et le deuxième est un niveau d'isolation de la transaction, l'instantané.

Niveau d'isolement basé sur le contrôle de version de ligne Définition
Instantané read committed (RCSI) Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est activé (ON), le niveau d'isolation READ COMMITTED utilise le contrôle de version de ligne pour procurer une lecture cohérente au niveau des instructions. Les opérations de lecture ont besoin uniquement de verrous de table SCH-S, et pas de verrous de page ni de ligne. À savoir, le moteur de base de données SQL Server 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.

Si l'option de base de données READ_COMMITTED_SNAPSHOT est désactivé (OFF, qui est la valeur par défaut), l'isolation READ COMMITTED utilise des verrous partagés pour empêcher d'autres transactions de modifier des lignes pendant que la transaction active exécute une opération de lecture. Les verrous partagés empêchent également l'instruction de lire des lignes modifiées par d'autres transactions, tant que celles-ci ne sont pas terminées. Les deux variantes sont conformes à la définition ISO de l'isolation READ COMMITTED.
Instantané 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 ont besoin uniquement de verrous de table SCH-S, et pas de verrous de page ni de ligne. Lorsque l'opération de lecture lit des lignes modifiées par une autre transaction, elle récupère la version de la ligne du début de la transaction. Vous pouvez utiliser l’isolation d’instantané pour une base de données uniquement quand la valeur ON est attribuée à l’option de base de données ALLOW_SNAPSHOT_ISOLATION. Par défaut, cette option est désactivée (OFF) pour les bases de données utilisateur.

Remarque : SQL Server ne prend pas en charge le contrôle de version des métadonnées. Pour cette raison, il existe des restrictions sur les opérations DDL pouvant être effectuées dans une transaction explicite exécutée avec le niveau d'isolement d'instantané. Les instructions DDL suivantes ne sont pas autorisées avec le niveau d'isolation d'instantané après une instruction BEGIN TRANSACTION : ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME ou toute autre instruction DDL utilisant le langage 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
Lecture non validée Oui Oui Oui
Lecture validée Non Oui Oui
Lecture renouvelée Non Non Oui
Capture instantanée Non Non Non
Sérialisable Non Non Non

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

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

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

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

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

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

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

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

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

Verrouillage du moteur de base de données

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

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

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

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

  • Lorsque le verrouillage optimisé est activé, seul un verrou d'ID de transaction (TID) est conservé pendant la durée de la transaction. Sous le niveau d'isolation par défaut, les transactions ne contiennent pas les verrous de ligne et de page nécessaires pour les écritures jusqu'à la fin de la transaction. Cela réduit la mémoire de verrou requise et réduit la nécessité d'une escalade de verrous. En outre, lorsque le verrouillage optimisé est activé, l'optimisation du verrou après qualification (LAQ) évalue les prédicats d'une requête sur la dernière version validée de la ligne sans acquérir de verrou, améliorant ainsi la concurrence.

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

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

Granularité et hiérarchie des verrous

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

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

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

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

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

2 Des ressources de verrouillage supplémentaires sont disponibles pour les ressources de verrouillage XACT. Consultez Ajouts de diagnostic pour le verrouillage optimisé.

Modes de verrouillage

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

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

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

Verrous partagés

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

Verrous de mise à jour

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

Les verrous de mise à jour (U) peuvent également être pris par des requêtes qui n'exécutent pas d'instruction UPDATE, lorsque l'indicateur de table UPDLOCK est spécifié dans la requête. Il est courant que les applications utilisent un modèle « sélectionner une ligne, puis mettre à jour la ligne », où la lecture et l'écriture sont explicitement séparées dans la transaction. Dans ce cas, si le niveau d'isolation est reproductible en lecture ou sérialisable, les mises à jour simultanées peuvent probablement bloquer. Au lieu de cela, les applications peuvent suivre un modèle « sélectionner une ligne avec l'indicateur UPDLOCK, puis mettre à jour la ligne ».

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

  • Dans le niveau d'isolation validé en lecture par défaut, les verrous S sont de courte durée, libérés dès qu'ils sont utilisés. Les verrous de courte durée sont peu susceptibles d'entraîner des interblocages.

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

Verrous exclusifs

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

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

Verrous intentionnels

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

Les verrous intentionnels ont deux fonctions :

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

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

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

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

Verrous de schéma

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

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

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

Verrous de mise à jour en bloc (BU)

Les verrous BU permettent à plusieurs threads de charger simultanément en masse des données dans la même table tout en empêchant les processus qui n'effectuent pas de chargement de données en masse d'accéder à cette table. Le moteur de base de données SQL Server utilise des verrous BU lorsque les deux conditions suivantes sont vraies.

  • Vous utilisez l'instruction Transact-SQL BULK INSERT, ou la fonction OPENROWSET(BULK), ou vous utilisez une des commandes d'API Bulk Insert, telles que .NET SqlBulkCopy, les API OLEDB Fast Load, ou les API ODBC Bulk Copy pour copier en bloc des données dans une table.
  • L’indicateur TABLOCK est spécifié 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’indicateur TABLOCK maintient un verrou d’exclusion intentionnelle (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 d'étendues de clés protègent une plage de lignes implicitement incluses dans un jeu d'enregistrements lu par une instruction Transact-SQL lors de l'utilisation du niveau d'isolement des transactions sérialisable. Le verrouillage d'étendues de clés empêche les lectures fantômes. Les verrous d'étendues de clés couvrent des enregistrements individuels et les étendues entre les enregistrements, empêchant les insertions ou les suppressions fantômes dans un ensemble d'enregistrements auquel accède une transaction.

Compatibilité de verrouillage

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

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

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

Remarque

Un verrou intent exclusif (IX) est compatible avec un mode de verrouillage IX car IX signifie l'intention de mettre à jour uniquement certaines lignes et non pas toutes les lignes. 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. De plus, si deux transactions essaient de mettre à jour la même ligne, un verrou IX sera accordé aux deux transactions au niveau de la table et de la page. Toutefois, un verrou X sera accordé à une transaction 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 SQL Server.

A table showing a matrix of lock conflicts and compatibility.

Verrouillage d’étendues de clés

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

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 sérialisable peut émettre une instruction SELECT qui lit toutes les lignes dont les valeurs de clés remplissent la condition BETWEEN 'AAA' AND 'CZZ'. Un verrou de groupes de clés sur les valeurs de clés comprises entre 'AAA' et 'CZZ' empêche les autres transactions d’insérer des lignes possédant des valeurs de clés comprises dans ce groupe, telles que 'ADG', 'BBD' ou 'CAL'.

Modes de verrouillage d'étendues de clés

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

  • L'étendue représente le mode de verrouillage protégeant l'étendue entre deux entrées d'index successives.
  • La ligne représente le mode de verrouillage protégeant l'entrée de l'index.
  • Le mode représente la combinaison de modes de verrouillage utilisée. Les modes de verrouillage d'étendues de clés comportent deux parties. La première représente le type de verrou utilisé pour verrouiller l’étendue d’index (RangeT) et la deuxième représente le type de verrou utilisé pour verrouiller une clé spécifique (K). Les deux parties sont reliées par un tiret (-), par exemple RangeT-K.
Plage Ligne Mode Description
RangeS S RangeS-S Verrou de ressource partagé, étendue partagée ; analyse de plage sérialisable.
RangeS U RangeS-U Verrou de mise à jour de ressource, étendue partagée ; analyse d'étendue sérialisable.
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.

Remarque

Le mode interne de verrouillage Null 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 requis
Partagé (S) Oui Oui No Oui Oui Oui Non
Mise à jour (U) Oui No Non Oui No Oui Non
Exclusif (X) Non Non Non Non Non Oui Non
RangeS-S Oui Oui No Oui Oui No Non
RangeS-U Oui No Non Oui No Non Non
RangeI-N Oui Oui Oui No Non Oui 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'isolement de la transaction doit être défini sur SERIALIZABLE.
  • Le processeur de requêtes doit utiliser un index pour implémenter le prédicat de filtre de l'étendue. Par exemple, la clause WHERE dans une instruction SELECT peut établir une condition d'étendue avec le prédicat suivant : ColumnX BETWEEN N**'AAA'** AND N**'CZZ'**. Un verrou de groupes de clés ne peut être acquis que si ColumnX est couvert par une clé d’index.

Exemples

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

A diagram of a sample of a 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';

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

Remarque

Le nombre de verrous RangeS-S maintenus est n+1, où n est le nombre de lignes répondant aux critères de la requête.

Extraction d’un singleton de données non existantes

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

SELECT name
FROM mytable
WHERE name = 'Bill';

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

Opération de suppression sans verrouillage optimisé

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

DELETE mytable
WHERE name = 'Bob';

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

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

Opération de suppression avec verrouillage optimisé

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

DELETE mytable
WHERE name = 'Bob';

Un verrou TID est placé sur toutes les lignes modifiées pendant la durée de la transaction. Un verrou est acquis sur le TID des entrées d'index correspondant au nom Bob. Avec le verrouillage optimisé, les verrous de page et de ligne continuent d'être acquis pour les mises à jour, mais chaque page et verrou de ligne est libéré dès que chaque ligne est mise à jour. Le verrou TID protège les lignes d'être mises à jour jusqu'à la fin de la transaction. Toute transaction tentant de lire, insérer ou supprimer la valeur Bob sera bloquée jusqu'à ce que la transaction effectuant la suppression soit validée ou restaurée. (L'option de base de données READ_COMMITTED_SNAPSHOT et le niveau d'isolation SNAPSHOT autorisent également les lectures à partir d'une version de ligne de l'état précédemment validé.)

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

Opération d'insertion sans verrouillage optimisé

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

INSERT mytable VALUES ('Dan');

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

Opération d'insertion avec verrouillage optimisé

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

INSERT mytable VALUES ('Dan');

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

Escalade de verrous

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

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

Escalade de verrous sans verrouillage optimisé

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

  • Lors du verrouillage de lignes ou de plages de clés d'index, le moteur de base de données place un verrou intentionnel sur les pages qui contiennent les lignes ou les clés.
  • Lors du verrouillage de pages, le moteur de base de données place un verrou intentionnel sur les objets de niveau supérieur qui contiennent ces pages. En plus du verrou d'intention sur l'objet, les verrous de page d'intention sont demandés sur les objets suivants :
    • Pages de niveau feuille d'index non-cluster
    • Pages de données d'index cluster
    • Pages de données de segment de mémoire

Le moteur de base de données peut verrouiller à la fois les lignes et les pages pour la même instruction afin de minimiser le nombre de verrous et de réduire la probabilité que l'escalade des verrous soit nécessaire. Par exemple, le moteur de base de données peut placer des verrous de page sur un index non cluster (si suffisamment de clés contiguës dans le nœud d’index sont sélectionnées pour satisfaire à la requête) et des verrous de ligne sur les données.

Pour escalader des verrous, le moteur de base de données essaie de remplacer le verrou intentionnel sur la table par le verrou complet correspondant, par exemple, en substituant un verrou exclusif (X) à un verrou d'exclusion intentionnelle (IX) ou un verrou partagé (S) à un verrou de partage intentionnel (IS). Si la tentative d'escalade de verrous réussit et que le verrou de table complet est acquis, tous les verrous tas ou B-tree, page (PAGE) ou RID (row-level) détenus par la transaction sur le tas ou l'index sont libérés. Si le verrou complet ne peut pas être acquis, aucune escalade de verrous ne se produit à ce stade et le moteur de base de données continue d’acquérir des verrous de ligne, de clé ou de page.

Le moteur de base de données n'escalade pas les verrous de ligne ou de plage de clés en verrous de page, mais il les escalade directement en verrous de table. De même, les verrous de page sont toujours réaffectés aux verrous de table. Le verrouillage des tables partitionnées peut être escaladé au niveau HoBT de la partition associée plutôt qu’au verrou de la table. Un verrou de niveau HoBT ne verrouille pas nécessairement les HoBT alignés pour la partition.

Remarque

Les verrous de niveau HoBT augmentent généralement la concurrence, mais introduisent le risque d'interblocages lorsque les transactions verrouillant des partitions différentes veulent étendre leurs verrous exclusifs aux autres partitions. Dans de rares cas, la granularité du verrouillage TABLE peut s’avérer plus performante.

Si une tentative d'escalade de verrous échoue en raison de verrous en conflit détenus par des transactions simultanées, le moteur de base de données renouvelle la tentative d'escalade de verrous chaque fois que la quantité de verrous supplémentaires acquis par la transaction atteint 1 250.

Chaque événement d'escalade fonctionne essentiellement au niveau d'une instruction Transact-SQL unique. Lorsque l'événement démarre, le moteur de base de données essaie d'escalader tous les verrous détenus par la transaction en cours dans toutes les tables ayant été référencées par l'instruction active, sous réserve que celle-ci satisfasse aux contraintes de seuil d'escalade. Si l'événement d'escalade démarre avant que l'instruction n'ait accédé à une table, aucune tentative n'est effectuée pour élever les verrous de cette table. Si l'escalade de verrous réussit, les verrous acquis par la transaction dans une instruction précédente et conservés au moment où l'événement démarre seront réaffectés si la table est référencée par l'instruction active et est incluse dans l'événement d'escalade.

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

  • Commence une transaction.
  • Met à jour TableA. Cela génère des verrous de ligne exclusifs dans TableA qui sont conservés jusqu'à la fin de la transaction.
  • Met à jour TableB. Cela génère des verrous de ligne exclusifs dans TableB qui sont conservés jusqu'à la fin de la transaction.
  • Exécute une instruction SELECT qui joint TableA à TableC. Le plan d’exécution de requête demande à ce que les lignes soient extraites de TableA avant d’être extraites de TableC.
  • L’instruction SELECT déclenche une escalade de verrous pendant qu’elle extrait les lignes de TableA et avant d’accéder à TableC.

Si l’escalade de verrous réussit, seuls les verrous détenus par la session sur TableA sont escaladés. Cela inclut les verrous partagés de l'instruction SELECT et les verrous exclusifs de l'instruction UPDATE précédente. Alors que seuls les verrous acquis par la session dans TableA pour l’instruction SELECT sont décomptés pour déterminer si l’escalade de verrous doit être réalisée, une fois que celle-ci a réussi, tous les verrous détenus par la session dans TableA sont escaladés en verrou exclusif sur la table et tous les autres verrous de granularité inférieure de TableA, y compris les verrous intentionnels, sont libérés.

Aucune tentative n’est réalisée pour escalader les verrous de TableB car l’instruction SELECT ne comportait aucune référence active à TableB. De même, aucune tentative n’est réalisée pour escalader les verrous de TableC car l’instruction n’avait toujours pas accédé à cette table au moment de l’escalade.

Escalade de verrous avec verrouillage optimisé

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

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

Seuils d’escalade de verrous

L'escalade de verrous est déclenchée lorsqu'elle n'est pas désactivée sur la table à l'aide de l'option ALTER TABLE SET LOCK_ESCALATION et lorsque l'une ou l'autre des conditions suivantes existe :

  • Une instruction Transact-SQL unique acquiert au moins 5 000 verrous sur une table ou un index unique et non partitionné.
  • Une instruction Transact-SQL unique acquiert au moins 5 000 verrous sur une partition unique d'une table partitionnée et l'option ALTER TABLE SET LOCK_ESCALATION a la valeur AUTO.
  • Le nombre de verrous dans une instance du moteur de base de données dépasse les seuils de mémoire ou de configuration.

Si des verrous ne peuvent pas être escaladés en raison de verrous en conflit, le moteur de base de données déclenche régulièrement une escalade de verrous chaque fois que le nombre de nouveaux verrous acquis atteint 1 250.

Seuil d’escalade pour une instruction Transact-SQL

Quand le moteur de base de données vérifie les escalades possibles chaque fois que 1 250 verrous ont été acquis, une escalade de verrous se produit si et seulement si une instruction Transact-SQL a acquis au moins 5 000 verrous sur une référence unique d'une table. L'escalade de verrous est déclenchée quand une instruction Transact-SQL acquiert au moins 5 000 verrous sur une seule référence d'une table. Par exemple, l'escalade de verrous n'est pas déclenchée si une instruction acquiert 3 000 verrous dans un index et 3 000 verrous dans un autre index de la même table. De même, l'escalade de verrous n'est pas déclenchée si une instruction a une jointure automatique sur une table, et chaque référence à la table n'acquiert que 3 000 verrous dans la table.

L'escalade de verrous se produit uniquement pour les tables auxquelles l'accès a été effectué au moment où l'escalade est déclenchée. Supposons une instruction SELECT unique représentant une jointure qui accède successivement à trois tables : TableA, TableB et TableC. L'instruction acquiert 3 000 verrous de ligne dans l'index cluster de TableA et au moins 5 000 verrous de ligne dans l'index cluster de TableB, mais n'a toujours pas accédé à TableC. Lorsque le moteur de base de données détecte que l'instruction a acquis au moins 5 000 verrous de ligne dans TableB, il essaie d'escalader tous les verrous détenus par la transaction en cours dans TableB. Il essaie également d’escalader tous les verrous détenus par la transaction en cours dans TableA mais, dans la mesure où le nombre de verrous dans TableA est inférieur à 5 000, l’escalade échoue. Aucune tentative d’escalade de verrous n’est réalisée pour TableC car l’instruction n’y avait toujours pas accédé lorsque l’escalade s’est produite.

Seuil d’escalade pour une instance du moteur de base de données

Chaque fois que le nombre de verrous est supérieur au seuil de mémoire pour l'escalade de verrous, le moteur de base de données déclenche l'escalade de verrous. Le seuil de mémoire dépend du paramétrage de l’option de configuration locks :

  • Si l’option locks est paramétrée sur sa valeur par défaut 0, le seuil d’escalade de verrous est atteint lorsque la mémoire utilisée par les objets de verrou représente 24 % de la mémoire utilisée par le moteur de base de données, à l’exclusion de la mémoire AWE. La structure de données utilisée pour représenter un verrou a une longueur d'environ 100 octets. Ce seuil est dynamique car le moteur de base de données acquiert et libère dynamiquement la mémoire en fonction de l’importance des charges de travail.

  • Si l’option locks n’a pas pour valeur 0, le seuil d’escalade de verrous représente 40 % de la valeur de l’option (ou moins en cas d’insuffisance de mémoire).

Le moteur de base de données peut choisir pour l'escalade n'importe quelle instruction active depuis n'importe quelle session et, chaque fois que 1 250 nouveaux verrous sont acquis, il choisit des instructions pour l'escalade, sous réserve que la mémoire des verrous utilisée dans l'instance demeure au-dessus du seuil.

Escalade de types de verrous mixtes

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

Par exemple, supposons qu'une session :

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

L'instruction UPDATE acquiert les verrous suivants :

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

L'instruction SELECT acquiert les verrous suivants :

  • Verrous partagés (S) sur toutes les lignes de données qu'il lit, sauf si la ligne est déjà protégée par un verrou X de l'instruction UPDATE.
  • Verrous de partage intentionnel sur toutes les pages d'index en grappe contenant ces lignes, à moins que la page ne soit déjà protégée par un verrou IX.
  • Aucun verrou sur la table ou l'index cluster, car ils sont déjà protégés par des verrous IX.

Si l'instruction SELECT acquiert suffisamment de verrous pour déclencher l'escalade des verrous et que l'escalade réussit, le verrou IX de la table est converti en verrou X, et toutes les lignes, pages et verrous d'index sont libérés. Les mises à jour et les lectures sont protégées par le verrou X sur la table.

Réduction du verrouillage et de l'escalade

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

  • Bénéficiez du verrouillage optimisé.

    • Le verrouillage optimisé offre un mécanisme de verrouillage amélioré réduisant la consommation de mémoire de verrouillage et le blocage au cours des transactions simultanées. L'escalade de verrous est beaucoup moins susceptible de se produire lorsque le verrouillage optimisé est activé.
    • Évitez d'utiliser des indicateurs de table avec verrouillage optimisé. Les indicateurs de table peuvent réduire l'efficacité du verrouillage optimisé.
    • Activez READ_COMMITTED_SNAPSHOT dans la base de données pour tirer le meilleur parti du verrouillage optimisé. C'est le niveau d'isolement par défaut dans Azure SQL Database.
    • Le verrouillage optimisé nécessite l'activation de la récupération de base de données accélérée (ADR) sur la base de données.

Si une instance du moteur de base de données génère beaucoup de verrous et donne lieu à de fréquentes escalades de verrous, pensez à réduire la quantité de verrouillage à l'aide :

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

    • Le niveau d'isolation READ COMMITTED avec l'option de base de données READ_COMMITTED_SNAPSHOT activée (ON).

    • Niveau d'isolement SNAPSHOT.

    • Niveau d'isolation UNCOMMITTED READ. Cela ne peut être utilisé que pour les systèmes qui peuvent fonctionner avec la lecture erronée.

      Remarque

      Le changement du niveau d'isolement affecte toutes les tables sur l'instance du moteur de base de données.

  • Utilise des indicateurs de table PAGLOCK ou TABLOCK afin que le moteur de base de données utilise des verrous de page, d'index ou de segment au lieu de verrous de ligne. Toutefois, l'utilisation de cette option augmente les problèmes des utilisateurs bloquant d'autres utilisateurs qui tentent d'accéder aux mêmes données et ne doivent pas être utilisés dans les systèmes avec plus de quelques utilisateurs simultanés.

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

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

    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'

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

    SET ROWCOUNT 500
delete_more:
  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

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

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

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

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

    Cette requête acquiert et conserve un verrou IX sur mytable pendant une heure, ce qui empêche l’escalade de verrous sur la table pendant cette période. Ce programme ne modifie pas les données et ne bloque pas les autres requêtes (sauf si l'autre requête force un verrou de table avec l'indicateur TABLOCK ou si un administrateur a désactivé des verrous de page ou de ligne à l'aide d'une procédure stockée sp_indexoption).

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

    Remarque

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

    Remarque

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

Surveiller l'escalade des verrous

Surveillez l'escalade de verrous en utilisant l'événement étendu (xEvent) 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

Important

L'événement étendu (xEvent) lock_escalation doit être utilisé à la place de la classe d'événements Lock:Escalation dans Trace SQL ou SQL Profiler.

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.

A graph of locking cost vs. concurrency cost.

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

Le verrouillage dynamique offre les avantages suivants :

  • Administration de bases de données simplifiée. Les administrateurs de la base de données ne doivent pas ajuster les seuils d'escalade des verrous.
  • Performances accrues. Le moteur de base de données SQL Server réduit la charge sur le système en utilisant les verrous appropriés pour la tâche.
  • Les développeurs d'applications peuvent se concentrer sur le développement. Le moteur de base de données SQL Server ajuste automatiquement le verrouillage.

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

Partitionnement de verrous

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

Comprendre le partitionnement de verrous

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

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

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

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

  • Mémoire. Permet de stocker les structures des ressources de verrous.

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

Implémenter et surveiller le partitionnement de verrous

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

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

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

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

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

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

Utilisation du partitionnement de verrous

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

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

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

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

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

Exemple A

Session 1 :

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

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

Session 2 :

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

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCK, HOLDLOCK);

Session 1 :

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

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Exemple B

Session 1 :

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

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

Session 2 :

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

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

BEGIN TRANSACTION
    SELECT col1
    FROM TestTable
    WITH (TABLOCKX, HOLDLOCK);

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

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

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

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

La base de données tempdb doit avoir suffisamment d’espace pour contenir la banque des versions. Quand la base de données tempdb est pleine, les opérations de mise à jour ne génèrent plus de versions et continuent d’aboutir, mais les opérations de lecture risquent d’échouer en raison de l’absence d’une version de ligne particulière requise. Ceci a des conséquences sur les opérations comme les déclencheurs, MARS et l'indexation en ligne.

L'utilisation du contrôle de version de ligne pour les transactions read committed et les transactions d'instantanés se fait en deux étapes :

  1. Activez (valeur ON) l’option de base de données READ_COMMITTED_SNAPSHOT et/ou l’option ALLOW_SNAPSHOT_ISOLATION.

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

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

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

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

Remarque

Dans le cas de la modification d’objets LOB, seul le fragment modifié est copié dans la banque des versions dans tempdb.

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 SQL Server recherche le plus ancien numéro de séquence de transaction et supprime de façon périodique toutes les versions de lignes dont le numéro de séquence de transaction est inférieur à celui-ci.

Lorsque les deux options de base de données sont désactivées, seules les lignes modifiées par des déclencheurs ou des sessions MARS, ou lues par des opérations d'index ONLINE, sont avec version. Ces versions de lignes sont libérées lorsqu'elles ne sont plus nécessaires. Un thread d'arrière-plan supprime périodiquement les versions de lignes dépassées.

Remarque

Pour les transactions de courte durée, il arrive qu’une version d’une ligne modifiée soit mise en cache dans le pool de mémoires tampons sans être écrite dans les fichiers de la base de données tempdb sur le disque. Si cette ligne avec version n'est plus nécessaire, elle est simplement supprimée du pool de mémoires tampons, ce qui lui évite de générer du trafic E/S.

Comportement lors de la lecture de données

Lorsque des transactions s'exécutant sous le régime d'isolement « contrôle de version de ligne », les opérations de lecture n'acquièrent pas de verrous partagés sur les données lues, et par conséquent ne bloquent pas les transactions qui modifient des données. De plus, la charge liée au verrouillage des ressources est minimisée en raison de la réduction du nombre de verrous acquis. L'isolement read committed avec contrôle de version de ligne et l'isolement d'instantané sont conçus pour garantir la cohérence des données avec version au niveau de l'instruction ou de la transaction.

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

Lorsqu'une transaction avec niveau d'isolement d'instantané est lancée, l'instance de moteur de base de données SQL Server enregistre toutes les transactions en cours. Lorsque la transaction lit une ligne qui a une chaîne de versions, le moteur de base de données SQL Server remonte la chaîne et récupère la ligne dont 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 d'instantané récupèrent la dernière version de chaque ligne validée au début de la transaction. 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 » avec contrôle de version de ligne fonctionnement pratiquement de la même manière. La différence est que ces transactions n'utilisent pas leurs propres numéros de séquence de transaction lors du choix des versions de lignes. Chaque fois qu'une instruction est lancée, la transaction lit le dernier numéro de séquence de transaction émis pour cette instance du moteur de base de données SQL Server. C'est ce numéro qui servira à sélectionner les bonnes versions de lignes pour cette instruction. Ceci permet aux transactions read committed de voir un instantané des données telles qu'elles existaient au début de chaque instruction.

Remarque

Même si les transactions read commited utilisant le contrôle de version de ligne fournissent une vue cohérente d'un point de vue transactionnel des données au niveau d'une instruction, les versions de ligne générées ou accédées par ce type de transaction sont conservées jusqu'à la fin de la transaction.

Comportement lors de la modification de données

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

Modification des données sans verrouillage optimisé

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

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

Remarque

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

une table avec contrainte FOREIGN KEY ;

une table à laquelle la contrainte FOREIGN KEY d'une autre table fait référence ;

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 c'est le cas, il y a conflit de mise à jour et la transaction est arrêtée. Les conflits de mise à jour doivent être gérés et retentés manuellement par l'application.

Modification des données avec verrouillage optimisé

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

L'activation du RCSI est recommandée pour une efficacité maximale avec un verrouillage optimisé. Lorsque vous utilisez des niveaux d'isolation plus stricts comme la lecture reproductible ou sérialisable, le moteur de base de données est forcé de conserver les verrous de ligne et de page jusqu'à la fin de la transaction, pour les lecteurs et les enregistreurs, ce qui entraîne un blocage accru et une mémoire de verrou.

Lorsque le RCSI est activé et que l'on utilise le niveau d'isolation READ COMMITTED par défaut, les enregistreurs qualifient les lignes par prédicat en fonction de la dernière version validée de la ligne, sans acquérir de verrous U. Une requête n'attend que si la ligne remplit les conditions requises et qu'il existe une transaction d'écriture active sur cette ligne ou cette page. La qualification sur la base de la dernière version validée et le verrouillage des seules lignes qualifiées réduisent les blocages et augmentent la concurrence.

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

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

Remarque

Pour plus d'informations sur les modifications de comportement avec la fonctionnalité de qualifiation (LAQ) de verrouillage optimisé, consultez Modifications du comportement de requête avec verrouillage optimisé et RCSI.

Synthèse des comportements

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

Propriété Niveau d'isolement READ COMMITED utilisant le contrôle de version de ligne Niveau d'isolement d'instantané
L'option de base de données doit être activée (ON) pour assurer la prise en charge nécessaire. READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION
Manière dont une session demande le type spécifique de contrôle de version de ligne. Utilisez le niveau d'isolement par défaut (read-committed) ou exécutez l'instruction SET TRANSACTION ISOLATION LEVEL pour spécifier le niveau d'isolement READ COMMITTED. Ceci peut se faire après le début de la transaction. Requiert l'exécution de l'instruction SET TRANSACTION ISOLATION LEVEL pour spécifier le niveau d'isolement SNAPSHOT avant le début de la transaction.
La version des données lue par les instructions. Toutes les données qui ont été validées avant le début de chaque instruction. Toutes les données qui ont été validées avant le début de chaque transaction.
Manière dont les mises à jour sont gérées. Sans verrouillage optimisé : revient des versions de lignes aux données réelles pour sélectionner les lignes à mettre à jour et utilise des verrous de mise à jour sur les lignes de données sélectionnées. Acquiert des verrous exclusifs sur les lignes à modifier réellement. Pas de détection de conflit de mise à jour.

Avec verrouillage optimisé : les lignes sont sélectionnées en fonction de la dernière version validée sans verrous acquis. Si les lignes sont éligibles pour la mise à jour, les verrous de ligne ou de page exclusifs sont acquis. Si des conflits de mise à jour sont détectés, ils sont gérés et retentés automatiquement.		 Utilise les versions de lignes pour sélectionner les lignes à mettre à jour. Essaie d'acquérir un verrou exclusif sur les lignes à modifier réellement et, si les données ont été modifiées par une autre transaction, génère un conflit de mise à jour qui entraîne l'arrêt de la transaction.
Détection d'un conflit de mise à jour Sans verrouillage optimisé : aucun.

Avec le verrouillage optimisé : si des conflits de mise à jour sont détectés, ils sont gérés et retentés automatiquement.		 Prise en charge intégrée. Ne peut être désactivée.

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

L'infrastructure de contrôle de version de ligne prend en charge les fonctionnalités suivantes dans SQL Server :

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

La structure de contrôle de version de ligne prend également en charge les niveaux d’isolement des transactions basé sur le contrôle de version de ligne, qui sont désactivés par défaut :

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

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

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

Espace occupé dans tempdb

Pour toute instance du moteur de base de données SQL Server, tempdb doit disposer d'un espace suffisant pour conserver les versions de ligne générées pour chaque base de données dans l'instance. L'administrateur de base de données doit s'assurer que tempdb dispose de suffisamment d'espace pour la prise en charge du magasin de versions. tempdb contient deux magasins de versions :

  • la banque des versions de construction d'index en ligne, utilisée pour les constructions d'index en ligne dans l'ensemble des bases de données ;
  • la banque des versions commune, utilisée pour toutes les autres opérations de modification des données dans l'ensemble des bases de données.

Les versions de ligne doivent être stockées pour toute la durée au cours de laquelle une transaction active doit être accessible. Une fois par minute, un thread d'arrière-plan supprime les versions de ligne qui ne sont plus nécessaires et libère de l'espace dans tempdb. 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.

Remarque

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. Ce point s'applique aussi aux transactions à lecture validée qui utilisent le contrôle de version 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.

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

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

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

Espace occupé dans les lignes de données

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

  • l’option READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION est activée (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.

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 :

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

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

La réduction du facteur de remplissage peut permettre d’empêcher ou de réduire la fragmentation des pages d’index. Pour afficher les informations de fragmentation des données et des index de la table ou vue spécifiée, vous pouvez utiliser sys.dm_db_index_physical_stats.

Espace occupé dans les objets volumineux

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

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

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

Les types de données nvarchar(max), varchar(max) et varbinary(max) ne sont pas disponibles dans les versions antérieures de SQL Server. Vous ne rencontrerez pas conséquent aucun problème de mise à niveau.

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

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

Pour surveiller les performances et les problèmes des processus de contrôle de version de ligne, de magasin de versions et d'isolation d'instantanés, SQL Server fournit des outils sous la forme de vues DMV (Dynamic Management Views) et de compteurs de performances dans le Moniteur système de Windows.

DMV

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

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

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

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

  • sys.dm_tran_top_version_generators. Renvoie une table virtuelle pour les objets générant la majorité des versions d'un magasin de versions. Agrégation des 256 premières longueurs d'enregistrement selon database_id et rowset_id. Utilisez cette fonction pour rechercher les clients les plus volumineux de la banque de versions. 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. Pour plus d'informations, consultez sys.dm_tran_version_store (Transact-SQL).

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

    Remarque

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

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

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

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

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

Compteurs de performance

Les compteurs de performance de SQL Server fournissent des informations sur les performances système affectées par les processus de SQL Server. Les compteurs de performances suivants contrôlent tempdb et le magasin de versions, ainsi que les transactions utilisant le contrôle de version de ligne. Les compteurs de performances se trouvent dans l'objet de performances SQLServer:Transactions.

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

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

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

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

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

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

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

    Remarque

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

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

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

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

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

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

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

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

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

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

    Remarque

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

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

Les exemples ci-dessous illustrent les différences de comportement entre les transactions d'isolement d'instantané et les transactions validées en écriture qui utilisent le contrôle de version de ligne.

A. Utilisation du niveau d'isolation d'instantané

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

Sur la session 1 :

USE AdventureWorks2022;
GO

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

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

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

Sur la session 2 :

USE AdventureWorks2022;
GO

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

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

Sur la session 1 :

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

Sur la session 2 :

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

Sur la session 1 :

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

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

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

B. Utilisation d'une transaction read-committed à l'aide du contrôle de version de ligne

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

  • lit les données modifiées une fois que l'autre transaction a validé les modifications de données ;
  • peut mettre à jour les données modifiées par l'autre transaction, alors que cette opération n'est pas possible avec la transaction d'instantané.

Sur la session 1 :

USE AdventureWorks2022;  -- Or any earlier version of the AdventureWorks database.
GO

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

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

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

Sur la session 2 :

USE AdventureWorks2022;
GO

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

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

Sur la session 1 :

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

Sur la session 2 :

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Sur la session 1 :

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

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

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

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

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

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

L'instruction Transact-SQL suivante active READ_COMMITTED_SNAPSHOT :

ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

Quand l'option de base de données ALLOW_SNAPSHOT_ISOLATION est activée (ON), l'instance du moteur de base de données SQL Server ne génère le contrôle de version de ligne pour les données modifiées que quand l'exécution de toutes les transactions actives modifiant les données de la base de données est terminée. Si des transactions de modification sont actives, SQL Server définit l'état de l'option sur PENDING_ON. Une fois l'exécution des transactions de modification terminées, l'état de l'option passe sur ON. Les utilisateurs ne peuvent lancer une transaction d'instantané que quand l'option a la valeur ON. La base de données passe par l’état PENDING_OFF quand son administrateur affecte à l’option ALLOW_SNAPSHOT_ISOLATION la valeur OFF.

L'instruction Transact-SQL suivante permet la prise en charge de l'option ALLOW_SNAPSHOT_ISOLATION :

ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

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

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

Aucune transaction d'isolement d'instantané n'est autorisée.
ACTIVÉ La prise en charge des transactions d'isolement d'instantané est activée.

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

Les transactions d'instantané lancées dès ce moment ne peuvent accéder à la base de données. Les transactions de mise à jour assument la responsabilité du versioning dans la base de données. Les transactions d'instantané existantes peuvent toujours accéder à la base de données sans aucun problème. L'état PENDING_OFF n'est désactivé (OFF) que lorsque toutes les transactions d'instantané qui étaient actives lorsque l'état d'isolation de l'instantané de la base de données était activé (ON) se terminent.

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

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

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

Les utilisateurs ne peuvent pas activer (ON) l'option READ_COMMITTED_SNAPSHOT dans master, tempdb ou msdb.

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

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

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 :

  • READCOMMITTED (lu-validé) avec utilisation du contrôle de version de ligne par l'activation de l'option de base de données READ_COMMITTED_SNAPSHOT (valeur ON) comme illustré dans l'exemple de code suivant :

    ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

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

  • Niveau d'isolement d'instantané en définissant l'option de base de données ALLOW_SNAPSHOT_ISOLATION avec la valeur ON comme illustré dans l'exemple de code suivant :

    ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

    Une transaction s'exécutant sous le niveau d'isolement d'instantané (SNAPSHOT) peut accéder aux tables de la base de données qui ont été activées pour les instantanés. Pour accéder aux tables qui n'ont pas été activées pour les instantanés, le niveau d'isolement doit être modifié. Ainsi, dans l'exemple de code suivant, une instruction SELECT exécutée dans le cadre d'une transaction d'instantané joint deux tables. Une table appartient à une base de données dans laquelle le niveau d'isolation d'instantané n'est pas activé. Lorsque l'instruction SELECT s'exécute sous le niveau d'isolement d'instantané, elle échoue.

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

    Dans l'exemple de code suivant, la même instruction SELECT a été modifiée pour faire passer le niveau d'isolation de la transaction à READCOMMITTED (lu-validé). Grâce à cette modification, l'exécution de l'instruction SELECT aboutit.

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

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

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

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

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

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

  • Les transactions d'instantané échouent dans les cas suivants :

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

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

  • SQL Server ne conserve pas plusieurs versions des métadonnées système. Les instructions DDL (Data Definition Language) portant sur des tables et autres objets de base de données (index, vues, types de données, procédures stockées et fonctions CLR (Common Language Runtime)) modifient les métadonnées. Si une instruction DDL modifie un objet, toute référence simultanée à l'objet sous le niveau d'isolement d'instantané entraînera l'échec de la transaction d'instantané. Les transactions READCOMMITTED (lu-validé) ne présentent pas cette limitation lorsque l'option de base de données READ_COMMITTED_SNAPSHOT est activée (valeur 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 READCOMMITTED (lu-validé) utilisant le contrôle de version de ligne ne sont pas affectées.

    Remarque

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

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

Personnalisation du délai d'expiration des verrous

Quand une instance du moteur de base de données Microsoft SQL Server ne peut pas accorder un verrou à une transaction, car une autre transaction a déjà un verrou en conflit sur la ressource, la première transaction se bloque, dans l'attente de la libération du verrou existant. Par défaut, il n'existe pas de délai d'expiration obligatoire et aucun moyen de tester si une ressource est déjà verrouillée avant de la verrouiller, excepté par une tentative d'accès aux données (avec un risque de blocage infini).

Remarque

Dans SQL Server, utilisez la vue de gestion dynamique sys.dm_os_waiting_tasks pour déterminer si un processus est bloqué et identifier l'auteur du blocage. Dans les versions antérieures de SQL Server, utilisez la procédure stockée système sp_who. Pour plus d'informations et d'exemples, consultez Compréhension et résolution des problèmes bloquants dans 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. Si l'attente d'une instruction dépasse la valeur du paramètre LOCK_TIMEOUT, l'instruction bloquée est automatiquement annulée, et le message d'erreur 1222 (Lock request time-out period exceeded) retourné à l'application. Cependant, toute transaction contenant l'instruction n'est pas restaurée ou annulée par SQL Server. L'application doit donc posséder un gestionnaire d'erreurs capable d'intercepter le message d'erreur 1222. Si une application ne gère pas cette erreur, elle peut continuer en ignorant que l'une des instructions de la transaction a été annulée. Des erreurs peuvent se produire lorsque les instructions ultérieures dans la transaction dépendent de l'instruction qui n'a jamais été exécutée.

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

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

SELECT @@lock_timeout;
GO

Personnalisation du niveau d'isolation de la transaction

READ COMMITTED est le niveau d'isolation par défaut pour le moteur de base de données Microsoft SQL Server. 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 managé System.Data.SqlClient peuvent spécifier une option IsolationLevel à l'aide de la méthode SqlConnection.BeginTransaction.
  • Les applications qui utilisent ADO peuvent définir la propriété Autocommit Isolation Levels.
  • Lors du lancement d'une transaction, les applications utilisant OLE DB peuvent appeler ITransactionLocal::StartTransaction avec le paramètre isoLevel défini sur le niveau d'isolation de la transaction souhaité. Lorsque vous spécifiez le niveau d'isolation en mode de validation automatique, les applications utilisant OLE DB peuvent affecter à la propriété DBPROP_SESS_AUTOCOMMITISOLEVELS de DBPROPSET_SESSION la valeur de niveau d'isolation de la transaction souhaitée.
  • Les applications qui utilisent ODBC peuvent définir l'attribut SQL_COPT_SS_TXN_ISOLATION à l'aide de SQLSetConnectAttr.

Lorsque le niveau d'isolation est spécifié, le comportement de verrouillage pour toutes les requêtes et instructions de langage de manipulation de données (DML) dans la session SQL Server 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;
GO

Le niveau d'isolation peut être remplacé si nécessaire pour des requêtes ou des instructions DML individuelles, en spécifiant un indicateur de niveau table. Un indicateur de niveau table n'affecte pas les autres instructions de la session. Il est recommandé de n'utiliser les indicateurs de niveau table pour modifier le comportement par défaut qu'en cas d'absolue nécessité.

Le moteur de base de données SQL Server peut être obligé d'acquérir des verrous lors de la lecture de métadonnées même si le niveau d'isolation est tel que les verrous partagés ne sont pas nécessaires pendant la lecture des données. Par exemple, une transaction qui s'exécute au niveau d'isolation read-uncommitted n'acquiert pas de verrous partagés pendant la lecture de données, mais elle peut en demander quelquefois lors de la lecture d'un affichage catalogue système. Autrement dit, une transaction de lecture non validée peut provoquer un blocage si elle interroge une table pendant qu'une transaction modifie simultanément les données de cette table.

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

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

(14 row(s) affected)

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

Indicateurs de verrouillage

Il est possible de spécifier des indicateurs de verrouillage pour des références de table individuelles dans les instructions SELECT, INSERT, UPDATE et DELETE. Ces indicateurs déterminent le type de verrouillage ou de contrôle de version de ligne qu'utilise l'instance du moteur de base de données SQL Server 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.

Remarque

Les indicateurs de verrouillage ne sont pas recommandés pour une utilisation lorsque le verrouillage optimisé est activé. Bien que les indicateurs de table et de requête soient respectés, ils réduisent les avantages du verrouillage optimisé. Pour plus d'informations, consultez Éviter les indicateurs de verrouillage avec le verrouillage optimisé.

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

Remarque

Le moteur de base de données SQL Server choisit presque toujours le niveau de verrouillage correct. 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. La désactivation d'un niveau de verrouillage peut affecter défavorablement la concurrence d'accès.

Il se peut que le moteur de base de données SQL Server doive obtenir des verrous lors de la lecture de métadonnées, même lors du traitement d'une sélection avec un indicateur de verrouillage qui empêche les demandes de verrous de partage lors de la lecture de données. Par exemple, une instruction SELECT qui utilise l'indicateur NOLOCK n'obtient pas de verrous partagés lors de la lecture de données, mais elle peut occasionnellement demander des verrous quand elle lit un affichage catalogue système. Cela signifie qu’il est possible qu’une instruction SELECT utilisant NOLOCK soit bloquée.

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

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

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

-- End the transaction.
ROLLBACK;
GO

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

Dans SQL Server, l'option LOCK_ESCALATION de l'instruction ALTER TABLE peut défavoriser des verrous de table et activer des verrous HoBT sur des tables partitionnées. Cette option n'est pas un indicateur de verrouillage, mais elle peut servir à réduire l'escalade de verrous. Pour plus d’informations, consultez ALTER TABLE (Transact-SQL).

Personnalisation du verrouillage pour un index

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

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

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

Parfois, un blocage se produit dans les circonstances suivantes : deux opérations simultanées acquièrent des verrous de ligne sur la même table, puis se bloquent, car elles doivent toutes les deux verrouiller la page. L'interdiction des verrous de ligne force l'une des opérations à attendre, ce qui évite le blocage. Pour plus d'informations sur les interblocages, consultez le Guide des interblocages.

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

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

Informations sur les transactions avancées

Transactions imbriquées

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

L'exemple suivant illustre cette utilisation des transactions imbriquées. La procédure TransProc applique sa transaction quel que soit le mode de transaction du processus qui l’exécute. Si TransProc est appelée alors qu'une transaction est active, la transaction imbriquée dans TransProc est en grande partie ignorée, et les instructions INSERT qu'elle contient sont validées ou restaurées en fonction de l'action finale acquise pour la transaction externe. Si TransProc est exécutée par un processus pour lequel aucune transaction n'est en cours, l'instruction COMMIT TRANSACTION qui se trouve à la fin de la procédure déclenche la validation effective des instructions INSERT.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO

La validation des transactions internes est ignorée par le moteur de base de données SQL Server. La transaction est validée ou restaurée en fonction de l'action prise à 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 d'une transaction imbriquée fait référence à la transaction externe, seule la transaction la plus intérieure est validée.

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

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

Utilisation de sessions associées

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

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

Remarque

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 fonction d'API qui permette à une application d'obtenir le jeton d'association pour une transaction démarrée par un autre processus. Les méthodes suivantes peuvent être utilisées pour transmettre un jeton d'association :

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

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

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

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

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

Types de sessions associées

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

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

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

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

Quand utiliser des sessions associées

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

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

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

Transactions efficaces en termes de code

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

Recommandations de code

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

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

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

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

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

  • Utilisez avec discernement les niveaux d'isolement de transaction inférieurs. De nombreuses applications peuvent être facilement codées pour utiliser le niveau d'isolement de lecture validée. Toutes les transactions ne nécessitent pas l'utilisation d'un niveau d'isolement de transaction sérialisable.

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

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

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

  • Évitez autant que possible d’utiliser des transactions implicites. Ces dernières peuvent introduire un comportement imprévisible en raison de leur nature. Consultez Transactions implicites et problèmes de concurrence.

  • Concevez des index avec un facteur de remplissage réduit. La réduction du facteur de remplissage peut vous aider à empêcher ou à réduire la fragmentation des pages d’index et donc à réduire les temps de recherche d’index, en particulier lors de leur récupération sur le disque. Pour afficher les informations de fragmentation des données et des index de la table ou vue spécifiée, vous pouvez utiliser sys.dm_db_index_physical_stats.

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

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

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

Gestion des transactions durables

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

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

Important

Dans Azure SQL Database, les transactions inactives (transactions qui n'ont pas écrit dans le journal des transactions pendant six heures) sont automatiquement arrêtées pour 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 durable, les colonnes intéressantes incluent l'heure du premier enregistrement de journal (database_transaction_begin_time), l'état actuel de la transaction (database_transaction_state) et le numéro séquentiel dans le journal (LSN) de l'enregistrement initial dans le journal des transactions (database_transaction_begin_lsn).

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

  • DBCC OPENTRAN

    Cette instruction vous permet d'identifier l'ID du propriétaire de la transaction et éventuellement de retrouver la source de la transaction pour y mettre fin dans les règles de l'art (la valider au lieu de la restaurer). Pour plus d'informations, consultez DBCC OPENTRAN (Transact-SQL).

Arrêter une transaction

Vous devrez peut-être utiliser 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 connexe