DÉFINIR LE NIVEAU D’ISOLATION DES TRANSACTIONS (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Base de données SQL dans Microsoft Fabric

Contrôle le verrouillage et le comportement de contrôle de version de ligne des instructions Transact-SQL émises par une connexion à SQL Server.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server, Azure SQL Database et base de données SQL dans Microsoft Fabric.

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Syntaxe pour Azure Synapse Analytics et Parallel Data Warehouse.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Remarque

Azure Synapse Analytics implémente des transactions ACID. Le niveau d’isolation par défaut est READ UNCOMMITTED. Vous pouvez le modifier READ COMMITTED SNAPSHOT ISOLATION en activant ON l’option READ_COMMITTED_SNAPSHOT de base de données d’une base de données utilisateur lorsqu’elle est connectée à la master base de données. Une fois activée, toutes les transactions de cette base de données sont exécutées sous READ COMMITTED SNAPSHOT ISOLATION et le paramètre READ UNCOMMITTED au niveau de la session n’est pas respecté. Pour plus d’informations, consultez les options ALTER DATABASE SET (Transact-SQL).

Arguments

LECTURE NON VALIDÉE

Spécifie que les instructions peuvent lire les lignes qui ont été modifiées par d’autres transactions, mais qui ne sont pas encore validées.

Les transactions exécutées au READ UNCOMMITTED niveau n’émettent pas de verrous partagés pour empêcher d’autres transactions de modifier les données lues par la transaction actuelle. READ UNCOMMITTED les transactions ne sont pas non plus bloquées par des verrous exclusifs qui empêcheraient la transaction actuelle de lire les lignes qui ont été modifiées, mais non validées par d’autres transactions. Lorsque cette option est définie, il est possible de lire des modifications non validées, appelées lectures incorrectes. 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. Cette option a le même effet que la définition NOLOCK sur toutes les tables dans toutes les SELECT instructions d’une transaction. Il s'agit du niveau d'isolement le moins restrictif.

Dans SQL 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 :

• Niveau READ COMMITTED d’isolation avec l’option READ_COMMITTED_SNAPSHOT de base de données définie sur ON.

• Niveau SNAPSHOT d’isolation. Pour plus d’informations sur l’isolation des instantanés, consultez Isolation d’instantanés dans SQL Server.

LECTURE VALIDÉE

Spécifie que les instructions ne peuvent pas lire les données qui ont été modifiées, mais non validées par d’autres transactions. Cela permet d'éviter les lectures incorrectes. 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. Cette option a la valeur par défaut SQL Server.

Le comportement de READ COMMITTED dépend du paramètre de l’option READ_COMMITTED_SNAPSHOT de base de données :

• S’il READ_COMMITTED_SNAPSHOT est défini OFF sur (valeur par défaut sur SQL Server), le moteur de base de données utilise des verrous partagés pour empêcher d’autres transactions de modifier des lignes pendant que la transaction actuelle exécute une opération de lecture. Les verrous partagés empêchent également l'instruction de lire des lignes modifiées par d'autres transactions, tant que celles-ci ne sont pas terminées. Le type de verrou partagé détermine quand il est libéré. Les verrous de ligne sont levés avant que la ligne suivante ne soit traitée. 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.

• S’il READ_COMMITTED_SNAPSHOT est défini ONsur , le moteur de base de données utilise le contrôle de version de ligne pour présenter chaque instruction avec un instantané cohérent transactionnel des données tel qu’il existait au début de l’instruction. Les verrous ne sont pas utilisés pour protéger les données contre les mises à jour par d’autres transactions.

  • READ_COMMITTED_SNAPSHOT ON est la base de données par défaut sur Azure SQL Database et SQL Database dans Microsoft Fabric.

Important

Le choix d’un niveau d’isolation des transactions n’affecte pas les verrous acquis pour protéger les modifications des données. Une transaction acquiert toujours un verrou exclusif sur les données qu'elle modifie et garde celui-ci jusqu'à ce qu'elle ait terminé son travail, indépendamment du niveau d'isolation défini pour elle. En outre, une mise à jour effectuée au niveau de l’isolation READ COMMITTED utilise des verrous de mise à jour sur les lignes de données sélectionnées, tandis qu’une mise à jour effectuée au niveau de l’isolation SNAPSHOT utilise des versions de lignes pour sélectionner des lignes à mettre à jour. 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. Pour plus d’informations, consultez le Guide de verrouillage des transactions et de contrôle de version des lignes.

L'isolement d'instantané prend en charge les données FILESTREAM. En mode d’isolation d’instantané, les données FILESTREAM lues par n’importe quelle instruction d’une transaction sont la version cohérente transactionnelle des données qui existaient au début de la transaction.

Lorsque l’option READ_COMMITTED_SNAPSHOT de base de données est ON, vous pouvez utiliser l’indicateur de table pour demander le READCOMMITTEDLOCK verrouillage partagé au lieu du contrôle de version de ligne pour des instructions individuelles dans les transactions exécutées au niveau de l’isolation READ COMMITTED .

Remarque

Lorsque vous définissez l’option READ_COMMITTED_SNAPSHOT , seule la connexion qui exécute la ALTER DATABASE commande est autorisée dans la base de données. Il ne doit pas y avoir d’autre connexion ouverte dans la base de données tant qu’elle ALTER DATABASE n’est pas terminée. La base de données n’a pas besoin d’être en mode mono-utilisateur.

LECTURE REPRODUCTIBLE

Spécifie que les instructions ne peuvent pas lire les données qui ont été modifiées mais qui n’ont pas encore été validées par d’autres transactions, et qu’aucune autre transaction ne peut modifier les données lues par la transaction actuelle tant que la transaction actuelle n’est pas terminée.

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. Cela empêche les autres transactions de modifier les lignes lues par la transaction actuelle. D'autres transactions peuvent insérer de nouvelles lignes lorsque celles-ci correspondent aux conditions de recherche des instructions émises par la transaction active. Si la transaction actuelle retente ensuite l’instruction, elle récupère les nouvelles lignes, ce qui entraîne des lectures fantômes. Étant donné que les verrous partagés sont conservés à la fin d’une transaction au lieu d’être libérés à la fin de chaque instruction, la concurrence est inférieure au niveau d’isolation par défaut READ COMMITTED . Utilisez cette option uniquement si c'est nécessaire.

SNAPSHOT

Spécifie que les données lues par n’importe quelle instruction d’une transaction sont la version cohérente transactionnelle des données qui existaient au début de la transaction. La transaction peut seulement reconnaître les modifications de données qui ont été validées avant qu'elle ne commence. Les modifications de données apportées par d’autres transactions après le début de la transaction actuelle ne sont pas visibles par les instructions s’exécutant dans la transaction actuelle. C’est comme si les instructions d’une transaction obtenaient un instantané des données validées telles qu’elles existaient au début de la transaction.

Sauf lorsqu’une base de données est récupérée, SNAPSHOT les transactions ne demandent pas de verrous lors de la lecture des données. SNAPSHOT les transactions qui lisent des données ne bloquent pas les autres transactions d’écriture de données. Les transactions écrivant des données ne bloquent pas les SNAPSHOT transactions de la lecture des données.

Pendant la phase de restauration d’une récupération de base de données, SNAPSHOT les transactions demandent un verrou si une tentative de lecture des données verrouillées par une autre transaction qui est restaurée. La SNAPSHOT transaction est bloquée jusqu’à ce que cette transaction soit restaurée. Le verrou est libéré immédiatement après son octroi.

L’option ALLOW_SNAPSHOT_ISOLATION de base de données doit être définie ON pour pouvoir démarrer une transaction qui utilise le SNAPSHOT niveau d’isolation. Si une transaction utilisant le SNAPSHOT niveau d’isolation accède aux données dans plusieurs bases de données, ALLOW_SNAPSHOT_ISOLATION doit être définie ON sur dans chaque base de données.

Une transaction ne peut pas être définie sur le SNAPSHOT niveau d’isolation qui a démarré avec un autre niveau d’isolation . Cela entraîne l’abandon de la transaction. Si une transaction démarre dans le SNAPSHOT niveau d’isolation, vous pouvez la remplacer par un autre niveau d’isolation, puis revenir à SNAPSHOT. Une transaction démarre la première fois qu'elle accède aux données.

Une transaction exécutée sous SNAPSHOT le niveau d’isolation peut afficher les modifications apportées par cette transaction. Par exemple, si la transaction effectue une UPDATE table, puis émet une SELECT instruction sur la même table, les données modifiées sont incluses dans le jeu de résultats.

Remarque

En mode d’isolation d’instantané, les données FILESTREAM lues par une instruction dans une transaction sont la version cohérente transactionnelle des données qui existaient au début de la transaction, et non au début de l’instruction.

SERIALIZABLE

Spécifie les conditions suivantes :

• Les instructions ne peuvent pas lire les données qui ont été modifiées, mais qui n’ont pas encore été validées par d’autres transactions.

• Aucune autre transaction ne peut modifier les données lues par la transaction actuelle jusqu’à la fin de la transaction actuelle.

• D’autres transactions ne peuvent pas insérer de nouvelles lignes avec des valeurs de clé qui tombent dans la plage de clés lues par les instructions de la transaction actuelle tant que la transaction actuelle n’est pas terminée.

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. 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. Cela signifie que si l’une des instructions d’une transaction est exécutée une deuxième fois, elles lisent le même ensemble de lignes. Les verrous de groupes sont conservés jusqu'au terme de la transaction. 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. Comme l'accès concurrentiel est plus limité, utilisez cette option uniquement lorsque cela s'avère nécessaire. Cette option a le même effet que la définition HOLDLOCK sur toutes les tables dans toutes les SELECT instructions d’une transaction.

Notes

Une seule des options de niveau d’isolation peut être définie à la fois, et elle reste définie pour cette connexion jusqu’à ce qu’elle soit explicitement modifiée. Toutes les opérations de lecture effectuées dans la transaction fonctionnent sous les règles du niveau d’isolation spécifié, sauf si une indication de table dans la FROM clause d’une instruction spécifie un comportement de verrouillage ou de contrôle de version différent pour une table.

Les niveaux d'isolation des transactions définissent le type de verrous placés sur les opérations de lecture. Les verrous partagés acquis pour READ COMMITTED ou REPEATABLE READ sont généralement des verrous de ligne, bien que les verrous de ligne puissent être réaffectés à des verrous de page ou de table si un nombre significatif de lignes d’une page ou d’une table sont référencées par la lecture. Si la transaction modifie une ligne après sa lecture, la transaction acquiert un verrou exclusif pour protéger cette ligne et le verrou exclusif est conservé jusqu’à la fin de la transaction. Par exemple, si une REPEATABLE READ transaction a un verrou partagé sur une ligne et que la transaction modifie ensuite la ligne, le verrou de ligne partagé est converti en verrou de ligne exclusif.

À une exception près, vous pouvez changer de niveau d'isolation à tout moment au cours d'une transaction. L’exception se produit lors de la modification d’un niveau d’isolation à l’isolation SNAPSHOT . Ce changement de niveau provoque l'échec et l'annulation de la transaction. Toutefois, vous pouvez modifier une transaction démarrée en SNAPSHOT isolation par n’importe quel autre niveau d’isolation.

Dans ce cas, les ressources lues après cette modification sont protégées conformément aux règles du nouveau niveau. Les ressources lues avant la modification continuent d'être protégées selon les règles du niveau précédent. Par exemple, si une transaction est passée de READ COMMITTED à SERIALIZABLE, les verrous partagés acquis après la modification sont maintenant conservés jusqu’à la fin de la transaction.

Si vous émettez SET TRANSACTION ISOLATION LEVEL dans une procédure stockée ou un déclencheur, lorsque l’objet retourne le niveau d’isolation est réinitialisé au niveau en vigueur lorsque l’objet a été appelé. Par exemple, si vous définissez REPEATABLE READ dans un lot et que le lot appelle ensuite une procédure stockée qui définit le niveau SERIALIZABLEd’isolation , le paramètre de niveau d’isolation revient au REPEATABLE READ moment où la procédure stockée retourne le contrôle au lot.

Remarque

Les fonctions définies par l’utilisateur et les types CLR (Common Language Runtime) ne peuvent pas s’exécuter SET TRANSACTION ISOLATION LEVEL. Cependant, vous pouvez remplacer le niveau d'isolation en utilisant un indicateur de table. Pour plus d’informations, consultez Indicateurs de table (Transact-SQL).

Lorsque vous utilisez sp_bindsession pour lier deux sessions, chaque session conserve son paramètre de niveau d’isolation. L’utilisation SET TRANSACTION ISOLATION LEVEL pour modifier le paramètre de niveau d’isolation d’une session n’affecte pas le paramètre d’autres sessions liées à celle-ci.

SET TRANSACTION ISOLATION LEVEL prend effet au moment de l’exécution ou de l’exécution, et non au moment de l’analyse.

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 :

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED utilisation du contrôle de version de ligne

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. Pour plus d’informations sur les opérations de chargement en bloc, consultez Importation et exportation en bloc de données (SQL Server).

Les bases de données compatibles FILESTREAM prennent en charge les niveaux d'isolement des transactions suivants.

Niveau d'isolation	accès Transact-SQL	Accès au système de fichiers
Lecture non validée	SQL Server	Non pris en charge
Lecture validée	SQL Server	SQL Server
Lecture reproductible	SQL Server	Non pris en charge
Sérialisables	SQL Server	Non pris en charge
Lecture d’un instantané validé	SQL Server	SQL Server
Snapshot	SQL Server	SQL Server

Exemples

L'exemple suivant définit le paramètre TRANSACTION ISOLATION LEVEL pour la session. Pour chaque instruction Transact-SQL suivante, SQL Server maintient tous les verrous partagés jusqu'à la fin de la transaction.

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Contenu connexe

• MODIFIER LA BASE DE DONNÉES (Transact-SQL)
• DBCC USEROPTIONS (Transact-SQL)
• SELECT (Transact-SQL)
• Instructions SET (Transact-SQL)
• Indicateurs de table (Transact-SQL)