Niveau d'isolation et anomalies transactionnelles

Au pire, on pourrait considérer que toute opération atomique devrait impérativement placer la ressource visée par la transaction en accès exclusif.

Mais ce mode de fonctionnement ne serait pas sain et surtout très peu performant. En effet, en imaginant une très grande table, il ne serait pas judicieux de bloquer l'accès à toutes les lignes de la table alors que l'on met à jour une seule ligne de cette table dans une transaction, tandis qu'une autre transaction supprime ou modifie un jeu de lignes situées ailleurs. C'est pourquoi différentes granularités de verrouillage existent ainsi que différents modes.

La granularité d'un verrou pose le problème de son étendue : la ligne, la page de données (donc généralement plusieurs lignes), l'extension (en fait 8 pages), la table…

Le mode de verrouillage est lié au traitement considéré. Ainsi une lecture oblige à un verrou de lecture qui empêche toute modification des données verrouillées, mais n'empêche pas que d'autres transactions lisent une partie ou la totalité des mêmes données. A contrario un verrou d'écriture empêche la pose de tout autre verrou qu'il soit de lecture ou d'écriture.

Voici par exemple les différents modes de verrouillage que propose MS SQL Server 2005 :

Mais ce n'est pas en principe au développeur d'indiquer le verrou qu'il entend utiliser¹ , c'est au moniteur de verrouillage de faire ce travail au mieux, en tenant compte de la concurrence des transactions, de la facilité d'acquérir tel ou tel type de verrou ou encore de l'existence préalable d'autres verrous. En d'autres termes, il est rare que la pose explicite de verrous fasse plus de bien que de mal, car elle oblige à statifier l'état du verrou, alors que le moniteur peut le faire dynamiquement en choisissant à bon escient le verrou le mieux adapté compte tenu des circonstances ou moment où le traitement s'exécute.

C'est pour cela que SQL propose d'utiliser un niveau logique de gestion de la concurrence, le fameux niveau d'isolation des transactions. Ces niveaux logiques permettent de s'affranchir de différentes anomalies transactionnelles qui peuvent être induites par la nature des verrous sous-jacents.

Mais au fait, qu'est-ce qu'une anomalie transactionnelle ? SQL (le langage) considère principalement trois anomalies transactionnelles :

• la lecture de données inconsistante (ou lecture sale) ;
• la lecture non répétitible de données ;
• la lecture de données « fantômes » c'est-à-dire apparaissant au beau milieu d'un traitement.

1. Même si cela est possible dans les requêtes SQL Server via des tags introduits par l'option WITH.

Voici un tableau résumant les différents niveaux d'isolation et les anomalies transactionnelles possibles ou non :

NOTA : le niveau d'isolation SNAPSHOT propre à SQL Server et présent aussi dans Oracle ne fait pas partie de la norme SQL.

Pour mieux comprendre ces anomalies et leur éradication à l'aide des différents niveaux d'isolation, rien ne vaut un exemple concret. Cet exemple consiste à calculer par deux fois une somme de nombres dans la même table et d'en faire la soustraction. En principe un tel calcul devrait systématiquement retourner zéro… Et pourtant !

Voyons tout d'abord notre jeu d'essais. Vous devrez le rejouer à chaque nouvelle tentative.

--> le script de la création de la base qui servira d'exemple
USE master
GO

IF EXISTS (SELECT *
           FROM   master.sys.databases
           WHERE  name = 'DB_ISO_LEVEL')
   DROP DATABASE DB_ISO_LEVEL
GO

CREATE DATABASE DB_ISO_LEVEL
GO

USE DB_ISO_LEVEL
GO

CREATE TABLE T_ISO ( COL INT)
GO

INSERT INTO T_ISO VALUES (1)
INSERT INTO T_ISO VALUES (2)
INSERT INTO T_ISO VALUES (3)
GO

USE master
GO

Par la suite, pour jouer les scripts SQL qui mettent en évidence les anomalies évoquées en titre, vous devrez placer le code à exécuter dans deux fenêtres. Lancez ensuite l'exécution de la première fenêtre puis de la seconde dans la foulée. Observez alors les résultats…

Dans les scripts ci-dessous qui simulent une concurrence, nous avons sciemment utilisé l'instruction WAITFOR qui permet de faire attendre artificiellement le traitement.

Ce type d'anomalie intervient si la transaction courante s'exécute avec un niveau d'isolation READ UNCOMMITTED, c'est-à-dire sans aucune pose de verrou d'aucune sorte lors des lectures.

--> fenêtre 1
USE DB_ISO_LEVEL
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
BEGIN TRANSACTION TRAN1

DECLARE @TOTAL INT

SELECT @TOTAL = SUM(COL)
FROM   T_ISO

WAITFOR DELAY '00:00:20'

SELECT @TOTAL = @TOTAL - SUM(COL)
FROM   T_ISO

SELECT @TOTAL AS TOTAL

COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

USE master
GO

--> fenêtre 2
USE DB_ISO_LEVEL
GO

BEGIN TRANSACTION TRAN2

UPDATE T_ISO
SET    COL = COL + 1

WAITFOR DELAY '00:00:20'

ROLLBACK TRANSACTION

USE master
GO

Le résultat de la transaction 1 affiche en principe -3.

Pour s'affranchir des anomalies de lectures « sales », il suffit de placer la transaction 1 au niveau d'isolation READ COMMITTED. Cela met en œuvre des verrous sur les lectures empêchant que la modification des lignes ne soit commise pendant la lecture.

Ce type d'anomalie intervient si la transaction courante s'exécute avec un niveau d'isolation READ COMMITTED :

NOTA : pour la démonstration, n'oubliez pas de reconstruire la base originale à l'aide du script n° 1

--> fenêtre 1
USE DB_ISO_LEVEL
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
BEGIN TRANSACTION TRAN1

DECLARE @TOTAL INT

SELECT @TOTAL = SUM(COL)
FROM   T_ISO

WAITFOR DELAY '00:00:20'

SELECT @TOTAL = @TOTAL - SUM(COL)
FROM   T_ISO

SELECT @TOTAL AS TOTAL

COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

USE master
GO

--> fenêtre 2
USE DB_ISO_LEVEL
GO

BEGIN TRANSACTION TRAN2

UPDATE T_ISO
SET    COL = COL + 1

WAITFOR DELAY '00:00:20'

COMMIT TRANSACTION

USE master
GO

À nouveau le résultat de la transaction 1 affiche -3. Que s'est-il passé ? Des verrous ont bien été posés sur les trois lignes qui figurent au départ dans la table. Mais les verrous n'ont été posés que le temps que chaque requête et n'ont pas été maintenus jusqu'à la fin de la transaction. Tant et si bien qu'entre les deux SELECT l'UPDATE a produit son effet.

Pour s'affranchir des anomalies de lectures « non répétitibles », il suffit de placer la transaction 1 au niveau d'isolation REPEATABLE READ. Ceci pose des verrous sur les lignes lues et maintient les verrous jusqu'à la fin de la transaction.

NOTA : pour la démonstration, n'oubliez pas de reconstruire la base originale à l'aide du script n° 1

Ce type d'anomalie intervient si la transaction courante s'exécute avec un niveau d'isolation REPEATABLE READ :

--> fenêtre 1
USE DB_ISO_LEVEL
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION TRAN1

DECLARE @TOTAL INT

SELECT @TOTAL = SUM(COL)
FROM   T_ISO

WAITFOR DELAY '00:00:20'

SELECT @TOTAL = @TOTAL - SUM(COL)
FROM   T_ISO

SELECT @TOTAL AS TOTAL

COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

USE master
GO

--> fenêtre 2
USE DB_ISO_LEVEL
GO

BEGIN TRANSACTION TRAN2

INSERT INTO T_ISO VALUES (4)

WAITFOR DELAY '00:00:20'

COMMIT TRANSACTION

USE master
GO

Pour s'affranchir des anomalies de lectures « fantôme » il suffit de placer la transaction 1 au niveau d'isolation SERIALIZABLE² . Ceci pose un verrou exclusif sur la table et le maintient jusqu'à la fin de la transaction.

Un moyen moins couteux en termes de verrouillage est proposé depuis la version 2005 de SQL Server. Il s'agit d'utiliser le niveau d'isolation SNAPSHOT qui réalise une copie des données à traiter pour la durée de la transaction.

2.En fait la norme SQL affirme que le mode SERIALIZABLE empêche toute anomalie transactionnelle de quelque nature que ce soit. Il existe en effet d'autres types d'anomalies transactionnelles moins faciles à mettre en évidence, et ces anomalies-là sont aussi éradiquées par le mode SERIALIZABLE.

Attention : une base de données est créée par défaut sans la possibilité d'utiliser ce niveau d'isolation, car ce dernier ne correspond pas à la norme SQL. Pour pouvoir le mettre en œuvre, il faut paramétrer la base de la sorte :

NOTA : pour la démonstration, n'oubliez pas de reconstruire la base originale à l'aide du script n° 1

USE master
GO

ALTER DATABASE DB_ISO_LEVEL
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Dès lors la nouvelle solution est la suivante :

--> fenêtre 1
USE DB_ISO_LEVEL
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION TRAN1

DECLARE @TOTAL INT

SELECT @TOTAL = SUM(COL)
FROM   T_ISO

WAITFOR DELAY '00:00:20'

SELECT @TOTAL = @TOTAL - SUM(COL)
FROM   T_ISO

SELECT @TOTAL AS TOTAL

COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

USE master
GO

--> fenêtre 2
USE DB_ISO_LEVEL
GO

BEGIN TRANSACTION TRAN2

INSERT INTO T_ISO VALUES (4)

WAITFOR DELAY '00:00:20'

COMMIT TRANSACTION

USE master
GO

L'inconvénient de cette méthode est de forcer des copies de pages de la base originale dans la tempdb. L'utilisation à outrance de ce niveau d'isolation peut donc engorger la base de données stockant les objets temporaires ce qui peut s'avérer pénalisant pour d'autres traitements.

Le passage à un niveau d'isolation supérieur provoque la pose de verrous dont la granularité, ou la rétention augmente. Certes ces verrous empêchent les anomalies susvisées et c'est bien leur rôle, mais ils augmentent la contention du fait d'un blocage plus fort. Augmenter le niveau d'isolation accroît donc en proportion la durée de blocage, mais aussi la probabilité d'obtenir des verrous mortels (deadlocks en anglais, ou encore interblocage, étreinte fatale…).

Ce serait donc une grave erreur de recourir systématiquement au niveau d'isolation le plus fort afin de s'affranchir de tout problème ! C'est d'ailleurs dans cet esprit que le mode d'isolation par défaut de toute session SQL Server est le mode READ COMMITTED, largement suffisant pour la plupart des traitements.

On le voit donc, le pilotage du niveau d'isolation est à apprécier au coup par coup en fonction du traitement effectué dans la transaction et du niveau de confiance que l'on veut sur les résultats. Par exemple accepter un niveau d'isolation READ UNCOMMITTED peut s'avérer payant lorsque l'on calcule des statistiques dont le résultat est censé indiquer une tendance et non la valeur exacte. En revanche, un tel niveau d'isolation ne doit jamais être utilisé lorsque des traitements portent sur des calculs comptables.

Bref, sans être affaire de spécialiste, le choix d'un niveau d'isolation est délicat et mérite d'être étudié de manière sereine.