[SQL Server] Accès concurrentiels: présentation pratique des anomalies transactionnelles

Ce billet présente les anomalies transactionnelles via une série de démonstrations en guise d’explications.

Vous pouvez jeter un coup d’œil ici si vous souhaitez lire la présentation des verrous sous SQL Server, ainsi que , si vous voulez comprendre la différence entre les modèles de concurrence utilisés par SQL Server. Vous pouvez également jeter un coup d’œil ici pour connaître les niveaux d’isolation sous SQL Server et leurs spécificités, où les anomalies transactionnelles sont déjà présentées du point de vue théorique dont des rappels seront effectués dans ce billet.

Rappels sur les anomalies transactionnelles

Les niveaux d’isolation peuvent conduire à des effets secondaires non-désirés (également appelés anomalies transactionnelles), à savoir :

  • Des lectures « sales » (ou inconsistantes) : cette situation se manifeste quand une transaction tente de lire des données en cours de modification par une autre transaction qui ne les a pas encore validées. Le risque, dans cette situation, est de se retrouver à travailler sur des données erronées.
  • Des mises-à-jour perdues : cette situation intervient quand plusieurs transactions tentent de mettre à jour simultanément une même donnée. Une mise-à-jour pertinente déjà faite peut être accidentellement remplacée par une autre mise-à-jour concurrente, ce qui induit, d’une certaine façon, une perte de données. Il existe 2 manières de voir ce type d’anomalie transactionnelle:
  1. Le fait qu’une transaction modifie une ligne de données qui est en cours de modification par une autre transaction. Ce genre de scénario est impossible sous SQL Server.
  2. Le fait qu’une transaction travaille sur un jeu de résultats déjà lu, mais obsolète car entre-temps modifié par une autre transaction. Ce genre de scénario est celui pris en compte en tant qu’anomalie transactionnelle pouvant se manifester sous SQL Server.
  • Des lectures répétitives : cette situation intervient quand une transaction tente de lire une même donnée plusieurs fois, mais tombe sur une valeur différente entre deux lectures. Cela peut arriver lorsqu’une autre transaction a procédé à la modification de la donnée entre chaque lecture.
  • Des lectures « fantômes » : cette situation apparaît lorsqu’une transaction a besoin d’exécuter une même requête plusieurs fois, mais tombe sur un jeu de résultats différent de ce qu’elle a obtenu précédemment, au niveau du nombre de lignes lues/à lire. Cela peut intervenir, par exemple, quand une transaction supprime entre-temps une ligne qui a été lue, et qui comptait être relue, ou ajoute une ligne qui n’existait pas dans le jeu de résultats précédemment lu.

Démonstrations…

Avant de commencer, créons une table de test MaTable et insérons-y un enregistrement :

CREATE TABLE MaTable(MaTable_ID INT)
GO
INSERT INTO MaTable(MaTable_ID) VALUES (1)
INSERT INTO MaTable(MaTable_ID) VALUES (2)
GO

SELECT *
FROM MaTable
GO

Voici le contenu initial de MaTable :


Les démonstrations à venir sont indépendantes les unes par rapport aux autres. En revanche, elles utilisent le script (et son résultat) ci-dessus comme prérequis.

… sur les lectures « sales »

Imaginons qu’une transaction T1 est lancée avec le niveau d’isolation READ UNCOMMITTED, suivie d’une transaction T2 travaillant sur le même jeu de résultats que T1, et durant plus longtemps.

  • Création et lancement d’une transaction T1 :
-- Session 1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN T1
   SELECT *
   FROM MaTable

-- On fait durer la transaction 10 secondes avant validation
   WAITFOR DELAY '00:00:10'

   SELECT *
   FROM MaTable
COMMIT
GO
  • Création et lancement d’une transaction T2 à partir d’une autre session (autre fenêtre SSMS, par exemple), sans attendre la complétion de la transaction T1 :
-- Session 2
BEGIN TRAN T2
   UPDATE MaTable
   SET MaTable_ID = -1
   WHERE MaTable_ID = 1

   -- On fait durer la transaction 30 secondes avant annulation
   WAITFOR DELAY '00:00:30'
ROLLBACK
GO

L’utilisation de ROLLBACK pour l’annulation de la transaction 2 est anecdotique dans notre exemple. Vous pouvez utiliser COMMIT car dans tous les cas, la validation se fera après celle de T1.

Voici les résultats :


Comme on peut le constater ci-dessus, la transaction T1 affiche également les changements non-validés effectués par la transaction T2. On parle alors de lecture « sale ».

Pour circonscrire les risques de lectures « sales » par une transaction, utilisez tous les niveaux d’isolation sauf READ UNCOMMITTED, sachant que READ COMMITTED est le plus bas niveau d’isolation accepté face à ce type d’anomalie transactionnelle. Ainsi, il sera possible pour SQL Server d’interdire l’accès aux données modifiées non-validées grâce à un verrou partagé pour les lectures.

… sur les pertes de mises-à-jour

Imaginons qu’une transaction T1 est lancée avec le niveau d’isolation READ COMMITTED, suivie d’une transaction T2 effectuant une modification sur le même jeu de résultats que T1.

  • Création et lancement d’une transaction T1 :
-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRAN T1
   SELECT *
   FROM MaTable

   -- On fait durer la transaction 10 secondes avant validation
   WAITFOR DELAY '00:00:10'

   SELECT *
   FROM MaTable
COMMIT
GO
  • Création et lancement d’une transaction T2 à partir d’une autre session (autre fenêtre SSMS, par exemple), sans attendre la complétion de la transaction T1 :
-- Session 2
BEGIN TRAN T2
   UPDATE MaTable
   SET MaTable_ID = 20
   WHERE MaTable_ID = 1
COMMIT
GO

Voici les résultats :

Comme on peut le remarquer ci-dessous, la mise-à-jour effectuée par T1 a été perdue. En effet, T1 a tenté de modifier un jeu de résultat devenu obsolète suite à une mise-à-jour effectuée, entre-temps, par T2. On peut parler de perte de (mise-à-jour de) données pour T1.

Pour circonscrire les risques de lectures « sales » par une transaction, utilisez tous les niveaux d’isolation sauf READ UNCOMMITTED et READ COMMITTED, sachant que REPEATABLE READ est le plus bas niveau d’isolation accepté face à ce type d’anomalie transactionnelle. Il sera ainsi possible à SQL Server de poser un verrou sur les lignes traitées par la transaction T1 et de l’y maintenir jusqu’à la fin de celle-ci, empêchant ainsi la mise-à-jour des lignes du jeu de résultats traité.

… sur les lectures non-répétitives

Imaginons qu’une transaction T1 est lancée avec le niveau d’isolation READ COMMITTED pour l’affichage, en 2 temps, d’un même jeu de résultats. Et imaginons que T1 est suivie d’une transaction T2 qui effectue une modification sur son jeu de résultats.

  • Création et lancement d’une transaction T1 :
-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRAN T1
   SELECT *
   FROM MaTable

   -- On fait durer la transaction 10 secondes avant validation
   WAITFOR DELAY '00:00:10'

   SELECT *
   FROM MaTable
COMMIT
GO
  • Création et lancement d’une transaction T2 à partir d’une autre session (autre fenêtre SSMS, par exemple), sans attendre la complétion de la transaction T1 :
-- Session 2
BEGIN TRAN T2
   DELETE FROM MaTable
   WHERE MaTable_ID = 1
COMMIT
GO

Voici les résultats :


Comme on peut le noter, au cours des 10 secondes d’exécution de la transaction T1, une transaction T2 a procédé à la suppression d’un enregistrement de la table MaTable. Cela a pour effet de donner 2 résultats différents pour T1. On parle, alors, de lectures non-répétitives.

Pour circonscrire les risques de lectures « sales » par une transaction, utilisez tous les niveaux d’isolation sauf READ UNCOMMITTED et READ COMMITTED, sachant que REPEATABLE READ est le plus bas niveau d’isolation accepté face à ce type d’anomalie transactionnelle. Il sera ainsi possible à SQL Server de poser un verrou sur les lignes traitées par la transaction T1 et de l’y maintenir jusqu’à la fin de celle-ci.

… sur les lectures « fantômes »

Imaginons qu’une transaction T1 est lancée avec le niveau d’isolation REPEATABLE READ pour l’affichage, en 2 temps, d’un même jeu de résultats. Et imaginons que T1 est suivie d’une transaction T2 qui effectue une insertion au sein de son jeu de résultats.

  • Création et lancement d’une transaction T1 :
Session 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN T1
   SELECT *
   FROM MaTable

   -- On fait durer la transaction 10 secondes avant validation
   WAITFOR DELAY '00:00:10'

   SELECT *
   FROM MaTable
COMMIT
GO
  • Création et lancement d’une transaction T2 à partir d’une autre session (autre fenêtre SSMS, par exemple), sans attendre la complétion de la transaction T1 :
-- Session 2
BEGIN TRAN T2
   INSERT INTO MaTable (MaTable_ID) VALUES (25)
COMMIT
GO

Voici les résultats :


Comme on peut le voir, au cours de l’exécution de la transaction T1, il y a eu l’insertion d’un nouvel enregistrement au sein du jeu de résultats traité. Cela a eu pour effet de renvoyer un jeu de résultats différent à celui initialement utilisé. On parle de l’apparition d’une lecture « fantôme ».

Pour circonscrire les risques de lectures « sales » par une transaction, utilisez les niveaux d’isolation SERIALIZABLE ou, à partir de SQL Server 2005, le niveau d’isolation SNAPSHOT. SQL Server pourra ainsi verrouiller soit un ensemble de valeurs de clés d’index utilisées en fonction de la requête, soit toute la table s’il n’y a pas d’index (comme dans notre cas), de façon à ce qu’aucune modification DML ne soit possible. Dans le cas du SNAPSHOT, SQL Server ne verrouillera pas les enregistrements, mais utilisera le row-versioning, c’est-à-dire une copie du, jeu de résultats de T1 dans tempDB, ce qui réduit les risques de blocages.

Pour attester l’idée selon laquelle le niveau d’isolation SERIALIZABLE permet de se prémunir contre toute anomalie transactionnelle, et donc les lectures « fantômes », voici ce que donne le remplacement de REPEATABLE READ par SERIALIZABLE :


Comme on peut le souligner, le niveau d’isolation SERIALIZABLE a permis de protéger la transaction T1 d’une éventuelle lecture « fantôme ».

Rappels et remarques sur les niveaux d’isolation d’instantané

Les niveaux d’isolation d’instantané offrent des solutions d’accès optimistes.

Si vous souhaitez utiliser le niveau d’isolation SNAPSHOT, vous devez, si ce n’est déjà fait,
effectuer une activation de l’option ALLOW_SNAPSHOT_ISOLATION au niveau de la base de données au sein de laquelle la (ou les) transaction(s) cible(s) sera (seront) utilisée(s) :

ALTER DATABASE MaBase
  SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Concernant READ COMMITTED SNAPSHOT, une simple activation au niveau de la base de données cible suffit :

ALTER DATABASE MaBase
  SET READ_COMMITTED_SNAPSHOT ON
GO

Les transactions utiliseront simplement, par défaut, le niveau d’isolation READ COMMITTED avec le row-versioning de SNAPSHOT.

Dans le contexte des anomalies transactionnelles :

  • SNAPSHOT permet de se prémunir contre tout effet secondaire de la concurrence, à l’instar de SERIALIZABLE, avec, en plus, moins de blocages et donc une vitesse d’exécution généralement plus élevée. Au prix, bien sûr, d’une consommation des ressources de tempDB pour le row-versioning. Il a l’avantage de permettre la détection de conflits.
  • READ COMMITTED SNAPSHOT n’empêche pas certains effets de la concurrence tels que les pertes de mises-à-jour, les lectures non-répétitives et les lectures « fantômes ». En revanche, il permet de réduire les risques de deadlocks, et utilise le row-versioning (et donc, tempDB). L’avantage du niveau READ COMMITTED SNAPSHOT par rapport au niveau d’isolation SNAPSHOT est qu’il est adapté pour les situations au cours desquelles les transactions de longue durée doivent travailler sur des jeux de résultats à jour.

En conclusion…

Le tableau ci-dessous résume les différents niveaux d’isolation par rapport à leurs (possibles) anomalies transactionnelles :

Niveau d’isolation Lectures « sales » Pertes de mises-à-jour (entre deux lectures) Lectures non-répétitives Lectures « fantômes »
READ_UNCOMMITTED Oui. Oui. Oui. Oui.
READ_COMMITTED Non. Oui. Oui. Oui.
READ COMMITTED SNAPSHOT Non. Oui. Oui. Oui.
REPEATABLE_READ Non. Non. Non. Oui.
SNAPSHOT Non. Non. Non. Non.
SERIALIZABLE Non. Non. Non. Non.

Plus le niveau d’isolation est élevé, moins il y a d’anomalies transactionnelles. En revanche, il est important de noter qu’un niveau d’isolation élevé peut conduire à des risques de blocages de longue durée ou de verrous mortels (deadlocks). A l’exception, bien sûr, des niveaux d’isolation d’instantané (SNAPSHOT et READ COMMITTED SNAPSHOT) qui sont généralement plus rapides que SERIALIZABLE du fait de la faible présence de verrous.

Même s’il serait tentant d’opter aveuglément pour l’un des niveaux d’isolation d’instantané, cela n’empêchera pas SQL Server d’être potentiellement impacté par des problèmes de performances en cas de mauvaise utilisation : saturation de la base de données tempDB (cette dernière étant utilisée par les niveaux d’isolation d’instantané pour le row-versioning), augmentation des activités I/O,…

C’est pour cette raison que le niveau d’isolation READ COMMITTED est utilisé par défaut par SQL Server, vu qu’il fournit généralement un bon équilibre entre performances et résultats attendus. 

Pour consulter d’autres billets relatifs aux accès concurrentiels, gardez un oeil ici.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s