Présentation des niveaux d'isolement basés sur le versioning de ligne

Mis à jour : 12 décembre 2006

Le versioning de ligne est un cadre général polyvalent dans SQL Server qui permet d'effectuer les opérations suivantes :

• Construction des tables inserted et deleted dans les déclencheurs. Toutes les lignes modifiées par le déclencheur reçoivent une version, 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.
• Prise en charge des ensembles de résultats actifs multiples (MARS, Multiple Active Result Sets). 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.
• Prise en charge des opérations d'index qui spécifient l'option ONLINE.
• Prise en charge des niveaux d'isolement des transactions basés sur le versioning de ligne :
  • Une nouvelle implémentation du niveau d'isolement read committed qui utilise le versioning de ligne pour assurer la cohérence de la lecture au niveau de l'instruction.
  • Un nouveau niveau d'isolement, la capture instantanée, pour assurer la cohérence de la lecture au niveau de la transaction.

La base de données tempdb doit avoir suffisamment d'espace pour contenir la banque des versions. Lorsque la base de données tempdb est pleine, les opérations de mise à jour ne génèrent plus de versions et continuent à aboutir, mais les opérations de lecture risquent d'échouer en raison de l'absence d'une version de ligne particulière. Ceci a des conséquences sur les opérations comme les déclencheurs, MARS et l'indexation en ligne. Pour plus d'informations, consultez Utilisation de la ressource de versioning de ligne.

L'utilisation du versioning de ligne pour les transactions read committed et les transactions de captures instantanées se fait en deux étapes :

1. Activez (ON) l'option de base de données READ_COMMITTED_SNAPSHOT et/ou l'option ALLOW_SNAPSHOT_ISOLATION.
2. Définissez le niveau d'isolement des transactions approprié dans une application :
   • Lorsque l'option READ_COMMITTED_SNAPSHOT est activée (ON), les transactions qui définissent le niveau d'isolement read committed utilisent le versioning de ligne.
   • Lorsque l'option de base de données ALLOW_SNAPSHOT_ISOLATION est activée (ON), les transactions peuvent définir le niveau d'isolement de capture instantanée.

Lorsqu'une seule des deux options est activée, le Moteur de base de données SQL Server 2005 attribue un numéro de séquence de transaction (XSN) à chaque transaction qui manipule des données au moyen du versioning de ligne. Les transactions démarrent au moment où une instruction BEGIN TRANSACTION est exécutée. 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. Ce numéro augmente de 1 à chaque fois qu'il est attribué.

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. Chaque fois qu'une ligne est modifiée par une transaction, l'instance de Moteur de base de données enregistre une version de l'image validée d'origine de la ligne dans la base de données tempdb. Chaque version porte le numéro de séquence de la transaction responsable de la modification. Les versions des lignes modifiées sont enchaînées au moyen d'une liste de liens. 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 aux versions gérées, stockées dans tempdb.

Remarque :
Dans le cas de la modification d'un objet volumineux (LOB, large object), seul le fragment modifié est copié dans la banque des versions dans la base de données 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 « versioning de ligne ». Le Moteur de base de données 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.

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. Ces versions de lignes sont libérées lorsqu'elles ne sont plus nécessaires. Un thread d'arrière-plan supprime périodiquement les versions de lignes dépassées.

Remarque :
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. 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.

Comportement lors de la lecture de données

Lorsque des transactions s'exécutant sous le régime d'isolement « versioning 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. De plus, la charge liée au verrouillage des ressources est minimisée en raison de la réduction du nombre de verrous acquis. L'isolement read committed avec versioning de ligne et l'isolement de capture instantanée sont conçus pour garantir la cohérence des données avec version au niveau de l'instruction ou de la transaction.

Toutes les requêtes, y compris les transactions qui s'exécutent sous les niveaux d'isolement basés sur le versioning de ligne, acquièrent des verrous de stabilité du schéma (Sch-S) au cours de la compilation et de l'exécution. 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. 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. Les transactions de type requête, y compris celles qui s'exécutent sous un niveau d'isolement basé sur le versioning de ligne, sont bloquées lors d'une tentative visant à acquérir un verrou Sch-S. Inversement, une requête détenant un verrou Sch-S bloque une transaction simultanée qui tente d'acquérir un verrou Sch-M. Pour plus d'informations sur le comportement des verrous, consultez Compatibilité de verrouillage (moteur de base de données).

Lorsqu'une transaction avec niveau d'isolement de capture instantanée est lancée, l'instance de Moteur de base de données enregistre toutes les transactions en cours. Lorsque la transaction lit une ligne qui a une chaîne de versions, le Moteur de base de données remonte la chaîne et récupère la ligne dont le numéro de séquence de transaction :

• se rapproche le plus, sans le dépasser, du numéro de séquence de la transaction qui lit la ligne ;
• ne figure pas dans la liste de transactions actives au moment de la création de la transaction.

Les opérations de lecture effectuées par une transaction de capture instantanée récupèrent la dernière version de chaque ligne validée au début de la transaction. Ceci permet de disposer d'une capture instantanée transactionnellement cohérente avec les données présentes au début de la transaction.

Les transactions « read committed » avec versioning de ligne fonctionnement pratiquement de la même manière. La différence est que ces transactions n'utilisent pas leurs propres numéros de séquence lors du choix des versions de lignes. 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ées. C'est ce numéro qui servira à sélectionner les bonnes versions de lignes pour cette instruction. Ceci permet aux transactions read committed de voir une capture instantanée des données telles qu'elles existaient au début de chaque instruction.

Remarque :
Même si les transactions read commited utilisant le versioning 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.

Comportement lors de la modification de données

Dans une transaction read committed avec versioning 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. La même chose se produit avec une transaction read committed qui n'utilise pas le versioning de ligne. 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.

Les transactions s'exécutant avec isolement de capture instantanée 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. Sinon, les verrous ne sont pas placés sur les données tant que celles-ci doivent être modifiées. 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. 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. Le conflit de mise à jour est géré par le Moteur de base de données. Il n'y a aucun moyen de désactiver la détection des conflits de mise à jour.

Remarque :

Les opérations de mise à jour s'exécutant avec isolation de capture instantanée s'exécutent en interne sous le régime d'isolement read committed lorsque la transaction accède à un des éléments suivants : une table avec contrainte FOREIGN KEY ; une table à laquelle la contrainte FOREIGN KEY d'une autre table fait référence ; une vue indexée faisant référence à plusieurs tables. 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. Si c'est le cas, il y a conflit de mise à jour et la transaction est arrêtée.

Synthèse des comportements

Le tableau suivant synthétise les différences entre l'isolement de capture instantanée et l'isolement read committed avec versioning de ligne :

Voir aussi

Référence

Exemple de niveau d'isolement basé sur le versioning de ligne

Concepts

Utilisation de la ressource de versioning de ligne
Choix des niveaux d'isolement selon le versioning de ligne
Activation des niveaux d'isolement selon le versioning de ligne
Utilisation de niveaux d'isolement basés sur la gestion de la version des lignes
Affichage des informations concernant le versioning de ligne

Autres ressources

ALTER DATABASE (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Indicateur de table (T-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications