[SQL Server] Accès concurrentiels : présentation des niveaux d’isolation

Ce billet présente les niveaux d’isolation qui régissent les accès concurrents sous SQL Server. Il offre également quelques éléments de réflexion sur le choix du bon niveau d’isolation.

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 aller ici si vous désirez un peu de lecture sur les transactions (présentation, propriétés ACID,…).

Les niveaux d’isolation

Les niveaux d’isolation : késako ?

Les niveaux d’isolation sont des mécanismes qui permettent d’isoler, suivant différents degrés de contrainte, des transactions par rapport aux traitements d’autres transactions. Ces mécanismes travaillent avec des verrous, choisis et établis en fonction du niveau d’isolation spécifié au niveau base de données.

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 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.
  • 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.

Pour modifier le niveau d’isolation d’une transaction pour la session en cours ou pour une transaction, vous pouvez exécuter la requête suivante :

USE MaBase
GO
SET TRANSACTION ISOLATION LEVEL <Niveau_Isolation>
GO
BEGIN TRAN …
…
GO

<Niveau_Isolation> correspond à un niveau d’isolation, en l’occurrence : READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT et SERIALIZABLE.

Et pour connaître le niveau d’isolation par défaut d’une base de données pour la connexion courante, vous pouvez utiliser DBCC USEROPTIONS. Exemple pour la base MaBase :

USE MaBase
GO
DBCC USEROPTIONS
GO

Et voici un exemple de résultat :


En bonus :

  • La requête suivante permet de connaître l’état des niveaux d’isolation d’instantané (voir plus loin) de chaque base de données, via sys.databases :
SELECT name,
   snapshot_isolation_state_desc,
   is_read_committed_snapshot_on
FROM sys.databases
GO

Vous pouvez filtrer la requête ci-dessus pour qu’elle n’affiche que la ou les bases souhaitées avec WHERE.

  • La requête suivante peut permettre de connaître le niveau d’isolation des sessions utilisateurs :
SELECT session_id,
   host_name,
   login_name,
   transaction_isolation_level
FROM sys.dm_exec_sessions
GO

Vous pouvez filtrer (via WHERE) la requête ci-dessus pour qu’elle n’affiche que les logins souhaités. Pour information, transaction_isolation_level vaut 1 s’il s’agit de READ UNCOMMITTED; 2, READ COMMITTED; 3, REPEATABLE READ; 4, SERIALIZABLE; 5, SNAPSHOT.

Notez qu’il n’est pas possible de modifier le niveau d’isolation par défaut d’une base de données, à moins d’utiliser l’un des niveaux d’isolation d’instantané (SNAPSHOT et READ COMMITTED SNAPSHOT). Voir plus loin.
Par défaut, READ COMMITTED est le niveau d’isolation des bases de données quelles que soient les sessions.

Les différents types de niveaux d’isolation

Les niveaux d’isolation de la norme SQL

La norme SQL-92 supporte principalement 4 types de niveaux d’isolation. Le tableau ci-dessous se charge de faire leur présentation :

Niveau d’isolation Description Pour être exact…
READ_UNCOMMITTED Ce niveau d’isolation permet, à une transaction, d’ignorer les verrous placés par d’autres transactions. Il s’agit du niveau d’isolation le moins contraignant. Toute transaction utilisant ce niveau d’isolation n’empêche pas d’autres transactions de modifier les données qu’elle est en train de traiter, et vice-versa. Dans la mesure où toute transaction peut voir les données qui ont été modifiées par d’autres transactions, mais qui n’ont pas été (encore) validées, cela peut conduire à ce qu’on appelle des lectures « sales » (ou inconsistantes).
READ_COMMITTED Ce niveau d’isolation est l’antithèse de READ UNCOMMITTED, empêchant ainsi les lectures « sales ». Il s’agit du niveau d’isolation par défaut de SQL Server. Ce niveau d’isolation empêche les lectures « sales » en interdisant l’accès aux données dont les modifications n’ont pas été encore validées par la transaction en cours. Une transaction avec ce niveau d’isolation pose des verrous partagés afin d’empêcher d’autres transactions de modifier les données qu’elle est en train de lire.
Dans la mesure où un verrou partagé ne peut être acquis que seulement s’il n’y a pas de verrou exclusif (nécessaire pour les modifications de données) par d’autres transactions, cela permet d’assurer la lecture de données validées, mais n’empêche pas les modifications par d’autres transactions entre chaque lecture, pouvant ainsi conduire à des lectures fantômes ou non-répétitives (voir REPEATABLE READ).
REPEATABLE_READ Ce niveau d’isolation reprend les spécificités de READ COMMITTED, mais en étant plus contraignant. En effet, il interdit la modification (mise-à-jour ou suppression) de toute donnée lue par une transaction active tant que cette dernière n’a pas été validée, ce qui permet également de faire face à des pertes de mises-à-jour.

Supposons qu’au sein d’une transaction utilisant le niveau d’isolation READ COMMITTED, il y a deux lectures d’un jeu de données. Ces deux lectures peuvent potentiellement donner des jeux de résultats différents si des données ont été modifiées entre ces deux lectures. En effet, un verrou partagé n’est acquis que seulement durant la lecture de ces données, avant d’être immédiatement libéré sitôt le traitement terminé. En l’occurrence, après la première lecture de données, le verrou partagé est libéré, et si d’autres transactions décident de modifier les données en question avant la seconde lecture de la transaction, cela pourrait donner une lecture différente.
Ainsi, dans le cas du niveau d’isolation REPEATABLE READ, au lieu d’être libéré à chaque fin de traitement de données d’une transaction sans attendre sa complétion, le verrou partagé n’est libéré qu’à la fin de la transaction. Toutefois, cela n’empêche pas ce qu’on appelle les lectures « fantômes », deux lectures d’une même transaction pouvant retourner un nombre d’enregistrements différents, y compris en ayant les mêmes prédicats.
SERIALIZABLE Ce niveau d’isolation est le plus contraignant. En effet, il reprend les spécificités de REPEATABLE READ mais en interdisant les lectures « fantômes ». Comment ce niveau d’isolation permet d’éviter les lectures « fantômes » ? Il utilise une plage entière de verrous sur un ensemble de valeurs clés qui correspondent aux prédicats de chaque instruction exécutée au sein de la transaction en cours de traitement.
Cela permet, ainsi, d’empêcher les autres transactions d’insérer ou de mettre à jour des données ciblées par les prédicats utilisés dans la transaction en cours. Les verrous posés ne seront libérés qu’à la fin de la transaction, ce qui fait que, pour parler du revers de la médaille, en cas de transaction longue, cela peut générer des verrous mortels (deadlocks).

Le tableau ci-dessus nous permet d’avoir le résumé suivant : plus le niveau d’isolation est faible (de SERIALIZABLE à READ UNCOMMITTED), plus le nombre d’utilisateurs susceptibles d’accéder simultanément aux données est élevé, et plus les anomalies transactionnelles (lectures « sales »,…) sont nombreuses. A contrario, plus le niveau d’isolation est élevé (de READ UNCOMMITTED à SERIALIZABLE), plus le nombre d’anomalies transactionnelles qu’un utilisateur est susceptible de rencontrer est réduit, sans que cela n’empêche une possible surconsommation des ressources systèmes, voire un blocage mutuel de transactions (deadlocks).

Niveaux d’isolation d’instantané

Comme on a pu le noter plus haut, les niveaux d’isolation de la norme SQL-92 sont conçus avec une approche pessimiste, de telle sorte que les lectures (verrous partagés) bloquent les écritures (verrous exclusifs) et vice-versa (on excepte, bien sûr, READ UNCOMMITTED, très sujet aux lectures « sales »).
A partir de SQL Server 2005, deux nouveaux niveaux d’isolation permettant de réduire les inter-blocages grâce à un contrôle d’accès concurrents optimiste sont introduits :

  • SNAPSHOT : ce niveau d’isolation augmente la concurrence tout en assurant la consistance des données sur le plan transactionnel. Les lectures ne sont pas bloquées par les écritures (modification de données). En effet, à la place, dans la mesure où SQL Server procède à la maintenance des versions des données traitées, la lecture se fait sur la dernière version pré-modification des données ciblées en consultant le version store au sein de tempDB.
  • READ COMMITTED SNAPSHOT : il s’agit d’une extension de READ COMMITTED qui inclut les vertus de SNAPSHOT. En revanche, contrairement au SNAPSHOT, il n’y a pas de détection des conflits de mises-à-jour.

Comme déjà évoqué, ces deux types de niveaux d’isolation, pour fonctionner, utilisent le row versioning, Concrètement, SQL Server maintient les versions des données précédemment validées dans un version store (banque de versions), au sein de la base de données tempDB, permettant ainsi de lire des anciennes versions consistantes des données avant le lancement de la transaction, y compris quand la version en cours est verrouillée et a été en cours de changement.

Quand un des deux niveaux d’isolation est utilisé, toute mise-à-jour d’une donnée sera marquée avec un timestamp (pour « situer » son ancienneté), et une version pré-modification/lecture de cette donnée stockée dans le version store avec un numéro de séquence en guise de pointeur permettant de référencer la donnée modifiée/vue.

L’activation d’un des deux niveaux d’isolation est très simple via T-SQL :

  • SNAPSHOT :
ALTER DATABASE MaBase
 SET ALLOW_SNAPSHOT_ISOLATION ON
GO
  • READ COMMITTED SNAPSHOT :
ALTER DATABASE MaBase
 SET READ_COMMITTED_SNAPSHOT ON
GO
Pour plus de détails sur le fonctionnement détaillé des niveaux d’isolation d’instantané, vous pouvez aller ici : http://msdn.microsoft.com/en-us/library/tcbchxcb.aspx.

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 obligatoirement à jour.

En résumé…

Le choix du niveau d’isolation dépend d’un certain nombre de facteurs, à commencer par les performances, la volumétrie et l’intégrité des données. Ainsi, le niveau d’isolation le plus élevé, SERIALIZABLE (ainsi que les niveaux d’isolation d’instantané), permet de faire face à un maximum d’anomalies transactionnelles provoquées par les accès concurrents, au prix d’un possible impact sur les performances du serveur. En revanche,
le niveau le plus bas, READ UNCOMMITTED, permet l’apparition de toutes les anomalies transactionnelles possibles, mais avec, généralement, l’assurance d’une réduction de la charge du serveur puisqu’il n’y a ni verrouillage de lecture, ni de row-versioning.

Il devient donc important de ne pas choisir n’importe quel niveau d’isolation. Néanmoins, un best-practice consiste souvent à laisser, dans la mesure du possible, le niveau d’isolation par défaut de chaque base de données tel quel (READ_COMMITTED) vu qu’il est à cheval entre les niveaux d’isolation bas et ceux élevés, permettant ainsi de profiter à un maximum d’avantages concurrentiels au niveau des transactions sans faire face à trop d’inconvénients. Et si de très nombreux deadlocks (verrous mortels) sont observés, le niveau d’isolation READ COMMITTED SNAPSHOT peut être envisagé (mais gardez bien en tête que l’omniprésence de deadlocks peut être due à un mauvais schéma relationnel et/ou à des requêtes mal-optimisées). Dans ce cas, jetez un coup d’œil ici pour en savoir plus sur les bonnes pratiques relatives à tempDB.

Sauf cas exceptionnels, le niveau d’isolation READ UNCOMMITTED doit être évité. Bien que ce soit qu’il limite au maximum tout risque de blocages, c’est aussi celui qui, paradoxalement, possède le plus grand nombre d’inconvénients : lectures « fantômes », données inconsistantes,…

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 » Type de concurrence Détection de conflits de mises-à-jour
READ_UNCOMMITTED Oui. Oui. Oui. Oui. Pessimiste. (*) Non.
READ_COMMITTED Non. Oui. Oui. Oui. Pessimiste. Non.
READ COMMITTED SNAPSHOT Non. Oui. Oui. Oui. Optimiste. Non.
REPEATABLE_READ Non. Non. Non. Oui. Pessimiste. Non.
SNAPSHOT Non. Non. Non. Non. Optimiste. Oui.
SERIALIZABLE Non. Non. Non. Non. Optimiste. Non.

(*) : Quand un niveau d’isolation pessimiste est utilisé (comportement par défaut de SQL Server), les écritures bloquent toujours les écritures tierces, ainsi que les lectures (sauf pour les transactions utilisant READ UNCOMMITTED car fonctionnent de la même façon qu’une requête avec un hint
NOLOCK, ce qui permet d’effectuer des lectures « sales », par exemple).

Pour aller plus loin…

Dans le futur, nous effectuerons quelques démonstrations techniques sur les anomalies transactionnelles.

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