Add new comment

SQL et niveau d'isolement des transactions

Un peu de thèorie

Tout d'abord rappelons-nous qu'une transaction est une séquence d'étapes qui effectue une unité logique de travail. La transaction garantit que toutes les étapes seront exécutée ou aucune d'elles ne doit être validées. D'autre part la propriété d'isolation des transactions assure que les modifications effectuées par une transaction sont isolées des autres transactions concurrentes. Dans cette article nous allons voir l'utilisation des différents niveaux d'isolement des transactions lors des applications pratiques en SQL Server 2005/2008

Avant d'avoir étudié les exemples pratiques rappelons-nous les niveau d'isolements standardisés par ANSI SQL-92.

READ UNCOMMITTED

C'est le niveau minimale qui ne garantie que l'intégrité physique des données lors de la lecture (i.e. touts les caractères d'une chaîne sont écrits dans le champ). Les instructions SQL peuvent lire des lignes qui ont été modifiées par d'autres transactions, mais pas encore validées.

READ COMMITTED

Spécifie que les instructions ne peuvent pas lire des données modifiées mais non validées par d'autres transactions. Les données peuvent être modifiées par d'autres transactions entre deux instructions au sein de la transaction active, ce qui aboutit à des lectures non renouvelables ou à des données fantômes.

REPEATABLE READ

Spécifie que les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions, et qu'aucune autre transaction ne peut modifier les données lues par la transaction active tant que celle-ci n'est pas terminée.

SERIALIZABLE

C'est le niveau d'isolement maximal qui spécifie les indications suivantes :

  • Les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions.
  • Aucune autre transaction ne peut modifier des données qui ont été lues par la transaction active tant que celle-ci n'est pas terminée.
  • Les autres transactions ne peuvent pas insérer de nouvelles lignes avec des valeurs de clés comprises dans le groupe de clés lues par des instructions de la transaction active, tant que celle-ci n'est pas terminée.

SNAPSHOT

Sauf ces niveaux standards répertoriés MS SQL Server introduit en version 2005 l'isolement SNAPSHOT qui spécifie que les données lues par n'importe quelle instruction d'une transaction représenteront la version cohérente d'un point de vue transactionnel des données qui existaient au début de la transaction.

Autrement dit, les modifications de données effectuées par d'autres transactions après le début de la transaction active ne sont pas visibles comme si les instructions d'une transaction obtenaient une capture instantanée des données validées telles qu'elles existaient au début de cette transaction.

Les essais

Pour nos essais il faut avoir installé MS SQL Server 2005 ou 2008 de n'importe quelle édition y compris «Express».

La préparation

Créez la base de données nommée «test» et exécutez quelques scripts SQL. Activez l'option d'isolement SNAPSHOT.

USE master
ALTER DATABASE Test SET ALLOW_SNAPSHOT_ISOLATION ON 
GO
USE test 

Créez une table «DevicesData». Imaginez que cette table serte à stocker les informations de nombreuses unités externes. Le champ «DeviceId» est l'identificateur de l'unité et le champ «Value» stocke la dernière valeur reçu.

CREATE TABLE DevicesData (
   DeviceId int not null, 
   Value int not null,
   CONSTRAINT PK_DevicesData PRIMARY KEY (DeviceId)
)
GO 

Initialisez notre table qui doit être assez grande pour visualiser les effets des transactions concurrentes. Va pour 1 million des unités et ces valeurs initiales sont égales à zéro.

TRUNCATE TABLE DevicesData
 
DECLARE @n int 
SET @n = 999
DECLARE @List TABLE (n int)
WHILE @n >= 0 BEGIN
   INSERT INTO @List (n)
   SELECT @n
   SET @n = @n - 1
END
 
INSERT INTO DevicesData (DeviceId, Value)
SELECT A.n * 1000 + B.n, 0 
   FROM @List A CROSS JOIN @List B
GO 

Les tests

Démarrez SQL Server Management Studio et ouvrez 2 connexions («Processus 1» et «Processus 2») vers la base «test».

READ COMMITTED

Lancer le processus 1 qui change deux valeurs choisis aléatoirement de maniérer que la somme totale de toutes les valeurs reste égale à zéro. Puis lancer à la main le processus 2 quelques fois de suite.

Processus 1 (écrivain) Processus 2 (lecteur)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
DECLARE @Id int
WHILE 1 = 1 BEGIN
   SET @Id = 500000 * rand()
   BEGIN TRANSACTION
   UPDATE DevicesData
      SET Value = Value + 1
      WHERE DeviceId = @Id
   UPDATE DevicesData
      SET Value = Value - 1
      WHERE DeviceId = 500000 + @Id
   COMMIT
   WAITFOR DELAY '00:00:00.100'
END 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
SELECT SUM(Value) FROM DevicesData
-----------
-1

Vous pouvez voir que le nivaux d'isolement choisi ne supporte pas l'intégrité des données et dans ce cas il s'agit de lecture non renouvelable. Le processus 2 ne tient pas les verrous lors de sa transaction.

REPEATABLE READ et SNAPSHOT

Pour éviter le problème ledit montez le niveau à REPEATABLE READ et répétez le test.

Processus 1 (écrivain) Processus 2 (lecteur)
SET
NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE 
READ
 
DECLARE @Id int
WHILE 1 = 1 BEGIN
 SET @Id = 500000 * rand()
 BEGIN TRANSACTION
 UPDATE DevicesData
 SET Value
= Value + 1
 WHERE DeviceId = @Id
 UPDATE DevicesData
 SET Value
= Value - 1
 WHERE DeviceId = 500000 + @Id
 COMMIT
 WAITFOR DELAY '00:00:00.100'
END
SET
NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE 
READ
 
BEGIN TRANSACTION
SELECT SUM(Value)
FROM DevicesData
EXEC sp_lock
COMMIT
-----------
0

Maintenant le processus-lecteur retourne toujours la somme totale égale à zéro. En même temps, la liste de verrous retourné par la procédure sp_lock nous montre que la transaction de lecture tient ces verrous partagés de pages jusqu'à la validation.

Si vous changez le niveau d'isolement à SNAPSHOT le résultat de la lecture sera égal à zéro aussi, mais la transaction d'écriture ne sera pas bloqué. Normalement, vous ne verrez qu'un seul verrou intentionnel au niveau de la table puisque la transaction balaye toute la table.

REPEATABLE READ et les données fantômes

Le niveau REPEATABLE READ nous garanti qu'aucune transaction ne peut modifier des données qui ont été lues par la transaction active tant que celle-ci n'est pas terminée. Par contre, les autres transactions peuvent insérer de nouvelles lignes avec des valeurs de clés comprises dans le groupe de clés lues.

Un exemple « classique » : vous êtes en train de calculez la quantité de produit sur le stock comme le résultat des opérations d'entrée et de sortie mais en même temps une nouvelle opération est insérée dans la table ce que vous donne la quantité fausse.

Démarrez le processus 1 et le processus 2 tout de suite (vous avez 3 secondes pour cela).

Processus 1 (lecteur) Processus 2 (écrivain)
SET
NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
 
BEGIN TRANSACTION 
SELECT SUM(Value)
FROM DevicesData WHERE DeviceId > 999000
WAITFOR DELAY '00:00:03'
SELECT SUM(Value)
FROM DevicesData WHERE DeviceId > 999000
COMMIT
SET
NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
 
BEGIN TRANSACTION
INSERT INTO DevicesData (DeviceId, Value) VALUES
(1000000, 111)
WAITFOR DELAY '00:00:00.100'
COMMIT
-----------
0
 
 
-----------
111

Et voilà, la deuxième lecture nous retourne la quantité qui différé de celle de la première lecture en cadre de la même transaction !

Après avoir changé le niveau d'isolement à SNAPSHOT vous recevrez le résultat « correcte », mais la ligne sera inséré toujours par le processus 2. Ce n'est pas la solution tout à fait si votre objectif est l'intégrité des donnée au niveau logique, par exemple, éviter l'insertion qui fausse l'expression pré-calculé.

Processus 1 (écrivain) Processus 2 (écrivain)
SET
NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL
SNAPSHOT
 
DECLARE @SumVal int
BEGIN TRANSACTION 
SELECT @SumVal = SUM(Value) FROM DevicesData WHERE
DeviceId > 999000
WAITFOR DELAY '00:00:03'
IF (@SumVal) = 0
 INSERT INTO DevicesData
(DeviceId, Value) VALUES
(1000000, 111)
COMMIT 
SET
NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
 
BEGIN TRANSACTION
INSERT INTO DevicesData (DeviceId, Value) VALUES
(1000000, 111)
WAITFOR DELAY '00:00:00.100'
COMMIT
Msg 2627, Level 14, State 1, Line 18 Violation of PRIMARY KEY constraint 'PK_DevicesData'. Cannot insert duplicate key in object 'dbo.DevicesData'. The statement has been terminated.

SERIALIZABLE

Montez le niveau d'isolement à SERIALIZABLE et répétez le test précédent.

Processus 1 (écrivain) Processus 2 (écrivain)
SET
NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE
 
DECLARE @SumVal int
BEGIN TRANSACTION 
SELECT @SumVal = SUM(Value) FROM DevicesData WHERE
DeviceId > 999000
WAITFOR DELAY '00:00:03'
IF (@SumVal) = 0
 INSERT INTO DevicesData
(DeviceId, Value) VALUES
(1000000, 111)
COMMIT 
SET
NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE
 
BEGIN TRANSACTION
INSERT INTO DevicesData (DeviceId, Value) VALUES
(1000000, 111)
COMMIT
Msg 2627, Level 14, State 1, Line 18 Violation of PRIMARY KEY constraint 'PK_DevicesData'. Cannot insert duplicate key in object 'dbo.DevicesData'. The statement has been terminated.

Maintenant c'est le processus 2 qui échoue et le processus 1 qui est complètement isolé. Imaginez, que vous avez 2 processus concurrents qui doivent réserver le produit sur le stock et la situation dont l'utilisation d'isolation de haut niveau est nécessaire vous semblera plus pratique.

Les conclusions

Le sujet d'isolement de transactions est très important dans le domaine de bases de données. La compréhension de ces mécanismes est principale pour le développement des solutions fiable et rapide de traitement de données.

Les niveaux plus hauts réduisent la possibilité de paralléliser le traitement et haussent les risques des conflit des verrouillages. C'est pourquoi l'utilisation d'un niveau d'isolement donné est le choix du développeur en fonction de plusieurs critères y compris ceux de la logique métier.