Niveaux d'isolement du moteur de base de données
Les transactions spécifient un niveau d'isolement. Ce niveau définit le degré d'isolement d'une transaction par rapport aux modifications de ressource ou de données apportées par d'autres transactions. Les niveaux d'isolement déterminent les effets secondaires de la concurrence (lectures incorrectes, lectures fantômes) qui sont autorisés.
Le niveau d'isolement d'une transaction régit les éléments suivants :
l'acquisition de verrous lors de la lecture de données, et le type de verrous nécessaires ;
la durée de vie des verrous de lecture ;
la réaction d'une opération de lecture qui fait référence à des lignes modifiées par une autre transaction :
blocage jusqu'à ce que le verrou exclusif sur la ligne soit levé,
récupération de la version validée de la ligne telle qu'elle était au début de l'instruction ou de la transaction,
lecture de la modification des données non validées.
Le choix d'un niveau d'isolement n'a aucune influence sur les verrous acquis pour protéger les modifications de données. Une transaction acquiert toujours un verrou exclusif sur les données qu'elle modifie et garde celui-ci jusqu'à ce qu'elle ait terminé son travail, indépendamment du niveau d'isolement défini pour elle. Dans le cas des opérations de lecture, le niveau d'isolement d'une transaction définit principalement son niveau de protection contre les effets des modifications apportées par les autres transactions.
Plus le niveau d'isolement est faible, plus le nombre de personnes susceptibles d'accéder aux données en même temps est élevé, et plus les effets secondaires de la concurrence (lectures incorrectes, mises à jour perdues) sont nombreux. Inversement, plus le niveau d'isolement est élevé, plus le nombre de types d'effets secondaires de la concurrence qu'un utilisateur est susceptible de rencontrer est réduit. Cependant, la quantité de ressources système nécessaires et la probabilité d'un blocage mutuel de transactions sont plus élevées. Le choix du niveau d'isolement adéquat dépend d'une mise en équilibre de l'espace réservé et des exigences en matière d'intégrité des données de l'application. Le niveau le plus élevé, sérialisable, garantit qu'une transaction récupère exactement les mêmes données à chaque fois qu'elle répète une opération de lecture, mais en utilisant un niveau de verrouillage susceptible de gêner les autres utilisateurs dans les systèmes multi-utilisateurs. Le niveau le plus bas, lecture non validée, permet la récupération de données qui ont été modifiées mais non validées par d'autres transactions. Ce niveau permet l'apparition de tous les effets secondaires de la concurrence, mais la charge du système est réduite puisqu'il n'y a ni verrouillage de lecture, ni versioning de ligne.
Niveaux d'isolement du moteur de base de données
La norme ISO définit les niveaux d'isolement suivants, tous pris en charge par le Moteur de base de données SQL Server :
lecture non validée « read uncommitted » (niveau d'isolement le plus bas et suffisant pour s'assurer que les données physiquement corrompues ne sont pas lues) ;
lecture validée « read committed » (niveau par défaut du moteur de base de données) ;
lecture renouvelable ;
sérialisable (niveau le plus élevé, dans lequel les transactions sont totalement isolées les unes des autres).
Important
Les opérations DDL et les transactions sur les tables répliquées peuvent échouer lorsque le niveau d'isolation sérialisable est demandé. Cela est dû au fait que les requêtes de réplication utilisent des indicateurs qui peuvent être incompatibles avec le niveau d'isolation sérialisable.
SQL Server prend également en charge deux niveaux d'isolation de transaction qui utilisent la gestion des versions de ligne. Le premier est une variante de l'isolement read committed, et le deuxième est un nouveau niveau d'isolement, celui de capture instantanée.
Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT a la valeur ON, le niveau d'isolement read committed utilise le versioning de ligne pour procurer une lecture cohérente au niveau des instructions. Les opérations de lecture ont besoin uniquement de verrous de table SCH-S, et pas de verrous de page ni de ligne. Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT a la valeur OFF, ce qui est le cas par défaut, le niveau d'isolement read committed agit de la même manière que dans les versions précédentes de SQL Server. Les deux variantes sont conformes à la définition de l'isolement read committed de l'ANSI.
Le niveau d'isolement de capture instantanée utilise le versioning de ligne pour assurer la cohérence des lectures au niveau de la transaction. Les opérations de lecture ont besoin uniquement de verrous de table SCH-S, et pas de verrous de page ni de ligne. Lorsque l'opération de lecture lit des lignes modifiées par une autre transaction, elle récupère la version de la ligne du début de la transaction. Vous pouvez utiliser l'isolation d'instantané pour une base de données uniquement lorsque la valeur ON est attribuée à l'option de base de données ALLOW_SNAPSHOT_ISOLATION. Par défaut, cette option est désactivée (OFF) pour les bases de données utilisateur.
[!REMARQUE]
SQL Server ne prend pas en charge la gestion des versions des métadonnées. Pour cette raison, il existe des restrictions sur les opérations DDL pouvant être effectuées dans une transaction explicite exécutée avec le niveau d'isolement de capture instantanée. Les instructions DDL suivantes ne sont pas autorisées avec le niveau d'isolement de capture 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 autre instruction DDL utilisant le langage CLR (Common Language Runtime). Ces instructions sont autorisées lorsque vous avez recours à l'isolement de capture instantanée au sein de transactions implicites. Par définition, une transaction implicite est une instruction unique qui permet d'appliquer la sémantique de l'isolement de capture instantanée, même avec des instructions DDL. Tout manquement à ce principe peut provoquer l'erreur 3961 : « La transaction d'isolement de capture instantanée a échoué dans la base de données '%.*ls' car l'objet auquel l'instruction a eu accès a été modifié par une instruction DDL dans une autre transaction simultanée depuis le début de cette transaction. Cela est interdit, car les métadonnées sont dépourvues de version. Une mise à jour simultanée des métadonnées peut provoquer des incohérences si elle est associée à un isolement de capture instantanée. »
Le tableau suivant répertorie les effets secondaires de la concurrence provoqués par les différents niveaux d'isolement.
Niveau d'isolement |
Lecture incorrecte |
Lecture non renouvelable |
Fantôme |
|---|---|---|---|
Lecture non validée |
Oui |
Oui |
Oui |
Lecture validée |
Non |
Oui |
Oui |
Lecture renouvelable |
Non |
Non |
Oui |
Capture instantanée |
Non |
Non |
Non |
Sérialisable |
Non |
Non |
Non |
Pour plus d'informations concernant les types de verrous spécifiques ou le versioning de ligne contrôlés par chaque niveau d'isolement de transaction, consultez SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
Voir aussi