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 repè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 SQL-99 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).
SQL Server 2005 prend également deux niveaux d'isolement de transaction qui utilisent le versioning 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. L'isolement de capture instantanée est activé lorsque l'option de base de données ALLOW_SNAPSHOT_ISOLATION est activée (ON). Par défaut, cette option est désactivée (OFF) pour les bases de données utilisateur.
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
Concepts
Effet des accès concurrentiels
Types de contrôles de concurrence