Depuis SQL Server 2005, le moteur de base de données a la capacité de générer des versions d’un même enregistrement. La base de données système Tempdb est utilisée pour cet usage et sert de conteneur de versions (version store).

Chaque enregistrement est susceptible d’avoir plusieurs versions en plus de son état actuel dans la base de données. Le versionnement ajoute aux enregistrements concernée un préfixe de 14 octets indiquant qu’une ou plusieurs autres versions de cet enregistrement se trouvent actuellement dans le conteneur de versions dans la base de données système Tempdb. Le préfixe contient entre autre le numéro de la transaction à laquelle est associée la version de cette enregistrement.

Cette fonctionnalité sert aux nouveaux niveaux d’isolations SNAPSHOT de SQL Server 2005 que l’on verra plus loin, mais aussi à :

Pseudo-tables inserted et deleted à l’intérieur des déclencheurs DML (modification de données).

Opérations d’index en ligne.

MARS (Mutiple Active ResultSet)

Il nous est possible de connaitre le volume de données utilisé dans le conteneur de la base de données système Tempdb :

Grace à cette requête vous connaitrez la taille allouée au conteneur de version dans Tempdb. Cette taille s’exprimant en pages, le chiffre est multiplié par 8 donnant un résultat en kilo-octets.

Les vues dynamiques sys.dm_tran_version_store et sys.dm_tran_top_version_generators peuvent aussi donner des informations utiles sur l’utilisation du conteneur de version de SQL Server.

Les niveaux d’isolation

L’isolation servant à éviter toute interférence entre 2 transactions elle est nécessaire dans toute application. Cependant comme cette isolation peut se révéler bloquante, le moteur et l’application gardent une certaine latitude dans la manière d’isoler les données. Les niveaux d’isolation prennent place ici, c’est de cette manière que nous indiquerons la manière dont nous souhaitons protéger les données.

On notera que les niveaux d’isolation, sont pour la majorité d’entre eux, définis par la norme SQL. SQL Server 2005 en a introduit 2 supplémentaires se basant sur le versionnement des données, ce qui en fait 6 en tout que nous allons détailler.

Le choix du niveau d’isolation de la connexion se fait à l’aide de la commande suivante :

Où x représente le niveau d’isolation a utilisé. Ce choix est valable jusqu’à la fermeture de la connexion ou la modification du niveau d’isolation pour un autre. La valeur par défaut pour toutes les connexions faites au serveur est READ COMMITTED.

Niveau READ UNCOMMITTED ou NOLOCK

Ce niveau est le niveau le plus bas en termes de verrouillage. Il ne demande pas à poser de verrou lorsqu’il s’apprête à réaliser une lecture. Cependant, lors d’une écriture il demande à poser des verrous exclusifs sur les éléments qu’il s’apprête à modifier, ces verrous sont conservés jusqu’à la fin de la transaction.

Même si ce niveau d’isolation est le moins bloquant de tous ceux à base de verrous, il présente quelques risques au niveau des lectures, si les données cibles sont en cours de modification, car il est alors possible de :

Lire des données temporaires

Avoir accès à des données qui seront annulées

Voir apparaître des doublons ou la disparaitre des enregistrements

Avoir tous les autres problèmes associés aux niveaux d’isolation supérieurs

Il est synonyme de la directive NOLOCK qui s’applique à une table, là où le changement du niveau d’isolation impacte la totalité de la connexion. La directive NOLOCK s’utilise comme suit au niveau d’une table :

Ce mode est extrêmement pratique pour diagnostiquer des problèmes lorsqu’une transaction est en cours d’exécution et que vous n’avez pas d’autres moyens de lire des données actuellement verrouillées sans toucher à une transaction en cours.

Pour les problématiques de verrouillages pour lesquelles on trouvera fréquemment des requêtes utilisant ce mode d’isolation pour éviter les blocages entre lectures et écritures, on préfèrera l’utilisation des niveaux d’isolation par versionnement disponibles depuis SQL Server 2005.

Niveau READ COMMITTED

C’est le niveau d’isolation par défaut de SQL Server. Il utilise des verrous partagés pour réaliser des lectures de données. Ces verrous ne sont conservés que pendant la phase de lecture et immédiatement relâchés après l’exécution du SELECT. Il est envisageable pour ce niveau de réaliser les lectures hors de la transaction, le comportement sera le même, mais réduira la durée de la transaction. L’écriture se comporte comme dans le niveau d’isolation précédent, conservant les verrous exclusifs jusqu’à la fin de la transaction.

Ce mode d’isolation protège des problèmes décris au niveau précédent, mais il subsiste des risques :

Ne pas pouvoir lire plusieurs fois la même valeur dans un enregistrement

Avoir tous les autres problèmes associés aux niveaux d’isolation supérieurs

Ce niveau convient à la majeure partie des utilisations courante des données, intéressez vous aux niveaux d’isolation supérieurs que si vous avez l’un des problèmes exposés dans le présent niveau.

SQL Server a fait bénéficier de quelques améliorations à ce mode en termes de verrouillage. Si la table n’est pas verrouiller lorsque le moteur entame une lecture à ce niveau, seul un verrou intention partagé au niveau table est posé, rendant ce niveau aussi efficace que le précédant dans le cas de seules lectures.

Niveau READ COMMITTED SNAPSHOT

Ce niveau est le premier niveau d’isolation utilisant le système de versionnement de SQL Server. Ce niveau est en tout point similaire à celui utilisé par Oracle. Il remplace lorsqu’il est activé sur une base de données, le niveau d’isolation par défaut de SQL Server qui est le READ COMMITTED, toutes les nouvelles transactions sont alors faites dans le niveau d’isolation READ COMMITTED SNAPSHOT.

L’activation au niveau de la base de données se fait via la commande suivante :

Aucune connexion ne doit être active sur la base de données pour que l’activation soit possible immédiatement. Dans le cas contraire le changement du niveau d’isolation par défaut est mis en attente de la fermeture de toutes les connexions actives présentes sur la base de données.

Dès que ce mode est actif, toutes les modifications de données génèrent des versions d’enregistrement (quelque soit leur niveau d’isolation) dans le conteneur de version à l’intérieur la base de données système Tempdb. Les lectures de données se font sur la dernière version validée des données, les cas pouvant se produire :

L’enregistrement n’est pas en cours de modification, il n’a pas d’entête de version, il est lu directement

L’enregistrement est en cours de modification, il y a donc un entête. Dans le conteneur de version, je recherche la version validée de l’enregistrement la plus récente, c’est elle qui est utilisée.

Les caractéristiques de ce niveau sont semblables à celle de son homologue utilisant les verrous. La lecture du même enregistrement au sein de la transaction peut amener des valeurs différentes, l’apparition d’enregistrements fantômes est là aussi possible, etc.

Les modifications de données posent un verrou exclusif sur les données qui sera supprimé à la fin de la transaction. Les écritures se bloquent entre elles, le but est d’empêcher plusieurs écritures simultanées sur les mêmes données dans plusieurs transactions.

Si une transaction travaille sur une valeur lue à partir d’une version qui n’a plus cours car une autre transaction a modifié ces enregistrements, elle peut elle-même modifier cette valeur, aucune erreur n’est renvoyé par SQL Server.

Les requêtes utilisant la directive NOLOCK n’utilisent pas le niveau d’isolation READ COMMITTED SNAPSHOT, car celui-ci remplace le READ COMMITTED et que NOLOCK place la requête à un niveau d’isolation moindre. Prenez garde à ces requêtes qui ne bénéficieront pas de ce nouveau mode sauf à les modifier pour supprimer la directive.

Pour des performances optimales, il est indispensable de placer la base de données système Tempdb sur des unités de disque rapide et dédiées à cet usage. Les niveaux d’isolation à base de versionnement offre des gains important pour les problématiques de lectures et écritures concurrentes, mais engendre une consommation disque importante.

Niveau REPEATABLE READ

Ce niveau d’isolation utilise des verrous partagés lorsqu’il lit les données et conserve les verrous jusqu’à la fin de la transaction. Pour les écritures, le comportement est le même que pour les lectures et identique aussi aux autres niveaux d’isolation à base de verrous.

Ce mode protège les lectures, permettant ainsi de s’assurer de pouvoir relire une valeur déjà lue. Les SELECT doivent se trouver dans la transaction pour profiter de ces verrous. En contrepartie il peut se révéler bloquant, car les lectures posent des verrous maintenus jusqu’à la fin de la transaction.

Parmi les problèmes rencontrés à ce niveau :

Apparition d’enregistrements fantômes possible

Accentue certains risques d’inter blocages

Ce mode est à conseiller dès lors que vous charger des données dans des variables ou tables temporaires avant de procéder à une mise à jour à l’aide de ces valeurs et que celles-ci ne doivent en aucune manière changer. Le cas de la réservation en ligne en est un bon exemple. Attention aux problématiques d’inter blocages qui seront vues plus loin, le scenario exposé ici bénéficierait de certaines améliorations qui seront exposées dans cette partie.

Niveau SERIALIZABLE

Plus haut d’isolation à base de verrous, il reprend les caractéristiques des écritures des niveaux précédents. Quant aux lectures, elles génèrent des verrous partagés conservés jusqu’à la fin de la transaction.

La différence vient de l’utilisation de verrou de plage de valeur (Range) associé à un type principal. Ce type particulier de verrou permet de définir une plage de données verrouillée dans laquelle il sera impossible d’ajouter toute valeur, phénomène qui provoque l’apparition d’enregistrements fantômes. Ces verrous peuvent aussi se définir vers l’infini positif ou négatif.

Ce mode bien que bloquant ne l’est pas tant que çà si la table est bien indexée, seul cas où le moteur pourra utiliser les verrous de plage de valeur. Il est donc possible à 2 transactions de travailler sur la même table en écriture à ce niveau.

Le seul problème réel d’un tel niveau est l’accentuation des problématiques de blocage et d’inter blocage du fait du verrouillage important réalisé par ce niveau.

On utilisera ce niveau quand il est indispensable qu’aucune insertion ne puisse être faite sur la table pendant la durée transaction. Typiquement les fonctions d’agrégation sont très sensibles à ces ajouts et bénéficierait particulièrement de ce niveau de verrouillage élevé.

Niveau SNAPSHOT

Second niveau d’isolation à base de versionnement de données. Il diffère du niveau d’isolation READ COMMITTED SNAPSHOT par plusieurs points.

L’activation de ce mode se fait dans un premier temps au niveau de la base de données

Puis on vérifie que le mode SNAPSHOT est bien actif sur la base de données :

Si des transactions sont actuellement en cours, l’état sera à « PENDING_ON », c'est-à-dire en attente de la terminaison des transactions courantes pour être activé. Pour la désactivation du mode SNAPSHOT, le changement d’état attendra la fin de toutes les transactions ouvertes.

Ce mode permet de garantir la stabilité des données lues de bout en bout de la transaction et non pas seulement au niveau d’une lecture come c’est le cas du précédent niveau utilisant le versionnement. En contrepartie, l’utilisation faite du conteneur de version est plus importante car plus la transaction déroulée au niveau SNAPSHOT est longue, plus le nombre de versions d’enregistrements sera important.

Autre différence, cette fois au niveau de l’écriture des données. En cas de conflit d’écriture, c'est-à-dire que 2 transactions lisent l’une après l’autre les mêmes données qu’elles s’apprêtent à mettre à jour, la première réussira cette mise à jour tandis que la seconde se verra notifiée une erreur indiquant que l’enregistrement sur lequel elle travaillait a déjà été modifié.

Pour illustrer ce cas, prenez cette requête

Mémoriser la liste de ces valeurs, puis exécutez dans une seconde transaction :

Attention la transaction doit bien être validée pour rentrer en conflit avec la seconde connexion, c’est pour cela qu’il n’y a pas de transaction explicite ici.

On termine dans la première connexion par :

Qui se soldera par l’erreur suivante :

Le moteur surveille à ce niveau que 2 mises à jour de données ne rentrent pas en conflit et renvoie une erreur à celle qui essayera en dernier d’effectuer sa mise à jour. Cela pourra se révéler un très utilise atout pour une application, qui pourra alors ré-exécuter la totalité de la transaction ou agir au niveau de l’application et prendre les mesures nécessaires.

Synthèse des différents niveaux

En tout et pour tous 6 niveaux d’isolations vous sont offert par SQL Server. Pour une vue synthétiques de ceux-ci voyez le tableau 23-2.

23-2

Synthèse des niveaux d’isolation

Niveau	Type	Plus	Moins
READ UNCOMMITTED	Verrou	Lecture non bloquée par des mises à jour	Lecture non consistante
READ COMMITTED	Verrou	Peu verrouillant	Lecture non répétable Lecture bloquée par les mises à jour
READ COMMITTED SNAPSHOT	Version	Lecture non bloquée par des mises à jour Lecture consistante	Lecture non répétable Consommateur de ressources dans Tempdb
REPEATABLE READ	Verrou	Lecture répétable	Enregistrements fantômes
SERIALIZABLE	Verrou	Utilisation de verrous Range	Très bloquant, particulièrement sans index
SNAPSHOT	Version	Lecture non bloquée par des mises à jour et consistante tout au long d’une transaction Détection des conflits de mise à jour	Très consommateur de ressources dans Tempdb

Le choix du niveau idéal se fera en fonction des impératifs de l’application. Un fort de blocage lié aux concurrences d’écriture et lecture nécessitera un des niveaux SNAPSHOT, tandis qu’une activité importe en écriture nous interdira de le faire.

Le calcul d’un bilan comptable nécessitera très certainement de passer en niveau SERIALIZABLE ou SNAPSHOT car gros consommateur d’agrégations et nécessitant une protection des données élevée. Tandis que le scénario de réservation en ligne bénéficiera des avantages du REPATBEL READ.