Guide du verrouillage des transactions et du contrôle de version de ligne SQL Server

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.

S'applique à : SQL Server 2005 jusqu'à SQL Server 2012, sauf indication contraire.

Dans ce guide

Transaction Basics

Locking and Row Versioning Basics

Locking in the Database Engine

Row Versioning-based Isolation Levels in the Database Engine

Customizing Locking and Row Versioning

Advanced Transaction Information

Principes de base sur les transactions

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

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

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

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

• Durabilité
  Lorsqu'une transaction 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.

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 de Moteur de base de données, se doit de fournir des mécanismes permettant de garantir l'intégrité physique de chaque transaction. Le Moteur de base de données fournit les éléments suivants :

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

• Des fonctionnalités de consignation assurant la durabilité des transactions. En cas de défaillance du matériel serveur, du système d'exploitation ou d'une instance du Moteur de base de données lui-même, l'instance utilise au redémarrage les journaux des transactions pour restaurer automatiquement toutes les transactions incomplètes jusqu'au moment de la défaillance du système.

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

Contrôle des transactions

Le contrôle des transactions par les applications consiste principalement à spécifier des points de début et de fin de chaque transaction. La spécification est effectuée à l'aide des instructions Transact-SQL ou des fonctions d'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 Instructions de transaction (Transact-SQL), Transactions dans ODBC et Transactions dans SQL Server Native Client (OLEDB).

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 Multiple Active Result Sets (MARS) dans SQL Server.

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

  ALTER DATABASE	CREATE DATABASE	DROP FULLTEXT INDEX
  ALTER FULLTEXT CATALOG	CREATE FULLTEXT CATALOG	RECONFIGURE
  ALTER FULLTEXT INDEX	CREATE FULLTEXT INDEX	RESTORE
  BACKUP	DROP DATABASE	Procédures stockées système de recherche en texte intégral
  CREATE DATABASE	DROP FULLTEXT CATALOG	Option 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.

  Notes

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

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

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

• Transaction dont l'étendue est définie par traitement
  Uniquement applicable aux ensembles de résultats MARS (Multiple Active Result Sets), 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 MS DTC (Microsoft Distributed Transaction Coordinator), 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, 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.

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

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

Fin des transactions

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

• COMMIT
  Si une transaction est réussie, validez-la. 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.

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 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 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 d'exécution (comme une violation de contrainte) dans un traitement, la réaction par défaut du Moteur de base de données est de restaurer seulement l'instruction ayant généré l'erreur. Vous pouvez modifier ce comportement à l'aide de l'instruction SET XACT_ABORT. 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 l'option 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 TRY…CATCH dans Transact-SQL. Pour plus d'informations et d'exemples portant sur les transactions, consultez TRY...CATCH (Transact-SQL). À partir de SQL Server 2012, l'instruction THROW peut être utilisée pour lever une exception et transférer l'exécution à un bloc CATCH d'une construction TRY…CATCH. Pour plus d'informations, consultez THROW (Transact-SQL).

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

En mode de validation automatique, il peut arriver qu'une instance du Moteur de base de données semble restaurer un lot entier au lieu d'une instruction SQL unique. Ceci se produit en cas d'erreur de compilation et non en cas d'erreur d'exécution. Une erreur de compilation empêche le Moteur de base de données de créer un plan d'exécution, de telle sorte qu'aucune instruction du traitement 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 traitement. Dans l'exemple qui suit, une erreur de compilation empêche l'exécution de toutes les instructions INSERT du troisième lot. Les deux premières instructions INSERT semblent avoir été restaurées alors qu'elles n'ont en fait jamais été exécutées.

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

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

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

Le moteur de base de données introduit la résolution de nom différée, dans laquelle la résolution des noms d'objets 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

[Top]

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.

• Contrôle de version 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 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
      SET name = 'New' WHERE ID = 5;
  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

    Lorsque 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 concurrence 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 API de base de données telles que ADO, ADO.NET, OLE DB et ODBC.

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

Les transactions spécifient un niveau d'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.

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'isolement	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 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. C'est le niveau par défaut du Moteur de base de données.
Lecture renouvelable	Le Moteur de base de données conserve les verrous de lecture et d'écriture acquis sur les données sélectionnées jusqu'à la fin de la transaction. 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 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. Important Les opérations DDL et les transactions sur les tables répliquées peuvent échouer lorsque 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'isolement 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'isolement, l'instantané.

Niveau d'isolement basé sur le contrôle de version de ligne	Définition
Instantané read committed	Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT a la valeur 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 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. Si l'option de base de données READ_COMMITTED_SNAPSHOT a la valeur OFF (valeur par défaut), l'isolement 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 de l'isolation read committed de l'ISO.
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 lorsque 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. Notes 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 en isolement d'instantanée après une instruction BEGIN TRANSACTION : ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME ou toute instruction DDL CLR (Common Language Runtime).Ces instructions sont autorisées lorsque vous avez recours à l'isolation d'instantané au sein des 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.Les violations de ce principe peuvent provoquer l'erreur 3961 : « La transaction d'isolement d'instantané a échoué dans la base de données '%.*ls', car l'objet auquel l'instruction a eu accès a été modifié par une instruction DDL dans une autre transaction simultanée depuis le début de cette transaction.Cela est interdit, car les métadonnées sont dépourvues de version.Une mise à jour simultanée des métadonnées peut provoquer des incohérences si elle est associée à une isolation d'instantané. »

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

Niveau d'isolement	Lecture incorrecte	Lecture non renouvelable	Fantôme
Lecture non validée	Oui	Oui	Oui
Lecture validée	Non	Oui	Oui
Lecture renouvelable	Non	Non	Oui
Instantané	Non	Non	Non
Sérialisable	Non	Non	Non

Pour plus d'informations concernant les types de verrous spécifiques ou le contrôle de version de ligne contrôlés par chaque niveau d'isolation de 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 SET TRANSACTION ISOLATION LEVEL.

• ADO
  Les applications ADO attribuent à la propriété IsolationLevel de l'objet Connection la valeur adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead ou adXactReadSerializable.

• ADO.NET
  Les applications ADO.NET qui utilisent l'espace de noms managé System.Data.SqlClient peuvent appeler la méthode SqlConnection.BeginTransaction et attribuer à l'option IsolationLevel la valeur Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable et Snapshot.

• OLE DB
  Au début d'une transaction, les applications qui utilisent OLE DB appellent ITransactionLocal::StartTransaction avec la valeur ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT ou ISOLATIONLEVEL_SERIALIZABLE attribuée au paramètre isoLevel.

  Lorsque vous spécifiez le niveau d'isolement d'une transaction en mode « autocommit », les applications OLE DB peuvent attribuer aux niveaux DBPROP_SESS_AUTOCOMMITISOLEVELS de la propriété DBPROPSET_SESSION la valeur DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED ou DBPROPVAL_TI_SNAPSHOT.

• ODBC
  Les applications ODBC appellent SQLSetConnectAttr avec la valeur SQL_ATTR_TXN_ISOLATION pour le paramètre Attribute et la valeur SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ ou SQL_TXN_SERIALIZABLE pour le paramètre ValuePtr.

  Pour les transactions d'instantanés, les applications appellent SQLSetConnectAttr avec la valeur SQL_COPT_SS_TXN_ISOLATION pour le paramètre Attribute et la valeur SQL_TXN_SS_SNAPSHOT pour le paramètre ValuePtr. Une transaction d'instantané peut être extraite à l'aide de SQL_COPT_SS_TXN_ISOLATION ou de SQL_ATTR_TXN_ISOLATION.

[Top]

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 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. Tous les verrous conservés par une transaction sont libérés lorsque cette dernière est terminée (validée ou restaurée).

En général, les applications ne demandent pas de verrous directement. Les verrous sont gérés en interne par une partie du Moteur de base de données, nommée gestionnaire de verrous. Lorsqu'une instance du Moteur de base de données traite une instruction Transact-SQL, le processeur de requêtes du Moteur de base de données détermine les ressources qui doivent être accédées. 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 verrouille automatiquement les ressources au niveau approprié pour la tâche. Le verrouillage à un faible niveau de granularité (tel que les lignes) augmente la simultanéité d'accès, mais à un coût plus élevé, puisqu'un grand nombre de verrous doit être maintenu si de nombreuses lignes sont verrouillées. Le verrouillage à un niveau de granularité élevé (tel que les tables) est coûteux en termes de simultanéité d'accès, car le verrouillage d'une table entière empêche les autres transactions d'accéder à d'autres parties de la table. Cependant, son coût est moindre puisque les verrous sont peu nombreux.

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

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

Ressource	Description
RID	Identificateur de ligne utilisé pour verrouiller une seule ligne dans un segment de mémoire.
KEY	Verrou de ligne 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	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	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.

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.

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 que plusieurs mises à jour s'effectuent simultanément sur une même ressource.
Intent	Permet d'établir une hiérarchie de verrouillage. Les types de verrouillage intentionnels sont les suivants : partage intentionnel (IS), exclusion intentionnelle (IX) et 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 suivants : modification de schéma (Sch-S) et stabilité de 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é.
Verrou de clé	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

Les verrous de mise à jour (U) empêchent une forme fréquente de blocage. 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 (page ou ligne), 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 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é 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é, un blocage se produit.

Les verrous de mise à jour (U) permettent de résoudre les problèmes de blocage. Une seule transaction à la fois peut obtenir un verrou de mise à jour (U) pour une ressource. Si une transaction modifie une ressource, le verrou de mise à jour (U) est converti en verrou exclusif (X).

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 utilise des verrous intentionnels pour protéger le placement de verrous partagés (S) ou exclusifs (X) sur une ressource hiérarchiquement inférieure. Les verrous intentionnels sont appelés ainsi parce qu'ils sont obtenus avant un verrou de niveau inférieur et signalent par conséquent l'intention de placer des verrous à un niveau inférieur.

Les verrous intentionnels ont deux fonctions :

• Empêcher les autres transactions de modifier la ressource de niveau supérieur de façon à invalider le verrou au niveau inférieur.

• Améliorer l'efficacité du moteur de base de données en matière de détection de conflits de verrous au plus haut niveau de granularité.

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

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

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

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

Le moteur de base de données utilise les verrous de stabilité du 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 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 sur sp_tableoption.

Verrous de clés

Les verrous d'étendues de clés protègent une plage de lignes implicitement incluses dans un jeu d'enregistrements lu par une instruction Transact-SQL lors de l'utilisation du niveau d'isolement des transactions 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 verrou les plus courants.

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

Utilisez le tableau suivant pour déterminer la compatibilité de tous les modes de verrou disponibles dans SQL Server.

Différences entre les versions

L'algorithme GRANT premier entré, premier sorti (FIFO) a été considérablement levé en SQL Server 2005 par rapport à SQL Server 2000. Cette relaxation a affecté les demandes qui sont compatibles avec tous les modes détenus et tous les modes en attente. Dans ces cas, le nouveau verrou pourrait être accordé immédiatement en passant toutes les demandes en attente. Parce qu'il est compatible avec toutes les demandes en attente, le nouveau mode demandé n'aurait pas pour résultat une privation. Dans SQL Server 2000, la nouvelle demande ne serait pas accordée parce que, dans l'implémentation stricte premier entré, premier sorti (FIFO), les nouvelles demandes ne peuvent être accordées jusqu'à ce que toutes les demandes faites précédemment avaient été accordées. Dans l'exemple suivant, les connexions 1 et 3 seraient accordées lorsqu'elles sont exécutées sur SQL Server 2005 dans l'ordre spécifié. Dans SQL Server 2000, seulement le connexion 1 sera accordée.

/* Conn 1 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'IntentExclusive';
/* Conn 2 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'Shared';
/* Conn 3 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'IntentShare;

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 sont comprises entre 'AAA' et 'CZZ'. Un verrou d'étendues 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 cette étendue, 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 verrouillage utilisé pour verrouiller l'étendue d'index (RangeT) et la deuxième représente le type de verrouillage 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, plage partagée ; analyse de mise à jour 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.

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
Mode requis	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Partagé (S)	Oui	Oui	Non	Oui	Oui	Oui	Non
Mise à jour (U)	Oui	Non	Non	Oui	Non	Oui	Non
Exclusif (X)	Non	Non	Non	Non	Non	Oui	Non
RangeS-S	Oui	Oui	Non	Oui	Oui	Non	Non
RangeS-U	Oui	Non	Non	Oui	Non	Non	Non
RangeI-N	Oui	Oui	Oui	Non	Non	Oui	Non
RangeX-X	Non	Non	Non	Non	Non	Non	Non

Verrous de conversion

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

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

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

Analyse d'étendue sérialisable, extraction 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 d'étendues de clés ne peut être acquis que si ColumnX est couvert par une clé d'index.

Exemples

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

Requête d'analyse d'étendue

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

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

Les verrous d'étendues de clés sont placés sur les entrées d'index correspondant à l'étendue de lignes de données dans laquelle name 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 d'étendues 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 d'étendues de clés RangeS-S sur l'entrée d'index pour Dale fait en sorte qu'aucun nom commençant par C ne puisse être ajouté après Carlos, comme Clive.

Extraction d'un singleton de données non existantes

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

SELECT name
    FROM mytable
    WHERE name = 'Bill';

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

Opération de suppression

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.

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 par l'intermédiaire d'options d'optimiseur telles que ROWLOCK, PAGLOCK ou TABLOCK. Lorsque l'option PAGLOCK ou TABLOCK est utilisée, le Moteur de base de données 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 d'insertion

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.

Verrouillage dynamique

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

Le Microsoft 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 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 améliorées. Le Moteur de base de données 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 adapte le verrouillage automatiquement.

Dans SQL Server 2008 et les versions ultérieures, 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.

Interblocage

Un interblocage se produit lorsque deux tâches ou plus se bloquent mutuellement de façon permanente. Dans ce cas, chaque tâche place un verrou sur une ressource que la ou les autres tâches essaient de verrouiller. Exemple :

• La transaction A obtient un verrou partagé sur la ligne 1.

• La transaction B obtient un verrou partagé sur la ligne 2.

• La transaction A demande un verrou exclusif sur la ligne 2, mais elle est bloquée jusqu'à la fin de la transaction B qui libérera le verrou partagé sur la ligne 2.

• La transaction B demande un verrou exclusif sur la ligne 1, mais elle est bloquée jusqu'à la fin de la transaction A qui libérera le verrou partagé sur la ligne 1.

La transaction A ne peut pas se terminer tant que la transaction B n'est pas terminée, mais la transaction B est bloquée par la transaction A. Cette condition est également appelée dépendance cyclique : La transaction A est dépendante de la transaction B, mais celle-ci ne peut pas s'exécuter car elle est dépendante de la transaction A.

Les deux transactions en interblocage attendent indéfiniment que la situation soit débloquée par un processus externe. Le processus de surveillance des blocages du Moteur de base de données SQL Server vérifie périodiquement l'existence de tâches en interblocage. S'il détecte une situation de dépendance cyclique, il désigne une des tâches comme victime et met fin à sa transaction avec un message d'erreur. Cela permet à l'autre tâche de terminer sa transaction. L'application qui exécutait la transaction abandonnée peut effectuer une nouvelle tentative qui réussit en général une fois que l'autre transaction est terminée.

L'interblocage est souvent confondu avec le blocage ordinaire. Lorsqu'une transaction demande un verrou sur une ressource verrouillée par une autre transaction, elle attend que le verrou soit libéré. Par défaut, les transactions SQL Server n'ont pas de délai d'expiration, à moins que LOCK_TIMEOUT ne soit activé. La transaction qui demande le verrou est bloquée, mais pas indéfiniment puisqu'elle n'a rien fait pour bloquer la transaction détenant le verrou. La transaction qui détient le verrou va finir par se terminer et libérer le verrou ; l'autre transaction pourra alors obtenir son verrou et s'exécuter normalement.

Les interblocages sont parfois appelés « étreintes fatales ».

Une situation d'interblocage peut se produire sur tout système multithread, pas uniquement sur les systèmes de gestion de bases de données relationnelles. Elle peut également concerner des ressources autres que les verrous sur les objets de base de données. Par exemple, un thread dans un système multithread peut acquérir une ou plusieurs ressources (par exemple, des blocs de mémoire). Si la ressource acquise appartient actuellement à une autre thread, la première thread devra éventuellement attendre que le thread propriétaire libère la ressource cible. le thread en attente a une dépendance sur le thread propriétaire de cette ressource particulière. Dans une instance du moteur de base de données, les sessions peuvent se bloquer mutuellement lors de l'acquisition de ressources autres que des objets de base de données, notamment des blocs de mémoire ou des threads.

Dans l'illustration, la transaction T1 est dépendante de la transaction T2 pour la ressource de verrouillage de table Part. De même, la transaction T2 est dépendante de T1 pour la ressource de verrouillage de table Supplier. Comme ces dépendances forment un cycle, il y a interblocage entre les transactions T1 et T2.

Des interblocages peuvent également se produire lorsqu'une table est partitionnée et que le paramètre LOCK_ESCALATION de TABLE ALTER a la valeur AUTO. Lorsque LOCK_ESCALATION a la valeur AUTO, la concurrence augmente en permettant au Moteur de base de données de verrouiller des partitions de table au niveau du HoBT plutôt qu'au niveau de la TABLE. Toutefois, lorsque des transactions distinctes maintiennent des verrous de partition dans une table et souhaitent un verrou sur l'autre partition de transactions, cela provoque un interblocage. Ce type d'interblocage peut être évité en affectant à LOCK_ESCALATION la valeur TABLE, bien que ce paramètre réduise la concurrence en forçant les mises à jour volumineuses d'une partition à attendre un verrou de table.

Détection et fin des blocages

Un interblocage se produit lorsque deux tâches ou plus se bloquent mutuellement de façon permanente. Dans ce cas, chaque tâche place un verrou sur une ressource que la ou les autres tâches essaient de verrouiller. Le graphique suivant présente un aperçu d'un état de blocage où :

• La tâche T1 a placé un verrou sur la ressource R1 (indiquée par la flèche reliant R1 à T1) et a demandé un verrou sur la ressource R2 (indiquée par la flèche reliant T1 à R2).

• La tâche T2 a placé un verrou sur la ressource R2 (indiquée par la flèche reliant R2 à T2) et a demandé un verrou sur la ressource R1 (indiquée par la flèche reliant T2 à R1).

• Dans la mesure où aucune des deux tâches ne peut continuer tant qu'il n'y a pas de ressource disponible et que ni l'une ni l'autre des ressources ne peut être libérée avant la poursuite d'une tâche, un état de blocage se produit.

Le Moteur de base de données SQL Server détecte automatiquement les cycles de blocage dans SQL Server. Le Moteur de base de données choisit l'une des sessions comme victime et la transaction en cours se termine par une erreur, ce qui met fin à la situation de blocage.

Ressources susceptibles de se bloquer

Chaque session utilisateur peut avoir une ou plusieurs tâches en cours d'exécution, chacune de ces tâches pouvant obtenir ou être en attente d'obtention de diverses ressources. Les types de ressources susceptibles de provoquer un blocage sont les suivants :

• Verrous. L'attente d'obtention de verrous sur des ressources, telles qu'objets, pages, lignes, métadonnées et applications peut provoquer un blocage. Par exemple, la transaction T1 a un verrou partagé (S) sur la ligne r1 et elle attend d'obtenir un verrou exclusif (X) sur r2. La transaction T2 a un verrou partagé (S) sur r2 et elle attend d'obtenir un verrou exclusif (X) sur la ligne r1. Il en résulte un cycle de verrouillage où T1 et T2 attendent l'une de l'autre la libération des ressources que chacune a verrouillées.

• Threads de travail. Une tâche en attente d'un thread de travail disponible peut provoquer un blocage. Si la tâche en file d'attente est propriétaire des ressources qui bloquent tous les threads de travail, un blocage en résulte. Par exemple, la session S1 démarre une transaction et obtient un verrou partagé (S) sur la ligne r1 pour ensuite se mettre en veille. Les sessions actives en cours d'exécution sur tous les threads de travail disponibles essaient d'obtenir des verrous exclusifs (X) sur la ligne r1. Étant donné que la session S1 ne peut pas obtenir de thread de travail, elle ne peut pas valider la transaction et libère le verrou au niveau sur la ligne r1. Cela produit un blocage.

• Mémoire. Lorsque des demandes concurrentes sont en attente d'allocation de mémoire qui ne peut être satisfaite faute de mémoire suffisante, un blocage peut se produire. Par exemple, deux demandes concurrentes, Q1 et Q2, qui s'exécutant en tant que fonctions définies par l'utilisateur, obtiennent respectivement 10 Mo et 20 Mo de mémoire. Si chaque requête nécessite 30 Mo et que la quantité de mémoire disponible est de 20 Mo, Q1 et Q2 doivent attendre que chacune libère la mémoire, ce qui entraîne un blocage.

• Ressources liées à l'exécution de requêtes parallèles. Les threads de coordination, production ou consommation associées à un port d'échange peuvent se bloquer mutuellement et provoquer un blocage qui se produit généralement lors de l'introduction d'au moins un autre processus étranger à la requête parallèle. De même, quand commence l'exécution d'une requête parallèle, SQL Server détermine le degré de parallélisme, ou le nombre de threads de travail, en fonction de la charge de travail en cours. Si la charge de travail change de façon inattendue, par exemple si de nouvelles requêtes commencent à s'exécuter sur le serveur ou que le système se trouve à court de threads de travail, il peut s'ensuivre un blocage.

• Ressources MARS (Multiple Active Result Sets). Ces ressources servent à contrôler l'entrelacement de plusieurs demandes actives sous MARS. Pour plus d'informations, consultez Multiple Active Result Sets (MARS) dans SQL Server.

  • Ressource utilisateur. Lorsqu'un thread est en attente d'une ressource potentiellement contrôlée par une application d'utilisateur, la ressource est considérée comme étant une ressource externe ou utilisateur et est traitée comme un verrou.

  • Exclusion mutuelle de session Les tâches exécutées au cours d'une session sont entrelacées, ce qui signifie que seule une tâche peut s'exécuter à un moment donné dans le cadre de la session. Avant de pouvoir s'exécuter, la tâche doit disposer d'un accès exclusif à l'exclusion mutuelle de la session.

  • Exclusion mutuelle de transaction Toutes les tâches qui s'exécutent lors d'une transaction sont entrelacées, ce qui signifie que seule une tâche peut s'exécuter à un moment donné dans le cadre de la transaction. Avant de pouvoir s'exécuter, la tâche doit disposer d'un accès exclusif à l'exclusion mutuelle de la transaction.

  Pour pouvoir s'exécuter sous MARS, une tâche doit obtenir l'exclusion mutuelle de session. Si la tâche s'exécute dans le cadre d'une transaction, elle doit obtenir l'exclusion mutuelle de transaction. Vous serez ainsi assuré qu'il n'y a qu'une seule tâche active à la fois pour une session et une transaction données. Dès lors que les exclusions mutuelles requises ont été acquises, la tâche peut s'exécuter. Quand la tâche est terminée ou qu'elle aboutit au milieu de la demande, elle libère l'exclusion mutuelle de transaction avant l'exclusion mutuelle de session, c'est-à-dire dans l'ordre inverse de leur acquisition. Cependant, des blocages peuvent se produire avec ces ressources. Dans l'exemple de code suivant, deux tâches, la demande d'utilisateur U1 et la demande d'utilisateur U2, s'exécutent lors d'une même session.

  U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
  U2:    Rs2=Command2.Execute("select colA from sometable");
  

  La procédure stockée qui s'exécute à partir de la demande d'utilisateur U1 a obtenu l'exclusion mutuelle de session. Si son exécution se prolonge, le Moteur de base de données considère que la procédure stockée attend une entrée de données de la part de l'utilisateur. La demande d'utilisateur U2 attend l'exclusion mutuelle de session alors que l'utilisateur attend le jeu de résultats d'U2, et U1 attend une ressource utilisateur. Il s'agit d'un état de blocage logiquement illustré ainsi :

Détection de blocage

Toutes les ressources énumérées dans la section précédente sont visées par le dispositif de détection de blocage du Moteur de base de données. La détection de blocage est mise en œuvre par un thread de contrôle des verrous qui lance périodiquement une recherche sur toutes les tâches d'une instance du Moteur de base de données. Le processus de recherche présente les caractéristiques suivantes :

• L'intervalle par défaut est de 5 secondes.

• Si le thread de contrôle des verrous détecte des blocages, de 5 secondes, l'intervalle de détection de blocage pourra descendre jusqu'à 100 millisecondes, en fonction de la fréquence des blocages.

• Si le thread de contrôle des verrous ne détecte plus de blocages, le Moteur de base de données refera passer l'intervalle de recherche à 5 secondes.

• Si un blocage vient d'être détecté, les prochains threads qui doivent attendre un verrou sont supposés entrer dans le cycle de blocage. Les deux premières attentes de verrous postérieures à une détection de blocage déclencheront immédiatement une recherche de blocage sans attendre le prochain intervalle de détection de blocage. Par exemple, si l'intervalle courant est de 5 secondes et qu'un blocage vient d'être détecté, la prochaine attente de verrou lancera immédiatement le détecteur de blocage. Si cette attente de verrou est impliquée dans un blocage, celui-ci sera détecté sur le champ et non lors de la prochaine recherche de blocage.

En règle générale, le Moteur de base de données n'opère qu'une détection de blocage périodique. Puisque le nombre de blocages rencontrés dans le système est généralement faible, la détection de blocages périodique permet de réduire l'intendance des détections de blocage dans le système.

Lorsque le contrôleur de verrous initialise une recherche de blocage pour une thread particulière, il identifie la ressource sur laquelle le thread est en attente. Il recherche ensuite le ou les propriétaires de la ressource concernée et continue la recherche de façon récursive, jusqu'à ce qu'il trouve un cycle. Un cycle identifié de cette manière forme un blocage.

Dès lors qu'un blocage est détecté, le Moteur de base de données met fin à un blocage en choisissant l'un des threads comme victime. Le Moteur de base de données met fin au traitement en cours d'exécution pour le thread, annule la transaction de la victime, puis retourne une erreur 1205 à l'application. L'annulation de la transaction de la victime du blocage a pour effet de libérer tous les verrous détenus par la transaction. Cela permet aux transactions des autres threads de se débloquer et de continuer. L'erreur de victime de blocage 1205 enregistre des informations sur les threads et les ressources impliqués dans un blocage dans le journal des erreurs.

Par défaut, le Moteur de base de données choisit comme victime du blocage la session qui exécute la transaction dont l'annulation est la moins coûteuse. Un utilisateur peut également spécifier la priorité des sessions dans une situation de blocage au moyen de l'instruction SET DEADLOCK_PRIORITY. DEADLOCK_PRIORITY accepte les valeurs LOW, NORMAL ou HIGH, voire toute valeur entière comprise entre -10 et 10. La valeur par défaut de la priorité de blocage est NORMAL. Si deux sessions ont des priorités de blocage différentes, c'est la session qui a la priorité la plus basse qui est choisie comme victime. Si les deux sessions ont la même priorité de blocage, c'est celle dont la transaction est la moins coûteuse à annuler qui est choisie. Si les sessions impliquées dans le cycle de blocage présentent une priorité de blocage et un coût identiques, la victime est choisie de façon aléatoire.

Lorsque les fonctionnalités CLR sont utilisées, le moniteur de blocage détecte automatiquement le blocage des ressources de synchronisation (moniteurs, verrou de lecture/écriture et jointure de thread) qui font l'objet d'accès à l'intérieur des procédures gérées. Toutefois, le blocage est résolu par la levée d'une exception dans la procédure qui a été sélectionnée comme victime du blocage. Il est important de comprendre que l'exception ne libère pas automatiquement les ressources actuellement détenues par la victime ; les ressources doivent être libérées explicitement. Conformément au comportement des exceptions, l'exception utilisée pour identifier une victime de blocage peut être interceptée et annulée.

Outils d'information sur les blocages

Pour afficher les informations sur le blocage, le Moteur de base de données fournit des outils de surveillance sous la forme de deux indicateurs de trace, ainsi que l'événement Deadlock Graph dans SQL Server Profiler.

Indicateur de trace 1204 et indicateur de trace 1222

En cas de situation de blocage, l'indicateur de trace 1204 et l'indicateur de trace 1222 retournent des informations qui sont recueillies dans le journal des erreurs SQL Server. L'indicateur de trace 1024 signale les informations de blocage mises en forme par chaque nœud impliqué dans le blocage. L'indicateur de trace 1222 met en forme les informations de blocage, en commençant par les processus et en poursuivant avec les ressources. Il est possible d'activer deux indicateurs de trace pour obtenir deux représentations du même événement de blocage.

En dehors de la définition des propriétés des indicateurs de trace 1204 et 1222, le tableau suivant contient également les ressemblances et les différences.

Propriété	Indicateur de trace 1204 et indicateur de trace 1222	Indicateur de trace 1204 uniquement	Indicateur de trace 1222 uniquement
Format de sortie	La sortie est capturée dans le journal des erreurs de SQL Server.	Les nœuds impliqués dans le blocage sont privilégiés. Chaque nœud dispose d'une section dédiée, tandis que la section finale décrit la victime du blocage.	Retourne des informations dans un format de type XML, mais non conforme au schéma XSD (XML Schema Definition). Le format possède trois sections principales. La première déclare la victime du blocage. La deuxième décrit chaque processus impliqué dans le blocage. La troisième décrit les ressources synonymes des nœuds de l'indicateur de trace 1204.
Identification d'attributs	SPID:<x> ECID:<x>. Identifie le thread de l'ID du processus système en cas de traitements parallèles. L'entrée SPID:<x> ECID:0, où la valeur SPID remplace <x>, représente le thread principal. L'entrée SPID:<x> ECID:<y>, où la valeur SPID remplace <x> et où <y> est supérieur à 0, représente les sous-threads du même SPID. BatchID (sbid pour l'indicateur de trace 1222). Identifie le traitement à partir duquel l'exécution du code demande ou détient un verrou. Lorsque MARS (Multiple Active Result Sets) est désactivé, la valeur BatchID est 0. Lorsque MARS est activé, la valeur des lots actifs est 1 pour n. Si la session ne comporte pas de traitements actifs, BatchID a pour valeur 0. Mode. Spécifie, pour une ressource particulière, le type de verrou demandé, accordé ou attendu par un thread. Les différents modes sont IS (intent partagé), S (partagé), U (mise à jour), IX (intent exclusif), SIX (partagé avec intent exclusif) et X (exclusif). Line # (line pour l'indicateur de trace 1222). Indique le numéro de ligne du traitement qui était en cours d'exécution lorsque le blocage s'est produit. Input Buf (inputbuf pour l'indicateur de trace 1222). Dresse la liste de toutes les instructions du traitement en cours.	Node. Il s'agit du numéro d'entrée dans la chaîne de blocage. Lists. Le propriétaire du verrou peut faire partie des listes suivantes : Grant List. Énumère les propriétaires actuels de la ressource. Convert List. Énumère les propriétaires en cours qui essaient de convertir leurs verrous vers un niveau supérieur. Wait List. Énumère les nouvelles demandes de verrou en cours pour la ressource. Statement Type. Décrit le type d'instructions DML (SELECT, INSERT, UPDATE ou DELETE) sur lesquelles les threads disposent d'autorisations. Victim Resource Owner. Spécifie le thread choisi comme victime par SQL Server pour rompre le cycle de blocage. Il est alors mis fin au thread choisi et à tous les sous-threads existants. Next Branch. Représente les deux sous-threads (ou plus) du même SPID qui participent au cycle de blocage.	deadlock victim. Représente l'adresse de mémoire physique de la tâche (consultez sys.dm_os_tasks (Transact-SQL)) qui a été sélectionnée comme victime du blocage. Elle est égale à 0 (zéro) en cas de non résolution du blocage. Une tâche en cours d'annulation ne peut pas être choisie comme victime de blocage. executionstack. Représente le code Transact-SQL en cours d'exécution lorsque le blocage se produit. priority. Représente la priorité de blocage. Dans certains cas, le Moteur de base de données peut choisir de modifier la priorité de blocage pendant un bref laps de temps afin de favoriser la concurrence. logused. Espace journal utilisé par la tâche. owner id. ID de la transaction qui contrôle la demande. status. État de la tâche. Il prend l'une des valeurs suivantes : pending. En attente d'un thread de travail. runnable. Prêt à s'exécuter, mais en attente d'un quantum. running. En cours d'exécution sur le planificateur. suspended. L'exécution est suspendue. done. La tâche est achevée. spinloop. En attente de libération d'un spinlock. waitresource. Ressource convoitée par la tâche. waittime. Délai d'attente de la ressource en millisecondes. schedulerid. Planificateur associé à cette tâche. Consultez sys.dm_os_schedulers (Transact-SQL). hostname. Nom de la station de travail. isolationlevel. Niveau d'isolement des transactions en cours. Xactid. ID de la transaction qui contrôle la demande. currentdb. ID de la base de données. lastbatchstarted. Dernière fois qu'un processus client a démarré une exécution de traitement. lastbatchcompleted. Dernière fois qu'un processus client a terminé une exécution de traitement. clientoption1 et clientoption2. Options définies pour cette connexion cliente. Il s'agit d'un masque de bits qui contient des informations sur les options habituellement contrôlées par les instructions SET, telles que SET NOCOUNT et SET XACTABORT. associatedObjectId. Représente l'ID HoBT (Heap or B-tree, segment de mémoire ou arborescence binaire).
Attributs des ressources	RID. Identifie la ligne d'une table pour laquelle un verrou est détenu ou demandé. RID est représenté en tant que RID: db_id:file_id:page_no:row_no. Par exemple, RID: 6:1:20789:0. OBJECT. Identifie la table pour laquelle un verrou est détenu ou demandé. OBJECT est représenté en tant qu'OBJECT: db_id:object_id. Par exemple, TAB: 6:2009058193. KEY. Identifie la plage de clés d'un index pour laquelle un verrou est détenu ou demandé. KEY est représenté en tant que KEY: db_id:hobt_id (index key hash value). Par exemple, KEY: 6:72057594057457664 (350007a4d329). PAG. Identifie la ressource de page pour laquelle un verrou est détenu ou demandé. PAG est représenté en tant que PAG: db_id:file_id:page_no. Par exemple, PAG: 6:1:20789. EXT. Identifie la structure d'extension. EXT est représenté en tant que EXT: db_id:file_id:extent_no. Par exemple, EXT: 6:1:9. DB. Identifie le verrou de base de données. DB est représenté de l'une des manières suivantes : DB : db_id DB : db_id[BULK-OP-DB], qui identifie le verrou de base de données pris par la base de données de sauvegarde. DB : db_id[BULK-OP-LOG], qui identifie le verrou pris par le journal de sauvegarde pour cette base de données spécifique. APP. Identifie le verrou pris par une ressource d'application. APP est représenté en tant que APP: lock_resource. Par exemple, APP: Formf370f478. METADATA. Représente les ressources de métadonnées impliquées dans un blocage. Comme METADATA possède de nombreuses sous-ressources, la valeur retournée dépend de la sous-ressource bloquée. Par exemple, METADATA.USER_TYPE retourne user_type_id = <integer_value>. Pour plus d'informations sur les ressources et sous-ressources METADATA, consultez sys.dm_tran_locks (Transact-SQL). HOBT. Représente un segment de mémoire ou d'arbre B (B-Tree) impliqué dans un blocage.	Non exclusif à cet indicateur de trace.	Non exclusif à cet indicateur de trace.

Exemple d'indicateur de trace 1204

L'exemple suivant illustre la sortie obtenue quand l'indicateur de trace 1204 est activé. Dans ce cas, la table du nœud 1 est un segment de mémoire sans index et la table du nœud 2 est un segment de mémoire avec un index non-cluster. La clé d'index du nœud 2 est en cours de mise à jour lorsque le blocage se produit.

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0 
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Exemple d'indicateur de trace 1222

L'exemple suivant illustre la sortie obtenue quand l'indicateur de trace 1222 est activé. Dans ce cas, une table est un segment de mémoire sans index et l'autre table un segment de mémoire avec un index non-cluster. Dans la seconde table, la clé d'index est en cours de mise à jour lorsque le blocage se produit.

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868 
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444 
   transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0 
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54 
   sbid=0 ecid=0 priority=0 transcount=2 
   lastbatchstarted=2005-09-05T11:22:42.733 
   lastbatchcompleted=2005-09-05T11:22:42.733 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310444 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2012.dbo.usp_p1 line=6 stmtstart=202 
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1     
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380 
   waitresource=KEY: 6:72057594057457664 (350007a4d329)   
   waittime=5015 ownerId=310462 transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U 
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0 
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077 
   lastbatchcompleted=2005-09-05T11:22:44.077 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310462 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2012.dbo.usp_p2 line=6 stmtstart=200 
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2     
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2    
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2012.dbo.T2 
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2012.dbo.T1 
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X 
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Evénement Deadlock Graph de SQL Profiler

Il s'agit d'un événement propre au SQL Server Profiler qui présente une description graphique des tâches et des ressources impliquées dans un blocage. L'exemple suivant illustre la sortie obtenue à partir de SQL Server Profiler quand l'événement Deadlock Graph est activé.

Pour plus d'informations sur l'exécution du Deadlock Graph SQL Server Profiler, consultez Enregistrer les événements Deadlock Graph (Générateur de profils SQL Server).

Gestion des blocages

Lorsqu'une instance du Moteur de base de données SQL Server choisit une transaction comme victime d'un interblocage, elle met fin au traitement en cours, annule la transaction, puis retourne le message d'erreur 1205 à l'application.

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

Dans la mesure où toute application soumettant des requêtes Transact-SQL peut être choisie comme victime de blocage, les applications doivent intégrer un gestionnaire d'erreurs capable d'intercepter le message d'erreur 1205. Si une application n'intercepte pas cette erreur, elle peut continuer en ignorant que sa transaction a été annulée, et des erreurs peuvent se produire.

L'implémentation d'un gestionnaire d'erreurs capable d'intercepter le message d'erreur 1205 permet à une application de gérer les situations de blocage et de réagir en conséquence, par exemple en re-soumettant automatiquement la requête impliquée dans le blocage. Cette nouvelle soumission automatique rend la gestion du blocage entièrement transparente pour l'utilisateur.

L'application doit marquer un bref temps d'arrêt avant de soumettre à nouveau la requête. Cela permet à l'autre transaction impliquée dans le blocage d'aboutir et de libérer ses verrous qui faisaient partie du cycle de blocage. Les risques qu'un blocage se reproduise au moment où la requête de nouveau soumise demande ses verrous sont ainsi réduits.

Réduction des blocages

Même si les interblocages ne peuvent pas être totalement évités, le respect de certaines conventions de codage peut minimiser le risque d'en générer. La réduction des blocages peut augmenter le débit des transactions et réduire la charge du système, car il y a moins de transactions :

• restaurées, en annulant ce qui a été accompli par la transaction ;

• resoumises par les applications, car ces transactions ont été restaurées lors du blocage.

Pour réduire le nombre de blocages :

• Accédez aux objets dans le même ordre.

• Évitez les interactions utilisateur dans les transactions.

• Créez des transactions courtes dans le même traitement.

• Utilisez un niveau d'isolement plus faible.

• Utilisez un niveau d'isolement basé sur le contrôle de version de ligne.

  • Affectez à l'option de base de données READ_COMMITTED_SNAPSHOT la valeur ON pour activer les transactions read-committed afin d'utiliser le contrôle de version de ligne.

  • Utilisez un isolement d'instantané.

• Utilisez des connexions liées.

Accès aux objets dans le même ordre

Si toutes les transactions concurrentes accèdent aux objets dans le même ordre, le risque de blocage diminue. Par exemple, si deux transactions concurrentes obtiennent un verrou sur la table Supplier, puis sur la table Part, l'une des transactions est bloquée sur la table Supplier jusqu'à ce que l'autre transaction se termine. Après la validation ou la restauration de la première transaction, la seconde continue et aucun blocage ne se produit. L'utilisation de procédures stockées pour toutes les modifications de données peut standardiser l'ordre d'accès aux objets.

Aucune interaction utilisateur dans les transactions

Évitez d'écrire des transactions comprenant une interaction utilisateur, car la vitesse d'exécution des traitements sans intervention de l'utilisateur est beaucoup plus rapide que la vitesse à laquelle un utilisateur doit répondre manuellement aux requêtes telles que la demande d'un paramètre requis par une application. Par exemple, si une transaction attend une entrée de la part de l'utilisateur, et si ce dernier va déjeuner ou rentre chez lui pour le week-end, l'utilisateur empêche la transaction de se terminer. Ceci dégrade les performances du système, car tous les verrous détenus par la transaction ne sont libérés qu'une fois la transaction validée ou restaurée. Même si une situation de blocage ne se produit pas, toutes les autres transactions en attente de la même ressource sont bloquées, en attente de la fin de la transaction.

Transactions courtes dans un seul traitement

Un blocage se produit souvent lorsque plusieurs transactions longues sont exécutées de manière concurrente dans la même base de données. Plus la transaction est longue, plus la durée de détention du verrou exclusif ou de mise à jour est importante, ce qui bloque les autres activités et peut entraîner une situation de blocage.

La création de transactions courtes dans un seul traitement limite les allers-retours sur le réseau en réduisant les délais potentiels d'achèvement de la transaction et de suppression des verrous.

Niveau d'isolement faible

Déterminez si une transaction peut être exécutée à un niveau d'isolement faible. L'implémentation de la lecture validée (read committed) permet à une transaction de lire des données lues auparavant (non modifiées) par une autre transaction, sans attendre la fin de la première transaction. L'utilisation d'un niveau d'isolement faible (comme la lecture validée, par exemple) permet de conserver les verrous partagés pendant une durée inférieure à celle d'un niveau d'isolement supérieur (comme le niveau sérialisable) et réduit ainsi la contention de verrouillage.

Niveau d'isolement basé sur le contrôle de version de ligne

Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT a la valeur ON, une transaction qui s'exécute sous un niveau d'isolement read committed utilise le contrôle de version de ligne plutôt que les verrous partagés lors des opérations de lecture.

L'isolement d'instantané utilise également le contrôle de version de ligne, qui n'emploie pas de verrous partagés pendant les opérations de lecture. Pour qu'une transaction puisse s'exécuter sous un isolement d'instantané, l'option de base de données ALLOW_SNAPSHOT_ISOLATION doit avoir la valeur ON.

Implémentez ces niveaux d'isolement pour minimiser les blocages pouvant survenir entre les opérations de lecture et d'écriture.

Connexions liées

En utilisant des connexions liées, deux connexions ou plus ouvertes par la même application peuvent coopérer entre elles. Tout verrou acquis par la connexion secondaire apparaît comme s'il avait été posé par la connexion primaire et vice-versa. Ils ne se bloquent donc pas réciproquement.

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'objets peuvent être partitionnés. Les verrous d'objets dotés d'un sous-type ne sont pas partitionnés. Pour plus d'informations, consultez sys.dm_tran_locks (Transact-SQL).

Présentation du partitionnement de verrous

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

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

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

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

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

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

Mise en œuvre et surveillance du partitionnement de verrous

Le partitionnement de verrous est activé par défaut pour les systèmes comportant 16 UC ou plus. Quand il 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 pour une ressource de verrou partitionnée. Pour plus d'informations, consultez sys.dm_tran_locks (Transact-SQL).

Sous l'événement Locks dans SQL Server Profiler, la colonne BigintData1 fournit l'ID de partition d'une ressource de verrou partitionnée.

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, on suppose que le verrou IS est acquis sur la partition ID 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);

[Top]

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

À partir de SQL Server 2005, le moteur de base de données introduit une implémentation d'un niveau d'isolement de la transaction existant, read committed, qui fournit un instantané au niveau des instructions basé sur le contrôle de version de ligne. Le moteur de base de données 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 une infrastructure générale 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 :

• Construction des tables inserted et deleted 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 publie une instruction de modification de données (par exemple, INSERT, UPDATE ou DELETE) à un moment où il y a un ensemble de résultats actif, les lignes concernées par l'instruction de modification sont avec version.

• Prise en charge des opérations d'index qui spécifient l'option ONLINE.

• Prise en charge des niveaux d'isolement des transactions basés sur le contrôle de version de ligne :

  • Une nouvelle implémentation du niveau d'isolement read committed qui utilise le contrôle de version de ligne pour assurer la cohérence de la lecture au niveau de l'instruction.

  • Un nouveau niveau d'isolement, 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. Lorsque la base de données tempdb est pleine, les opérations de mise à jour ne génèrent plus de versions et continuent à aboutir, mais les opérations de lecture risquent d'échouer en raison de l'absence d'une version de ligne particulière. 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 (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 :

   • Lorsque l'option READ_COMMITTED_SNAPSHOT est activée (ON), les transactions qui définissent le niveau d'isolement read committed utilisent le contrôle de version de ligne.

   • Lorsque l'option de base de données ALLOW_SNAPSHOT_ISOLATION est activée (ON), les transactions peuvent définir le niveau d'isolement d'instantané.

Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION est activée, 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é.

Lorsqu'une seule des deux options de base de données (READ_COMMITTED_SNAPSHOT et 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 de Moteur de base de données enregistre une version de l'image validée d'origine de la ligne dans la base de données 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 aux versions gérées, stockées 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 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.

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 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 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 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 émis pour cette instance du Moteur de base de données. 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.

Comportement lors de la modification de données

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 concomitante validée après elle. 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. Il n'y a aucun moyen de désactiver la détection des conflits de mise à jour.

Synthèse des comportements

Le tableau suivant synthétise 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.	Passe 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 sélectionnées. Acquiert des verrous exclusifs sur les lignes à modifier réellement. Pas de détection de conflit de mise à jour.	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.	Aucun.	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 :

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

• Lorsque 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, 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 de la banque des versions. Il existe deux banques de versions dans tempdb :

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

Lorsque tempdb n'a plus d'espace, le moteur de base de données force la réduction des banques de 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 du magasin de 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 https://msdn.microsoft.com/fr-fr/library/ms177459(v=sql.110) peut permettre d'empêcher ou de réduire la fragmentation des pages d'index. Pour afficher les paramètres de fragmentation des données et des index de la table ou vue spécifiée, vous pouvez utiliser DBCC SHOWCONTIG.

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) 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 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

SQL Server fournit des outils pour le contrôle du contrôle de version de ligne, du magasin de versions et des processus d'isolement d'instantané : les vues DMV (Dynamic Management Views) et les compteurs de performances dans le Moniteur système Windows.

Vues 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_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. sys.dm_tran_current_snapshot est similaire à sys.dm_tran_transactions_snapshot, mais elle ne renvoie que les transactions actives pour l'instantané actif. 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 performance 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'isolement 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 de magasin de versions générées par minute] * [délai le plus long d'exécution (en minutes) de transaction]

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

Taille du magasin de versions (Ko). 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.

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

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

Troncation d'unité dans le magasin de versions. Contrôle le nombre total d'unités tronquées dans le magasin de versions depuis le démarrage de l'instance. Une unité de magasin de versions est tronquée lorsque SQL Server 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 d'instantanés de mise à jour présentant des conflits de mise à jour par rapport au nombre total de transactions d'instantanés de mise à jour.

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.

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'isolement 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 AdventureWorks2012;  -- Or the 2008 or 2008R2 version of the AdventureWorks database.
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2012
    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 AdventureWorks2012;
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 validée en lecture à 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 AdventureWorks2012;  -- 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 AdventureWorks2012
-- database.
ALTER DATABASE AdventureWorks2012
    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 AdventureWorks2012;
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

Activation des niveaux d'isolement selon 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.

Lorsque 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 permet la prise en charge de l'option READ_COMMITTED_SNAPSHOT :

ALTER DATABASE AdventureWorks2012
    SET READ_COMMITTED_SNAPSHOT ON;

Lorsque 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 lorsque l'exécution de toutes les transactions actives modifiant les données de la base de données est terminée. En cas de transactions de modification actives, SQL Server affecte à l'option l'état 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 lorsque 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 AdventureWorks2012
    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.
ON	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 ne passe sur OFF qu'à la fin de l'exécution de toutes les transactions d'instantané activées lorsque l'état d'isolement d'instantané de la base de données correspondait à ON.

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 les tables de données master et msdb génèrent des versions 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'isolement basés sur le contrôle de version de ligne

L'infrastructure de contrôle de version de ligne est toujours activée dans SQL Server et est utilisée par plusieurs fonctionnalités. En plus de fournir des niveaux d'isolement basés sur le contrôle de version de ligne, elle permet la prise en charge des modifications apportées aux déclencheurs et aux sessions MARS (Multiple Active Result Sets), ainsi que la prise en charge des lectures de données pour les opérations d'index 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 AdventureWorks2012
      SET READ_COMMITTED_SNAPSHOT ON;
  

  Lorsque 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'isolement READCOMMITTED 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 AdventureWorks2012
      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'isolement d'instantané (SNAPSHOT) 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 :

  • Définissez l'option de base de données ALLOW_SNAPSHOT_ISOLATION sur ON (activé) 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. 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 AdventureWorks2012;
  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.

  Notes

  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.

[Top]

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

Personnalisation du délai d'attente de verrouillage

Lorsqu'une instance du  Moteur de base de données SQL Server Microsoft ne peut pas accorder un verrou à une transaction, car une autre transaction possède 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).

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. Toute transaction contenant la commande n'est toutefois 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 la valeur actuelle du paramètre LOCK_TIMEOUT, exécutez la fonction @@LOCK_TIMEOUT :

SELECT @@lock_timeout;
GO

Personnalisation du niveau d'isolation des transactions

Pour le moteur de base de données SQL Server de Microsoft, READ COMMITTED est le niveau d'isolation par défaut. 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 utilisant l'espace de noms géré System.Data.SqlClient peuvent indiquer une option IsolationLevel via la méthode SqlConnection.BeginTransaction.

• Les applications qui utilisent ADO peuvent définir la propriété Autocommit Isolation Levels.

• Lors du lancement d'une transaction, les applications utilisant OLE DB peuvent appeler ITransactionLocal::StartTransaction avec le paramètre isoLevel défini sur le niveau d'isolation de la transaction souhaité. Lorsque vous spécifiez le niveau d'isolation en mode de validation automatique, les applications utilisant OLE DB peuvent affecter à la propriété DBPROP_SESS_AUTOCOMMITISOLEVELS de DBPROPSET_SESSION la valeur de niveau d'isolation de la transaction souhaitée.

• Les applications qui utilisent ODBC peuvent définir l'attribut SQL_COPT_SS_TXN_ISOLATION à l'aide de SQLSetConnectAttr.

Lorsque le niveau d'isolation est spécifié, le verrouillage s'applique à ce niveau d'isolation, à toutes les requêtes et instructions DML de la session SQL Server. 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 AdventureWorks2012;
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 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 AdventureWorks2012;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Voici l'ensemble des résultats.

Set Option Value

---------------------------- -------------------------------------------

textsize 2147483647

language us_english

dateformat mdy

datefirst 7

... ...

Isolation level repeatable read

(14 row(s) affected)

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

Indicateurs de verrouillage

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

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

Il se peut que le Moteur de base de données doive obtenir des verrous lors de la lecture de métadonnées, même lors du traitement d'une 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 le verrou NOLOCK n'obtient pas de verrous de partage lors de la lecture de données, mais elle peut occasionnellement demander des verrous lorsqu'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'isolement d'une transaction est SERIALIZABLE et que l'indicateur de verrouillage NOLOCK au niveau des tables 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 AdventureWorks2012;
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 2012, 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 l'accès concurrentiel à 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 goulots 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.

La granularité de verrouillage utilisée pour 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

[Top]

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 la dernière action effectuée dans la transaction la plus 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 interne. Même si l'instruction COMMIT TRANSACTION transaction_name d'une transaction imbriquée fait référence à la transaction externe, seule la transaction interne 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 nommées imbriquées. transaction_name ne peut faire référence qu'au nom de la transaction la plus à l'extérieur. 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, alors toutes les transactions imbriquées sont restaurées. Si une instruction ROLLBACK WORK ou ROLLBACK TRANSACTION est exécutée à un niveau d'imbrication quelconque sans que le paramètre transaction_name soit spécifié, toutes les transactions imbriquées, la plus externe comprise, sont restaurées.

La fonction @@TRANCOUNT enregistre le niveau d'imbrication de la transaction en cours. Chaque instruction BEGIN TRANSACTION incrémente la valeur de @@TRANCOUNT d'une unité. Chaque instruction COMMIT TRANSACTION ou COMMIT WORK la décrémente de la même valeur. 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 externe d'un groupe de transactions imbriquées est spécifié restaure toutes les transactions imbriquées et remet @@TRANCOUNT à 0. Si vous n'êtes pas sûr qu'une transaction est déjà active, utilisez l'instruction SELECT @@TRANCOUNT pour déterminer si sa valeur est égale ou supérieure 1 Si la valeur de @@TRANCOUNT est 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 faire partie d'une session associée, une session doit appeler sp_getbindtoken ou srv_getbindtoken (via Open Data Services) pour obtenir un jeton d'association. 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 d'association reçu de la première session.

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

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

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

• Les jetons d'association peuvent être stockés dans une instance de 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.

• Sessions associées locales

  Les sessions associées locales peuvent partager l'espace de transaction d'une transaction unique dans une seule instance du Moteur de base de données.

• Sessions associées distribuées

  Les sessions associées distribuées peuvent partager la même transaction entre plusieurs instances jusqu'à ce que la transaction complète soit validée ou annulée à l'aide de 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 associé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 associée locale est automatiquement promue au rang de transaction associée distribuée par MS DTC et une session MS DTC est lancée.

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

Écriture de transactions performantes

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'isolement des transactions, les instructions SELECT peuvent activer 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.

Directives de codage

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.

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 à 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, lorsque le niveau d'isolement d'instantané est activé, même si une nouvelle transaction ne contient pas de verrous, une exécution très longue empêche la suppression des anciennes versions dans tempdb.

Gestion des transactions de longue durée

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

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

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

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

Découverte des transactions de longue durée

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

• sys.dm_tran_database_transactions

  Cet affichage de gestion dynamique retourne des informations sur les transactions au niveau de la base de données. Pour une transaction de longue durée, les colonnes particulièrement intéressantes 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 de l'enregistrement initial dans le journal des transactions (database_transaction_begin_lsn).

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

• DBCC OPENTRAN

  Cette instruction vous permet d'identifier l'ID 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êt d'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).

[Top]

Voir aussi

Autres ressources

Niveaux d'isolement des transactions basés sur le contrôle de version de ligne SQL Server 2005

Surcharge du contrôle de version de ligne

Procédure : créer une transaction autonome dans SQL Server 2008