Guide du verrouillage des transactions et du contrôle de version de ligne SQL ServerSQL Server Transaction Locking and Row Versioning Guide

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.In any database, mismanagement of transactions often leads to contention and performance problems in systems that have many users. 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.As the number of users that access the data increases, it becomes important to have applications that use transactions efficiently. 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 ServerSQL Server Database Engine 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.This guide describes the locking and row versioning mechanisms the Moteur de base de données SQL ServerSQL Server Database Engine uses to ensure the physical integrity of each transaction and provides information on how applications can control transactions efficiently.

S'applique à: SQL Server 2005SQL Server 2005 et SQL Server 2014SQL Server 2014 , sauf indication contraire.Applies to: SQL Server 2005SQL Server 2005 through SQL Server 2014SQL Server 2014 unless noted otherwise.

Dans ce GuideIn This Guide

Principes fondamentaux de transactionTransaction Basics

Le verrouillage et principes de base de contrôle de version de ligneLocking and Row Versioning Basics

Verrouillage dans le moteur de base de donnéesLocking in the Database Engine

Niveaux d'isolation basés sur le contrôle de version de ligne dans le moteur de base de donnéesRow Versioning-based Isolation Levels in the Database Engine

Personnalisation du verrouillage pour un IndexCustomizing Locking for an Index

Informations sur les transactions avancéesAdvanced Transaction Information

Principes de base sur les transactionsTransaction Basics

Une transaction est une suite d'opérations effectuées comme une seule unité logique de travail.A transaction is a sequence of operations performed as a single logical unit of work. 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 :A logical unit of work must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction.

AtomicitéAtomicity
Une transaction doit être une unité de travail indivisible ; soit toutes les modifications de données sont effectuées, soit aucune ne l'est.A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them are performed.

CohérenceConsistency
Lorsqu'elle est terminée, une transaction doit laisser les données dans un état cohérent.When completed, a transaction must leave all data in a consistent state. 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.In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. 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.All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.

IsolementIsolation
Les modifications effectuées par des transactions concurrentes doivent être isolées transaction par transaction.Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. 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.A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state. 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.This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

DurabilitéDurability
Lorsqu'une transaction durable est terminée, ses effets sur le système sont permanents.After a fully durable transaction has completed, its effects are permanently in place in the system. Les modifications sont conservées même en cas de défaillance du système.The modifications persist even in the event of a system failure. SQL Server 2014SQL Server 2014 et versions ultérieures permettent les transactions durables retardées.and later enable delayed durable transactions. Les transactions durables retardées sont validées avant de consigner l'enregistrement du journal des transactions sur le disque.Delayed durable transactions commit before the transaction log record is persisted to disk. Pour plus d’informations sur la durabilité des transactions retardées, consultez la rubrique Durabilité des transactions.For more information on delayed transaction durability see the topic Transaction Durability.

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.SQL programmers are responsible for starting and ending transactions at points that enforce the logical consistency of the data. 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é.The programmer must define the sequence of data modifications that leave the data in a consistent state relative to the organization's business rules. 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 ServerSQL Server Database Engine puisse garantir l'intégrité physique de la transaction.The programmer includes these modification statements in a single transaction so that the Moteur de base de données SQL ServerSQL Server Database Engine can enforce the physical integrity of the transaction.

Un système de base de données d'entreprise, par exemple une instance de Moteur de base de donnéesDatabase Engine, se doit de fournir des mécanismes permettant de garantir l'intégrité physique de chaque transaction.It is the responsibility of an enterprise database system, such as an instance of the Moteur de base de donnéesDatabase Engine, to provide mechanisms ensuring the physical integrity of each transaction. Le Moteur de base de donnéesDatabase Engine fournit les éléments suivants :The Moteur de base de donnéesDatabase Engine provides:

  • Des fonctionnalités de verrouillage permettant d'assurer l'isolement des transactions.Locking facilities that preserve transaction isolation.

  • Des fonctionnalités de consignation assurent la durabilité des transactions.Logging facilities ensure transaction durability. Pour les transactions durables, l'enregistrement du journal est renforcé sur le disque avant les validations des transactions.For fully durable transactions the log record is hardened to disk before the transactions commits. Ainsi, en cas de défaillance du matériel serveur, du système d’exploitation ou de l’instance du Moteur de base de donnéesDatabase Engine 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.Thus, even if the server hardware, operating system, or the instance of the Moteur de base de donnéesDatabase Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any uncompleted transactions to the point of the system failure. Les transactions durables retardées sont validées avant de renforcer l'enregistrement du journal des transactions sur le disque.Delayed durable transactions commit before the transaction log record is hardened to disk. Ces transactions peuvent être perdues en cas de défaillance du système avant que l'enregistrement du journal ne soit renforcé sur le disque.Such transactions may be lost if there is a system failure before the log record is hardened to disk. Pour plus d’informations sur la durabilité des transactions retardées, consultez la rubrique Durabilité des transactions.For more information on delayed transaction durability see the topic Transaction Durability.

  • Des fonctionnalités de gestion des transactions qui assurent l'atomicité et la cohérence des transactions.Transaction management features that enforce transaction atomicity and consistency. 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éesDatabase Engine annule toutes les modifications effectuées sur les données depuis le début de la transaction.After a transaction has started, it must be successfully completed (committed), or the Moteur de base de donnéesDatabase Engine undoes all of the data modifications made since the transaction started. Cette opération est appelée restauration d'une transaction, car elle retourne les données telles qu'elles étaient avant ces modifications.This operation is referred to as rolling back a transaction because it returns the data to the state it was prior to those changes.

Contrôle des transactionsControlling Transactions

Le contrôle des transactions par les applications consiste principalement à spécifier des points de début et de fin de chaque transaction.Applications control transactions mainly by specifying when a transaction starts and ends. La spécification est effectuée à l'aide des instructions Transact-SQLTransact-SQL ou des fonctions d'API de base de données.This can be specified by using either Transact-SQLTransact-SQL statements or database application programming interface (API) functions. Le système doit aussi être capable de gérer les erreurs interrompant une transaction avant sa fin normale.The system must also be able to correctly handle errors that terminate a transaction before it completes. Pour plus d’informations, consultez instructions Transaction (Transact-SQL), Transactions dans ODBC et Transactions dans SQL Server Native Client (OLEDB).For more information, see Transaction Statements (Transact-SQL), Transactions in ODBC and Transactions in SQL Server Native Client (OLEDB).

Par défaut, les transactions sont gérées au niveau de la connexion.By default, transactions are managed at the connection level. Lorsqu'une transaction est démarrée lors d'une connexion, toutes les instructions Transact-SQLTransact-SQL exécutées lors de cette connexion font partie de la transaction jusqu'à la fin de celle-ci.When a transaction is started on a connection, all Transact-SQLTransact-SQL statements executed on that connection are part of the transaction until the transaction ends. Toutefois, dans une session MARS (Multiple Active Result Set), une transaction Transact-SQLTransact-SQL explicite ou implicite devient une transaction dont l'étendue est définie par traitement gérée au niveau du lot.However, under a multiple active result set (MARS) session, a Transact-SQLTransact-SQL explicit or implicit transaction becomes a batch-scoped transaction that is managed at the batch level. À 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 ServerSQL Server.When the batch completes, if the batch-scoped transaction is not committed or rolled back, it is automatically rolled back by SQL ServerSQL Server. Pour plus d’informations, consultez Multiple Active Result Sets (MARS) dans SQL Server.For more information, see Multiple Active Result Sets (MARS) in SQL Server.

Démarrage des transactionsStarting Transactions

À l'aide des fonctions API et des instructions Transact-SQLTransact-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 ServerSQL Server Database Engine.Using API functions and Transact-SQLTransact-SQL statements, you can start transactions in an instance of the Moteur de base de données SQL ServerSQL Server Database Engine as explicit, autocommit, or implicit transactions.

Transactions explicitesExplicit Transactions
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-SQLTransact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION ou ROLLBACK WORKTransact-SQLTransact-SQL.An explicit transaction is one in which you explicitly define both the start and end of the transaction through an API function or by issuing the Transact-SQLTransact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQLTransact-SQL statements. À 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.When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started, either implicit or autocommit mode.

Vous pouvez utiliser toutes les instructions Transact-SQLTransact-SQL dans une transaction explicite, à l'exception des instructions suivantes :You can use all Transact-SQLTransact-SQL statements in an explicit transaction, except for the following statements:

ALTER DATABASEALTER DATABASE CREATE DATABASECREATE DATABASE DROP FULLTEXT INDEXDROP FULLTEXT INDEX
ALTER FULLTEXT CATALOGALTER FULLTEXT CATALOG CREATE FULLTEXT CATALOGCREATE FULLTEXT CATALOG RECONFIGURERECONFIGURE
ALTER FULLTEXT INDEXALTER FULLTEXT INDEX CREATE FULLTEXT INDEXCREATE FULLTEXT INDEX RESTORERESTORE
BACKUPBACKUP DROP DATABASEDROP DATABASE Procédures stockées système de recherche en texte intégralFull-text system stored procedures
CREATE DATABASECREATE DATABASE DROP FULLTEXT CATALOGDROP 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.sp_dboption to set database options or any system procedure that modifies the master database inside explicit or implicit transactions.

Note

UPDATE STATISTICS peut être utilisée à l'intérieur d'une transaction explicite.UPDATE STATISTICS can be used inside an explicit transaction. Toutefois, UPDATE STATISTICS est validée indépendamment de la transaction qui la contient et ne peut pas être restaurée.However, UPDATE STATISTICS commits independently of the enclosing transaction and cannot be rolled back.

Transactions en mode de validation automatiqueAutocommit Transactions
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.Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Chaque instruction Transact-SQL est validée ou restaurée dès qu'elle se termine.Every Transact-SQL statement is committed or rolled back when it completes. Lorsqu'une instruction est exécutée avec succès, elle est validée ; si une erreur se produit, elle est restaurée.If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. 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.A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. Le mode autocommit est également le mode par défaut pour ADO, OLE DB, ODBC et DB-Library.Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

Transactions implicitesImplicit Transactions
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.When a connection is operating in implicit transaction mode, the instance of the Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. Vous n'avez pas à définir le début d'une transaction, il vous suffit de valider ou de restaurer chaque transaction.You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. Le mode de transaction implicite génère une succession continue de transactions.Implicit transaction mode generates a continuous chain of transactions. Activez le mode de transaction implicite en utilisant une fonction d'API ou l'instruction Transact-SQLTransact-SQL SET IMPLICIT_TRANSACTIONS ON.Set implicit transaction mode on through either an API function or the Transact-SQLTransact-SQL SET IMPLICIT_TRANSACTIONS ON statement.

Lorsque le mode de transaction implicite est activé pour une connexion, l'instance de Moteur de base de donnéesDatabase Engine démarre automatiquement une transaction lorsqu'il exécute pour la première fois l'une des instructions suivantes :After implicit transaction mode has been set on for a connection, the instance of the Moteur de base de donnéesDatabase Engine automatically starts a transaction when it first executes any of these statements:

ALTER TABLEALTER TABLE FETCHFETCH REVOKEREVOKE
CREATECREATE GRANTGRANT SELECTSELECT
SuppressionDELETE INSERTINSERT TRUNCATE TABLETRUNCATE TABLE
DROPDROP OPENOPEN UPDATEUPDATE

Transactions délimitées au traitementBatch-scoped Transactions
Uniquement applicable aux ensembles de résultats MARS (Multiple Active Result Sets), une transaction Transact-SQLTransact-SQL explicite ou implicite qui démarre sous une session MARS devient une transaction dont l'étendue est définie par traitement.Applicable only to multiple active result sets (MARS), a Transact-SQLTransact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. 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 ServerSQL Server.A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL ServerSQL Server.

Transactions distribuéesDistributed Transactions
Les transactions distribuées sont réparties sur plusieurs serveurs nommés gestionnaires de ressources.Distributed transactions span two or more servers known as resource managers. La gestion de la transaction doit être coordonnée entre les gestionnaires de ressources par un composant du serveur nommé gestionnaire de transactions.The management of the transaction must be coordinated between the resource managers by a server component called a transaction manager. Chaque instance du Moteur de base de données SQL ServerSQL Server Database Engine peut être utilisée comme gestionnaire de ressources dans les transactions distribuées coordonnées par un gestionnaire de transactions tel que MS DTC (MicrosoftMicrosoft 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.Each instance of the Moteur de base de données SQL ServerSQL Server Database Engine can operate as a resource manager in distributed transactions coordinated by transaction managers, such as MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC), or other transaction managers that support the Open Group XA specification for distributed transaction processing. Pour plus d'informations, consultez la documentation MS DTC.For more information, see the MS DTC documentation.

Une transaction exécutée sur une seule instance du Moteur de base de donnéesDatabase Engine, mais utilisant plusieurs bases de données, est en réalité une transaction distribuée.A transaction within a single instance of the Moteur de base de donnéesDatabase Engine that spans two or more databases is actually a distributed transaction. Cette instance gère la transaction distribuée de manière interne ; elle apparaît comme une transaction locale pour l'utilisateur.The instance manages the distributed transaction internally; to the user, it operates as a local transaction.

Dans une application, une transaction distribuée est gérée de manière comparable à une transaction locale.At the application, a distributed transaction is managed much the same as a local transaction. À la fin de la transaction, l'application requiert que la transaction soit validée ou restaurée.At the end of the transaction, the application requests the transaction to be either committed or rolled back. 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.A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction. 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).This is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC).

Phase de préparationPrepare phase
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.When the transaction manager receives a commit request, it sends a prepare command to all of the resource managers involved in the 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.Each resource manager then does everything required to make the transaction durable, and all buffers holding log images for the transaction are flushed to disk. 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.As each resource manager completes the prepare phase, it returns success or failure of the prepare to the transaction manager. SQL Server 2014SQL Server 2014 a introduit la durabilité des transactions retardées.introduced delayed transaction durability. Les transactions durables retardées sont validées avant de vider les images de journal pour la transaction sur le disque.Delayed durable transactions commit before log images for the transaction are flushed to disk. Pour plus d’informations sur la durabilité des transactions retardées, consultez la rubrique Durabilité des transactions.For more information on delayed transaction durability see the topic Transaction Durability.

Phase de validationCommit phase
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.If the transaction manager receives successful prepares from all of the resource managers, it sends commit commands to each resource manager. Les gestionnaires de ressources peuvent alors effectuer la validation.The resource managers can then complete the commit. 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.If all of the resource managers report a successful commit, the transaction manager then sends a success notification to the 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.If any resource manager reported a failure to prepare, the transaction manager sends a rollback command to each resource manager and indicates the failure of the commit to the application.

Les applications du Moteur de base de donnéesDatabase Engine peuvent gérer les transactions distribuées à l'aide de Transact-SQLTransact-SQL ou de l'API de base de données.Moteur de base de donnéesDatabase Engine applications can manage distributed transactions either through Transact-SQLTransact-SQL or the database API. Pour plus d’informations, consultez BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).For more information, see BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Fin des transactionsEnding Transactions

Terminez les transactions avec une instruction COMMIT ou ROLLBACK, ou au moyen d'une fonction API correspondante.You can end transactions with either a COMMIT or ROLLBACK statement, or through a corresponding API function.

COMMITCOMMIT
Si une transaction est réussie, validez-la.If a transaction is successful, commit it. L'instruction COMMIT garantit que toutes les modifications effectuées sur la base de données au cours de la transaction sont permanentes.A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. Cette instruction libère également les ressources, telles que les verrous, qui ont été utilisées par la transaction.A COMMIT also frees resources, such as locks, used by the transaction.

ROLLBACKROLLBACK
Si une erreur se produit pendant une transaction ou si l'utilisateur décide de l'abandonner, restaurez-la.If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. 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.A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. Cette instruction libère également les ressources bloquées par la transaction.A ROLLBACK also frees resources held by the transaction.

Note

Dans le cas des connexions prenant en charge les ensembles de résultats MARS (Multiple Active Result Sets), une transaction explicite démarrée par le biais d'une fonction API ne peut pas être validée alors que des demandes sont en attente d'exécution.Under connections enabled to support multiple active result sets (MARS), an explicit transaction started through an API function cannot be committed while there are pending requests for execution. Toute tentative de validation d'une transaction de ce type entraîne une erreur si des opérations sont toujours en attente.Any attempt to commit this type of transaction while there are outstanding operations running will result in an error.

Erreurs de traitement au cours d'une transactionErrors During Transaction Processing

Si une erreur entrave le bon déroulement d'une transaction, SQL ServerSQL Server la restaure automatiquement et libère toutes les ressources bloquées par la transaction.If an error prevents the successful completion of a transaction, SQL ServerSQL Server automatically rolls back the transaction and frees all resources held by the transaction. Si la connexion réseau du client à une instance du Moteur de base de donnéesDatabase Engine 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.If the client's network connection to an instance of the Moteur de base de donnéesDatabase Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. 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éesDatabase Engine restaure toutes les transactions en cours au moment de la notification de la panne par le réseau.If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Moteur de base de donnéesDatabase Engine rolls back any outstanding connections when the network notifies it of the break. Si le client se déconnecte de l'application, toutes les transactions en cours sont restaurées.If the client logs off the application, any outstanding transactions are rolled back.

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éesDatabase Engine est de restaurer seulement l'instruction ayant généré l'erreur.If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Moteur de base de donnéesDatabase Engine is to roll back only the statement that generated the error. Vous pouvez modifier ce comportement à l'aide de l'instruction SET XACT_ABORT.You can change this behavior using the SET XACT_ABORT statement. 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.After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Les erreurs de compilation, comme les erreurs de syntaxe, ne sont pas affectées par l'option SET XACT_ABORT.Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. Pour plus d’informations, consultez SET XACT_ABORT (Transact-SQL).For more information, see 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.When errors occur, corrective action (COMMIT or ROLLBACK) should be included in application code. Est un outil efficace pour gérer les erreurs, y compris celles dans les transactions, le Transact-SQLTransact-SQL essayez... INTERCEPTER la construction.One effective tool for handling errors, including those in transactions, is the Transact-SQLTransact-SQL TRY...CATCH construct. Pour plus d’informations et d’exemples portant sur les transactions, consultez TRY...CATCH (Transact-SQL).For more information with examples that include transactions, see TRY...CATCH (Transact-SQL). À partir de SQL Server 2012SQL Server 2012, vous pouvez utiliser l’instruction THROW pour lever une exception et transférer l’exécution à un bloc CATCH d’un bloc TRY... INTERCEPTER la construction.Beginning with SQL Server 2012SQL Server 2012, you can use the THROW statement to raise an exception and transfers execution to a CATCH block of a TRY...CATCH construct. Pour plus d’informations, consultez THROW (Transact-SQL).For more information, see THROW (Transact-SQL).

Erreurs de compilation et d'exécution en mode de validation automatiqueCompile and Run-time Errors in Autocommit mode

En mode de validation automatique, il peut arriver qu'une instance du Moteur de base de donnéesDatabase Engine semble restaurer un lot entier au lieu d'une instruction SQL unique.In autocommit mode, it sometimes appears as if an instance of the Moteur de base de donnéesDatabase Engine has rolled back an entire batch instead of just one SQL statement. Ceci se produit en cas d'erreur de compilation et non en cas d'erreur d'exécution.This happens if the error encountered is a compile error, not a run-time error. Une erreur de compilation empêche le Moteur de base de donnéesDatabase Engine de créer un plan d'exécution, de telle sorte qu'aucune instruction du traitement n'est exécutée.A compile error prevents the Moteur de base de donnéesDatabase Engine from building an execution plan, so nothing in the batch is executed. Bien qu'il semble que toutes les instructions précédant celle qui a produit l'erreur soient restaurées, en réalité l'erreur rend impossible l'exécution de toutes les instructions du lot.Although it appears that all of the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. Dans l'exemple qui suit, une erreur de compilation empêche l'exécution de toutes les instructions INSERT du troisième lot.In the following example, none of the INSERT statements in the third batch are executed because of a compile error. Les deux premières instructions INSERT semblent avoir été restaurées alors qu'elles n'ont en fait jamais été exécutées.It appears that the first two INSERT statements are rolled back when they are never executed.

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.In the following example, the third INSERT statement generates a run-time duplicate primary key error. Les deux premières instructions INSERT étant correctes et validées, elles ne sont pas restaurées après l'erreur d'exécution.The first two INSERT statements are successful and committed, so they remain after the run-time error.

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éesDatabase Engine introduit la résolution de nom différée, dans laquelle la résolution des noms d’objet est effectuée au moment de l’exécution seulement.The Moteur de base de donnéesDatabase Engine uses deferred name resolution, in which object names are not resolved until execution time. 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.In the following example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.

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  

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Principes de base sur le verrouillage et le contrôle de version de ligneLocking and Row Versioning Basics

Le Moteur de base de données SQL ServerSQL Server Database Engine 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 :The Moteur de base de données SQL ServerSQL Server Database Engine uses the following mechanisms to ensure the integrity of transactions and maintain the consistency of databases when multiple users are accessing data at the same time:

  • VerrouillageLocking

    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.Each transaction requests locks of different types on the resources, such as rows, pages, or tables, on which the transaction is dependent. Les verrous demandés empêchent les autres transactions d'apporter aux ressources des modifications susceptibles de nuire à la transaction.The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock. Chaque transaction libère ses verrous lorsqu'elle ne dépend plus des ressources verrouillées.Each transaction frees its locks when it no longer has a dependency on the locked resources.

  • Contrôle de version de ligneRow versioning

    Lorsqu'un niveau d'isolement basé sur le contrôle de version de ligne est activé, le Moteur de base de donnéesDatabase Engine gère les versions de chaque ligne modifiée.When a row versioning-based isolation level is enabled, the Moteur de base de donnéesDatabase Engine maintains versions of each row that is modified. 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.Applications can specify that a transaction use the row versions to view data as it existed at the start of the transaction or query instead of protecting all reads with locks. L'utilisation du contrôle de version de ligne réduit sensiblement le risque qu'une opération de lecture bloque les autres transactions.By using row versioning, the chance that a read operation will block other transactions is greatly reduced.

    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.Locking and row versioning prevent users from reading uncommitted data and prevent multiple users from attempting to change the same data at the same time. 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.Without locking or row versioning, queries executed against that data could produce unexpected results by returning data that has not yet been committed in the database.

    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.Applications can choose transaction isolation levels, which define the level of protection for the transaction from modifications made by other transactions. Vous pouvez spécifier des indicateurs de table pour des instructions Transact-SQLTransact-SQL spécifiques en fonction des besoins de l'application.Table-level hints can be specified for individual Transact-SQLTransact-SQL statements to further tailor behavior to fit the requirements of the application.

Gestion de l'accès concurrentiel aux donnéesManaging Concurrent Data Access

Lorsque plusieurs utilisateurs accèdent à une ressource en même temps, on parle d'accès concurrentiel.Users who access a resource at the same time are said to be accessing the resource concurrently. 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.Concurrent data access requires mechanisms to prevent adverse effects when multiple users try to modify resources that other users are actively using.

Effet des accès concurrentielsConcurrency Effects

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.Users modifying data can affect other users who are reading or modifying the same data at the same time. On dit que ces utilisateurs accèdent aux données de manière concurrentielle.These users are said to be accessing the data concurrently. 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 :If a data storage system has no concurrency control, users could see the following side effects:

  • Mises à jour perduesLost updates

    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.Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Aucune transaction n'a connaissance des autres transactions.Each transaction is unaware of the other 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.The last update overwrites updates made by the other transactions, which results in lost data.

    Exemple : deux éditeurs font une copie électronique du même document.For example, two editors make an electronic copy of the same document. Chaque éditeur modifie son document et l'enregistre ensuite, en écrasant le document original.Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. Le dernier éditeur à avoir enregistré le document écrase les modifications effectuées par l'autre éditeur.The editor who saves the changed copy last overwrites the changes made by the other editor. 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.This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.

  • Dépendance non validée (lecture erronée)Uncommitted dependency (dirty read)

    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.Uncommitted dependency occurs when a second transaction selects a row that is being updated by another 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.The second transaction is reading data that has not been committed yet and may be changed by the transaction updating the row.

    Supposons par exemple qu'un éditeur effectue des modifications dans un document électronique.For example, an editor is making changes to an electronic document. 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.During the changes, a second editor takes a copy of the document that includes all the changes made so far, and distributes the document to the intended audience. Le premier éditeur décide alors que les modifications effectuées sont incorrectes, les supprime et enregistre le document.The first editor then decides the changes made so far are wrong and removes the edits and saves the 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é.The distributed document contains edits that no longer exist and should be treated as if they never existed. 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.This problem could be avoided if no one could read the changed document until the first editor does the final save of modifications and commits the transaction.

  • Analyse incohérente (lecture non reproductible)Inconsistent analysis (nonrepeatable read)

    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.Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time. 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.Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. 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.However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. 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.Also, inconsistent analysis involves multiple reads (two or more) of the same row, and each time the information is changed by another transaction; thus, the term nonrepeatable read.

    Par exemple, un éditeur relit le même document deux fois, mais l'auteur réécrit le document entre les relectures.For example, an editor reads the same document twice, but between each reading the writer rewrites the document. Lorsque l'éditeur relit le document pour la seconde fois, le document a changé.When the editor reads the document for the second time, it has changed. La relecture initiale n'est donc pas renouvelable.The original read was not repeatable. 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.This problem could be avoided if the writer could not change the document until the editor has finished reading it for the last time.

  • Lectures fantômes.Phantom reads

    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.A phantom read is a situation that occurs when two identical queries are executed and the collection of rows returned by the second query is different. L'exemple suivant montre comment cela peut arriver.The example below shows how this may occur. Supposons que les deux transactions ci-dessous s'exécutent en même temps.Assume the two transactions below are executing at the same time. 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.The two SELECT statements in the first transaction may return different results because the INSERT statement in the second transaction changes the data used by both.

    --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 ligneMissing and double reads caused by row updates

    • Manquer une ligne mise à jour ou consulter une ligne mise à jour plusieurs foisMissing a updated row or seeing an updated row multiple times

      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.Transactions that are running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. 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.Transactions that are running at the READ COMMITTED level do issue shared locks, but the row or page locks are released after the row is read. 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.In either case, when you are scanning an index, if another user changes the index key column of the row during your read, the row might appear again if the key change moved the row to a position ahead of your scan. 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.Similarly, the row might not appear if the key change moved the row to a position in the index that you had already read. Pour éviter cela, utilisez l'indicateur SERIALIZABLE ou HOLDLOCK ou le contrôle de version de ligne.To avoid this, use the SERIALIZABLE or HOLDLOCK hint, or row versioning. Pour plus d’informations, consultez Indicateurs de table (Transact-SQL).For more information, see Table Hints (Transact-SQL).

    • Manquer une ou plusieurs lignes qui n'étaient pas la cible de la mise à jourMissing one or more rows that were not the target of update

      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.When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. 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.This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits.

Types de concurrenceTypes of Concurrency

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.When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. Ce système s'appelle le contrôle de concurrence.This is called concurrency control.

La théorie du contrôle de concurrence repose sur deux méthodes de classification :Concurrency control theory has two classifications for the methods of instituting concurrency control:

  • Contrôle de concurrence pessimistePessimistic concurrency control

    Un système de verrouillage empêche les utilisateurs de modifier des données d'une manière qui affecterait les autres utilisateurs.A system of locks prevents users from modifying data in a way that affects other users. 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.After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases it. 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.This is called pessimistic control because it is mainly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

  • Contrôle de concurrence optimisteOptimistic concurrency control

    Dans le cas du contrôle de concurrence optimiste, les utilisateurs ne verrouillent pas les données quand ils les lisent.In optimistic concurrency control, users do not lock data when they read it. Lors d'une mise à jour, le système vérifie si un autre utilisateur a modifié les données après leur lecture.When a user updates data, the system checks to see if another user changed the data after it was read. Si tel est le cas, une erreur est générée.If another user updated the data, an error is raised. Généralement, l'utilisateur recevant l'erreur restaure la transaction et recommence.Typically, the user receiving the error rolls back the transaction and starts over. 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.This is called optimistic because it is mainly used in environments where there is low contention for data, and where the cost of occasionally rolling back a transaction is lower than the cost of locking data when read.

    SQL ServerSQL Server prend en charge plusieurs niveaux de contrôle de concurrence.supports a range of concurrency control. 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.Users specify the type of concurrency control by selecting transaction isolation levels for connections or concurrency options on cursors. Ces attributs peuvent être définis à l'aide des instructions Transact-SQLTransact-SQL ou des propriétés et attributs des API de base de données telles que ADO, ADO.NET, OLE DB et ODBC.These attributes can be defined using Transact-SQLTransact-SQL statements, or through the properties and attributes of database application programming interfaces (APIs) such as ADO, ADO.NET, OLE DB, and ODBC.

Niveaux d'isolement du moteur de base de donnéesIsolation Levels in the Database Engine

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.Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Les niveaux d'isolation déterminent les effets secondaires de la concurrence (lectures incorrectes, lectures fantômes) qui sont autorisés.Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

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

  • l'acquisition de verrous lors de la lecture de données, et le type de verrous nécessaires ;Whether locks are taken when data is read, and what type of locks are requested.

  • la durée de vie des verrous de lecture ;How long the read locks are held.

  • la réaction d'une opération de lecture qui fait référence à des lignes modifiées par une autre transaction :Whether a read operation referencing rows modified by another transaction:

    • blocage jusqu'à ce que le verrou exclusif sur la ligne soit levé,Blocks until the exclusive lock on the row is freed.

    • 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,Retrieves the committed version of the row that existed at the time the statement or transaction started.

    • lecture de la modification des données non validées.Reads the uncommitted data modification.

Important

Le choix d'un niveau d'isolation n'a aucune influence sur les verrous acquis pour protéger les modifications de données.Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. Une transaction acquiert toujours un verrou exclusif sur les données qu'elle modifie et garde celui-ci jusqu'à ce qu'elle ait terminé son travail, indépendamment du niveau d'isolation défini pour elle.A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. Dans le cas des opérations de lecture, le niveau d'isolation d'une transaction définit principalement son niveau de protection contre les effets des modifications apportées par les autres transactions.For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

Plus le niveau d'isolation est faible, plus le nombre de personnes susceptibles d'accéder aux données en même temps est élevé, et plus les effets secondaires de la concurrence (lectures incorrectes, mises à jour perdues) sont nombreux.A lower isolation level increases the ability of many users to access data at the same time, but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter. 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.Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another. 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.Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level. 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.The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems. 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.The lowest isolation level, read uncommitted, may retrieve data that has been modified but not committed by other 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.All of the concurrency side effects can happen in read uncommitted, but there is no read locking or versioning, so overhead is minimized.

Niveaux d'isolation du moteur de base de donnéesDatabase Engine Isolation Levels

La norme ISO définit les niveaux d'isolation suivants, tous pris en charge par le Moteur de base de données SQL ServerSQL Server Database Engine :The ISO standard defines the following isolation levels, all of which are supported by the Moteur de base de données SQL ServerSQL Server Database Engine:

Niveau d'isolementIsolation Level DéfinitionDefinition
Lecture non validéeRead uncommitted Le niveau d'isolement le plus bas et suffisant pour s'assurer que les données physiquement corrompues ne sont pas lues.The lowest isolation level where transactions are isolated only enough to ensure that physically corrupt data is not read. À 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.In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions.
Lecture validéeRead 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.Allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. Le Moteur de base de donnéesDatabase Engine 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.The Moteur de base de donnéesDatabase Engine keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed. C'est le niveau par défaut du Moteur de base de donnéesDatabase Engine.This is the Moteur de base de donnéesDatabase Engine default level.
Lecture renouvelableRepeatable read Le Moteur de base de donnéesDatabase Engine conserve les verrous de lecture et d'écriture acquis sur les données sélectionnées jusqu'à la fin de la transaction.The Moteur de base de donnéesDatabase Engine keeps read and write locks that are acquired on selected data until the end of the transaction. Cependant, étant donné que les verrous d'étendus ne sont pas gérés, des lectures fantômes peuvent se produire.However, because range-locks are not managed, phantom reads can occur.
SérialisableSerializable Niveau le plus élevé, dans lequel les transactions sont totalement isolées les unes des autres.The highest level where transactions are completely isolated from one another. Le Moteur de base de donnéesDatabase Engine 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.The Moteur de base de donnéesDatabase Engine keeps read and write locks acquired on selected data to be released at the end of the 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.Range-locks are acquired when a SELECT operation uses a ranged WHERE clause, especially to avoid phantom reads.

Remarque : Les opérations DDL et les transactions sur les tables répliquées peuvent échouer lorsque le niveau d'isolation sérialisable est demandé.Note: DDL operations and transactions on replicated tables may fail when serializable isolation level is requested. 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.This is because replication queries use hints that may be incompatible with serializable isolation level.

SQL ServerSQL Server prend également en charge deux niveaux d'isolement de la transaction supplémentaires qui utilisent le contrôle de version de ligne.also supports two additional transaction isolation levels that use row versioning. Le premier est une implémentation de l'isolement read committed, et le deuxième est un niveau d'isolement, l'instantané.One is an implementation of read committed isolation, and one is a transaction isolation level, snapshot.

Niveau d'isolement basé sur le contrôle de version de ligneRow Versioning Isolation Level DéfinitionDefinition
Instantané read committedRead Committed Snapshot 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.When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Les opérations de lecture ont besoin uniquement de verrous de table SCH-S, et pas de verrous de page ni de ligne.Read operations require only SCH-S table level locks and no page or row locks. À 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.That is, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Les verrous ne sont pas utilisés pour protéger les données des mises à jour par d'autres transactions.Locks are not used to protect the data from updates by other transactions. Une fonction définie par l'utilisateur peut retourner des données qui ont été validées après l'heure de début de l'instruction contenant cette fonction.A user-defined function can return data that was committed after the time the statement containing the UDF began.

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.When the READ_COMMITTED_SNAPSHOT database option is set OFF, which is the default setting, read committed isolation uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. 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.The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. Les deux variantes sont conformes à la définition de l'isolation read committed de l'ISO.Both implementations meet the ISO definition of read committed isolation.
SnapshotSnapshot 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.The snapshot isolation level uses row versioning to provide transaction-level read consistency. Les opérations de lecture ont besoin uniquement de verrous de table SCH-S, et pas de verrous de page ni de ligne.Read operations acquire no page or row locks; only SCH-S table locks are acquired. 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.When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started. 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.You can only use Snapshot isolation against a database when the ALLOW_SNAPSHOT_ISOLATION database option is set ON. Par défaut, cette option est désactivée (OFF) pour les bases de données utilisateur.By default, this option is set OFF for user databases.

Remarque : SQL ServerSQL Server ne prend pas en charge le contrôle de version des métadonnées.Note: SQL ServerSQL Server does not support versioning of metadata. 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é.For this reason, there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation. 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).The following DDL statements are not permitted under snapshot isolation after a BEGIN TRANSACTION statement: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or any common language runtime (CLR) DDL statement. Ces instructions sont autorisées lorsque vous avez recours à l'isolation d'instantané au sein des transactions implicites.These statements are permitted when you are using snapshot isolation within implicit transactions. 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.An implicit transaction, by definition, is a single statement that makes it possible to enforce the semantics of snapshot isolation, even with DDL statements. 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.Violations of this principle can cause error 3961: "Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. Cela est interdit, car les métadonnées sont dépourvues de version.It is not allowed because the metadata is not versioned. Une mise à jour simultanée des métadonnées peut provoquer des incohérences si elle est associée à une isolation d'instantané. »A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation."

Le tableau suivant répertorie les effets secondaires de la concurrence provoqués par les différents niveaux d'isolation.The following table shows the concurrency side effects enabled by the different isolation levels.

Niveau d'isolationIsolation level Lecture incorrecteDirty read Lecture non renouvelableNonrepeatable read FantômePhantom
Lecture non validéeRead uncommitted OuiYes OuiYes OuiYes
Lecture validéeRead committed NonNo OuiYes OuiYes
Lecture renouveléeRepeatable read NonNo NonNo OuiYes
SnapshotSnapshot NonNo NonNo NonNo
SérialisableSerializable NonNo NonNo NonNo

Pour plus d’informations sur les types spécifiques de verrouillage ou de contrôle de version de ligne contrôlés par chaque niveau d’isolation de la transaction, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).For more information about the specific types of locking or row versioning controlled by each transaction isolation level, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Les niveaux d'isolement des transactions peuvent être définis à l'aide de Transact-SQLTransact-SQL ou d'une API de base de données.Transaction isolation levels can be set using Transact-SQLTransact-SQL or through a database API.

Transact-SQLTransact-SQL
Les scripts Transact-SQLTransact-SQL utilisent l'instruction SET TRANSACTION ISOLATION LEVEL.Transact-SQLTransact-SQL scripts use the SET TRANSACTION ISOLATION LEVEL statement.

ADOADO
Les applications ADO attribuent à la propriété IsolationLevel de l’objet Connection la valeur adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead ou adXactReadSerializable.ADO applications set the IsolationLevel property of the Connection object to adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, or adXactReadSerializable.

ADO.NETADO.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.ADO.NET applications using the System.Data.SqlClient managed namespace can call the SqlConnection.BeginTransaction method and set the IsolationLevel option to Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.

OLE DBOLE 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.When starting a transaction, applications using OLE DB call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT, or ISOLATIONLEVEL_SERIALIZABLE.

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.When specifying the transaction isolation level in autocommit mode, OLE DB applications can set the DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS to DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED, or DBPROPVAL_TI_SNAPSHOT.

ODBCODBC
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.ODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE.

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.For snapshot transactions, applications call SQLSetConnectAttr with Attribute set to SQL_COPT_SS_TXN_ISOLATION and ValuePtr set to SQL_TXN_SS_SNAPSHOT. Une transaction d'instantané peut être extraite à l'aide de SQL_COPT_SS_TXN_ISOLATION ou de SQL_ATTR_TXN_ISOLATION.A snapshot transaction can be retrieved using either SQL_COPT_SS_TXN_ISOLATION or SQL_ATTR_TXN_ISOLATION.

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Verrouillage dans le moteur de base de donnéesLocking in the Database Engine

Le verrouillage est un mécanisme utilisé par le Moteur de base de données SQL ServerSQL Server Database Engine pour synchroniser l'accès simultané de plusieurs utilisateurs à la même donnée.Locking is a mechanism used by the Moteur de base de données SQL ServerSQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.

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.Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data. Pour ce faire, la transaction demande un verrou sur l'élément de données.The transaction does this by requesting a lock on the piece of data. Le verrou possède plusieurs modes, par exemple partagé ou exclusif.Locks have different modes, such as shared or exclusive. Le mode de verrouillage définit le niveau de dépendance de la transaction sur les données.The lock mode defines the level of dependency the transaction has on the data. 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.No transaction can be granted a lock that would conflict with the mode of a lock already granted on that data to another 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éesDatabase Engine suspend la transaction concernée jusqu'à ce que le premier verrou soit libéré.If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the Moteur de base de donnéesDatabase Engine will pause the requesting transaction until the first lock is released.

Lorsqu'une transaction modifie une donnée, elle conserve le verrou qui protège la modification jusqu'à la fin de la transaction.When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the 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.How long a transaction holds the locks acquired to protect read operations depends on the transaction isolation level setting. Tous les verrous conservés par une transaction sont libérés lorsque cette dernière est terminée (validée ou restaurée).All locks held by a transaction are released when the transaction completes (either commits or rolls back).

En général, les applications ne demandent pas de verrous directement.Applications do not typically request locks directly. Les verrous sont gérés en interne par une partie du Moteur de base de donnéesDatabase Engine, nommée gestionnaire de verrous.Locks are managed internally by a part of the Moteur de base de donnéesDatabase Engine called the lock manager. Lorsqu'une instance du Moteur de base de donnéesDatabase Engine traite une instruction Transact-SQLTransact-SQL, le processeur de requêtes du Moteur de base de donnéesDatabase Engine détermine les ressources qui doivent être accédées.When an instance of the Moteur de base de donnéesDatabase Engine processes a Transact-SQLTransact-SQL statement, the Moteur de base de donnéesDatabase Engine query processor determines which resources are to be accessed. 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.The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. Le processeur de requêtes demande ensuite les verrous appropriés auprès du gestionnaire de verrous.The query processor then requests the appropriate locks from the lock manager. Le gestionnaire de verrous accorde les verrous s'il n'existe aucun verrou en conflit détenu par d'autres transactions.The lock manager grants the locks if there are no conflicting locks held by other transactions.

Granularité et hiérarchie des verrousLock Granularity and Hierarchies

Le Moteur de base de données SQL ServerSQL Server Database Engine possède un verrouillage multigranulaire qui permet à différents types de ressources d'être verrouillés par une transaction.The Moteur de base de données SQL ServerSQL Server Database Engine has multigranular locking that allows different types of resources to be locked by a transaction. Pour minimiser le coût du verrouillage, le Moteur de base de donnéesDatabase Engine verrouille automatiquement les ressources au niveau approprié pour la tâche.To minimize the cost of locking, the Moteur de base de donnéesDatabase Engine locks resources automatically at a level appropriate to the task. 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.Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. 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.Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. Cependant, son coût est moindre puisque les verrous sont peu nombreux.However, it has a lower overhead because fewer locks are being maintained.

Le Moteur de base de donnéesDatabase Engine doit souvent acquérir des verrous à plusieurs niveaux de granularité pour protéger intégralement une ressource.The Moteur de base de donnéesDatabase Engine often has to acquire locks at multiple levels of granularity to fully protect a resource. Ce groupe de verrous à plusieurs niveaux de granularité est appelé « hiérarchie des verrous ».This group of locks at multiple levels of granularity is called a lock hierarchy. Par exemple, pour protéger complètement la lecture d'un index, une instance du Moteur de base de donnéesDatabase Engine devra peut-être acquérir des verrous partagés sur les lignes et des verrous partagés Intent sur les pages et la table.For example, to fully protect a read of an index, an instance of the Moteur de base de donnéesDatabase Engine may have to acquire share locks on rows and intent share locks on the pages and table.

Le tableau suivant répertorie les ressources que le Moteur de base de donnéesDatabase Engine peut verrouiller.The following table shows the resources that the Moteur de base de donnéesDatabase Engine can lock.

RessourceResource DescriptionDescription
RIDRID Identificateur de ligne utilisé pour verrouiller une seule ligne dans un segment de mémoire.A row identifier used to lock a single row within a heap.
KEYKEY Verrou de ligne dans un index utilisé pour protéger des groupes de clés dans les transactions susceptibles d'être sérialisées.A row lock within an index used to protect key ranges in serializable transactions.
PAGEPAGE Page de 8 kilo-octets (Ko) dans une base de données, par exemple des pages de données ou d'index.An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENTEXTENT Groupe contigu de huit pages, par exemple des pages de données ou d'index.A contiguous group of eight pages, such as data or index pages.
HoBTHoBT Segment de mémoire ou arbre B (B-tree).A heap or 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.A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.
TABLETABLE Table complète comprenant tous les index et toutes les données.The entire table, including all data and indexes.
FILEFILE Fichier de base de données.A database file.
APPLICATIONAPPLICATION Ressource spécifiée par une application.An application-specified resource.
METADATAMETADATA Verrous des métadonnées.Metadata locks.
ALLOCATION_UNITALLOCATION_UNIT Unité d'allocation.An allocation unit.
DATABASEDATABASE Base de données complète.The entire database.

Note

Les verrous HoBT et TABLE peuvent être affectés par l’option LOCK_ESCALATION de l’instruction ALTER TABLE.HoBT and TABLE locks can be affected by the LOCK_ESCALATION option of ALTER TABLE.

Modes de verrouillageLock Modes

Le Moteur de base de données SQL ServerSQL Server Database Engine 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.The Moteur de base de données SQL ServerSQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.

Le tableau suivant illustre les modes de verrouillage des ressources utilisés par le Moteur de base de donnéesDatabase Engine.The following table shows the resource lock modes that the Moteur de base de donnéesDatabase Engine uses.

Mode de verrouillageLock mode DescriptionDescription
Partagé (S)Shared (S) Utilisé pour les opérations de lecture qui n'effectuent aucune modification ou mise à jour des données, par exemple une instruction SELECT.Used for read operations that do not change or update data, such as a SELECT statement.
Mise à jour (U)Update (U) Utilisé pour les ressources pouvant être mises à jour.Used on resources that can be updated. Empêche une forme de blocage courante qui se produit lorsque plusieurs sessions lisent, verrouillent et mettent à jour des ressources ultérieurement.Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusif (X)Exclusive (X) Utilisé par les opérations de modification de données, telles que INSERT, UPDATE ou DELETE.Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Empêche des mises à jour multiples sur la même ressource au même moment.Ensures that multiple updates cannot be made to the same resource at the same time.
IntentionnelIntent Permet d'établir une hiérarchie de verrouillage.Used to establish a lock hierarchy. Les types de verrouillage intentionnels sont les suivants : les verrous de partage intentionnel (IS), d'exclusion intentionnelle (IX) et de partage intentionnel exclusif (SIX).The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
schémaSchema Utilisé lors de l'exécution d'une opération associée au schéma d'une table.Used when an operation dependent on the schema of a table is executing. Les types de verrouillage de schéma sont les suivant : la stabilité du schéma (Sch-S) et la modification du schéma (Sch-M).The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Mise à jour en bloc (BU)Bulk Update (BU) Utilisé lorsque en bloc copie des données dans une table et le TABLOCK indicateur est spécifié.Used when bulk copying data into a table and the TABLOCK hint is specified.
Verrou de cléKey-range Protège la plage de lignes lue par une requête lorsque le niveau d'isolation des transactions SERIALIZABLE est utilisé.Protects the range of rows read by a query when using the serializable transaction isolation level. 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.Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Verrous partagésShared Locks

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.Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. Aucune autre transaction ne peut modifier les données de la ressource tant que des verrous partagés (S) existent sur la ressource.No other transactions can modify the data while shared (S) locks exist on the resource. 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.Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

Verrous de mise à jourUpdate Locks

Les verrous de mise à jour (U) empêchent une forme fréquente de blocage.Update (U) locks prevent a common form of deadlock. 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).In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. 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).If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. 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.The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. La deuxième transaction impliquée essaie d'acquérir un verrou exclusif (X) pour sa mise à jour.The second transaction attempts to acquire an exclusive (X) lock for its update. 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.Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

Les verrous de mise à jour (U) permettent de résoudre les problèmes de blocage.To avoid this potential deadlock problem, update (U) locks are used. Une seule transaction à la fois peut obtenir un verrou de mise à jour (U) pour une ressource.Only one transaction can obtain an update (U) lock to a resource at a time. Si une transaction modifie une ressource, le verrou de mise à jour (U) est converti en verrou exclusif (X).If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.

Verrous exclusifsExclusive Locks

Les verrous exclusifs (X) empêchent l'accès à une ressource par des transactions simultanées.Exclusive (X) locks prevent access to a resource by concurrent transactions. 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 ».With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.

Les instructions qui modifient les données telles que INSERT, UPDATE et DELETE combinent des opérations de modification et de lecture.Data modification statements, such as INSERT, UPDATE, and DELETE combine both modification and read operations. Elles commencent par les opérations de lecture pour obtenir les données, puis elles effectuent les opérations de modification.The statement first performs read operations to acquire data before performing the required modification operations. Par conséquent, les instructions qui modifient les données demandent généralement à la fois des verrous partagés et des verrous exclusifs.Data modification statements, therefore, typically request both shared locks and exclusive locks. Ainsi, une instruction UPDATE peut modifier les lignes d'une table en fonction d'une jointure avec une autre table.For example, an UPDATE statement might modify rows in one table based on a join with another 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.In this case, the UPDATE statement requests shared locks on the rows read in the join table in addition to requesting exclusive locks on the updated rows.

Verrous intentionnelsIntent Locks

Le Moteur de base de donnéesDatabase Engine utilise des verrous intentionnels pour protéger le placement de verrous partagés (S) ou exclusifs (X) sur une ressource hiérarchiquement inférieure.The Moteur de base de donnéesDatabase Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. 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.Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

Les verrous intentionnels ont deux fonctions :Intent locks serve two purposes:

  • Empêcher les autres transactions de modifier la ressource de niveau supérieur de façon à invalider le verrou au niveau inférieur.To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.

  • Améliorer l’efficacité du Moteur de base de donnéesDatabase Engine en matière de détection de conflits de verrous au plus haut niveau de granularité.To improve the efficiency of the Moteur de base de donnéesDatabase Engine in detecting lock conflicts at the higher level of granularity.

    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.For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that 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.Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Les verrous intentionnels améliorent les performances, car le Moteur de base de donnéesDatabase Engine 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.Intent locks improve performance because the Moteur de base de donnéesDatabase Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that 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.This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

    Les verrous intentionnels comprennent les verrous de partage intentionnel (IS), d'exclusion intentionnelle (IX), et de partage intentionnel exclusif (SIX).Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Mode de verrouillageLock mode DescriptionDescription
Intent partagé (IS)Intent shared (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.Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.
Intent exclusif (IX)Intent exclusive (IX) Protège les verrous exclusifs demandés ou acquis sur certaines ressources (mais pas toutes) de niveau inférieur dans la hiérarchie.Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX est un sur-ensemble du mode IS qui protège également les demandes de verrou partagé sur des ressources de niveau inférieur.IX is a superset of IS, and it also protects requesting shared locks on lower level resources.
Partagé avec intent exclusif (SIX)Shared with intent exclusive (SIX) Protège les verrous partagés (S) demandés ou acquis sur toutes les ressources de niveau inférieur dans la hiérarchie et les verrous d'exclusion intentionnelle sur certaines ressources de niveau inférieur (mais pas toutes).Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. Les lectures simultanées sur les ressources de niveau supérieur sont autorisées.Concurrent IS locks at the top-level resource are allowed. 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.For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. 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.There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.
Mise à jour intentionnelle (IU)Intent update (IU) Protège les verrous de mise à jour demandés ou acquis sur toutes les ressources de niveau inférieur dans la hiérarchie.Protects requested or acquired update locks on all resources lower in the hierarchy. Les verrous IU sont utilisés uniquement sur les ressources de page.IU locks are used only on page resources. Les verrous IU sont convertis en verrous IX si une opération de mise à jour a lieu.IU locks are converted to IX locks if an update operation takes place.
Mise à jour intentionnelle partagée (SIU)Shared intent update (SIU) Combinaison de verrous S et IU résultant de l'acquisition séparée de ces verrous et de leur gestion simultanée.A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. Par exemple, une transaction peut exécuter une requête avec l'indicateur PAGLOCK, puis une opération de mise à jour.For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. La requête contenant l'indicateur PAGLOCK obtient le verrou S et l'opération de mise à jour obtient le verrou IU.The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.
Mise à jour intentionnelle exclusive (UIX)Update intent 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.A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

Verrous de schémaSchema Locks

Le Moteur de base de donnéesDatabase Engine utilise les verrous de modification de schémas (Sch-M) quand une opération de langage de définition de données (DDL, Data Definition Language) est effectuée sur une table (ajout d’une colonne ou suppression d’une table, par exemple).The Moteur de base de donnéesDatabase Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. Pendant le temps de sa détention, le verrou Sch-M empêche les accès simultanés à la table.During the time that it is held, the Sch-M lock prevents concurrent access to the table. Cela signifie que le verrou Sch-M bloque toutes les opérations externes jusqu'à ce que le verrou soit libéré.This means the Sch-M lock blocks all outside operations until the lock is released.

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.Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.

Le Moteur de base de donnéesDatabase Engine utilise les verrous de stabilité de schéma (Sch-S) lors de la compilation et l’exécution des requêtes.The Moteur de base de donnéesDatabase Engine uses schema stability (Sch-S) locks when compiling and executing queries. Les verrous Sch-S ne bloquent aucun verrou transactionnel, verrous exclusifs (X) y compris.Sch-S locks do not block any transactional locks, including exclusive (X) locks. 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.Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. 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.However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.

Verrous de mise à jour en bloc (BU)Bulk Update Locks

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.Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table. Le Moteur de base de donnéesDatabase Engine utilise des verrous BU lorsque les deux conditions suivantes sont vraies.The Moteur de base de donnéesDatabase Engine uses bulk update (BU) locks when both of the following conditions are true.

  • 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.You use the Transact-SQL BULK INSERT statement, or the OPENROWSET(BULK) function, or you use one of the Bulk Insert API commands such as .NET SqlBulkCopy, OLEDB Fast Load APIs, or the ODBC Bulk Copy APIs to bulk copy data into a table.

  • L’indicateur TABLOCK est spécifié ou l’option de table table lock on bulk load est définie à l’aide de sp_tableoption.The TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption.

Conseil

Contrairement à l’instruction BULK INSERT, qui maintient un verrou de mise à jour en bloc moins restrictif, INSERT INTO...SELECT avec l’indicateur TABLOCK maintient un verrou exclusif (X) sur la table.Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO...SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. Cela signifie que vous ne pouvez pas insérer de lignes à l'aide d'opérations d'insertion parallèles.This means that you cannot insert rows using parallel insert operations.

Verrous de clésKey-Range Locks

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-SQLTransact-SQL lors de l'utilisation du niveau d'isolement des transactions sérialisable.Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQLTransact-SQL statement while using the serializable transaction isolation level. Le verrouillage d'étendues de clés empêche les lectures fantômes.Key-range locking prevents phantom reads. 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.By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

Compatibilité de verrouillageLock Compatibility

La compatibilité de verrouillage détermine si plusieurs transactions peuvent simultanément acquérir des verrous sur la même ressource.Lock compatibility controls whether multiple transactions can acquire locks on the same resource at the same time. 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.If a resource is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. 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é.If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire. Par exemple, aucun mode de verrou n'est compatible avec les verrous exclusifs.For example, no lock modes are compatible with exclusive locks. 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é.While an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released. 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.Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item even if the first transaction has not completed. Toutefois, les autres transactions ne peuvent pas acquérir un verrou exclusif tant que le verrou partagé n'a pas été libéré.However, other transactions cannot acquire an exclusive lock until the shared lock has been released.

Le tableau suivant décrit la compatibilité des modes de verrou les plus courants.The following table shows the compatibility of the most commonly encountered lock modes.

Mode accordé existantExisting granted mode
Mode requisRequested mode ISIS SS UU IXIX SIXSIX XX
Intent partagé (IS)Intent shared (IS) OuiYes OuiYes OuiYes OuiYes OuiYes NonNo
Partagé (S)Shared (S) OuiYes OuiYes OuiYes NonNo NonNo NonNo
Mise à jour (U)Update (U) OuiYes OuiYes NonNo NonNo NonNo NonNo
Intent exclusif (IX)Intent exclusive (IX) OuiYes NonNo NonNo OuiYes NonNo NonNo
Partagé avec intent exclusif (SIX)Shared with intent exclusive (SIX) OuiYes NonNo NonNo NonNo NonNo NonNo
Exclusif (X)Exclusive (X) NonNo NonNo NonNo NonNo NonNo NonNo

Note

Un verrou intent exclusif (IX) est compatible avec un mode de verrouillage IX car IX signifie l'intention de mettre à jour uniquement certaines lignes et non pas toutes les lignes.An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. Les autres transactions qui essaient de lire ou de mettre à jour certaines lignes sont aussi autorisées si elles ne mettent pas à jour les lignes en cours de mise à jour par les autres transactions.Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions. De plus, si deux transactions essaient de mettre à jour la même ligne, un verrou IX sera accordé aux deux transactions au niveau de la table et de la page.Further, if two transactions attempt to update the same row, both transactions will be granted an IX lock at table and page level. Toutefois, un verrou X sera accordé à une transaction au niveau de la ligne.However, one transaction will be granted an X lock at row level. L'autre transaction doit attendre jusqu'à ce que le verrouillage au niveau de la ligne soit supprimé.The other transaction must wait until the row-level lock is removed.

Utilisez le tableau suivant pour déterminer la compatibilité de tous les modes de verrou disponibles dans SQL ServerSQL Server.Use the following table to determine the compatibility of all the lock modes available in SQL ServerSQL Server.

Matrice de compatibilité de verrouillage diagramme montrantDiagram showing lock compatibility matrix

Verrouillage d'étendues de clésKey-Range Locking

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-SQLTransact-SQL lors de l'utilisation du niveau d'isolement des transactions sérialisable.Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQLTransact-SQL statement while using the serializable transaction isolation level. 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.The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the 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.A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.

Le verrouillage d'étendues de clés empêche les lectures fantômes.Key-range locking prevents phantom reads. 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.By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.

Un verrou d'étendues de clés est placé sur un index, spécifiant une valeur de clé de début et de fin.A key-range lock is placed on an index, specifying a beginning and ending key value. 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.This lock blocks any attempt to insert, update, or delete any row with a key value that falls in the range because those operations would first have to acquire a lock on the 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 '.For example, a serializable transaction could issue a SELECT statement that reads all rows whose key values are between ' AAA ' and ' CZZ '. Un verrou de groupes de clés sur les valeurs de clés comprises entre ' AAA ' et ' CZZ ' empêche les autres transactions d’insérer des lignes possédant des valeurs de clés comprises dans ce groupe, telles que ' ADG ', ' BBD ' ou ' CAL '.A key-range lock on the key values in the range from ' AAA ' to ' CZZ ' prevents other transactions from inserting rows with key values anywhere in that range, such as ' ADG ', ' BBD ', or ' CAL '.

Modes de verrouillage d'étendues de clésKey-Range Lock Modes

Les verrous d'étendues de clés comprennent un composant étendue et un composant ligne, au format étendue-ligne :Key-range locks include both a range and a row component specified in range-row format:

  • L'étendue représente le mode de verrouillage protégeant l'étendue entre deux entrées d'index successives.Range represents the lock mode protecting the range between two consecutive index entries.

  • La ligne représente le mode de verrouillage protégeant l'entrée de l'index.Row represents the lock mode protecting the index entry.

  • Le mode représente la combinaison de modes de verrouillage utilisée.Mode represents the combined lock mode used. Les modes de verrouillage d'étendues de clés comportent deux parties.Key-range lock modes consist of two parts. La première représente le type de verrou utilisé pour verrouiller l’étendue d’index (RangeT) et la deuxième représente le type de verrou utilisé pour verrouiller une clé spécifique (K).The first represents the type of lock used to lock the index range (RangeT) and the second represents the lock type used to lock a specific key (K). Les deux parties sont reliées par un tiret (-), par exemple RangeT-K.The two parts are connected with a hyphen (-), such as RangeT-K.

    PlageRange LigneRow ModeMode DescriptionDescription
    RangeSRangeS SS RangeS-SRangeS-S Verrou de ressource partagé, étendue partagée ; analyse de plage sérialisable.Shared range, shared resource lock; serializable range scan.
    RangeSRangeS UU RangeS-URangeS-U Verrou de mise à jour de ressource, étendue partagée ; analyse d'étendue sérialisable.Shared range, update resource lock; serializable update scan.
    RangeIRangeI NullNull RangeI-NRangeI-N Verrou de ressource NULL, étendue d'insertion ; utilisé pour tester les étendues avant l'insertion d'une nouvelle clé dans un index.Insert range, null resource lock; used to test ranges before inserting a new key into an index.
    RangeXRangeX XX RangeX-XRangeX-X Verrou de ressource exclusif, étendue exclusive ; utilisé lors de la mise à jour d'une clé dans une étendue.Exclusive range, exclusive resource lock; used when updating a key in a range.

Note

Le mode interne de verrouillage Null est compatible avec tous les autres modes de verrouillage.The internal Null lock mode is compatible with all other lock modes.

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.Key-range lock modes have a compatibility matrix that shows which locks are compatible with other locks obtained on overlapping keys and ranges.

Mode accordé existantExisting granted mode
Mode requisRequested mode SS UU XX RangeS-SRangeS-S RangeS-URangeS-U RangeI-NRangeI-N RangeX-XRangeX-X
Partagé (S)Shared (S) OuiYes OuiYes NonNo OuiYes OuiYes OuiYes NonNo
Mise à jour (U)Update (U) OuiYes NonNo NonNo OuiYes NonNo OuiYes NonNo
Exclusif (X)Exclusive (X) NonNo NonNo NonNo NonNo NonNo OuiYes NonNo
RangeS-SRangeS-S OuiYes OuiYes NonNo OuiYes OuiYes NonNo NonNo
RangeS-URangeS-U OuiYes NonNo NonNo OuiYes NonNo NonNo NonNo
RangeI-NRangeI-N OuiYes OuiYes OuiYes NonNo NonNo OuiYes NonNo
RangeX-XRangeX-X NonNo NonNo NonNo NonNo NonNo NonNo NonNo

Verrous de conversionConversion Locks

Les verrous de conversion sont créés lorsqu'un verrou d'étendue de clés chevauche un autre verrou.Conversion locks are created when a key-range lock overlaps another lock.

Verrou 1Lock 1 Verrou 2Lock 2 Verrou de conversionConversion lock
SS RangeI-NRangeI-N RangeI-SRangeI-S
UU RangeI-NRangeI-N RangeI-URangeI-U
XX RangeI-NRangeI-N RangeI-XRangeI-X
RangeI-NRangeI-N RangeS-SRangeS-S RangeX-SRangeX-S
RangeI-NRangeI-N RangeS-URangeS-U RangeX-URangeX-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.Conversion locks can be observed for a short period of time under different complex circumstances, sometimes while running concurrent processes.

Analyse d'étendue sérialisable, extraction singleton, suppression et insertionSerializable Range Scan, Singleton Fetch, Delete, and Insert

Le verrouillage d'étendues de clés permet la sérialisation des opérations suivantes :Key-range locking ensures that the following operations are serializable:

  • Requête d'analyse d'étendueRange scan query

  • Extraction singleton de ligne inexistanteSingleton fetch of nonexistent row

  • Opération de suppressionDelete operation

  • Opération d'insertionInsert operation

    Les conditions suivantes doivent être satisfaites pour qu'un verrouillage d'étendues de clés puisse se produire :Before key-range locking can occur, the following conditions must be satisfied:

  • Le niveau d'isolement de la transaction doit être défini sur SERIALIZABLE.The transaction-isolation level must be set to SERIALIZABLE.

  • Le processeur de requêtes doit utiliser un index pour implémenter le prédicat de filtre de l'étendue.The query processor must use an index to implement the range filter predicate. Par exemple, la clause WHERE dans une instruction SELECT peut établir une condition d'étendue avec le prédicat suivant : ColonneX BETWEEN N AAA AND N CZZ .For example, the WHERE clause in a SELECT statement could establish a range condition with this predicate: ColumnX BETWEEN N ' AAA ' AND N ' CZZ '. Un verrou de groupes de clés ne peut être acquis que si ColumnX est couvert par une clé d’index.A key-range lock can only be acquired if ColumnX is covered by an index key.

ExemplesExamples

La table et l'index suivants sont utilisés comme base pour les exemples de verrouillage d'étendues de clés ci-dessous.The following table and index are used as a basis for the key-range locking examples that follow.

Table de base de données avec illustration de b-tree indexDatabase table with index b-tree illustration

Requête d'analyse d'étendueRange Scan Query

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.To ensure a range scan query is serializable, the same query should return the same results each time it is executed within the same 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.New rows must not be inserted within the range scan query by other transactions; otherwise, these become phantom inserts. Par exemple, la requête suivante utilise la table et l'index de l'illustration précédente :For example, the following query uses the table and index in the previous illustration:

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.Key-range locks are placed on the index entries corresponding to the range of data rows where the name is between the values Adam and Dale, preventing new rows qualifying in the previous query from being added or deleted. 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.Although the first name in this range is Adam, the RangeS-S mode key-range lock on this index entry ensures that no new names beginning with the letter A can be added before Adam, such as 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.Similarly, the RangeS-S key-range lock on the index entry for Dale ensures that no new names beginning with the letter C can be added after Carlos, such as Clive.

Note

Le nombre de verrous RangeS-S maintenus est n+1, où n est le nombre de lignes répondant aux critères de la requête.The number of RangeS-S locks held is n+1, where n is the number of rows that satisfy the query.

Extraction d'un singleton de données non existantesSingleton Fetch of Nonexistent Data

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.If a query within a transaction attempts to select a row that does not exist, issuing the query at a later point within the same transaction has to return the same result. Aucune autre transaction ne peut être autorisée à insérer cette ligne inexistante.No other transaction can be allowed to insert that nonexistent row. Supposons par exemple la requête suivante :For example, given this query:

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.A key-range lock is placed on the index entry corresponding to the name range from Ben to Bing because the name Bill would be inserted between these two adjacent index entries. Le verrou d'étendues de clés du mode RangeS-S est placé sur l'entrée d'index Bing.The RangeS-S mode key-range lock is placed on the index entry Bing. Ceci empêche toute autre transaction d'insérer des valeurs, telles que Bill, entre les entrées d'index Ben et Bing.This prevents any other transaction from inserting values, such as Bill, between the index entries Ben and Bing.

Opération de suppressionDelete Operation

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.When deleting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the delete operation. Le verrouillage de la valeur de clé supprimée jusqu'à la fin de la transaction est suffisant pour assurer la sérialisation.Locking the deleted key value until the end of the transaction is sufficient to maintain serializability. Par exemple, pour l'instruction DELETE suivante :For example, given this DELETE statement:

DELETE mytable  
    WHERE name = 'Bob';  

Un verrou exclusif (X) est placé sur l'entrée d'index correspondant au nom Bob.An exclusive (X) lock is placed on the index entry corresponding to the name Bob. Les autres transactions peuvent insérer ou supprimer des valeurs avant ou après la valeur effacée Bob.Other transactions can insert or delete values before or after the deleted value 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.However, any transaction that attempts to read, insert, or delete the value Bob will be blocked until the deleting transaction either commits or rolls back.

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.Range delete can be executed using three basic lock modes: row, page, or table lock. 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.The row, page, or table locking strategy is decided by query optimizer or can be specified by the user through optimizer hints such as ROWLOCK, PAGLOCK, or TABLOCK. Lorsque l'option PAGLOCK ou TABLOCK est utilisée, le Moteur de base de donnéesDatabase Engine désalloue immédiatement une page d'index page si toutes les lignes qu'elle contient sont supprimées.When PAGLOCK or TABLOCK is used, the Moteur de base de donnéesDatabase Engine immediately deallocates an index page if all rows are deleted from this page. 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.In contrast, when ROWLOCK is used, all deleted rows are marked only as deleted; they are removed from the index page later using a background task.

Opération d'insertionInsert Operation

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.When inserting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the insert operation. Le verrouillage de la valeur de clé jusqu'à la fin de la transaction suffit pour assurer la sérialisation.Locking the inserted key value until the end of the transaction is sufficient to maintain serializability. Par exemple, étant donné l'instruction INSERT suivante :For example, given this INSERT statement:

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.The RangeI-N mode key-range lock is placed on the index entry corresponding to the name David to test the range. Si le verrou est accordé, la valeur Dan est insérée et un verrou exclusif (X) est placé sur la valeur Dan.If the lock is granted, Dan is inserted and an exclusive (X) lock is placed on the value 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.The RangeI-N mode key-range lock is necessary only to test the range and is not held for the duration of the transaction performing the insert operation. D'autres transactions peuvent insérer ou supprimer des valeurs avant ou après la valeur Dan insérée.Other transactions can insert or delete values before or after the inserted value Dan. 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.However, any transaction attempting to read, insert, or delete the value Dan will be locked until the inserting transaction either commits or rolls back.

Verrouillage dynamiqueDynamic Locking

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.Using low-level locks, such as row locks, increases concurrency by decreasing the probability that two transactions will request locks on the same piece of data at the same time. L'utilisation de verrous de bas niveau augmente également le nombre de verrous et les ressources nécessaires à leur gestion.Using low-level locks also increases the number of locks and the resources needed to manage them. Les verrous de table ou de page de haut niveau réduisent la charge mais au détriment de la concurrence.Using high-level table or page locks lowers overhead, but at the expense of lowering concurrency.

Diagramme montrant les coûts par rapport à la granularitéDiagram showing cost versus granularity

Le MicrosoftMicrosoft Moteur de base de données SQL ServerSQL Server Database Engine utilise une stratégie de verrouillage dynamique pour déterminer les verrous les plus rentables.The MicrosoftMicrosoft Moteur de base de données SQL ServerSQL Server Database Engine uses a dynamic locking strategy to determine the most cost-effective locks. Lorsqu'une requête est exécutée, le Moteur de base de donnéesDatabase Engine détermine automatiquement les verrous les plus appropriés sur la base des caractéristiques du schéma et de la requête.The Moteur de base de donnéesDatabase Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query. 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.For example, to reduce the overhead of locking, the optimizer may choose page-level locks in an index when performing an index scan.

Le verrouillage dynamique offre les avantages suivants :Dynamic locking has the following advantages:

  • Administration de bases de données simplifiée.Simplified database administration. Les administrateurs de la base de données ne doivent pas ajuster les seuils d'escalade des verrous.Database administrators do not have to adjust lock escalation thresholds.

  • Performances améliorées.Increased performance. Le Moteur de base de donnéesDatabase Engine réduit la charge sur le système en utilisant les verrous appropriés pour la tâche.The Moteur de base de donnéesDatabase Engine minimizes system overhead by using locks appropriate to the task.

  • Les développeurs d'applications peuvent se concentrer sur le développement.Application developers can concentrate on development. Le Moteur de base de donnéesDatabase Engine adapte le verrouillage automatiquement.The Moteur de base de donnéesDatabase Engine adjusts locking automatically.

    Dans SQL Server 2008SQL Server 2008 et versions ultérieures, le comportement d’escalade de verrous a changé avec l’introduction de l’option LOCK_ESCALATION.In SQL Server 2008SQL Server 2008 and later versions, the behavior of lock escalation has changed with the introduction of the LOCK_ESCALATION option. Pour plus d’informations, consultez l’option LOCK_ESCALATION de ALTER TABLE.For more information, see the LOCK_ESCALATION option of ALTER TABLE.

InterblocageDeadlocking

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.A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. Exemple :For example:

  • La transaction A obtient un verrou partagé sur la ligne 1.Transaction A acquires a share lock on row 1.

  • La transaction B obtient un verrou partagé sur la ligne 2.Transaction B acquires a share lock on row 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.Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 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.Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 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. Il s'agit d'une 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.Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

    Les deux transactions en interblocage attendent indéfiniment que la situation soit débloquée par un processus externe.Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. Le moniteur d’interblocage du Moteur de base de données SQL ServerSQL Server Database Engine recherche périodiquement les tâches d’interblocage.The Moteur de base de données SQL ServerSQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. 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.If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. Cela permet à l'autre tâche de terminer sa transaction.This allows the other task to complete its 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.The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

    L'interblocage est souvent confondu avec le blocage ordinaire.Deadlocking is often confused with normal blocking. Lorsqu'une transaction demande un verrou sur une ressource verrouillée par une autre transaction, elle attend que le verrou soit libéré.When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. Par défaut, les transactions SQL ServerSQL Server n'ont pas de délai d'expiration, à moins que LOCK_TIMEOUT ne soit activé.By default, SQL ServerSQL Server transactions do not time out, unless LOCK_TIMEOUT is set. 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.The requesting transaction is blocked, not deadlocked, because the requesting transaction has not done anything to block the transaction owning the lock. 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.Eventually, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and proceed.

    Les interblocages sont parfois appelés « étreintes fatales ».Deadlocks are sometimes called a deadly embrace.

    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.Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. Par exemple, un thread dans un système multithread peut acquérir une ou plusieurs ressources (par exemple, des blocs de mémoire).For example, a thread in a multithreaded operating system might acquire one or more resources, such as blocks of memory. 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.If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. le thread en attente a une dépendance sur le thread propriétaire de cette ressource particulière.The waiting thread is said to have a dependency on the owning thread for that particular resource. Dans une instance du Moteur de base de donnéesDatabase Engine, les sessions peuvent se bloquer lors de l’acquisition de ressources autres que des objets de base de données, notamment des blocs de mémoire ou des threads.In an instance of the Moteur de base de donnéesDatabase Engine, sessions can deadlock when acquiring nondatabase resources, such as memory or threads.

    Diagramme montrant le blocage transactionDiagram showing transaction deadlock

    Dans l’illustration, la transaction T1 est dépendante de la transaction T2 pour la ressource de verrou de table Part.In the illustration, transaction T1 has a dependency on transaction T2 for the Part table lock resource. De même, la transaction T2 est dépendante de T1 pour la ressource de verrou de table Supplier.Similarly, transaction T2 has a dependency on transaction T1 for the Supplier table lock resource. Comme ces dépendances forment un cycle, il y a interblocage entre les transactions T1 et T2.Because these dependencies form a cycle, there is a deadlock between transactions T1 and 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.Deadlocks can also occur when a table is partitioned and the LOCK_ESCALATION setting of ALTER TABLE is set to AUTO. Lorsque LOCK_ESCALATION a la valeur automatique, la concurrence augmente en permettant la Moteur de base de donnéesDatabase Engine de verrouiller des partitions de table au niveau du HoBT au lieu d’au niveau de la TABLE.When LOCK_ESCALATION is set to AUTO, concurrency increases by allowing the Moteur de base de donnéesDatabase Engine to lock table partitions at the HoBT level instead of at the TABLE level. 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.However, when separate transactions hold partition locks in a table and want a lock somewhere on the other transactions partition, this causes a deadlock. Ce type d’interblocage peut être évité en affectant à LOCK_ESCALATION pour la 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.This type of deadlock can be avoided by setting LOCK_ESCALATION to TABLE; although this setting will reduce concurrency by forcing large updates to a partition to wait for a table lock.

Détection et fin des blocagesDetecting and Ending Deadlocks

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.A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. Le graphique suivant présente un aperçu d'un état de blocage où :The following graph presents a high level view of a deadlock state where:

  • 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).Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to 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).Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to 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.Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

    Diagramme affichant des tâches dans un état de blocageDiagram showing tasks in a deadlock state

    Le Moteur de base de données SQL ServerSQL Server Database Engine détecte automatiquement les cycles de blocage dans SQL ServerSQL Server.The Moteur de base de données SQL ServerSQL Server Database Engine automatically detects deadlock cycles within SQL ServerSQL Server. Le Moteur de base de donnéesDatabase Engine 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.The Moteur de base de donnéesDatabase Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

Ressources susceptibles de se bloquerResources That Can Deadlock

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.Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. Les types de ressources susceptibles de provoquer un blocage sont les suivants :The following types of resources can cause blocking that could result in a deadlock.

  • Verrous.Locks. L'attente d'obtention de verrous sur des ressources, telles qu'objets, pages, lignes, métadonnées et applications peut provoquer un blocage.Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. 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.For example, transaction T1 has a shared (S) lock on row r1 and is waiting to get an exclusive (X) lock on r2. La transaction T2 a un verrou partagé (S) sur r2 et elle attend d'obtenir un verrou exclusif (X) sur la ligne r1.Transaction T2 has a shared (S) lock on r2 and is waiting to get an exclusive (X) lock on row 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.This results in a lock cycle in which T1 and T2 wait for each other to release the locked resources.

  • Threads de travail.Worker threads. Une tâche en attente d'un thread de travail disponible peut provoquer un blocage.A queued task waiting for an available worker thread can cause deadlock. 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.If the queued task owns resources that are blocking all worker threads, a deadlock will result. 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.For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. 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.Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row 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.Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. Cela produit un blocage.This results in a deadlock.

  • Mémoire.Memory. 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.When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. 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.For example, two concurrent queries, Q1 and Q2, execute as user-defined functions that acquire 10MB and 20MB of memory respectively. 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.If each query needs 30MB and the total available memory is 20MB, then Q1 and Q2 must wait for each other to release memory, and this results in a deadlock.

  • 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 interblocage qui se produit généralement lors de l’introduction d’au moins un autre processus étranger à la requête parallèle.Parallel query execution-related resources Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. De même, quand commence l'exécution d'une requête parallèle, SQL ServerSQL Server détermine le degré de parallélisme, ou le nombre de threads de travail, en fonction de la charge de travail en cours.Also, when a parallel query starts execution, SQL ServerSQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. 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.If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.

  • Ressources MARS (Multiple Active Result Sets).Multiple Active Result Sets (MARS) resources. Ces ressources servent à contrôler l'entrelacement de plusieurs demandes actives sous MARS.These resources are used to control interleaving of multiple active requests under MARS. Pour plus d’informations, consultez Multiple Active Result Sets (MARS) dans SQL Server.For more information, see Multiple Active Result Sets (MARS) in SQL Server.

    • Ressource utilisateur.User resource. 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.When a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock.

    • Exclusion mutuelle de session.Session mutex. 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.The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. Avant de pouvoir s'exécuter, la tâche doit disposer d'un accès exclusif à l'exclusion mutuelle de la session.Before the task can run, it must have exclusive access to the session mutex.

    • Exclusion mutuelle de transaction.Transaction mutex. 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.All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. Avant de pouvoir s'exécuter, la tâche doit disposer d'un accès exclusif à l'exclusion mutuelle de la transaction.Before the task can run, it must have exclusive access to the transaction mutex.

      Pour pouvoir s'exécuter sous MARS, une tâche doit obtenir l'exclusion mutuelle de session.In order for a task to run under MARS, it must acquire the session mutex. Si la tâche s'exécute dans le cadre d'une transaction, elle doit obtenir l'exclusion mutuelle de transaction.If the task is running under a transaction, it must then acquire the transaction mutex. 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.This guarantees that only one task is active at one time in a given session and a given transaction. Dès lors que les exclusions mutuelles requises ont été acquises, la tâche peut s'exécuter.Once the required mutexes have been acquired, the task can execute. 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.When the task finishes, or yields in the middle of the request, it will first release transaction mutex followed by the session mutex in reverse order of acquisition. Cependant, des blocages peuvent se produire avec ces ressources.However, deadlocks can occur with these resources. 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.In the following code example, two tasks, user request U1 and user request U2, are running in the same 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.The stored procedure executing from user request U1 has acquired the session mutex. Si son exécution se prolonge, le Moteur de base de donnéesDatabase Engine considère que la procédure stockée attend une entrée de données de la part de l'utilisateur.If the stored procedure takes a long time to execute, it is assumed by the Moteur de base de donnéesDatabase Engine that the stored procedure is waiting for input from the user. 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.User request U2 is waiting for the session mutex while the user is waiting for the result set from U2, and U1 is waiting for a user resource. Il s'agit d'un état de blocage logiquement illustré ainsi :This is deadlock state logically illustrated as:

    Blocage de processus d’utilisateur d’affichage Diagramme logique. Logic diagram showing user process deadlock.

Détection de blocageDeadlock Detection

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éesDatabase Engine.All of the resources listed in the section above participate in the Moteur de base de donnéesDatabase Engine deadlock detection scheme. 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éesDatabase Engine.Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Moteur de base de donnéesDatabase Engine. Le processus de recherche présente les caractéristiques suivantes :The following points describe the search process:

  • L'intervalle par défaut est de 5 secondes.The default interval is 5 seconds.

  • 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.If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.

  • Si le thread de contrôle des verrous ne détecte plus de blocages, le Moteur de base de donnéesDatabase Engine refera passer l'intervalle de recherche à 5 secondes.If the lock monitor thread stops finding deadlocks, the Moteur de base de donnéesDatabase Engine increases the intervals between searches to 5 seconds.

  • 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.If a deadlock has just been detected, it is assumed that the next threads that must wait for a lock are entering the deadlock cycle. 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.The first couple of lock waits after a deadlock has been detected will immediately trigger a deadlock search rather than wait for the next deadlock detection interval. 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.For example, if the current interval is 5 seconds, and a deadlock was just detected, the next lock wait will kick off the deadlock detector immediately. 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.If this lock wait is part of a deadlock, it will be detected right away rather than during next deadlock search.

    En règle générale, le Moteur de base de donnéesDatabase Engine n'opère qu'une détection de blocage périodique.The Moteur de base de donnéesDatabase Engine typically performs periodic deadlock detection only. 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.Because the number of deadlocks encountered in the system is usually small, periodic deadlock detection helps to reduce the overhead of deadlock detection in the system.

    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.When the lock monitor initiates deadlock search for a particular thread, it identifies the resource on which the thread is waiting. 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.The lock monitor then finds the owner(s) for that particular resource and recursively continues the deadlock search for those threads until it finds a cycle. Un cycle identifié de cette manière forme un blocage.A cycle identified in this manner forms a deadlock.

    Dès lors qu'un blocage est détecté, le Moteur de base de donnéesDatabase Engine met fin à un blocage en choisissant l'un des threads comme victime.After a deadlock is detected, the Moteur de base de donnéesDatabase Engine ends a deadlock by choosing one of the threads as a deadlock victim. Le Moteur de base de donnéesDatabase Engine 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.The Moteur de base de donnéesDatabase Engine terminates the current batch being executed for the thread, rolls back the transaction of the deadlock victim, and returns a 1205 error to the 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.Rolling back the transaction for the deadlock victim releases all locks held by the transaction. Cela permet aux transactions des autres threads de se débloquer et de continuer.This allows the transactions of the other threads to become unblocked and continue. 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.The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.

    Par défaut, le Moteur de base de donnéesDatabase Engine choisit comme victime du blocage la session qui exécute la transaction dont l'annulation est la moins coûteuse.By default, the Moteur de base de donnéesDatabase Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. Un utilisateur peut également spécifier la priorité des sessions dans une situation de blocage au moyen de l'instruction SET DEADLOCK_PRIORITY.Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. DEADLOCK_PRIORITY accepte les valeurs LOW, NORMAL ou HIGH, voire toute valeur entière comprise entre -10 et 10.DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). La valeur par défaut de la priorité de blocage est NORMAL.The deadlock priority defaults to 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.If two sessions have different deadlock priorities, the session with the lower priority is chosen as the deadlock victim. 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.If both sessions have the same deadlock priority, the session with the transaction that is least expensive to roll back is chosen. 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.If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly.

    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.When working with CLR, the deadlock monitor automatically detects deadlock for synchronization resources (monitors, reader/writer lock and thread join) accessed inside managed procedures. 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.However, the deadlock is resolved by throwing an exception in the procedure that was selected to be the deadlock victim. 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.It is important to understand that the exception does not automatically release resources currently owned by the victim; the resources must be explicitly released. Conformément au comportement des exceptions, l'exception utilisée pour identifier une victime de blocage peut être interceptée et annulée.Consistent with exception behavior, the exception used to identify a deadlock victim can be caught and dismissed.

Outils d'information sur les blocagesDeadlock Information Tools

Pour afficher les informations sur le blocage, le Moteur de base de donnéesDatabase Engine fournit des outils de surveillance sous la forme de deux indicateurs de trace, ainsi que l'événement Deadlock Graph dans SQL Server ProfilerSQL Server Profiler.To view deadlock information, the Moteur de base de donnéesDatabase Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server ProfilerSQL Server Profiler.

Indicateur de trace 1204 et indicateur de trace 1222Trace Flag 1204 and Trace Flag 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 ServerSQL Server.When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL ServerSQL Server error log. L'indicateur de trace 1024 signale les informations de blocage mises en forme par chaque nœud impliqué dans le blocage.Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. L'indicateur de trace 1222 met en forme les informations de blocage, en commençant par les processus et en poursuivant avec les ressources.Trace flag 1222 formats deadlock information, first by processes and then by resources. Il est possible d'activer deux indicateurs de trace pour obtenir deux représentations du même événement de blocage.It is possible to enable both trace flags to obtain two representations of the same deadlock event.

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.In addition to defining the properties of trace flag 1204 and 1222, the following table also shows the similarities and differences.

PropriétéProperty Indicateur de trace 1204 et indicateur de trace 1222Trace Flag 1204 and Trace Flag 1222 Indicateur de trace 1204 uniquementTrace Flag 1204 only Indicateur de trace 1222 uniquementTrace Flag 1222 only
Format de sortieOutput format La sortie est capturée dans le journal des erreurs de SQL ServerSQL Server.Output is captured in the SQL ServerSQL Server error log. Les nœuds impliqués dans le blocage sont privilégiés.Focused on the nodes involved in the deadlock. Chaque nœud dispose d'une section dédiée, tandis que la section finale décrit la victime du blocage.Each node has a dedicated section, and the final section describes the deadlock victim. Retourne des informations dans un format de type XML, mais non conforme au schéma XSD (XML Schema Definition).Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. Le format possède trois sections principales.The format has three major sections. La première déclare la victime du blocage.The first section declares the deadlock victim. La deuxième décrit chaque processus impliqué dans le blocage.The second section describes each process involved in the deadlock. La troisième décrit les ressources synonymes des nœuds de l'indicateur de trace 1204.The third section describes the resources that are synonymous with nodes in trace flag 1204.
Identification d'attributsIdentifying attributes SPID :<x > ECID :<x >.SPID:<x> ECID:<x>. Identifie le thread de l'ID du processus système en cas de traitements parallèles.Identifies the system process ID thread in cases of parallel processes. L’entrée SPID:<x> ECID:0, où <x > est remplacé par la valeur SPID, représente le thread principal.The entry SPID:<x> ECID:0, where <x> is replaced by the SPID value, represents the main thread. L’entrée SPID:<x> ECID:<y>, où <x > est remplacé par la valeur SPID et <y > est supérieur à 0, représente les sous-threads du même SPID.The entry SPID:<x> ECID:<y>, where <x> is replaced by the SPID value and <y> is greater than 0, represents the sub-threads for the same SPID.

BatchID (sbid pour l’indicateur de trace 1222).BatchID (sbid for trace flag 1222). Identifie le traitement à partir duquel l'exécution du code demande ou détient un verrou.Identifies the batch from which code execution is requesting or holding a lock. Lorsque MARS (Multiple Active Result Sets) est désactivé, la valeur BatchID est 0.When Multiple Active Result Sets (MARS) is disabled, the BatchID value is 0. Quand MARS est activé, la valeur des lots actifs est 1 pour n.When MARS is enabled, the value for active batches is 1 to n. Si la session ne comporte pas de traitements actifs, BatchID a pour valeur 0.If there are no active batches in the session, BatchID is 0.

Mode.Mode. Spécifie, pour une ressource particulière, le type de verrou demandé, accordé ou attendu par un thread.Specifies the type of lock for a particular resource that is requested, granted, or waited on by a 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).Mode can be IS (Intent Shared), S (Shared), U (Update), IX (Intent Exclusive), SIX (Shared with Intent Exclusive), and X (Exclusive).

Line # (line pour l’indicateur de trace 1222).Line # (line for trace flag 1222). Indique le numéro de ligne du traitement qui était en cours d'exécution lorsque le blocage s'est produit.Lists the line number in the current batch of statements that was being executed when the deadlock occurred.

Input Buf (inputbuf pour l’indicateur de trace 1222).Input Buf (inputbuf for trace flag 1222). Dresse la liste de toutes les instructions du traitement en cours.Lists all the statements in the current batch.
Node.Node. Il s'agit du numéro d'entrée dans la chaîne de blocage.Represents the entry number in the deadlock chain.

Lists.Lists. Le propriétaire du verrou peut faire partie des listes suivantes :The lock owner can be part of these lists:

Grant List.Grant List. Énumère les propriétaires actuels de la ressource.Enumerates the current owners of the resource.

Convert List.Convert List. Énumère les propriétaires en cours qui essaient de convertir leurs verrous vers un niveau supérieur.Enumerates the current owners that are trying to convert their locks to a higher level.

Wait List.Wait List. Énumère les nouvelles demandes de verrou en cours pour la ressource.Enumerates current new lock requests for the resource.

Statement Type.Statement Type. Décrit le type d'instructions DML (SELECT, INSERT, UPDATE ou DELETE) sur lesquelles les threads disposent d'autorisations.Describes the type of DML statement (SELECT, INSERT, UPDATE, or DELETE) on which the threads have permissions.

Victim Resource Owner.Victim Resource Owner. Spécifie le thread choisi comme victime par SQL ServerSQL Server pour rompre le cycle de blocage.Specifies the participating thread that SQL ServerSQL Server chooses as the victim to break the deadlock cycle. Il est alors mis fin au thread choisi et à tous les sous-threads existants.The chosen thread and all existing sub-threads are terminated.

Next Branch.Next Branch. Représente les deux sous-threads (ou plus) du même SPID qui participent au cycle de blocage.Represents the two or more sub-threads from the same SPID that are involved in the deadlock cycle.
deadlock victim.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 de l’interblocage.Represents the physical memory address of the task (see sys.dm_os_tasks (Transact-SQL)) that was selected as a deadlock victim. Elle est égale à 0 (zéro) en cas de non résolution du blocage.It may be 0 (zero) in the case of an unresolved deadlock. Une tâche en cours d'annulation ne peut pas être choisie comme victime de blocage.A task that is rolling back cannot be chosen as a deadlock victim.

executionstack.executionstack. Représente le code Transact-SQLTransact-SQL en cours d'exécution lorsque le blocage se produit.Represents Transact-SQLTransact-SQL code that is being executed at the time the deadlock occurs.

priority.priority. Représente la priorité de blocage.Represents deadlock priority. Dans certains cas, le Moteur de base de donnéesDatabase Engine peut choisir de modifier la priorité de blocage pendant un bref laps de temps afin de favoriser la concurrence.In certain cases, the Moteur de base de donnéesDatabase Engine may opt to alter the deadlock priority for a short duration to achieve better concurrency.

logused.logused. Espace journal utilisé par la tâche.Log space used by the task.

owner id. ID de la transaction qui contrôle la demande.owner id. The ID of the transaction that has control of the request.

status.status. État de la tâche.State of the task. Il prend l'une des valeurs suivantes :It is one of the following values:

>> pending.>> pending. En attente d'un thread de travail.Waiting for a worker thread.

>> runnable.>> runnable. Prêt à s'exécuter, mais en attente d'un quantum.Ready to run but waiting for a quantum.

>> running.>> running. En cours d'exécution sur le planificateur.Currently running on the scheduler.

>> suspended.>> suspended. L'exécution est suspendue.Execution is suspended.

>> done.>> done. La tâche est achevée.Task has completed.

>> spinloop.>> spinloop. En attente de libération d'un spinlock.Waiting for a spinlock to become free.

waitresource.waitresource. Ressource convoitée par la tâche.The resource needed by the task.

waittime.waittime. Délai d'attente de la ressource en millisecondes.Time in milliseconds waiting for the resource.

schedulerid.schedulerid. Planificateur associé à cette tâche.Scheduler associated with this task. Consultez sys.dm_os_schedulers (Transact-SQL).See sys.dm_os_schedulers (Transact-SQL).

hostname.hostname. Nom de la station de travail.The name of the workstation.

isolationlevel.isolationlevel. Niveau d'isolement des transactions en cours.The current transaction isolation level.

Xactid.Xactid. ID de la transaction qui contrôle la demande.The ID of the transaction that has control of the request.

currentdb.currentdb. ID de la base de données.The ID of the database.

lastbatchstarted.lastbatchstarted. Dernière fois qu'un processus client a démarré une exécution de traitement.The last time a client process started batch execution.

lastbatchcompleted.lastbatchcompleted. Dernière fois qu'un processus client a terminé une exécution de traitement.The last time a client process completed batch execution.

clientoption1 et clientoption2.clientoption1 and clientoption2. Options définies pour cette connexion cliente.Set options on this client connection. 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.This is a bitmask that includes information about options usually controlled by SET statements such as SET NOCOUNT and SET XACTABORT.

associatedObjectId.associatedObjectId. Représente l'ID HoBT (Heap or B-tree, segment de mémoire ou arborescence binaire).Represents the HoBT (heap or b-tree) ID.
Attributs des ressourcesResource attributes RID.RID. Identifie la ligne d'une table pour laquelle un verrou est détenu ou demandé.Identifies the single row within a table on which a lock is held or requested. RID est représenté comme RID : db_id:file_id:page_no:row_no.RID is represented as RID: db_id:file_id:page_no:row_no. Par exemple, RID: 6:1:20789:0.For example, RID: 6:1:20789:0.

OBJECT.OBJECT. Identifie la table pour laquelle un verrou est détenu ou demandé.Identifies the table on which a lock is held or requested. OBJECT est représenté comme OBJECT : db_id:object_id.OBJECT is represented as OBJECT: db_id:object_id. Par exemple, TAB: 6:2009058193.For example, TAB: 6:2009058193.

KEY.KEY. Identifie la plage de clés d'un index pour laquelle un verrou est détenu ou demandé.Identifies the key range within an index on which a lock is held or requested. KEY est représenté comme KEY : db_id:hobt_id (valeur de hachage de la clé d’index).KEY is represented as KEY: db_id:hobt_id (index key hash value). Par exemple, KEY: 6:72057594057457664 (350007a4d329).For example, KEY: 6:72057594057457664 (350007a4d329).

PAG.PAG. Identifie la ressource de page pour laquelle un verrou est détenu ou demandé.Identifies the page resource on which a lock is held or requested. PAG est représenté comme PAG : db_id:file_id:page_no.PAG is represented as PAG: db_id:file_id:page_no. Par exemple, PAG: 6:1:20789.For example, PAG: 6:1:20789.

EXT.EXT. Identifie la structure d'extension.Identifies the extent structure. EXT est représenté comme EXT : db_id:file_id:extent_no.EXT is represented as EXT: db_id:file_id:extent_no. Par exemple, EXT: 6:1:9.For example, EXT: 6:1:9.

DB.DB. Identifie le verrou de base de données.Identifies the database lock. DB est représenté de l’une des manières suivantes :DB is represented in one of the following ways:

DB : db_idDB: 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-DB], which identifies the database lock taken by the backup database.

DB : db_id[BULK-OP-LOG], qui identifie le verrou pris par le journal de sauvegarde pour cette base de données spécifique.DB: db_id[BULK-OP-LOG], which identifies the lock taken by the backup log for that particular database.

APP.APP. Identifie le verrou pris par une ressource d'application.Identifies the lock taken by an application resource. APP est représenté comme APP : lock_resource.APP is represented as APP: lock_resource. Par exemple, APP: Formf370f478.For example, APP: Formf370f478.

METADATA.METADATA. Représente les ressources de métadonnées impliquées dans un blocage.Represents metadata resources involved in a deadlock. Comme METADATA possède de nombreuses sous-ressources, la valeur retournée dépend de la sous-ressource bloquée.Because METADATA has many subresources, the value returned depends upon the subresource that has deadlocked. Par exemple, les métadonnées. Retourne USER_TYPE user_type_id = < integer_value>.For example, METADATA.USER_TYPE returns user_type_id = <integer_value>. Pour plus d’informations sur les ressources et sous-ressources METADATA, consultez sys.dm_tran_locks (Transact-SQL).For more information about METADATA resources and subresources, see sys.dm_tran_locks (Transact-SQL).

HOBT.HOBT. Représente un segment de mémoire ou d'arbre B (B-Tree) impliqué dans un blocage.Represents a heap or b-tree involved in a deadlock.
Non exclusif à cet indicateur de trace.None exclusive to this trace flag. Non exclusif à cet indicateur de trace.None exclusive to this trace flag.
Exemple d'indicateur de trace 1204Trace Flag 1204 Example

L'exemple suivant illustre la sortie obtenue quand l'indicateur de trace 1204 est activé.The following example shows the output when trace flag 1204 is turned on. 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.In this case, the table in Node 1 is a heap with no indexes, and the table in Node 2 is a heap with a nonclustered index. La clé d'index du nœud 2 est en cours de mise à jour lorsque le blocage se produit.The index key in Node 2 is being updated when the deadlock occurs.

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 1222Trace Flag 1222 Example

L'exemple suivant illustre la sortie obtenue quand l'indicateur de trace 1222 est activé.The following example shows the output when trace flag 1222 is turned on. 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.In this case, one table is a heap with no indexes, and the other table is a heap with a nonclustered index. Dans la seconde table, la clé d'index est en cours de mise à jour lorsque le blocage se produit.In the second table, the index key is being updated when the deadlock occurs.

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 ProfilerProfiler Deadlock Graph Event

Il s'agit d'un événement propre au SQL Server ProfilerSQL Server Profiler qui présente une description graphique des tâches et des ressources impliquées dans un blocage.This is an event in SQL Server ProfilerSQL Server Profiler that presents a graphical depiction of the tasks and resources involved in a deadlock. L'exemple suivant illustre la sortie obtenue à partir de SQL Server ProfilerSQL Server Profiler quand l'événement Deadlock Graph est activé.The following example shows the output from SQL Server ProfilerSQL Server Profiler when the deadlock graph event is turned on.

Blocage de processus logique organigramme montrant utilisateur. Logic flow diagram showing user process deadlock.

Pour plus d’informations sur l’exécution du SQL Server ProfilerSQL Server Profiler deadlock graph, consultez enregistrer les événements Deadlock Graph (SQL Server Profiler).For more information about running the SQL Server ProfilerSQL Server Profiler deadlock graph, see Save Deadlock Graphs (SQL Server Profiler).

Gestion des blocagesHandling Deadlocks

Quand une instance du Moteur de base de données SQL ServerSQL Server Database Engine choisit une transaction comme victime d’un interblocage, elle met fin au lot en cours, annule la transaction, puis retourne le message d’erreur 1205 à l’application.When an instance of the Moteur de base de données SQL ServerSQL Server Database Engine chooses a transaction as a deadlock victim, it terminates the current batch, rolls back the transaction, and returns error message 1205 to the 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-SQLTransact-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.Because any application submitting Transact-SQLTransact-SQL queries can be chosen as the deadlock victim, applications should have an error handler that can trap error message 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.If an application does not trap the error, the application can proceed unaware that its transaction has been rolled back and errors can occur.

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.Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation and take remedial action (for example, automatically resubmitting the query that was involved in the deadlock). Cette nouvelle soumission automatique rend la gestion du blocage entièrement transparente pour l'utilisateur.By resubmitting the query automatically, the user does not need to know that a deadlock occurred.

L'application doit marquer un bref temps d'arrêt avant de soumettre à nouveau la requête.The application should pause briefly before resubmitting its query. 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.This gives the other transaction involved in the deadlock a chance to complete and release its locks that formed part of the deadlock cycle. Les risques qu'un blocage se reproduise au moment où la requête de nouveau soumise demande ses verrous sont ainsi réduits.This minimizes the likelihood of the deadlock reoccurring when the resubmitted query requests its locks.

Réduction des blocagesMinimizing Deadlocks

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.Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. 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 :Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

  • restaurées, en annulant ce qui a été accompli par la transaction ;Rolled back, undoing all the work performed by the transaction.

  • resoumises par les applications, car ces transactions ont été restaurées lors du blocage.Resubmitted by applications because they were rolled back when deadlocked.

    Pour réduire le nombre de blocages :To help minimize deadlocks:

  • Accédez aux objets dans le même ordre.Access objects in the same order.

  • Évitez les interactions utilisateur dans les transactions.Avoid user interaction in transactions.

  • Créez des transactions courtes dans le même traitement.Keep transactions short and in one batch.

  • Utilisez un niveau d'isolement plus faible.Use a lower isolation level.

  • Utilisez un niveau d'isolement basé sur le contrôle de version de ligne.Use a row versioning-based isolation level.

    • 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.Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.

    • Utilisez un isolement d'instantané.Use snapshot isolation.

  • Utilisez des connexions liées.Use bound connections.

Accès aux objets dans le même ordreAccess Objects in the Same Order

Si toutes les transactions concurrentes accèdent aux objets dans le même ordre, le risque de blocage diminue.If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. 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.For example, if two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. Après la validation ou la restauration de la première transaction, la seconde continue et aucun blocage ne se produit.After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. L'utilisation de procédures stockées pour toutes les modifications de données peut standardiser l'ordre d'accès aux objets.Using stored procedures for all data modifications can standardize the order of accessing objects.

Diagramme montrant les éviter les verrous mortelsDiagram showing deadlock avoidance

Aucune interaction utilisateur dans les transactionsAvoid User Interaction in 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.Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an 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.For example, if a transaction is waiting for user input and the user goes to lunch or even home for the weekend, the user delays the transaction from completing. 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.This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. 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.Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked while waiting for the transaction to complete.

Transactions courtes dans un seul traitementKeep Transactions Short and in One Batch

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.A deadlock typically occurs when several long-running transactions execute concurrently in the same database. 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.The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.

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.Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.

Niveau d'isolement faibleUse a Lower Isolation Level

Déterminez si une transaction peut être exécutée à un niveau d'isolement faible.Determine whether a transaction can run at a lower isolation level. 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.Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. 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)Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. et réduit ainsi la contention de verrouillage.This reduces locking contention.

Niveau d'isolement basé sur le contrôle de version de ligneUse a Row Versioning-based Isolation Level

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.When the READ_COMMITTED_SNAPSHOT database option is set ON, a transaction running under read committed isolation level uses row versioning rather than shared locks during read operations.

Note

Certaines applications se basent sur le comportement de verrouillage et de blocage de l'isolement de lecture validée.Some applications rely upon locking and blocking behavior of read committed isolation. Avec ces applications, certaines modifications sont nécessaires pour pouvoir activer cette option.For these applications, some change is required before this option can be enabled.

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.Snapshot isolation also uses row versioning, which does not use shared locks during read operations. 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.Before a transaction can run under snapshot isolation, the ALLOW_SNAPSHOT_ISOLATION database option must be set ON.

Implémentez ces niveaux d'isolement pour minimiser les blocages pouvant survenir entre les opérations de lecture et d'écriture.Implement these isolation levels to minimize deadlocks that can occur between read and write operations.

Connexions liéesUse Bound Connections

En utilisant des connexions liées, deux connexions ou plus ouvertes par la même application peuvent coopérer entre elles.Using bound connections, two or more connections opened by the same application can cooperate with each other. Tout verrou acquis par la connexion secondaire apparaît comme s'il avait été posé par la connexion primaire et vice-versa.Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Ils ne se bloquent donc pas réciproquement.Therefore they do not block each other.

Partitionnement de verrousLock Partitioning

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.For large computer systems, locks on frequently referenced objects can become a performance bottleneck as acquiring and releasing locks place contention on internal locking resources. Le partitionnement de verrous améliore les performances du verrouillage en fractionnant une ressource de verrou en plusieurs.Lock partitioning enhances locking performance by splitting a single lock resource into multiple lock resources. 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.This feature is only available for systems with 16 or more CPUs, and is automatically enabled and cannot be disabled. Seuls les verrous d'objets peuvent être partitionnés. Les verrous d'objets dotés d'un sous-type ne sont pas partitionnés.Only object locks can be partitioned.Object locks that have a subtype are not partitioned. Pour plus d’informations, consultez sys.dm_tran_locks (Transact-SQL).For more information, see sys.dm_tran_locks (Transact-SQL).

Présentation du partitionnement de verrousUnderstanding Lock Partitioning

Les opérations de verrouillage accèdent à plusieurs ressources partagées, dont deux sont optimisées par le partitionnement de verrous :Locking tasks access several shared resources, two of which are optimized by lock partitioning:

  • Verrouillage tournant.Spinlock. Contrôle l'accès à une ressource de verrou, par exemple une ligne ou une table.This controls access to a lock resource, such as a row or a table.

    Sans partitionnement de verrous, un verrouillage spinlock gère toutes les demandes de verrouillage pour une seule ressource de verrou.Without lock partitioning, one spinlock manages all lock requests for a single lock resource. 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.On systems that experience a large volume of activity, contention can occur as lock requests wait for the spinlock to become available. Dans ce cas, l'acquisition de verrous peut causer un goulet d'étranglement et détériorer les performances.Under this situation, acquiring locks can become a bottleneck and can negatively impact performance.

    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.To reduce contention on a single lock resource, lock partitioning splits a single lock resource into multiple lock resources to distribute the load across multiple spinlocks.

  • Mémoire.Memory. Permet de stocker les structures des ressources de verrous.This is used to store the lock resource structures.

    Une fois le verrouillage spinlock acquis, les structures des verrous sont stockées dans la mémoire, puis utilisées et éventuellement modifiées.Once the spinlock is acquired, lock structures are stored in memory and then accessed and possibly modified. 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.Distributing lock access across multiple resources helps to eliminate the need to transfer memory blocks between CPUs, which will help to improve performance.

Mise en œuvre et surveillance du partitionnement de verrousImplementing and Monitoring Lock Partitioning

Le partitionnement de verrous est activé par défaut pour les systèmes comportant 16 UC ou plus.Lock partitioning is turned on by default for systems with 16 or more CPUs. Quand il est activé, un message d'informations est inscrit dans le journal des erreurs de SQL ServerSQL Server.When lock partitioning is enabled, an informational message is recorded in the SQL ServerSQL Server error log.

Lors de l'acquisition de verrous sur une ressource partitionnée :When acquiring locks on a partitioned resource:

  • Seuls les modes de verrouillage NL, SCH-S, IS, IU et IX sont acquis sur une seule partition.Only NL, SCH-S, IS, IU, and IX lock modes are acquired on a single 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.Shared (S), exclusive (X), and other locks in modes other than NL, SCH-S, IS, IU, and IX must be acquired on all partitions starting with partition ID 0 and following in partition ID order. 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.These locks on a partitioned resource will use more memory than locks in the same mode on a non-partitioned resource since each partition is effectively a separate lock. La quantité de mémoire en plus est déterminée par le nombre de partitions.The memory increase is determined by the number of partitions. Les compteurs de verrous SQL ServerSQL 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.The SQL ServerSQL Server lock counters in the Windows Performance Monitor will display information about memory used by partitioned and non-partitioned locks.

    Une transaction est affectée à une partition au début de la transaction.A transaction is assigned to a partition when the transaction starts. Pour la transaction, toutes les demandes de verrou qui peuvent être partitionnés utilisent la partition attribuée à cette transaction.For the transaction, all lock requests that can be partitioned use the partition assigned to that 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.By this method, access to lock resources of the same object by different transactions is distributed across different 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.The resource_lock_partition column in the sys.dm_tran_locks Dynamic Management View provides the lock partition ID for a lock partitioned resource. Pour plus d’informations, consultez sys.dm_tran_locks (Transact-SQL).For more information, see sys.dm_tran_locks (Transact-SQL).

    Sous l'événement Locks dans SQL Server ProfilerSQL Server Profiler, la colonne BigintData1 fournit l'ID de partition d'une ressource de verrou partitionnée.Under the Locks event in SQL Server ProfilerSQL Server Profiler, the BigintData1 column provides the lock partition ID for a lock partitioned resource.

Utilisation du partitionnement de verrousWorking with Lock Partitioning

Les exemples de code suivants illustrent le partitionnement de verrous :The following code examples illustrate lock partitioning. 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.In the examples, two transactions are executed in two different sessions in order to show lock partitioning behavior on a computer system with 16 CPUs.

Ces instructions Transact-SQLTransact-SQL créent des objets de test logiques qui sont utilisés dans les exemples qui suivent.These Transact-SQLTransact-SQL statements create test objects that are used in the examples that follow.

-- 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 AExample A

Session 1 :Session 1:

Une instruction SELECT est exécutée sous une transaction.A SELECT statement is executed under a 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).Because of the HOLDLOCK lock hint, this statement will acquire and retain an Intent shared (IS) lock on the table (for this illustration, row and page locks are ignored). Le verrou IS sera acquis uniquement sur la partition attribuée à la transaction.The IS lock will be acquired only on the partition assigned to the transaction. Pour cet exemple, il est supposé que le verrou IS est acquis sur l'ID de partition 7.For this example, it is assumed that the IS lock is acquired on 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 :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.A transaction is started, and the SELECT statement running under this transaction will acquire and retain a shared (S) lock on the table. Le verrou S sera acquis sur toutes les partitions, ce qui aboutit à plusieurs verrous de table, un pour chaque partition.The S lock will be acquired on all partitions which results in multiple table locks, one for each partition. Par exemple, sur un système à 16 UC, 16 verrous S seront créés sur les ID de partition de 0 à 15.For example, on a 16-cpu system, 16 S locks will be issued across lock partition IDs 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.Because the S lock is compatible with the IS lock being held on partition ID 7 by the transaction in session 1, there is no blocking between transactions.

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

Session 1 :Session 1:

L'instruction SELECT suivante est exécutée sous la transaction qui est encore active sous la session 1.The following SELECT statement is executed under the transaction that is still active under 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.Because of the exclusive (X) table lock hint, the transaction will attempt to acquire an X lock on the 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. However, the S lock that is being held by the transaction in session 2 will block the X lock at partition ID 0.

SELECT col1  
    FROM TestTable  
    WITH (TABLOCKX);  
Exemple BExample B

Session 1 :Session 1:

Une instruction SELECT est exécutée sous une transaction.A SELECT statement is executed under a 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).Because of the HOLDLOCK lock hint, this statement will acquire and retain an Intent shared (IS) lock on the table (for this illustration, row and page locks are ignored). Le verrou IS sera acquis uniquement sur la partition attribuée à la transaction.The IS lock will be acquired only on the partition assigned to the transaction. Pour cet exemple, on suppose que le verrou IS est acquis sur la partition ID 6.For this example, it is assumed that the IS lock is acquired on 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 :Session 2:

Une instruction SELECT est exécutée sous une transaction.A SELECT statement is executed under a transaction. En raison de l'indicateur de verrou TABLOCKX, la transaction essaie d'acquérir un verrou exclusif (X) sur la table.Because of the TABLOCKX lock hint, the transaction tries to acquire an exclusive (X) lock on the table. Souvenez-vous que le verrou X doit être acquis sur toutes les partitions à partir de la partition ID 0.Remember that the X lock must be acquired on all partitions starting with 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. The X lock will be acquired on all partitions IDs 0-5 but will be blocked by the IS lock that is acquired on 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.On partition IDs 7-15 that the X lock has not yet reached, other transactions can continue to acquire locks.

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

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Niveaux d’isolement basé sur le contrôle de version de ligne dans le moteur de base de donnéesRow Versioning-based Isolation Levels in the Database Engine

À 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.Starting with SQL Server 2005, the Database Engine offers an implementation of an existing transaction isolation level, read committed, that provides a statement level snapshot using row versioning. 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.SQL Server Database Engine also offers a transaction isolation level, snapshot, that provides a transaction level snapshot also using row versioning.

Le contrôle de version de ligne est une infrastructure générale de SQL ServerSQL Server qui appelle un mécanisme de copie sur écriture lorsqu'une ligne est modifiée ou supprimée.Row versioning is a general framework in SQL ServerSQL Server that invokes a copy-on-write mechanism when a row is modified or deleted. 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.This requires that while the transaction is running, the old version of the row must be available for transactions that require an earlier transactionally consistent state. Le contrôle de version de ligne est utilisé pour la prise en charge des fonctionnalités suivantes :Row versioning is used to do the following:

  • Génération des tables insérées et supprimées dans les déclencheurs.Build the inserted and deleted tables in triggers. Toutes les lignes modifiées par le déclencheur reçoivent une version,Any rows modified by the trigger are versioned. 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.This includes the rows modified by the statement that launched the trigger, as well as any data modifications made by the trigger.

  • Prise en charge des ensembles de résultats actifs multiples (MARS, Multiple Active Result Sets).Support Multiple Active Result Sets (MARS). 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.If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) at a time there is an active result set, the rows affected by the modification statement are versioned.

  • Prise en charge des opérations d'index qui spécifient l'option ONLINE.Support index operations that specify the ONLINE option.

  • Prise en charge des niveaux d'isolement des transactions basés sur le contrôle de version de ligne :Support row versioning-based transaction isolation levels:

    • 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.A new implementation of read committed isolation level that uses row versioning to provide statement-level read consistency.

    • Un nouveau niveau d'isolement, l'instantané, pour assurer la cohérence de la lecture au niveau de la transaction.A new isolation level, snapshot, to provide transaction-level read consistency.

    La base de données tempdb doit avoir suffisamment d’espace pour contenir la banque des versions.The tempdb database must have enough space for the version store. Quand la base de données tempdb est pleine, les opérations de mise à jour ne génèrent plus de versions et continuent d’aboutir, mais les opérations de lecture risquent d’échouer en raison de l’absence d’une version de ligne particulière requise.When tempdb is full, update operations will stop generating versions and continue to succeed, but read operations might fail because a particular row version that is needed no longer exists. Ceci a des conséquences sur les opérations comme les déclencheurs, MARS et l'indexation en ligne.This affects operations like triggers, MARS, and online indexing.

    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 :Using row versioning for read-committed and snapshot transactions is a two-step process:

  1. Activez (ON) l'option de base de données READ_COMMITTED_SNAPSHOT et/ou l'option ALLOW_SNAPSHOT_ISOLATION.Set either or both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options ON.

  2. Définissez le niveau d'isolement des transactions approprié dans une application :Set the appropriate transaction isolation level in an 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.When the READ_COMMITTED_SNAPSHOT database option is ON, transactions setting the read committed isolation level use row versioning.

    • 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é.When the ALLOW_SNAPSHOT_ISOLATION database option is ON, transactions can set the snapshot isolation level.

    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 ServerSQL Server Database Engine 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.When either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is set ON, the Moteur de base de données SQL ServerSQL Server Database Engine assigns a transaction sequence number (XSN) to each transaction that manipulates data using row versioning. Les transactions démarrent au moment où une instruction BEGIN TRANSACTION est exécutée.Transactions start at the time a BEGIN TRANSACTION statement is executed. 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.However, the transaction sequence number starts with the first read or write operation after the BEGIN TRANSACTION statement. Ce numéro augmente de 1 à chaque fois qu'il est attribué.The transaction sequence number is incremented by one each time it is assigned.

    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.When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Chaque fois qu’une ligne est modifiée par une transaction, l’instance du Moteur de base de donnéesDatabase Engine enregistre une version de l’image précédemment validée de la ligne dans tempdb.Every time a row is modified by a specific transaction, the instance of the Moteur de base de donnéesDatabase Engine stores a version of the previously committed image of the row in tempdb. Chaque version porte le numéro de séquence de la transaction responsable de la modification.Each version is marked with the transaction sequence number of the transaction that made the change. Les versions des lignes modifiées sont enchaînées au moyen d'une liste de liens.The versions of modified rows are chained using a link list. La valeur de ligne la plus récente est toujours stockée dans la base de données active et enchaînée aux lignes avec contrôle de version stockées dans tempdb.The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

Note

Dans le cas de la modification d’objets LOB, seul le fragment modifié est copié dans la banque des versions dans tempdb.For modification of large objects (LOBs), only the changed fragment is copied to the version store in 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 ».Row versions are held long enough to satisfy the requirements of transactions running under row versioning-based isolation levels. Le Moteur de base de donnéesDatabase Engine 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.The Moteur de base de donnéesDatabase Engine tracks the earliest useful transaction sequence number and periodically deletes all row versions stamped with transaction sequence numbers that are lower than the earliest useful sequence number.

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.When both database options are set to OFF, only rows modified by triggers or MARS sessions, or read by ONLINE index operations, are versioned. Ces versions de lignes sont libérées lorsqu'elles ne sont plus nécessaires.Those row versions are released when no longer needed. Un thread d'arrière-plan supprime périodiquement les versions de lignes dépassées.A background thread periodically executes to remove stale row versions.

Note

Pour les transactions de courte durée, il arrive qu’une version d’une ligne modifiée soit mise en cache dans le pool de mémoires tampons sans être écrite dans les fichiers de la base de données tempdb sur le disque.For short-running transactions, a version of a modified row may get cached in the buffer pool without getting written into the disk files of the tempdb database. Si cette ligne avec version n'est plus nécessaire, elle est simplement supprimée du pool de mémoires tampons, ce qui lui évite de générer du trafic E/S.If the need for the versioned row is short-lived, it will simply get dropped from the buffer pool and may not necessarily incur I/O overhead.

Comportement lors de la lecture de donnéesBehavior When Reading Data

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.When transactions running under row versioning-based isolation read data, the read operations do not acquire shared (S) locks on the data being read, and therefore do not block transactions that are modifying data. De plus, la charge liée au verrouillage des ressources est minimisée en raison de la réduction du nombre de verrous acquis.Also, the overhead of locking resources is minimized as the number of locks acquired is reduced. 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.Read committed isolation using row versioning and snapshot isolation are designed to provide statement-level or transaction-level read consistencies of versioned data.

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.All queries, including transactions running under row versioning-based isolation levels, acquire Sch-S (schema stability) locks during compilation and execution. 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.Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the 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.For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the 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.Query transactions, including those running under a row versioning-based isolation level, are blocked when attempting to acquire a Sch-S lock. 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.Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

Lorsqu'une transaction avec niveau d'isolement d'instantané est lancée, l'instance de Moteur de base de donnéesDatabase Engine enregistre toutes les transactions en cours.When a transaction using the snapshot isolation level starts, the instance of the Moteur de base de donnéesDatabase Engine records all of the currently active transactions. Lorsque la transaction lit une ligne qui a une chaîne de versions, le Moteur de base de donnéesDatabase Engine remonte la chaîne et récupère la ligne dont le numéro de séquence de transaction :When the snapshot transaction reads a row that has a version chain, the Moteur de base de donnéesDatabase Engine follows the chain and retrieves the row where the transaction sequence number is:

  • se rapproche le plus, sans le dépasser, du numéro de séquence de la transaction qui lit la ligne ;Closest to but lower than the sequence number of the snapshot transaction reading the row.

  • ne figure pas dans la liste de transactions actives au moment de la création de la transaction.Not in the list of the transactions active when the snapshot transaction started.

    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.Read operations performed by a snapshot transaction retrieve the last version of each row that had been committed at the time the snapshot transaction started. Ceci permet de disposer d'un instantané cohérent de manière transactionnelle des données présentes au début de la transaction.This provides a transactionally consistent snapshot of the data as it existed at the start of the transaction.

    Les transactions « read committed » avec contrôle de version de ligne fonctionnement pratiquement de la même manière.Read-committed transactions using row versioning operate in much the same way. La différence est que ces transactions n'utilisent pas leurs propres numéros de séquence lors du choix des versions de lignes.The difference is that the read-committed transaction does not use its own transaction sequence number when choosing row versions. 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éesDatabase Engine.Each time a statement is started, the read-committed transaction reads the latest transaction sequence number issued for that instance of the Moteur de base de donnéesDatabase Engine. C'est ce numéro qui servira à sélectionner les bonnes versions de lignes pour cette instruction.This is the transaction sequence number used to select the correct row versions for that statement. Ceci permet aux transactions read committed de voir un instantané des données telles qu'elles existaient au début de chaque instruction.This allows read-committed transactions to see a snapshot of the data as it exists at the start of each statement.

Note

Même si les transactions read commited utilisant le contrôle de version de ligne fournissent une vue cohérente d'un point de vue transactionnel des données au niveau d'une instruction, les versions de ligne générées ou accédées par ce type de transaction sont conservées jusqu'à la fin de la transaction.Even though read-committed transactions using row versioning provides a transactionally consistent view of the data at a statement level, row versions generated or accessed by this type of transaction are maintained until the transaction completes.

Comportement lors de la modification de donnéesBehavior When Modifying Data

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.In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read. La même chose se produit avec une transaction read committed qui n'utilise pas le contrôle de version de ligne.This is the same as a read-committed transaction that does not use row versioning. 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.If the data row does not meet the update criteria, the update lock is released on that row and the next row is locked and scanned.

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.Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. Sinon, les verrous ne sont pas placés sur les données tant que celles-ci doivent être modifiées.Otherwise, locks are not acquired on data until the data is to be modified. 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.When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. 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.If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. Le conflit de mise à jour est géré par le Moteur de base de donnéesDatabase Engine. Il n'y a aucun moyen de désactiver la détection des conflits de mise à jour.The update conflict is handled by the Moteur de base de donnéesDatabase Engine and there is no way to disable the update conflict detection.

Note

Les opérations de mise à jour s'exécutant avec isolement d'instantané s'exécutent en interne sous le régime d'isolement read committed lorsque la transaction accède à un des éléments suivants :Update operations running under snapshot isolation internally execute under read committed isolation when the snapshot transaction accesses any of the following:

une table avec contrainte FOREIGN KEY ;A table with a FOREIGN KEY constraint.

une table à laquelle la contrainte FOREIGN KEY d'une autre table fait référence ;A table that is referenced in the FOREIGN KEY constraint of another table.

une vue indexée faisant référence à plusieurs tables.An indexed view referencing more than one table.

Cependant, même sous ces conditions, l'opération de mise à jour continue à vérifier que les données n'ont pas été modifiées par une autre transaction.However, even under these conditions the update operation will continue to verify that the data has not been modified by another transaction. Si c'est le cas, il y a conflit de mise à jour et la transaction est arrêtée.If data has been modified by another transaction, the snapshot transaction encounters an update conflict and is terminated.

Synthèse des comportementsBehavior in Summary

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 :The following table summarizes the differences between snapshot isolation and read committed isolation using row versioning.

PropriétéProperty Niveau d'isolement READ COMMITED utilisant le contrôle de version de ligneRead-committed isolation level using row versioning Niveau d'isolement d'instantanéSnapshot isolation level
L'option de base de données doit être activée (ON) pour assurer la prise en charge nécessaire.The database option that must be set to ON to enable the required support. READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION
Manière dont une session demande le type spécifique de contrôle de version de ligne.How a session requests the specific type of row versioning. 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.Use the default read-committed isolation level, or run the SET TRANSACTION ISOLATION LEVEL statement to specify the READ COMMITTED isolation level. Ceci peut se faire après le début de la transaction.This can be done after the transaction starts. 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.Requires the execution of SET TRANSACTION ISOLATION LEVEL to specify the SNAPSHOT isolation level before the start of the transaction.
La version des données lue par les instructions.The version of data read by statements. Toutes les données qui ont été validées avant le début de chaque instruction.All data that was committed before the start of each statement. Toutes les données qui ont été validées avant le début de chaque transaction.All data that was committed before the start of each transaction.
Manière dont les mises à jour sont gérées.How updates are handled. 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.Reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. Acquiert des verrous exclusifs sur les lignes à modifier réellement.Acquires exclusive locks on actual data rows to be modified. Pas de détection de conflit de mise à jour.No update conflict detection. Utilise les versions de lignes pour sélectionner les lignes à mettre à jour.Uses row versions to select rows to update. 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.Tries to acquire an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.
Détection d'un conflit de mise à jour.Update conflict detection. Aucun.None. Prise en charge intégrée.Integrated support. Ne peut être désactivée.Cannot be disabled.

Utilisation de la ressource de contrôle de version de ligneRow Versioning Resource Usage

L'infrastructure de contrôle de version de ligne prend en charge les fonctionnalités suivantes dans SQL ServerSQL Server :The row versioning framework supports the following features available in SQL ServerSQL Server:

  • DéclencheursTriggers

  • MARS (Multiple Active Results Sets)Multiple Active Results Sets (MARS)

  • Indexation en ligneOnline indexing

    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 :The row versioning framework also supports the following row versioning-based transaction isolation levels, which by default are not enabled:

  • 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.When the READ_COMMITTED_SNAPSHOT database option is ON, READ_COMMITTED transactions provide statement-level read consistency using row versioning.

  • 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.When the ALLOW_SNAPSHOT_ISOLATION database option is ON, SNAPSHOT transactions provide transaction-level read consistency using row versioning.

    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.Row versioning-based isolation levels reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. Les performances système sont ainsi accrues et les ressources nécessaires à la gestion des verrous diminuées.This increases system performance by reducing the resources used to manage locks. La réduction des blocages d'une transaction par des verrous obtenus par d'autres transactions permet également d'augmenter les performances.Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions.

    Les niveaux d'isolement basé sur le contrôle de version de ligne augmentent les ressources nécessaires pour la modification de données.Row versioning-based isolation levels increase the resources needed by data modifications. 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.Enabling these options causes all data modifications for the database to be versioned. 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.A copy of the data before modification is stored in tempdb even when there are no active transactions using row versioning-based isolation. Les données modifiées contiennent un pointeur vers les données de version stockées dans tempdb.The data after modification includes a pointer to the versioned data stored in tempdb. En ce qui concerne les objets volumineux, seule la partie de l'objet ayant été modifiée est copiée dans tempdb.For large objects, only part of the object that changed is copied to tempdb.

Espace occupé dans tempdbSpace Used in tempdb

Pour toute instance du Moteur de base de donnéesDatabase Engine, 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.For each instance of the Moteur de base de donnéesDatabase Engine, tempdb must have enough space to hold the row versions generated for every database in the 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.The database administrator must ensure that tempdb has ample space to support the version store. tempdb intègre deux banques des versions :There are two version stores in 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 ;The online index build version store is used for online index builds in all databases.

  • 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.The common version store is used for all other data modification operations in all databases.

    Les versions de ligne doivent être stockées pour toute la durée au cours de laquelle une transaction active doit être accessible.Row versions must be stored for as long as an active transaction needs to access it. 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.Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in tempdb. Une transaction longue empêche la libération d'espace dans une banque des versions si l'une des conditions suivantes est remplie :A long-running transaction prevents space in the version store from being released if it meets any of the following conditions:

  • elle utilise l'isolement basé sur le contrôle de version de ligne ;It uses row versioning-based isolation.

  • elle utilise des déclencheurs, des jeux MARS ou des opérations de construction d'index en ligne ;It uses triggers, MARS, or online index build operations.

  • elle génère des versions de ligne.It generates row versions.

Note

Quand un déclencheur est appelé au sein d'une transaction, les versions de ligne créées par le déclencheur sont conservées jusqu'à la fin de la transaction, même si les versions de ligne ne sont plus nécessaires après l'exécution du déclencheur.When a trigger is invoked inside a transaction, the row versions created by the trigger are maintained until the end of the transaction, even though the row versions are no longer needed after the trigger completes. Ce point s'applique aussi aux transactions à lecture validée qui utilisent le contrôle de version de ligne.This also applies to read-committed transactions that use row versioning. Dans ce type de transaction, une vue cohérente sur le plan transactionnel de la base de données n'est nécessaire que pour chaque instruction de la transaction.With this type of transaction, a transactionally consistent view of the database is needed only for each statement in the transaction. Cela signifie que les versions de ligne créées pour une instruction de la transaction ne sont plus nécessaires une fois l'instruction exécutée.This means that the row versions created for a statement in the transaction are no longer needed after the statement completes. Cependant, les versions de ligne créées par chaque instruction de la transaction sont conservées jusqu'à la fin de la transaction.However, row versions created by each statement in the transaction are maintained until the transaction completes.

Quand tempdb n’a plus d’espace, le Moteur de base de donnéesDatabase Engine force la réduction des banques des versions.When tempdb runs out of space, the Moteur de base de donnéesDatabase Engine forces the version stores to shrink. 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.During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. Le message 3967 est inscrit dans le journal d'erreurs pour chaque transaction victime.A message 3967 is generated in the error log for each victim transaction. Toute transaction marquée comme victime ne peut plus lire les versions de ligne de la banque des versions.If a transaction is marked as a victim, it can no longer read the row versions in the version store. En cas de tentative de lecture des versions de ligne, le message 3966 est généré et la transaction est restaurée.When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. En cas de réussite du processus de réduction, l'espace est disponible dans tempdb.If the shrinking process succeeds, space becomes available in tempdb. Dans le cas contraire, l'espace de tempdb devient insuffisant, avec les conséquences suivantes :Otherwise, tempdb runs out of space and the following occurs:

  • L'exécution des opérations d'écriture se poursuit, mais sans génération de versions.Write operations continue to execute but do not generate 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.An information message (3959) appears in the error log, but the transaction that writes data is not affected.

  • 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.Transactions that attempt to access row versions that were not generated because of a tempdb full rollback terminate with an error 3958.

Espace occupé dans les lignes de donnéesSpace Used in Data Rows

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.Each database row may use up to 14 bytes at the end of the row for row versioning information. 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.The row versioning information contains the transaction sequence number of the transaction that committed the version and the pointer to the versioned row. 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 :These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions:

  • l'option READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION est activée (ON) ;READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION options are ON.

  • la table comporte un déclencheur ;The table has a trigger.

  • des jeux MARS (Multiple Active Results Sets) sont en cours d'utilisation ;Multiple Active Results Sets (MARS) is being used.

  • des opérations de construction d'index en ligne sont en cours d'exécution dans la table.Online index build operations are currently running on the 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 :These 14 bytes are removed from the database row the first time the row is modified under all of these conditions:

  • les options READ_COMMITTED_SNAPSHOT et ALLOW_SNAPSHOT_ISOLATION sont désactivées (OFF) ;READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options are OFF.

  • le déclencheur n'existe plus dans la table ;The trigger no longer exists on the table.

  • les jeux MARS ne sont pas en cours d'utilisation ;MARS is not being used.

  • aucune opération de construction d'index en ligne n'est en cours d'exécution.Online index build operations are not currently running.

    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.If you use any of the row versioning features, you might need to allocate additional disk space for the database to accommodate the 14 bytes per database row. 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.Adding the row versioning information can cause index page splits or the allocation of a new data page if there is not enough space available on the current page. 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.For example, if the average row length is 100 bytes, the additional 14 bytes cause an existing table to grow up to 14 percent.

    La réduction du facteur de remplissage peut permettre d’empêcher ou de réduire la fragmentation des pages d’index.Decreasing the fill factor might help to prevent or decrease fragmentation of index pages. Pour afficher les informations de fragmentation pour les données et les index d’une table ou une vue, vous pouvez utiliser DBCC SHOWCONTIG.To view fragmentation information for the data and indexes of a table or view, you can use DBCC SHOWCONTIG.

Espace occupé dans les objets volumineuxSpace Used in Large Objects

Le Moteur de base de données SQL ServerSQL Server Database Engine prend en charge six types de données pouvant contenir des chaînes volumineuses d’une longueur de 2 gigaoctets (Go) au maximum : nvarchar(max), varchar(max), varbinary(max), ntext, text et image.The Moteur de base de données SQL ServerSQL Server Database Engine supports six data types that can hold large strings up to 2 gigabytes (GB) in length: nvarchar(max), varchar(max), varbinary(max), ntext, text, and 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.Large strings stored using these data types are stored in a series of data fragments that are linked to the data row. Les informations sur le contrôle de version de ligne sont stockées dans chaque fragment utilisé pour le stockage des chaînes volumineuses.Row versioning information is stored in each fragment used to store these large strings. Les fragments de données sont un ensemble de pages dédiées aux objets volumineux d'une table.Data fragments are a collection of pages dedicated to large objects in a 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.As new large values are added to a database, they are allocated using a maximum of 8040 bytes of data per fragment. Les versions antérieures du Moteur de base de donnéesDatabase Engine pouvaient stocker jusqu’à 8 080 octets de données ntext, text ou image par fragment.Earlier versions of the Moteur de base de donnéesDatabase Engine stored up to 8080 bytes of ntext, text, or image data per 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 ServerSQL Server à partir d'une version antérieure de SQL ServerSQL Server.Existing ntext, text, and image large object (LOB) data is not updated to make space for the row versioning information when a database is upgraded to SQL ServerSQL Server from an earlier version of SQL ServerSQL 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,However, the first time the LOB data is modified, it is dynamically upgraded to enable storage of versioning information. même si des versions de lignes sont générées.This will happen even if row versions are not generated. 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.After the LOB data is upgraded, the maximum number of bytes stored per fragment is reduced from 8080 bytes to 8040 bytes. Le processus de mise à niveau équivaut à supprimer la valeur LOB et à réinsérer la même valeur.The upgrade process is equivalent to deleting the LOB value and reinserting the same value. Les données LOB sont mises à niveau même en cas de modification d'un seul octet.The LOB data is upgraded even if only one byte is modified. 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,This is a one-time operation for each ntext, text, or image column, but each operation may generate a large amount of page allocations and I/O activity depending upon the size of the LOB data. ainsi qu'une activité importante d'écriture dans le journal si la modification doit être écrite en entier dans le journal.It may also generate a large amount of logging activity if the modification is fully logged. 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.WRITETEXT and UPDATETEXT operations are minimally logged if database recovery mode is not set to FULL.

Les types de données nvarchar(max), varchar(max) et varbinary(max) ne sont pas disponibles dans les versions antérieures de SQL ServerSQL Server.The nvarchar(max), varchar(max), and varbinary(max) data types are not available in earlier versions of SQL ServerSQL Server. Vous ne rencontrerez pas conséquent aucun problème de mise à niveau.Therefore, they have no upgrade issues.

Un espace disque suffisant doit être alloué pour satisfaire à cette exigence.Enough disk space should be allocated to accommodate this requirement.

Contrôle du contrôle de version de ligne et du magasin de versionsMonitoring Row Versioning and the Version Store

SQL ServerSQL 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.For monitoring row versioning, version store, and snapshot isolation processes for performance and problems, SQL ServerSQL Server provides tools in the form of Dynamic Management Views (DMVs) and performance counters in Windows System Monitor.

Vues DMVDMVs

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.The following DMVs provide information about the current system state of tempdb and the version store, as well as transactions using row versioning.

sys.dm_db_file_space_usage.sys.dm_db_file_space_usage. Retourne des informations sur l'utilisation de l'espace pour chaque fichier de la base de données.Returns space usage information for each file in the database. Pour plus d’informations, consultez sys.dm_db_file_space_usage (Transact-SQL).For more information, see sys.dm_db_file_space_usage (Transact-SQL).

sys.dm_db_session_space_usage.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.Returns page allocation and deallocation activity by session for the database. Pour plus d’informations, consultez sys.dm_db_session_space_usage (Transact-SQL).For more information, see sys.dm_db_session_space_usage (Transact-SQL).

sys.dm_db_task_space_usage.sys.dm_db_task_space_usage. Renvoie l'activité d'allocation/désallocation des pages par tâche pour la base de données.Returns page allocation and deallocation activity by task for the database. Pour plus d’informations, consultez sys.dm_db_task_space_usage (Transact-SQL).For more information, see sys.dm_db_task_space_usage (Transact-SQL).

sys.dm_tran_top_version_generators.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.Returns a virtual table for the objects producing the most versions in the version store. Agrégation des 256 premières longueurs d'enregistrement selon database_id et rowset_id.It groups the top 256 aggregated record lengths by database_id and rowset_id. Utilisez cette fonction pour rechercher les clients les plus volumineux de la banque de versions.Use this function to find the largest consumers of the version store. Pour plus d’informations, consultez sys.dm_tran_top_version_generators (Transact-SQL).For more information, see sys.dm_tran_top_version_generators (Transact-SQL).

sys.dm_tran_version_store.sys.dm_tran_version_store. Renvoie une table virtuelle qui affiche tous les enregistrements de version du magasin de versions commun.Returns a virtual table that displays all version records in the common version store. Pour plus d’informations, consultez sys.dm_tran_version_store (Transact-SQL).For more information, see sys.dm_tran_version_store (Transact-SQL).

Note

sys.dm_tran_top_version_generators et sys.dm_tran_version_store sont des fonctions potentiellement très compliquées à exécuter dans la mesure où elles interrogent toutes deux le magasin de versions entier, qui peut s'avérer très volumineux.sys.dm_tran_top_version_generators and sys.dm_tran_version_store are potentially very expensive functions to run, since both query the entire version store, which could be very large.

sys.dm_tran_active_snapshot_database_transactions.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 ServerSQL Server utilisant le contrôle de version de ligne.Returns a virtual table for all active transactions in all databases within the SQL ServerSQL Server instance that use row versioning. Les transactions système n'apparaissent pas dans cette vue DMV.System transactions do not appear in this DMV. Pour plus d’informations, consultez sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).For more information, see sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

sys.dm_tran_transactions_snapshot.sys.dm_tran_transactions_snapshot. Renvoie une table virtuelle qui affiche les instantanés pris par chaque transaction.Returns a virtual table that displays snapshots taken by each transaction. L'instantané contient le numéro de séquence des transactions actives utilisant le contrôle de version de ligne.The snapshot contains the sequence number of the active transactions that use row versioning. Pour plus d’informations, consultez sys.dm_tran_transactions_snapshot (Transact-SQL).For more information, see sys.dm_tran_transactions_snapshot (Transact-SQL).

sys.dm_tran_current_transaction.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.Returns a single row that displays row versioning-related state information of the transaction in the current session. Pour plus d’informations, consultez sys.dm_tran_current_transaction (Transact-SQL).For more information, see sys.dm_tran_current_transaction (Transact-SQL).

sys.dm_tran_current_snapshot.sys.dm_tran_current_snapshot. Retourne une table virtuelle affichant toutes les transactions actives au début de la transaction d'isolement d'instantané.Returns a virtual table that displays all active transactions at the time the current snapshot isolation transaction starts. Si la transaction actuelle utilise l'isolement d'instantané, cette fonction ne retourne aucune ligne.If the current transaction is using snapshot isolation, this function returns no rows. sys.dm_tran_current_snapshot est similaire à sys.dm_tran_transactions_snapshot, mis à part qu’elle retourne uniquement les transactions actives pour l’instantané actuel.sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that it returns only the active transactions for the current snapshot. Pour plus d’informations, consultez sys.dm_tran_current_snapshot (Transact-SQL).For more information, see sys.dm_tran_current_snapshot (Transact-SQL).

Compteurs de performancesPerformance Counters

Les compteurs de performance de SQL ServerSQL Server fournissent des informations sur les performances système affectées par les processus de SQL ServerSQL Server.SQL ServerSQL Server performance counters provide information about the system performance impacted by SQL ServerSQL Server processes. Les compteurs de performances suivants contrôlent tempdb et le magasin de versions, ainsi que les transactions utilisant le contrôle de version de ligne.The following performance counters monitor tempdb and the version store, as well as transactions using row versioning. Les compteurs de performances se trouvent dans l'objet de performances SQLServer:Transactions.The performance counters are contained in the SQLServer:Transactions performance object.

Espace disponible dans tempdb (Ko).Free Space in tempdb (KB). Contrôle la quantité, en kilooctets (Ko), d'espace libre dans la base de données tempdb.Monitors the amount, in kilobytes (KB), of free space in the tempdb database. tempdb doit disposer d'un espace libre suffisant pour gérer le magasin de versions prenant en charge l'isolement d'instantané.There must be enough free space in tempdb to handle the version store that supports snapshot isolation.

La formule ci-dessous vous donne une estimation grossière de la taille du magasin de versions.The following formula provides a rough estimate of the size of the version store. 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.For long-running transactions, it may be useful to monitor the generation and cleanup rate to estimate the maximum size of the version store.

[taille de la banque des versions commune] = 2 * [données de la banque des versions générées par minute] * [délai d’exécution le plus long (en minutes) de la transaction][size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

Le délai le plus long d'exécution de transaction ne doit pas inclure les constructions d'un index en ligne.The longest running time of transactions should not include online index builds. Étant donné que ces dernières opérations peuvent prendre un certain temps pour les tables volumineuses, elles utilisent un autre magasin de versions.Because these operations may take a long time on very large tables, online index builds use a separate version store. 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.The approximate size of the online index build version store equals the amount of data modified in the table, including all indexes, while the online index build is active.

Taille de la banque des versions (Ko).Version Store Size (KB). Contrôle la taille en Ko de tous les magasins de versions.Monitors the size in KB of all version stores. Cette information permet de déterminer la quantité d'espace nécessaire dans la base de données tempdb pour le magasin de versions.This information helps determine the amount of space needed in the tempdb database for the version store. Le contrôle de ce compteur sur une période de temps fournit une estimation utile de l'espace supplémentaire requis pour tempdb.Monitoring this counter over a period of time provides a useful estimate of additional space needed for tempdb.

Version Generation rate (KB/s) .Version Generation rate (KB/s). Contrôle le taux de génération de version en Ko par seconde pour tous les magasins de versions.Monitors the version generation rate in KB per second in all version stores.

Version Cleanup rate (KB/s) .Version Cleanup rate (KB/s). Contrôle le taux de nettoyage de version en Ko par seconde pour tous les magasins de versions.Monitors the version cleanup rate in KB per second in all version stores.

Note

Les informations obtenues à l'aide des compteurs Taux de génération de version (Ko/s) et Taux de nettoyage de version (Ko/s) permettent de prévoir l'espace nécessaire pour tempdb.Information from Version Generation rate (KB/s) and Version Cleanup rate (KB/s) can be used to predict tempdb space requirements.

Nombre d’unités dans la banque des versions.Version Store unit count. Contrôle le nombre d'unités dans le magasin de versions.Monitors the count of version store units.

Création d’unité dans la banque des versions.Version Store unit creation. 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.Monitors the total number of version store units created to store row versions since the instance was started.

Troncation d’unité dans la banque des versions.Version Store unit truncation. Contrôle le nombre total d'unités tronquées dans le magasin de versions depuis le démarrage de l'instance.Monitors the total number of version store units truncated since the instance was started. Une unité de magasin de versions est tronquée lorsque SQL ServerSQL 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.A version store unit is truncated when SQL ServerSQL Server determines that none of the version rows stored in the version store unit are needed to run active transactions.

Proportion de conflits de mise à jour.Update conflict ratio. 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.Monitors the ratio of update snapshot transaction that have update conflicts to the total number of update snapshot transactions.

Délai le plus long d’exécution de transaction.Longest Transaction Running Time. Contrôle le délai le plus long (en secondes) d'exécution de toute transaction utilisant le contrôle de version de ligne.Monitors the longest running time in seconds of any transaction using row versioning. Ce compteur permet de déterminer si l'exécution de l'une des transactions est trop longue.This can be used to determine if any transaction is running for an unreasonable amount of time.

Transactions.Transactions. Contrôle le nombre total de transactions actives.Monitors the total number of active transactions. Les transactions système ne sont pas prises en compte.This does not include system transactions.

Snapshot Transactions .Snapshot Transactions. Contrôle le nombre total de transactions d'instantanés actives.Monitors the total number of active snapshot transactions.

Update Snapshot Transactions .Update Snapshot Transactions. Contrôle le nombre total de transactions d'instantanés effectuant des opérations de mise à jour.Monitors the total number of active snapshot transactions that perform update operations.

NonSnapshot Version Transactions .NonSnapshot Version Transactions. Contrôle le nombre total de transactions actives non liées à des instantanés générant des enregistrements de versions.Monitors the total number of active non-snapshot transactions that generate version records.

Note

La somme des compteurs Transactions d'instantanés de mise à jour et Transactions de versions non liées à des instantanés représente le nombre total de transactions participant à la génération d'une version.The sum of Update Snapshot Transactions and NonSnapshot Version Transactions represents the total number of transactions that participate in version generation. La différence entre les compteurs Transactions d'instantanés et Transactions d'instantanés de mise à jour indique le nombre de transactions d'instantanés en lecture seule.The difference of Snapshot Transactions and Update Snapshot Transactions reports the number of read-only snapshot transactions.

Exemple de niveau d'isolement basé sur le contrôle de version de ligneRow Versioning-based Isolation Level Example

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.The following examples show the differences in behavior between snapshot isolation transactions and read-committed transactions that use row versioning.

A.A. Utilisation du niveau d'isolement d'instantanéWorking with snapshot isolation

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.In this example, a transaction running under snapshot isolation reads data that is then modified by another 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.The snapshot transaction does not block the update operation executed by the other transaction, and it continues to read data from the versioned row, ignoring the data modification. 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.However, when the snapshot transaction attempts to modify the data that has already been modified by the other transaction, the snapshot transaction generates an error and is terminated.

Sur la session 1 :On 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 :On 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 :On 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 :On session 2:

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

Sur la session 1 :On 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.B. Utilisation d'une transaction validée en lecture à l'aide du contrôle de version de ligneWorking with read-committed using row versioning

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.In this example, a read-committed transaction using row versioning runs concurrently with another transaction. La transaction validée en lecture se comporte différemment de la transaction d'instantané.The read-committed transaction behaves differently than a snapshot transaction. À 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.Like a snapshot transaction, the read-committed transaction will read versioned rows even after the other transaction has modified data. Toutefois, contrairement à une transaction d'instantané, la transaction validée en lecture :However, unlike a snapshot transaction, the read-committed transaction will:

  • lit les données modifiées une fois que l'autre transaction a validé les modifications de données ;Read the modified data after the other transaction commits the data changes.

  • 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é.Be able to update the data modified by the other transaction where the snapshot transaction could not.

    Sur la session 1 :On 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 :On 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 :On 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 :On session 2:

-- Commit the transaction.  
COMMIT TRANSACTION;  
GO  

Sur la session 1 :On 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 ligneEnabling Row Versioning-Based Isolation Levels

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.Database administrators control the database-level settings for row versioning by using the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options in the ALTER DATABASE statement.

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.When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. 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.When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. La base de données ne peut contenir aucune autre connexion ouverte avant la fin de l'exécution de la commande ALTER DATABASE.There must be no other open connection in the database until ALTER DATABASE is complete. Il n'est pas nécessaire que la base de données soit en mode mono-utilisateur.The database does not have to be in single-user mode.

L'instruction Transact-SQLTransact-SQL suivante permet la prise en charge de l'option READ_COMMITTED_SNAPSHOT :The following Transact-SQLTransact-SQL statement enables 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 ServerSQL Server Database Engine 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.When the ALLOW_SNAPSHOT_ISOLATION database option is set ON, the instance of the Moteur de base de données SQL ServerSQL Server Database Engine does not generate row versions for modified data until all active transactions that have modified data in the database complete. En cas de transactions de modification actives, SQL ServerSQL Server affecte à l'option l'état PENDING_ON.If there are active modification transactions, SQL ServerSQL Server sets the state of the option to PENDING_ON. Une fois l'exécution des transactions de modification terminées, l'état de l'option passe sur ON.After all of the modification transactions complete, the state of the option is changed to ON. Les utilisateurs ne peuvent lancer une transaction d'instantané que quand l'option a la valeur ON.Users cannot start a snapshot transaction in that database until the option is fully ON. La base de données passe par l'état PENDING_OFF lorsque son administrateur affecte à l'option ALLOW_SNAPSHOT_ISOLATION la valeur OFF.The database passes through a PENDING_OFF state when the database administrator sets the ALLOW_SNAPSHOT_ISOLATION option to OFF.

L'instruction Transact-SQLTransact-SQL suivante permet la prise en charge de l'option ALLOW_SNAPSHOT_ISOLATION :The following Transact-SQLTransact-SQL statement will enable 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.The following table lists and describes the states of the ALLOW_SNAPSHOT_ISOLATION option. 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.Using ALTER DATABASE with the ALLOW_SNAPSHOT_ISOLATION option does not block users who are currently accessing the database data.

État de l'infrastructure d'isolement d'instantané pour la base de données actuelleState of snapshot isolation framework for current database DescriptionDescription
OFFOFF La prise en charge des transactions d'isolement d'instantané n'est pas activée.The support for snapshot isolation transactions is not activated. Aucune transaction d'isolement d'instantané n'est autorisée.No snapshot isolation transactions are allowed.
PENDING_ONPENDING_ON La prise en charge des transactions d'isolement d'instantané est en état de transition (de OFF à ON).The support for snapshot isolation transactions is in transition state (from OFF to ON). L'exécution de toutes les transactions ouvertes doit être terminée.Open transactions must complete.

Aucune transaction d'isolement d'instantané n'est autorisée.No snapshot isolation transactions are allowed.
ONON La prise en charge des transactions d'isolement d'instantané est activée.The support for snapshot isolation transactions is activated.

Les transactions d'isolement d'instantané sont autorisées.Snapshot transactions are allowed.
PENDING_OFFPENDING_OFF La prise en charge des transactions d'isolement d'instantané est en état de transition (de ON à OFF).The support for snapshot isolation transactions is in transition state (from ON to OFF).

Les transactions d'instantané lancées dès ce moment ne peuvent accéder à la base de données.Snapshot transactions started after this time cannot access this database. Les transactions de mise à jour assument la responsabilité du versioning dans la base de données.Update transactions still pay the cost of versioning in this database. Les transactions d'instantané existantes peuvent toujours accéder à la base de données sans aucun problème.Existing snapshot transactions can still access this database without a problem. 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.The state PENDING_OFF does not become OFF until all snapshot transactions that were active when the database snapshot isolation state was ON finish.

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.Use the sys.databases catalog view to determine the state of both row versioning database options.

Toutes les mises à jour des tables utilisateur et de certaines tables système stocku dans les tables de données master et msdb génère le contrôle de version de ligne.All updates to user tables and some system tables stored in master and msdb generate row versions.

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.The ALLOW_SNAPSHOT_ISOLATION option is automatically set ON in the master and msdb databases, and cannot be disabled.

Les bases de données master, tempdb et msdb ne permettent pas aux utilisateurs d'affecter à l'option READ_COMMITED_SNAPSHOT la valeur ON.Users cannot set the READ_COMMITTED_SNAPSHOT option ON in master, tempdb, or msdb.

Utilisation de niveaux d'isolement basés sur le contrôle de version de ligneUsing Row Versioning-based Isolation Levels

L'infrastructure de contrôle de version de ligne est toujours activée dans SQL ServerSQL Server et est utilisée par plusieurs fonctionnalités.The row versioning framework is always enabled in SQL ServerSQL Server, and is used by multiple features. 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.Besides providing row versioning-based isolation levels, it is used to support modifications made in triggers and multiple active result sets (MARS) sessions, and to support data reads for ONLINE index operations.

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.Row versioning-based isolation levels are enabled at the database level. 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 :Any application accessing objects from enabled databases can run queries using the following isolation levels:

  • 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 :Read-committed that uses row versioning by setting the READ_COMMITTED_SNAPSHOT database option to ON as shown in the following code example:

    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.When the database is enabled for READ_COMMITTED_SNAPSHOT, all queries running under the read committed isolation level use row versioning, which means that read operations do not block update operations.

  • 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 :Snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option to ON as shown in the following code example:

    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.A transaction running under snapshot isolation can access tables in the database that have been enabled for snapshot. Pour accéder aux tables qui n'ont pas été activées pour les instantanés, le niveau d'isolement doit être modifié.To access tables that have not been enabled for snapshot, the isolation level must be changed. Ainsi, dans l'exemple de code suivant, une instruction SELECT exécutée dans le cadre d'une transaction d'instantané joint deux tables.For example, the following code example shows a SELECT statement that joins two tables while running under a snapshot transaction. Une table appartient à une base de données dans laquelle le niveau d'isolement d'instantané (SNAPSHOT) n'est pas activé.One table belongs to a database in which snapshot isolation is not enabled. Lorsque l'instruction SELECT s'exécute sous le niveau d'isolement d'instantané, elle échoue.When the SELECT statement runs under snapshot isolation, it fails to execute successfully.

    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é).The following code example shows the same SELECT statement that has been modified to change the transaction isolation level to read-committed. Grâce à cette modification, l'exécution de l'instruction SELECT aboutit.Because of this change, the SELECT statement executes successfully.

    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 ligneLimitations of Transactions Using Row Versioning-based Isolation Levels

Tenez compte des limites suivantes lors de l'utilisation des niveaux d'isolement basés sur le contrôle de version de ligne :Consider the following limitations when working with row versioning-based isolation levels:

  • READ_COMMITTED_SNAPSHOT ne peut pas être activé dans les bases de données tempdb, msdb et master.READ_COMMITTED_SNAPSHOT cannot be enabled in tempdb, msdb, or master.

  • Les tables temporaires globales sont stockées dans tempdb.Global temp tables are stored in tempdb. Si une transaction d'instantané implique l'accès à des tables temporaires globales, vous devez effectuer l'une des opérations suivantes :When accessing global temp tables inside a snapshot transaction, one of the following must happen:

    • Définir l'option de base de données ALLOW_SNAPSHOT_ISOLATION sur ON (activé) dans tempdb.Set the ALLOW_SNAPSHOT_ISOLATION database option ON in tempdb.

    • Utilisez un indicateur d'isolement afin de modifier le niveau d'isolement pour l'instruction.Use an isolation hint to change the isolation level for the statement.

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

    • 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.A database is made read-only after the snapshot transaction starts, but before the snapshot transaction accesses the database.

    • 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.If accessing objects from multiple databases, a database state was changed in such a way that database recovery occurred after a snapshot transaction starts, but before the snapshot transaction accesses the database. Par exemple, la base de données a été définie sur OFFLINE puis sur ONLINE, la base de données s'est fermée automatiquement puis rouverte, ou elle s'est détachée puis rattachée.For example: the database was set to OFFLINE and then to ONLINE, database autoclose and open, or database detach and attach.

  • 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é.Distributed transactions, including queries in distributed partitioned databases, are not supported under snapshot isolation.

  • SQL ServerSQL Server ne conserve pas plusieurs versions des métadonnées système.does not keep multiple versions of system metadata. 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.Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata. 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é.If a DDL statement modifies an object, any concurrent reference to the object under snapshot isolation causes the snapshot transaction to fail. 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).Read-committed transactions do not have this limitation when the READ_COMMITTED_SNAPSHOT database option is ON.

    Supposons, par exemple, qu'un administrateur de base de données exécute l'instruction ALTER INDEX suivante.For example, a database administrator executes the following ALTER INDEX statement.

    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.Any snapshot transaction that is active when the ALTER INDEX statement is executed receives an error if it attempts to reference the HumanResources.Employee table after the ALTER INDEX statement is executed. Les transactions READCOMMITTED (lu-validé) utilisant le contrôle de version de ligne ne sont pas affectées.Read-committed transactions using row versioning are not affected.

    Note

    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).BULK INSERT operations may cause changes to target table metadata (for example, when disabling constraint checks). Dans ce cas, les transactions simultanées d'isolement d'instantané qui accèdent à des tables faisant l'objet d'insertion en bloc échouent.When this happens, concurrent snapshot isolation transactions accessing bulk inserted tables fail.

    Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Personnalisation du verrouillage et du contrôle de version de ligneCustomizing Locking and Row Versioning

Personnalisation du délai d'attente de verrouillageCustomizing the Lock Time-Out

Quand une instance du Moteur de base de données SQL ServerSQL Server Database Engine MicrosoftMicrosoft 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.When an instance of the MicrosoftMicrosoft Moteur de base de données SQL ServerSQL Server Database Engine cannot grant a lock to a transaction because another transaction already owns a conflicting lock on the resource, the first transaction becomes blocked waiting for the existing lock to be released. 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).By default, there is no mandatory time-out period and no way to test whether a resource is locked before locking it, except to attempt to access the data (and potentially get blocked indefinitely).

Note

Dans SQL ServerSQL Server, utilisez la vue de gestion dynamique sys.dm_os_waiting_tasks pour déterminer si un processus est bloqué et identifier l’auteur du blocage.In SQL ServerSQL Server, use the sys.dm_os_waiting_tasks dynamic management view to determine whether a process is being blocked and who is blocking it. Dans les versions antérieures de SQL ServerSQL Server, utilisez la procédure stockée système sp_who.In earlier versions of SQL ServerSQL Server, use the sp_who system stored procedure.

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.The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. 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.When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 (Lock request time-out period exceeded) is returned to the application. Toute transaction contenant la commande n'est toutefois pas restaurée ou annulée par SQL ServerSQL Server.Any transaction containing the statement, however, is not rolled back or canceled by SQL ServerSQL Server. L'application doit donc posséder un gestionnaire d'erreurs capable d'intercepter le message d'erreur 1222.Therefore, the application must have an error handler that can trap error message 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.If an application does not trap the error, the application can proceed unaware that an individual statement within a transaction has been canceled, and errors can occur because statements later in the transaction might depend on the statement that was never executed.

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.Implementing an error handler that traps error message 1222 allows an application to handle the time-out situation and take remedial action, such as: automatically resubmitting the statement that was blocked or rolling back the entire transaction.

Pour déterminer le paramètre LOCK_TIMEOUT actuel, exécutez le @@LOCK_TIMEOUT (fonction) :To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT function:

SELECT @@lock_timeout;  
GO  

Personnalisation du niveau d'isolation des transactionsCustomizing Transaction Isolation Level

READ COMMITTED est le niveau d’isolation par défaut pour le Moteur de base de données SQL ServerSQL Server Database Engine MicrosoftMicrosoft.READ COMMITTED is the default isolation level for the MicrosoftMicrosoft Moteur de base de données SQL ServerSQL Server Database Engine. Si une application doit fonctionner à un niveau d'isolation différent, elle peut le définir selon plusieurs méthodes :If an application must operate at a different isolation level, it can use the following methods to set the isolation level:

  • Exécuter l’instruction SET TRANSACTION ISOLATION LEVEL.Run the SET TRANSACTION ISOLATION LEVEL statement.

  • Les applications ADO.NET utilisant l’espace de noms managé System.Data.SqlClient peuvent indiquer une option IsolationLevel via la méthode SqlConnection.BeginTransaction.ADO.NET applications that use the System.Data.SqlClient managed namespace can specify an IsolationLevel option by using the SqlConnection.BeginTransaction method.

  • Les applications qui utilisent ADO peuvent définir la propriété Autocommit Isolation Levels.Applications that use ADO can set the Autocommit Isolation Levels property.

  • 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é.When starting a transaction, applications using OLE DB can call ITransactionLocal::StartTransaction with isoLevel set to the desired transaction isolation level. 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.When specifying the isolation level in autocommit mode, applications that use OLE DB can set the DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS to the desired transaction isolation level.

  • Les applications qui utilisent ODBC peuvent définir l’attribut SQL_COPT_SS_TXN_ISOLATION à l’aide de SQLSetConnectAttr.Applications that use ODBC can set the SQL_COPT_SS_TXN_ISOLATION attribute by using 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 ServerSQL Server.When the isolation level is specified, the locking behavior for all queries and data manipulation language (DML) statements in the SQL ServerSQL Server session operates at that isolation level. Le niveau d'isolation reste en vigueur jusqu'à la fin de la session ou jusqu'à ce qu'il soit modifié.The isolation level remains in effect until the session terminates or until the isolation level is set to another level.

    L'exemple suivant montre comment définir le niveau d'isolation SERIALIZABLE :The following example sets the SERIALIZABLE isolation level:

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.The isolation level can be overridden for individual query or DML statements, if necessary, by specifying a table-level hint. Un indicateur de niveau table n'affecte pas les autres instructions de la session.Specifying a table-level hint does not affect other statements in the 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é.We recommend that table-level hints be used to change the default behavior only when absolutely necessary.

Le Moteur de base de donnéesDatabase Engine 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.The Moteur de base de donnéesDatabase Engine might have to acquire locks when reading metadata even when the isolation level is set to a level where share locks are not requested when reading data. 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.For example, a transaction running at the read-uncommitted isolation level does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. 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.This means it is possible for a read uncommitted transaction to cause blocking when querying a table when a concurrent transaction is modifying the metadata of that table.

Pour déterminer le niveau d'isolation des transactions en cours, utilisez l'instruction DBCC USEROPTIONS comme dans l'exemple qui suit.To determine the transaction isolation level currently set, use the DBCC USEROPTIONS statement as shown in the following example. Le jeu de résultats peut être différent sur votre système.The result set may vary from the result set on your system.

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

Voici le jeu des résultats.Here is the result set.

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 verrouillageLocking Hints

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.Locking hints can be specified for individual table references in the SELECT, INSERT, UPDATE, and DELETE statements. Ces indicateurs déterminent le type de verrouillage ou de contrôle de version de ligne qu’utilise l’instance du Moteur de base de données SQL ServerSQL Server Database Engine pour les données de la table.The hints specify the type of locking or row versioning the instance of the Moteur de base de données SQL ServerSQL Server Database Engine uses for the table data. 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.Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. Ces options de verrouillage remplacent le niveau d'isolement courant de la transaction pour la session.These locking hints override the current transaction isolation level for the session.

Pour plus d’informations sur les indicateurs de verrouillage spécifiques et leurs comportements, consultez Indicateurs de table (Transact-SQL).For more information about the specific locking hints and their behaviors, see Table Hints (Transact-SQL).

Note

L'optimiseur de requête du Moteur de base de donnéesDatabase Engine choisit presque toujours le niveau de verrouillage correct.The Moteur de base de donnéesDatabase Engine query optimizer almost always chooses the correct locking level. Nous vous recommandons d'utiliser les indicateurs de verrouillage au niveau des tables à la place du verrouillage par défaut seulement lorsque cela est nécessaire.We recommend that table-level locking hints be used to change the default locking behavior only when necessary. La désactivation d'un niveau de verrouillage peut affecter défavorablement la concurrence d'accès.Disallowing a locking level can adversely affect concurrency.

Il se peut que le Moteur de base de donnéesDatabase Engine 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.The Moteur de base de donnéesDatabase Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for share locks when reading data. 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.For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. Cela signifie qu'il est possible qu'une instruction SELECT utilisant NOLOCK soit bloquée.This means it is possible for a SELECT statement using NOLOCK to be blocked.

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.As shown in the following example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

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).The only lock taken that references HumanResources.Employee is a schema stability (Sch-S) lock. Dans ce cas, la possibilité de sérialisation n'est plus garantie.In this case, serializability is no longer guaranteed.

Dans SQL Server 2014SQL Server 2014, 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.In SQL Server 2014SQL Server 2014, the LOCK_ESCALATION option of ALTER TABLE can disfavor table locks, and enable HoBT locks on partitioned tables. Cette option n'est pas un indicateur de verrouillage, mais elle peut servir à réduire l'escalade de verrous.This option is not a locking hint, but can but used to reduce lock escalation. Pour plus d’informations, consultez ALTER TABLE (Transact-SQL).For more information, see ALTER TABLE (Transact-SQL).

Personnalisation du verrouillage pour un indexCustomizing Locking for an Index

Dans la plupart des cas, le Moteur de base de données SQL ServerSQL Server Database Engine utilise une stratégie de verrouillage dynamique qui choisit automatiquement la granularité de verrouillage la plus appropriée pour les requêtes.The Moteur de base de données SQL ServerSQL Server Database Engine uses a dynamic locking strategy that automatically chooses the best locking granularity for queries in most cases. 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.We recommend that you do not override the default locking levels, which have page and row locking on, unless table or index access patterns are well understood and consistent, and there is a resource contention problem to solve. Le remplacement d’un niveau de verrouillage peut affecter considérablement les accès simultanés à une table ou un index.Overriding a locking level can significantly impede concurrent access to a table or index. Par exemple, la spécification de verrous de niveau table uniquement sur une table de grande taille à laquelle les utilisateurs accèdent fréquemment peut provoquer des goulets d’étranglement, car les utilisateurs doivent attendre que le verrou de niveau table soit libéré avant de pouvoir accéder à la table.For example, specifying only table-level locks on a large table that users access heavily can cause bottlenecks because users must wait for the table-level lock to be released before accessing the 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.There are a few cases where disallowing page or row locking can be beneficial, if the access patterns are well understood and consistent. Par exemple, une application de base de données utilise une table de recherche mise à jour chaque semaine via un processus par lot.For example, a database application uses a lookup table that is updated weekly in a batch process. 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).Concurrent readers access the table with a shared (S) lock and the weekly batch update accesses the table with an exclusive (X) lock. 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.Turning off page and row locking on the table reduces the locking overhead throughout the week by allowing readers to concurrently access the table through shared table locks. 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.When the batch job runs, it can complete the update efficiently because it obtains an exclusive table lock.

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.Turning off page and row locking might or might not be acceptable because the weekly batch update will block the concurrent readers from accessing the table while the update runs. 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.If the batch job only changes a few rows or pages, you can change the locking level to allow row or page level locking, which will enable other sessions to read from the table without blocking. 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.If the batch job has a large number of updates, obtaining an exclusive lock on the table may be the best way to ensure the batch job finishes efficiently.

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.Occasionally a deadlock occurs when two concurrent operations acquire row locks on the same table and then block because they both need to lock the page. L'interdiction des verrous de ligne force l'une des opérations à attendre, ce qui évite le blocage.Disallowing row locks forces one of the operations to wait, avoiding the deadlock.

La granularité de verrouillage utilisée pour un index peut être définie via les instructions CREATE INDEX et ALTER INDEX.The granularity of locking used on an index can be set using the CREATE INDEX and ALTER INDEX statements. Les paramètres de verrouillage s'appliquent à la fois aux pages d'index et aux pages de table.The lock settings apply to both the index pages and the table pages. 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.In addition, the CREATE TABLE and ALTER TABLE statements can be used to set locking granularity on PRIMARY KEY and UNIQUE constraints. Pour descendante compatibilité, le sp_indexoption procédure stockée système peut également définir la granularité.For backwards compatibility, the sp_indexoption system stored procedure can also set the granularity. Pour afficher l'option de verrouillage en cours pour un index donné, utilisez la fonction INDEXPROPERTY.To display the current locking option for a given index, use the INDEXPROPERTY function. Les verrous au niveau des pages, des lignes, ou une combinaison de ces derniers peuvent être refusés pour un index donné.Page-level locks, row-level locks, or a combination of page-level and row-level locks can be disallowed for a given index.

Verrous refusésDisallowed locks Accès à l'index parIndex accessed by
Niveau pagePage level Verrous au niveau des lignes et des tablesRow-level and table-level locks
Niveau ligneRow level Verrous au niveau des pages et des tablesPage-level and table-level locks
Niveau page et niveau lignePage level and row level Verrous au niveau des tablesTable-level locks

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Informations sur les transactions avancéesAdvanced Transaction Information

Transactions imbriquéesNesting Transactions

Les transactions explicites peuvent être imbriquées.Explicit transactions can be nested. 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.This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

L'exemple suivant illustre cette utilisation des transactions imbriquées.The following example shows the intended use of nested transactions. La procédure TransProc applique sa transaction quel que soit le mode de transaction du processus qui l'exécute.The procedure TransProc enforces its transaction regardless of the transaction mode of any process that executes it. 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.If TransProc is called when a transaction is active, the nested transaction in TransProc is largely ignored, and its INSERT statements are committed or rolled back based on the final action taken for the outer transaction. 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.If TransProc is executed by a process that does not have an outstanding transaction, the COMMIT TRANSACTION at the end of the procedure effectively commits the INSERT statements.

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 ServerSQL Server Database Engine.Committing inner transactions is ignored by the Moteur de base de données SQL ServerSQL Server Database Engine. La transaction est validée ou restaurée en fonction de l'action prise à la fin de la transaction la plus externe.The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. Si la transaction externe est validée, les transactions imbriquées dans celle-ci le sont aussi.If the outer transaction is committed, the inner nested transactions are also committed. Si la transaction externe est restaurée, toutes les transactions internes le sont aussi, qu'elles aient été validées individuellement ou non.If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

Chaque appel à COMMIT TRANSACTION ou COMMIT WORK s'applique à la dernière instruction BEGIN TRANSACTION exécutée.Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. 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.If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. Même si une TRANSACTION de validation transaction_name instruction dans une transaction imbriquée fait référence à la transaction de la transaction externe, la validation s’applique uniquement à la transaction la plus intérieure.Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.

Il n’est pas légal pour le transaction_name paramètre d’une instruction ROLLBACK TRANSACTION pour faire référence aux transactions internes d’un ensemble de transactions nommées imbriquées.It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name ne peut faire référence qu’au nom de la transaction la plus extérieure.transaction_name can refer only to the transaction name of the outermost transaction. Si une instruction ROLLBACK TRANSACTION transaction_name utilisant le nom de la transaction externe est exécutée à n’importe quel niveau d’un ensemble de transactions imbriquées, toutes les transactions imbriquées sont restaurées.If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. Si une instruction ROLLBACK WORK ou ROLLBACK TRANSACTION sans un transaction_name paramètre est exécuté à n’importe quel niveau d’un ensemble de la transaction imbriquée, il restaure toutes les transactions imbriquées, y compris la transaction la plus extérieure.If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

Le @@TRANCOUNT fonction enregistre le niveau d’imbrication de transactions en cours.The @@TRANCOUNT function records the current transaction nesting level. Chaque instruction BEGIN TRANSACTION incrémente@TRANCOUNT d’une unité.Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Chaque décrémente d’instruction COMMIT TRANSACTION ou COMMIT WORK @@TRANCOUNT d’une unité.Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. Un ROLLBACK WORK ou une instruction ROLLBACK TRANSACTION qui n’a pas un nom de la transaction restaure toutes les transactions imbriquées et remet @@TRANCOUNT à 0.A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. Une instruction ROLLBACK TRANSACTION qui utilise le nom de transaction de la transaction la plus extérieure d’un ensemble de transactions imbriquées restaure toutes les transactions imbriquées et décrémente @@TRANCOUNT à 0.A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all of the nested transactions and decrements @@TRANCOUNT to 0. Lorsque vous ne savez pas si vous êtes déjà dans une transaction, sélectionnez@TRANCOUNT pour déterminer s’il est 1 ou plus.When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT est 0, vous n’êtes pas dans une transaction.If @@TRANCOUNT is 0, you are not in a transaction.

Utilisation de sessions associéesUsing Bound Sessions

Les sessions associées facilitent la coordination des actions entre plusieurs sessions exécutées sur le même serveur.Bound sessions ease the coordination of actions across multiple sessions on the same server. 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.Bound sessions allow two or more sessions to share the same transaction and locks, and can work on the same data without lock conflicts. 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.Bound sessions can be created from multiple sessions within the same application or from multiple applications with separate sessions.

Pour participer à une session liée, une session doit appeler sp_getbindtoken ou srv_getbindtoken (via Open Data Services) pour obtenir un jeton une liaison.To participate in a bound session, a session calls sp_getbindtoken or srv_getbindtoken (through Open Data Services) to get a bind token. Un jeton d'association est une chaîne de caractères qui identifie de manière unique chaque transaction associée.A bind token is a character string that uniquely identifies each bound transaction. Le jeton d'association est ensuite transmis aux autres sessions à associer à la session en cours.The bind token is then sent to the other sessions to be bound with the current session. Les autres sessions se lient à la transaction en appelant sp_bindsession avec le jeton de liaison reçu de la première session.The other sessions bind to the transaction by calling sp_bindsession, using the bind token received from the first session.

Note

Une session doit avoir une transaction utilisateur active afin que sp_getbindtoken ou srv_getbindtoken réussisse.A session must have an active user transaction in order for sp_getbindtoken or srv_getbindtoken to succeed.

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.Bind tokens must be transmitted from the application code that makes the first session to the application code that subsequently binds their sessions to the first session. Il n'existe aucune instruction Transact-SQLTransact-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.There is no Transact-SQLTransact-SQL statement or API function that an application can use to get the bind token for a transaction started by another process. Les méthodes suivantes peuvent être utilisées pour transmettre un jeton d'association :Some of the methods that can be used to transmit a bind token include the following:

  • 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.If the sessions are all initiated from the same application process, bind tokens can be stored in global memory or passed into functions as a parameter.

  • 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).If the sessions are made from separate application processes, bind tokens can be transmitted using interprocess communication (IPC), such as a remote procedure call (RPC) or dynamic data exchange (DDE).

  • Les jetons d'association peuvent être stockés dans une instance de Moteur de base de données SQL ServerSQL Server Database Engine dans une table qui peut être lue par les processus voulant s'associer à la première session.Bind tokens can be stored in a table in an instance of the Moteur de base de données SQL ServerSQL Server Database Engine that can be read by processes wanting to bind to the first session.

    Dans un ensemble de sessions associées, une seule session peut être active à la fois.Only one session in a set of bound sessions can be active at any time. 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.If one session is executing a statement on the instance or has results pending from the instance, no other session bound to it can access the instance until the current session finishes processing or cancels the current statement. 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.If the instance is busy processing a statement from another of the bound sessions, an error occurs indicating that the transaction space is in use and the session should retry later.

    Chacune des sessions associées conserve son niveau d'isolation propre.When you bind sessions, each session retains its isolation level setting. 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.Using SET TRANSACTION ISOLATION LEVEL to change the isolation level setting of one session does not affect the setting of any other session bound to it.

Types de sessions associéesTypes of Bound Sessions

Les sessions associées peuvent être locales ou distribuées.The two types of bound sessions are local and distributed.

  • Sessions associées localesLocal bound session

    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éesDatabase Engine.Allows bound sessions to share the transaction space of a single transaction in a single instance of the Moteur de base de donnéesDatabase Engine.

  • Sessions associées distribuéesDistributed bound session

    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 MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC).Allows bound sessions to share the same transaction across two or more instances until the entire transaction is either committed or rolled back by using MicrosoftMicrosoft 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.Distributed bound sessions are not identified by a character string bind token; they are identified by distributed transaction identification numbers. 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.If a bound session is involved in a local transaction and executes an RPC on a remote server with SET REMOTE_PROC_TRANSACTIONS ON, the local bound transaction is automatically promoted to a distributed bound transaction by MS DTC and an MS DTC session is started.

Utilisation des sessions associéesWhen to Use Bound Sessions

Dans les versions précédentes de SQL ServerSQL Server, les sessions associées étaient principalement utilisées pour développer des procédures stockées étendues devant exécuter des instructions Transact-SQLTransact-SQL pour le processus qui les appelle.In earlier versions of SQL ServerSQL Server, bound sessions were primarily used in developing extended stored procedures that must execute Transact-SQLTransact-SQL statements on behalf of the process that calls them. 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.Having the calling process pass in a bind token as one parameter of the extended stored procedure allows the procedure to join the transaction space of the calling process, thereby integrating the extended stored procedure with the calling process.

Dans le Moteur de base de données SQL ServerSQL Server Database Engine, 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é.In the Moteur de base de données SQL ServerSQL Server Database Engine, stored procedures written using CLR are more secure, scalable, and stable than extended stored procedures. Procédures stockées CLR utilisent le SqlContext objet pour joindre le contexte de la session appelante, ne pas sp_bindsession.CLR-stored procedures use the SqlContext object to join the context of the calling session, not sp_bindsession.

Les sessions associées peuvent être utilisées pour développer des applications à trois niveaux où la logique de gestion est incluse dans des programmes distincts qui peuvent travailler en coopération sur une seule transaction commerciale.Bound sessions can be used to develop three-tier applications in which business logic is incorporated into separate programs that work cooperatively on a single business transaction. Ces programmes doivent être codés de manière à coordonner soigneusement leur accès à une base de données.These programs must be coded to carefully coordinate their access to a database. 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.Because the two sessions share the same locks, the two programs must not try to modify the same data at the same time. A tout moment, une seule session peut participer à la transaction ; aucune exécution en parallèle n'est possible.At any point in time, only one session can be doing work as part of the transaction; there can be no parallel execution. 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.The transaction can only be switched between sessions at well-defined yield points, such as when all DML statements have completed and their results have been retrieved.

Écriture de transactions performantesCoding Efficient Transactions

Il est important de réduire la durée des transactions au minimum.It is important to keep transactions as short as possible. 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.When a transaction is started, a database management system (DBMS) must hold many resources until the end of the transaction to protect the atomicity, consistency, isolation, and durability (ACID) properties of the 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.If data is modified, the modified rows must be protected with exclusive locks that prevent any other transaction from reading the rows, and exclusive locks must be held until the transaction is committed or rolled back. 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.Depending on transaction isolation level settings, SELECT statements may acquire locks that must be held until the transaction is committed or rolled back. 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.Especially in systems with many users, transactions must be kept as short as possible to reduce locking contention for resources between concurrent connections. 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.Long-running, inefficient transactions may not be a problem with small numbers of users, but they are intolerable in a system with thousands of users. À partir de SQL Server 2014SQL Server 2014, SQL ServerSQL Server prend en charge les transactions durables retardées.Beginning with SQL Server 2014SQL Server 2014SQL ServerSQL Server supports delayed durable transactions. Les transactions durables retardées ne garantissent pas la durabilité.Delayed durable transactions do not guarantee durability. Consultez la rubrique Durabilité des transactions pour plus d’informations.See the topic Transaction Durability for more information.

Directives de codageCoding Guidelines

Vous trouverez ci-dessous des directives vous permettant de coder des transactions performantes :These are guidelines for coding efficient transactions:

  • Évitez l'entrée de données par l'utilisateur au cours d'une transaction.Do not require input from users during a transaction.

    Effectuez toutes les entrées de données par l'utilisateur avant le début d'une transaction.Get all required input from users before a transaction is started. 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.If additional user input is required during a transaction, roll back the current transaction and restart the transaction after the user input is supplied. 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.Even if users respond immediately, human reaction times are vastly slower than computer speeds. 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.All resources held by the transaction are held for an extremely long time, which has the potential to cause blocking problems. 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.If users do not respond, the transaction remains active, locking critical resources until they respond, which may not happen for several minutes or even hours.

  • Si possible, n'ouvrez pas une transaction alors que vous êtes en train de consulter des données.Do not open a transaction while browsing through data, if at all possible.

    Ne démarrez pas de transaction avant que l'analyse préliminaire des données soit terminée.Transactions should not be started until all preliminary data analysis has been completed.

  • Limitez la durée de la transaction autant que possible.Keep the transaction as short as 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.After you know the modifications that have to be made, start a transaction, execute the modification statements, and then immediately commit or roll back. N'ouvrez pas la transaction tant que ce n'est pas nécessaire.Do not open the transaction before it is required.

  • 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.To reduce blocking, consider using a row versioning-based isolation level for read-only queries.

  • Utilisez avec discernement les niveaux d'isolement de transaction inférieurs.Make intelligent use of lower transaction isolation levels.

    De nombreuses applications peuvent être facilement codées pour utiliser le niveau d'isolement de lecture validée.Many applications can be readily coded to use a read-committed transaction isolation level. Toutes les transactions ne nécessitent pas l'utilisation d'un niveau d'isolement de transaction sérialisable.Not all transactions require the serializable transaction isolation level.

  • Utilisez les options de concurrence d'accès des curseurs les plus faibles, telles que les options de concurrence d'accès optimistes.Make intelligent use of lower cursor concurrency options, such as optimistic concurrency options.

    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.In a system with a low probability of concurrent updates, the overhead of dealing with an occasional "somebody else changed your data after you read it" error can be much lower than the overhead of always locking rows as they are read.

  • Limitez autant que possible le volume de données auxquelles accède votre transaction.Access the least amount of data possible while in a transaction.

    Le nombre de lignes verrouillées est ainsi limité, ce qui limite également la contention des transactions.This lessens the number of locked rows, thereby reducing contention between transactions.

Prévention des problèmes de concurrence et de ressourcesAvoiding Concurrency and Resource Problems

Pour prévenir les problèmes de concurrence et de ressources, soyez minutieux dans la gestion des transactions implicites.To prevent concurrency and resource problems, manage implicit transactions carefully. Dans les transactions implicites, l'instruction Transact-SQLTransact-SQL qui suit une instruction COMMIT ou ROLLBACK démarre automatiquement une nouvelle transaction.When using implicit transactions, the next Transact-SQLTransact-SQL statement after COMMIT or ROLLBACK automatically starts a new 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.This can cause a new transaction to be opened while the application browses through data, or even when it requires input from the user. 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.After completing the last transaction required to protect data modifications, turn off implicit transactions until a transaction is once again required to protect data modifications. Cette procédure permet à Moteur de base de données SQL ServerSQL Server Database Engine d'utiliser le mode autocommit lorsque l'application consulte des données ou attend une entrée de données par l'utilisateur.This process lets the Moteur de base de données SQL ServerSQL Server Database Engine use autocommit mode while the application is browsing data and getting input from the user.

De plus, quand le niveau d’isolation d’instantané est activé, même si une nouvelle transaction ne contient pas de verrous, une exécution longue empêche la suppression des anciennes versions dans tempdb.In addition, when the snapshot isolation level is enabled, although a new transaction will not hold locks, a long-running transaction will prevent the old versions from being removed from tempdb.

Gestion des transactions de longue duréeManaging Long-Running Transactions

Une transaction longue est une transaction active qui n’a pas été validée ou restaurée à temps.A long-running transaction is an active transaction that has not been committed or roll backed the transaction in a timely manner. 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.For example, if the beginning and end of a transaction is controlled by the user, a typical cause of a long-running transaction is a user starting a transaction and then leaving while the transaction waits for a response from the user.

Une transaction longue peut entraîner de graves problèmes pour une base de données, comme suit :A long running transaction can cause serious problems for a database, as follows:

  • Si une instance de serveur est arrêtée après qu’une transaction active a effectué de nombreuses modifications non validées, la phase de récupération du redémarrage suivant peut prendre beaucoup plus longue que la durée spécifiée par la intervalle de récupération server option de configuration ou par l’instruction ALTER DATABASE... SET TARGET_RECOVERY_TIME.If a server instance is shut down after an active transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take much longer than the time specified by the recovery interval server configuration option or by the ALTER DATABASE... SET TARGET_RECOVERY_TIME option. Ces options contrôlent la fréquence des points de contrôle actifs et indirects, respectivement.These options control the frequency of active and indirect checkpoints, respectively. Pour plus d’informations sur les types de points de contrôle, consultez Points de contrôle de base de données (SQL Server).For more information about the types of checkpoints, see Database Checkpoints (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.More importantly, although a waiting transaction might generate very little log, it holds up log truncation indefinitely, causing the transaction log to grow and possibly fill up. Si le journal des transactions est rempli, la base de données ne peut plus effectuer de mises à jour.If the transaction log fills up, the database cannot perform any more updates. Pour plus d’informations, consultez résoudre les problèmes d’un journal des transactions saturé (erreur SQL Server 9002), et journal des transactions (SQL Server).For more information, see Troubleshoot a Full Transaction Log (SQL Server Error 9002), and The Transaction Log (SQL Server).

Découverte des transactions de longue duréeDiscovering Long-Running Transactions

Pour rechercher des transactions de longue durée, appliquez une des procédures suivantes :To look for long-running transactions, use one of the following:

  • sys.dm_tran_database_transactionssys.dm_tran_database_transactions

    Cet affichage de gestion dynamique retourne des informations sur les transactions au niveau de la base de données.This dynamic management view returns information about transactions at the database level. Pour une transaction longue, 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).For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).

    Pour plus d’informations, consultez sys.dm_tran_database_transactions (Transact-SQL).For more information, see sys.dm_tran_database_transactions (Transact-SQL).

  • DBCC OPENTRANDBCC 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).This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back). Pour plus d’informations, consultez DBCC OPENTRAN (Transact-SQL).For more information, see DBCC OPENTRAN (Transact-SQL).

Arrêt d'une transactionStopping a Transaction

Vous devrez peut-être utiliser l'instruction KILL.You may have to use the KILL statement. Utilisez cette instruction avec précaution, particulièrement lorsque des processus critiques sont en cours d'exécution.Use this statement very carefully, however, especially when critical processes are running. Pour plus d’informations, consultez KILL (Transact-SQL).For more information, see KILL (Transact-SQL).

Icône de flèche utilisée avec le lien Retour au début dans ce GuideArrow icon used with Back to Top link In This Guide

Voir aussiSee Also

Isolation de SQL Server 2005 Row Versioning des transactions SQL Server 2005 Row Versioning-Based Transaction Isolation
Charge du contrôle de version de ligne Overhead of Row Versioning
Comment créer une transaction autonome dans SQL Server 2008How to create an autonomous transaction in SQL Server 2008