• Article

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

Le contrôle des versions de ligne est une structure générale de SQL Server qui est utilisé pour la prise en charge des fonctionnalités 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.

Lorsque l'option de base de données READ_COMMITTED_SNAPSHOT ou ALLOW_SNAPSHOT_ISOLATION est activée, le Moteur de base de données SQL Server affecte un numéro de séquence de transaction (XSN) à chaque transaction qui manipule des données à l'aide des versions 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 cohérente de manière transactionnelle des 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 :

Propriété

Niveau d'isolement READ COMMITED utilisant le versioning de ligne

Niveau d'isolement de capture instantanée

L'option de base de données doit être activée (ON) pour assurer la prise en charge nécessaire.

READ_COMMITTED_SNAPSHOT

ALLOW_SNAPSHOT_ISOLATION

Manière dont une session demande le type spécifique de versioning de ligne.

Utilisez le niveau d'isolement par défaut (read-committed) ou exécutez l'instruction SET TRANSACTION ISOLATION LEVEL pour spécifier le niveau d'isolement READ COMMITTED. Ceci peut se faire après le début de la transaction.

Requiert l'exécution de l'instruction SET TRANSACTION ISOLATION LEVEL pour spécifier le niveau d'isolement SNAPSHOT avant le début de la transaction.

La version des données lue par les instructions.

Toutes les données qui ont été validées avant le début de chaque instruction.

Toutes les données qui ont été validées avant le début de chaque transaction.

Manière dont les mises à jour sont gérées.

Passe des versions de lignes aux données réelles pour sélectionner les lignes à mettre à jour et utilise des verrous de mise à jour sur les lignes sélectionnées. Acquiert des verrous exclusifs sur les lignes à modifier réellement. Pas de détection de conflit de mise à jour.

Utilise les versions de lignes pour sélectionner les lignes à mettre à jour. Essaie d'acquérir un verrou exclusif sur les lignes à modifier réellement et, si les données ont été modifiées par une autre transaction, génère un conflit de mise à jour qui entraîne l'arrêt de la transaction.

Détection d'un conflit de mise à jour.

Aucune.

Prise en charge intégrée. Ne peut être désactivée.