Qu'est ce que l'isolation ?

Isolation image L'isolation est le I de ACID (Note Wikipedia). C'est la propriété d'un SGBD à prendre en charge les accès concurrents aux données (tables).
Sans aucune isolation, deux utilisateurs pourraient modifier la même donnée au même instant, créant une corruption.

Les SGBD mettent en place un système de verrou pour garantir ce fonctionnement. Généralement, lorsqu'une donnée est modifiée par un utilisateur, un verrou est posé avant la modification ; il sera libéré à la validation de la modification (fin de transaction). Tant que ce verrou est présent, aucun autre utilisateur, ne pourra poser de nouveau verrou sur cette donnée (donc impossible de modifier en parallèle). Bien entendu, lorsque je dis, "Les SGDB ont un système de verrous", cela inclus SQL Server :) . Nous allons voir que comme sur Oracle, derrière ces grands principes se cache différentes façon de prendre en charge l'isolation et que les verrous sont de différents types.

Standard Isolation (ou Read Commited)

C'est le mode d'isolation le plus simple : les lectures se font en posant des verrous partagés ; les écritures demandent un verrou exclusif. Le SGBD accepte plusieurs verrous partagés sur la même donnée mais un verrou exclusif bloquera tous les autres !

Standard isolation - select

2 transactions fonctionnent en parallèle (T1 + T2) ; elles lisent la même donnée.
Pour commencer une lecture, chacune doit poser un verrou partagé sur la donnée (shared-lock en vert) puis lisent et enfin libèrent le verrou. Nous voyons qu'à un instant donné 2 verrous partagés sont posés sur la même donnée.

Que ce passe-t-il lors d'une modification de donnée ? Standard isolation - update

Toujours 2 transactions en parallèle. T3 va modifier une donnée via un UPDATE (note : on peut parler ici de modification de donnée puisqu'un UPDATE, INSERT ou DELETE aurait le même effet sur les autres transactions). T4 exécute un SELECT en parallèle de T3. La transaction T4 demande un verrou partagé (comme sur le schéma précédent) mais un verrou exclusif est déjà posé par T3. La transaction T4 est mise en attente de pouvoir poser son verrou. Lorsque T3 valide ses données (ou si elle avait annulée), le verrou exclusif est libéré et T4 peut lire la donnée (fetch).
Ce mode de fonctionnement n'existe pas sur Oracle. Dans ce mode ci, il faut faire attention à avoir un ratio de lectures/écritures élevé sans quoi, les transactions de lecture se trouvent souvent bloquées. Si votre application réalise beaucoup de modifications pour peu de lectures, voyez si les autres modes d'isolation ne pourraient pas convenir.

Read Commited snapshot

Dans ce mode là, SQL Server va nous permettre de lire les données même si une transaction est en cours d'écriture !
Comme précédemment, il est obligatoire de ne permettre la lecture que de données valides (READ COMMITED). Voyons comment cela se traduit techniquement ?

Pour pouvoir lire les données alors qu'une transaction est en train de les modifier ; pas de secret, il faut stocker l'ancienne valeur. Chez Oracle, c'est le rôle de l'UNDO ; sur SQL Server, les données sont mises dans la tempdb. Par ailleurs, il faut une solution pour suivre les changements (pour savoir quelle instruction est "arrivée" en premier sur le SGBD). Chez Oracle le SCN ; chez Microsoft, XCN (quelle inspiration !).


Voyons un schéma : à gauche les transactions, à droite (fond gris), les actions sur les fichiers disque. Read commited isolation

Nouveautés sur les transactions

Première révolution : les lectures (SELECT de la transaction T2) ne posent plus de verrous ! C'est exactement ce que nous voulions : les lectures ne peuvent plus être bloquées. Autre nouveauté, les transactions sont prises en charge avec un numéro de XCN.

Nouveautés avec la tempdb

Dans ce mode d'isolation, la tempdb va être fortement utilisée : lorsqu'une modification est effectuée, la page est copiée en tempdb avant modification et un pointeur est créé dans la ligne actuelle vers son ancienne valeur en tempdb.
Si une nouvelle modification est réalisée, la valeur intermédiaire est aussi copiée en tempdb et le pointeur est créé ; on se retrouve avec une liste chainée.

Comment se passent les mises à jours ?

Dans ce mode aussi, un update commence par poser les verrous (partagé puis exclusif) ; on le voit sur la transaction T1. Ensuite, changement de programme : comme expliqué, la ligne va être copiée en tempdb et un pointeur est créé en fin de ligne.
La ligne va être modifiée à son emplacement de départ (fichier DATA) et le numéro XCN de la transaction T1 est noté. Cet update fait passer la ligne de "Bleu" à "Vert". Lorsque l'ordre de commit est exécuté, les verrous sont libérés.

Si on regarde la transaction T3 qui fait elle aussi une modification, son comportement est impacté par T1. En effet, comme prévu, cette transaction pose un verrou partagé puis un verrou exclusif. Ce second verrou ne peut pas être donné a T3 puisque T1 a déjà un verrou exclusif sur la même donnée.
T3 se met donc en attente de verrou exclusif jusqu'à libération par T1. Dès que T1 libère son verrou exclusif, T3 le prend et réalise les mêmes actions : copie de la ligne en tempdb + création du pointeur vers la ligne nouvellement copiée en tempdb + modification de la ligne dans la table.
Note : lorsque la transaction T3 débute, la valeur de la ligne est "Bleu" ; T3 modifie pour passer à "Rouge" ; T3 n'est à aucun moment informé que la donnée a été modifié à "Vert" par T1 !

Et les lectures ?

La transaction T2 fait 2 fois la lecture de la même donnée. Point important, cette transaction est unique ; il n'y a pas de déconnexion, re-connexion ou de COMMIT ou ROLLBACK. Simplement, un SELECT - une pause (pointillés) - un autre SELECT.
Voyons maintenant comment se passe le SELECT techniquement. Comme je vous l'ai déjà indiqué, les lectures ne posent plus de verrou ; cela n'est pas un problème car les lectures ne se font que sur des lignes validées (Post COMMIT). En effet, avant de faire la lecture d'une ligne, SQL Server commence par rechercher quels lignes sont valides pour la transaction en cours. Pour cela, il va sélectionner les lignes dont le XCN est inférieur ou égal au XCN de la transaction en cours, c-à-d ni validée (COMMIT) ni annulée (ROLLBACK) ET qui ne sont pas modifiées par une transaction cours d'exécution.

  • Premier SELECT :

L’instruction du SELECT a débutée au XCN 10 ; la ligne retournée doit être marquée avec un XCN≤10. Ici c'est simple, dans le fichier data, la ligne active "Bleu" date du XCN=1 ; cette ligne n'est pas en cours de modification par une transaction active ; c'est donc la ligne retournée à T2.

  • Second SELECT :

L'instruction du SELECT a débutée à XCN 30 ; cette fois, SQL Server trouve une ligne dans le fichier data ("Rouge") qui a un XCN de 15 sauf que cette ligne est modifiée par une transaction qui est en cours (modifiée par T3). Le moteur doit donc trouver une autre version de la ligne. Pour cela il va utiliser le pointeur et va lire la copie de la ligne qui est en tempdb ("Vert"). Cette copie de ligne a un XCN de 1 et n'est pas modifié par une transaction en cours. C'est bon ! Cette ligne est acceptable.

Quelles limites ?

Nous venons de voir un premier mode d'isolation où les lectures n'ont plus besoin de poser de verrou (donc plus performant), l'impact est une utilisation forte de la tempdb (à relativiser car dans de nombreux cas, les données restent en mémoire). Autre point à noter, dans le cas des transactions de modifications exécutées en parallèle, la seconde transaction (T3) a la possibilité de modifier une données sans être informée qu'une modification à eu lieu. C'est ce dernier point qui change avec le dernier mode d'isolation que nous allons voir.

Snapshot Isolation

Ce mode est très proche du précédent. Les lignes sont aussi copiées en tempdb ; les transactions ont un numéro de XCN ; les lignes sont taggées avec leur XCN de modification.
Voyons un schéma puis les différences avec le mode précédent.

Snapshot isolation

Différences

  • Identifiant de transaction : Le numéro de XCN reste fixe durant toute la transaction. Alors qu'en mode Read Commited Snapshot, le XCN est mis à jour à chaque instruction (pour les SELECT), ici, la transaction T2 conserve son XCN à 10. Le second SELECT exécuté par T2 retourne les mêmes données ("Bleu") même si une autre transaction (T1) a déjà validé une modification.
  • Modification de données après validation : La transaction T3 ne fonctionne pas dans ce mode (elle échoue avec une erreur). En effet, alors que dans le mode précédent, les modifications se faisaient en posant des verrous, en isolation snapshot isolation, avant de poser les verrous classiques, la transaction va faire une validation de la donnée : elle doit posséder un XCN ≤ à son XCN. Dans le schéma, T3 possède un XCN de 15 et lorsqu'elle exécute un UPDATE, la ligne a déjà été validée par T1 qui a un XCN de 1. Cette situation mène à une erreur.

Dans ce mode là, les transactions ont plus de contraintes pour les écritures.

Quels impacts ?

Dans ce mode, un point d'attention est toujours l'utilisation de la tempdb. Alors que son principe n'a pas changé, les lignes vont y rester plus longtemps. En effet, les lignes en tempdb sont purgées lorsque plus aucune transaction active n'en a besoin. Ex : un ligne copiée avec un XCN de 12 sera purgée de la tempdb lorsque la plus ancienne transaction a un XCN>12. En mode snapshot isolation, une transaction (T2) peut démarrer avec un XCN=10 et rester présente longtemps (en faisant ou non des actions) ; tant que cette transaction est présente, la ligne en tempdb ne sera pas purgée.
C'est un mode impactant pour les applications qui utilisent des pools de connexions et ne libèrent jamais les transactions. Il faut réaliser des COMMIT ou ROLLBACK réguliers pour éviter tout problème de tempdb énorme.

Conclusion

Nous venons de voir les différents modes d'isolation dans SQL Server. Le mode Read Commited est le mode d'isolation par défaut depuis SQL Server 2005.
Point notable, en SQL Server 2014, les tables In-Memory (et uniquement elles) sont par défaut en isolation snapshot avec un mode particulier de résolution des conflits (première transaction qui modifie les données = transaction valide).

L'isolation par défaut (Read Commited) n'existe pas sous Oracle ; ce choix pourrait s'expliquer par la volonté de Microsoft d'être plus performant sur les systèmes ayant un nombre de SELECT bien plus importants que de modifications. Je vais essayer de faire un test approfondi des performances sur les 2 systèmes pour valider ou non cette hypothèse. Mise à jour : J'ai fait le test :)