SET TRANSACTION ISOLATION LEVEL (Transact-SQL)SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Cette rubrique s’applique à : OuiSQL Server (à partir de 2008)Ouibase de données SQL AzureOuiAzure SQL Data Warehouse Oui Parallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Contrôle le verrouillage et le comportement de contrôle de version de ligne des instructions Transact-SQLTransact-SQL émises par une connexion à SQL ServerSQL Server.Controls the locking and row versioning behavior of Transact-SQLTransact-SQL statements issued by a connection to SQL ServerSQL Server.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

-- Syntax for SQL Server and Azure SQL Database

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

ArgumentsArguments

READ UNCOMMITTEDREAD UNCOMMITTED
Spécifie que les instructions peuvent lire des lignes qui ont été modifiées par d'autres transactions, mais pas encore validées.Specifies that statements can read rows that have been modified by other transactions but not yet committed.

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 running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. Par ailleurs, les transactions READ UNCOMMITTED ne sont pas bloquées par des verrous exclusifs qui empêcheraient la transaction active de lire des lignes modifiées, mais non validées, par d'autres transactions.READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. Lorsque cette option est définie, vous avez la possibilité de lire des modifications non validées, appelées lectures incorrectes.When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Les valeurs peuvent changer dans les données et des lignes peuvent apparaître ou disparaître dans le dataset avant la fin de la transaction.Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. Cette option a le même effet que l'activation de l'option NOLOCK dans toutes les tables de toutes les instructions SELECT d'une transaction.This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. Il s'agit du niveau d'isolement le moins restrictif.This is the least restrictive of the isolation levels.

Dans SQL ServerSQL Server, vous pouvez également limiter les contentions de verrouillage tout en protégeant les transactions de lectures erronées de modifications de données non validées en utilisant :In SQL ServerSQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

  • Le niveau d’isolation READ COMMITTED avec l’option de base de données READ_COMMITTED_SNAPSHOT activée (ON)The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

  • le niveau d'isolement SNAPSHOT.The SNAPSHOT isolation level.

    READ COMMITTEDREAD COMMITTED
    Spécifie que les instructions ne peuvent pas lire les données modifiées mais non validées par d'autres transactions.Specifies that statements cannot read data that has been modified but not committed by other transactions. Cela permet d'éviter les lectures incorrectes.This prevents dirty reads. Les données peuvent être modifiées par d'autres transactions entre deux instructions au sein de la transaction active, ce qui aboutit à des lectures non renouvelables ou à des données fantômes.Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. Il s'agit de l'option par défaut dans SQL ServerSQL Server.This option is the SQL ServerSQL Server default.

    Le comportement de READ COMMITTED dépend de la valeur affectée à l'option de base de données READ_COMMITTED_SNAPSHOT :The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

  • Si l'option READ_COMMITTED_SNAPSHOT a la valeur OFF (valeur par défaut), le Moteur de base de donnéesDatabase Engine 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.If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Moteur de base de donnéesDatabase Engine 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. Le type du verrou partagé détermine quand il sera levé.The shared lock type determines when it will be released. Les verrous de ligne sont levés avant que la ligne suivante ne soit traitée.Row locks are released before the next row is processed. Les verrous de page sont levés quand la page suivante est lue et les verrous de table sont levés quand l’exécution de l’instruction se termine.Page locks are released when the next page is read, and table locks are released when the statement finishes.

    Note

    Si READ_COMMITTED_SNAPSHOT a la valeur ON, le Moteur de base de donnéesDatabase Engine 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.If READ_COMMITTED_SNAPSHOT is set to ON, the Moteur de base de donnéesDatabase 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.

    L'isolement d'instantané prend en charge les données FILESTREAM.Snapshot isolation supports FILESTREAM data. En mode d'isolement d'instantané, les données FILESTREAM lues par n'importe quelle instruction d'une transaction représenteront la version cohérente d'un point de vue transactionnel des données qui existaient au début de la transaction.Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

    Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT a la valeur ON, vous pouvez utiliser l'indicateur de table READCOMMITTEDLOCK pour demander le verrouillage partagé au lieu du contrôle de version de ligne pour chacune des instructions individuelles des transactions qui s'exécutent au niveau d'isolation READ COMMITTED.When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.

Note

Lorsque vous définissez l'option READ_COMMITTED_SNAPSHOT, seule la connexion exécutant la commande ALTER DATABASE est autorisée dans la base de données.When you set 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.

REPEATABLE READREPEATABLE READ
Spécifie que les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions, et qu'aucune autre transaction ne peut modifier les données lues par la transaction active tant que celle-ci n'est pas terminée.Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Des verrous partagés sont placés sur toutes les données lues par chaque instruction de la transaction et maintenus jusqu'à la fin de la transaction.Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. Cela évite que d'autres transactions modifient des lignes qui ont été lues par la transaction active.This prevents other transactions from modifying any rows that have been read by the current transaction. D'autres transactions peuvent insérer de nouvelles lignes lorsque celles-ci correspondent aux conditions de recherche des instructions émises par la transaction active.Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. Si par la suite la transaction active réexécute l'instruction, elle récupère les nouvelles lignes, ce qui aboutit à des lectures fantômes.If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Comme les verrous partagés sont maintenus jusqu'à la fin d'une transaction au lieu d'être débloqués à la fin de chaque instruction, l'accès concurrentiel est moindre qu'avec le niveau d'isolation READ COMMITTED par défaut.Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Utilisez cette option uniquement si c'est nécessaire.Use this option only when necessary.

SNAPSHOTSNAPSHOT
Spécifie que les données lues par n’importe quelle instruction d’une transaction représentent la version cohérente d’un point de vue transactionnel des données qui existaient au début de la transaction.Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. La transaction peut seulement reconnaître les modifications de données qui ont été validées avant qu'elle ne commence.The transaction can only recognize data modifications that were committed before the start of the transaction. Autrement dit, les modifications de données effectuées par d'autres transactions après le début de la transaction active ne sont pas visibles pour les instructions qui s'exécutent dans le cadre de ladite transaction.Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. Tout se passe comme si les instructions d'une transaction obtenaient un instantané des données validées telles qu'elles existaient au début de cette transaction.The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Sauf lors de la récupération d'une base de données, les transactions SNAPSHOT ne demandent pas de verrouillage lors de la lecture des données.Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. Les transactions SNAPSHOT qui lisent des données n'empêchent pas d'autres transactions d'écrire des données.SNAPSHOT transactions reading data do not block other transactions from writing data. De même, les transactions qui écrivent des données n'empêchent pas des transactions SNAPSHOT de lire des données.Transactions writing data do not block SNAPSHOT transactions from reading data.

Au cours de la phase de restauration de la récupération d'une base de données, les transactions SNAPSHOT demandent un verrou en cas de tentative de lecture de données verrouillées par une autre transaction en cours de restauration.During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. La transaction SNAPSHOT est bloquée jusqu'à la restauration de l'autre transaction.The SNAPSHOT transaction is blocked until that transaction has been rolled back. Le verrouillage est levé dès qu'il a été accordé.The lock is released immediately after it has been granted.

L'option de base de données ALLOW_SNAPSHOT_ISOLATION doit être activée (ON) pour que vous puissiez démarrer une transaction utilisant le niveau d'isolation SNAPSHOT.The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. Si une transaction qui utilise le niveau d'isolation SNAPSHOT accède à des données figurant dans plusieurs bases de données, l'option ALLOW_SNAPSHOT_ISOLATION doit avoir la valeur ON dans chacune de ces bases de données.If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

Une transaction ne peut pas être définie avec le niveau d'isolation SNAPSHOT si elle a commencé avec un autre niveau d'isolation ; cela la ferait échouer.A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. Si une transaction commence au niveau d'isolation SNAPSHOT, vous pouvez la faire passer à un autre niveau d'isolation puis revenir au niveau SNAPSHOT.If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. Une transaction démarre la première fois qu'elle accède aux données.A transaction starts the first time it accesses data.

Une transaction exécutée avec le niveau d'isolation SNAPSHOT peut voir les modifications qui ont été effectuées par cette transaction.A transaction running under SNAPSHOT isolation level can view changes made by that transaction. Par exemple, si la transaction effectue une opération UPDATE sur une table puis exécute une instruction SELECT sur cette même table, les données modifiées figureront dans le jeu de résultats.For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

Note

En mode d'isolement d'instantané, les données FILESTREAM lues par n'importe quelle instruction d'une transaction représenteront la version cohérente d'un point de vue transactionnel des données qui existaient au début de la transaction, et non au début de l'instruction.Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction, not at the start of the statement.

SERIALIZABLESERIALIZABLE
Spécifie les indications suivantes :Specifies the following:

  • Les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions.Statements cannot read data that has been modified but not yet committed by other transactions.

  • Aucune autre transaction ne peut modifier des données qui ont été lues par la transaction active tant que celle-ci n'est pas terminée.No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  • Les autres transactions ne peuvent pas insérer de nouvelles lignes avec des valeurs de clés comprises dans le groupe de clés lues par des instructions de la transaction active, tant que celle-ci n'est pas terminée.Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

    Des verrous de groupes sont placés dans les groupes de valeurs de clés qui correspondent aux conditions de recherche de chaque instruction exécutée dans une transaction.Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. Cela empêche les autres transactions de mettre à jour ou d'insérer des lignes qui pourraient intervenir dans les instructions exécutées par la transaction active.This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. Autrement dit, si l'une ou l'autre des instructions d'une transaction est exécutée une seconde fois, elle lira le même groupe de lignes.This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. Les verrous de groupes sont conservés jusqu'au terme de la transaction.The range locks are held until the transaction completes. C'est le plus restrictif des niveaux d'isolation, parce qu'il verrouille des groupes de clés entiers et laisse les verrous en place jusqu'à la fin de la transaction.This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Comme l'accès concurrentiel est plus limité, utilisez cette option uniquement lorsque cela s'avère nécessaire.Because concurrency is lower, use this option only when necessary. Cette option a le même effet que l'utilisation de l'option HOLDLOCK dans toutes les tables de toutes les instructions SELECT d'une transaction.This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Notes Remarks

Une seule des options de niveau d'isolation peut être définie à la fois, et elle reste en vigueur durant cette connexion tant qu'elle n'est pas explicitement modifiée.Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. Toutes les opérations de lecture effectuées au sein de la transaction obéissent aux règles du niveau d'isolation spécifié, sauf si un indicateur de table de la clause FROM d'une instruction spécifie un autre verrouillage ou un autre comportement de contrôle de version pour une table.All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

Les niveaux d'isolation des transactions définissent le type de verrous placés sur les opérations de lecture.The transaction isolation levels define the type of locks acquired on read operations. Les verrous partagés placés pour READ COMMITTED ou REPEATABLE READ sont généralement des verrous de ligne, bien que ceux-ci puissent être promus au rang de verrous de page ou de table si la lecture fait référence à un nombre important de lignes d'une page ou d'une table.Shared locks acquired for READ COMMITTED or REPEATABLE READ are generally row locks, although the row locks can be escalated to page or table locks if a significant number of the rows in a page or table are referenced by the read. Si une ligne est modifiée par la transaction après avoir été lue, la transaction acquiert un verrou exclusif pour protéger cette ligne et ce verrou est maintenu jusqu'à la fin de la transaction.If a row is modified by the transaction after it has been read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes. Par exemple, si une transaction REPEATABLE READ utilise un verrou partagé sur une ligne et qu'elle modifie ensuite cette ligne, le verrou partagé est converti en verrou exclusif de ligne.For example, if a REPEATABLE READ transaction has a shared lock on a row, and the transaction then modifies the row, the shared row lock is converted to an exclusive row lock.

À une exception près, vous pouvez changer de niveau d'isolation à tout moment au cours d'une transaction.With one exception, you can switch from one isolation level to another at any time during a transaction. L'exception intervient lorsque vous passez d'un niveau d'isolation (quel qu'il soit) au niveau d'isolation SNAPSHOT.The exception occurs when changing from any isolation level to SNAPSHOT isolation. Ce changement de niveau provoque l'échec et l'annulation de la transaction.Doing this causes the transaction to fail and roll back. Cependant, vous pouvez faire passer une transaction démarrée au niveau d'isolation SNAPSHOT à un autre niveau d'isolation.However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.

Dans ce cas, les ressources lues après cette modification sont protégées conformément aux règles du nouveau niveau.When you change a transaction from one isolation level to another, resources that are read after the change are protected according to the rules of the new level. Les ressources lues avant la modification continuent d'être protégées selon les règles du niveau précédent.Resources that are read before the change continue to be protected according to the rules of the previous level. Par exemple, si une transaction est passée du niveau READ COMMITTED au niveau SERIALIZABLE, les verrous partagés acquis après le changement sont alors maintenus jusqu'à la fin de la transaction.For example, if a transaction changed from READ COMMITTED to SERIALIZABLE, the shared locks acquired after the change are now held until the end of the transaction.

Si vous émettez SET TRANSACTION ISOLATION LEVEL dans une procédure stockée ou un déclencheur, lorsque l'objet rend le contrôle, le niveau d'isolation est rétabli au niveau qui était en vigueur lors de l'appel de cet objet.If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. Par exemple, si vous définissez REPEATABLE READ dans un traitement et que ce traitement appelle ensuite une procédure stockée qui affecte la valeur SERIALIZABLE au niveau d'isolation, vous revenez au niveau d'isolation REPEATABLE READ lorsque la procédure stockée rend le contrôle au traitement.For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

Note

Les fonctions définies par l'utilisateur et les types CLR (Common Language Runtime) définis par l'utilisateur ne peuvent pas exécuter SET TRANSACTION ISOLATION LEVEL.User-defined functions and common language runtime (CLR) user-defined types cannot execute SET TRANSACTION ISOLATION LEVEL. Cependant, vous pouvez remplacer le niveau d'isolation en utilisant un indicateur de table.However, you can override the isolation level by using a table hint. Pour plus d’informations, consultez Indicateurs de table (Transact-SQL).For more information, see Table Hints (Transact-SQL).

Lorsque vous utilisez sp_bindsession pour lier deux sessions, chacune d'elles conserve son niveau d'isolation.When you use sp_bindsession to bind two sessions, each session retains its isolation level setting. L'utilisation de SET TRANSACTION ISOLATION LEVEL pour redéfinir le niveau d'isolation d'une session n'affecte pas celui des autres sessions liées.Using SET TRANSACTION ISOLATION LEVEL to change the isolation level setting of one session does not affect the setting of any other sessions bound to it.

SET TRANSACTION ISOLATION LEVEL prend effet au moment de l'exécution, et non durant l'analyse.SET TRANSACTION ISOLATION LEVEL takes effect at execute or run time, and not at parse time.

Les opérations de chargement en masse optimisées portant sur des segments verrouillent les requêtes qui s'exécutent sous les niveaux d'isolation suivants :Optimized bulk load operations on heaps block queries that are running under the following isolation levels:

  • SNAPSHOTSNAPSHOT

  • READ UNCOMMITTEDREAD UNCOMMITTED

  • READ COMMITTED avec le contrôle de version de ligneREAD COMMITTED using row versioning

    Inversement, les requêtes exécutées sous ces niveaux d'isolation bloquent les opérations de chargement en masse optimisées portant sur des segments.Conversely, queries that run under these isolation levels block optimized bulk load operations on heaps. Pour plus d’informations sur les opérations de chargement en bloc, consultez Importation et exportation en bloc de données (SQL Server).For more information about bulk load operations, see Bulk Import and Export of Data (SQL Server).

    Les bases de données compatibles FILESTREAM prennent en charge les niveaux d'isolement des transactions suivants.FILESTREAM-enabled databases support the following transaction isolation levels.

Niveau d'isolationIsolation level Accès Transact SQLTransact SQL access Accès au système de fichiersFile system access
Lecture non validéeRead uncommitted SQL Server 2017SQL Server 2017 Non pris en chargeUnsupported
Lecture validéeRead committed SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
Lecture renouvelableRepeatable read SQL Server 2017SQL Server 2017 Non pris en chargeUnsupported
SérialisableSerializable SQL Server 2017SQL Server 2017 Non pris en chargeUnsupported
Capture instantanée Read CommittedRead committed snapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
SnapshotSnapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017

ExemplesExamples

L'exemple suivant définit le paramètre TRANSACTION ISOLATION LEVEL pour la session.The following example sets the TRANSACTION ISOLATION LEVEL for the session. Pour chaque instruction Transact-SQLTransact-SQL suivante, SQL ServerSQL Server maintient tous les verrous partagés jusqu'à la fin de la transaction.For each Transact-SQLTransact-SQL statement that follows, SQL ServerSQL Server holds all of the shared locks until the end of the transaction.

USE AdventureWorks2012;  
GO  
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
GO  
BEGIN TRANSACTION;  
GO  
SELECT *   
    FROM HumanResources.EmployeePayHistory;  
GO  
SELECT *   
    FROM HumanResources.Department;  
GO  
COMMIT TRANSACTION;  
GO  

Voir aussiSee Also

ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
DBCC USEROPTIONS (Transact-SQL) DBCC USEROPTIONS (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
Instructions SET (Transact-SQL) SET Statements (Transact-SQL)
Indicateurs de table (Transact-SQL)Table Hints (Transact-SQL)