Transactions¶

Quand on développe un programme P accédant à une base de données, on effectue en général plus ou moins explicitement deux hypothèses:

P s’exécutera indépendamment de tout autre programme ou utilisateur;

l’exécution de P se déroulera toujours intégralement.

Il est clair que ces deux hypothèses ne se vérifient pas toujours. D’une part les bases de données constituent des ressources accessibles simultanément à plusieurs utilisateurs qui peuvent y rechercher, créer, modifier ou détruire des informations: les accès simultanés à une même ressource sont dits concurrents, et l’absence de contrôle de cette concurrence peut entraîner de graves problèmes de cohérence dus aux interactions des opérations effectuées par les différents utilisateurs. D’autre part on peut envisager beaucoup de raisons pour qu’un programme ne s’exécute pas jusqu’à son terme. Citons par exemple:

l’arrêt du serveur de données;

une erreur de programmation entrainant l’arrêt de l’application;

la violation d’une contrainte amenant le système à rejeter les opérations demandées;

une annulation décidée par l’utilisateur.

Une interruption de l’exécution peut laisser la base dans un état transitoire incohérent, ce qui nécessite une opération de réparation consistant à ramener la base au dernier état cohérent connu avant l’interruption. Les SGBD relationnels assurent, par des mécanismes complexes, un partage concurrent des données et une gestion des interruptions qui permettent d’assurer à l’utilisateur que les deux hypothèses adoptées intuitivement sont satisfaites, à savoir:

son programme se comporte, au moment où il s’exécute, comme s’il était seul à accéder à la base de données;

en cas d’interruption intempestive, les mises à jour effectuées depuis le dernier état cohérent seront annulées par le système.

On désigne respectivement par les termes de contrôle de concurrence et de reprise sur panne l’ensemble des techniques assurant ce comportement. En théorie le programmeur peut s’appuyer sur ces techniques, intégrées au système, et n’a donc pas à se soucier des interactions avec les autres utilisateurs. En pratique les choses ne sont pas si simples, et le contrôle de concurrence a pour contreparties certaines conséquences qu’il est souvent important de prendre en compte dans l’écriture des applications. En voici la liste, chacune étant développée dans le reste de ce chapitre:

Définition des points de sauvegardes. La reprise sur panne garantit le retour au dernier état cohérent de la base précédant l’interruption, mais c’est au programmeur de définir ces points de cohérence (ou points de sauvegarde) dans le code des programmes.

Blocages des autres utilisateurs. Le contrôle de concurrence s’appuie sur le verrouillage de certaines resources (tables blocs, n-uplets), ce qui peut bloquer temporairement d’autres utilisateurs. Dans certains cas des interblocages peuvent même apparaître, amenant le système à rejeter l’exécution d’un des programmes en cause.

Choix d’un niveau d’isolation. Une isolation totale des programmes garantit la cohérence, mais entraîne une dégradation des performances due aux verrouillages et aux contrôles appliqués par le SGBD. Or, dans beaucoup de cas, le verrouillage/contrôle est trop strict et place en attente des programmes dont l’exécution ne met pas en danger la cohérence de la base. Le programmeur peut alors choisir d’obtenir plus de concurrence (autrement dit, plus de fluidité dans les exécutions concurrentes), en demandant au système un niveau d’isolation moins strict, et en prenant éventuellement lui-même en charge le verrouillage des ressources critiques.

Ce chapitre est consacré à la concurrence d’accès, vue par le programmeur d’application. Il ne traite pas, ou très superficiellement, des algorithmes implantés par les SGBD. L’objectif est de prendre conscience des principales techniques nécessaires à la préservation de la cohérence dans un système multi-utilisateurs, et d’évaluer leur impact en pratique sur la réalisation d’applications bases de données. La gestion de la concurrence, du point de vue de l’utilisateur, se ramène en fait à la recherche du bon compromis entre deux solutions extrêmes:

une cohérence maximale impliquant un risque d’interblocage relativement élevé;

ou une fluidité concurrentielle totale au prix de risques importants pour l’intégrité de la base.

Ce compromis dépend de l’application et de son contexte (niveau de risque acceptable vs niveau de performance souhaité) et relève donc du choix du concepteur de l’application. Mais pour que ce choix existe, et puisse être fait de manière éclairée, encore faut-il être conscient des risques et des conséquences d’une concurrence mal gérée. Ces conséquences sont insidieuses, souvent erratiques, et il est bien difficile d’imputer au défaut de concurrence des comportements que l’on a bien du mal à interpréter. Tout ce qui suit vise à vous éviter ce fort désagrément.

Le chapitre débute par une définition de la notion de transaction, et montre ensuite, sur différents exemples, les problèmes qui peuvent survenir. Pour finir nous présentons les niveaux d’isolation définis par la norme SQL.

S4: choisir un niveau d’isolation¶

Supports complémentaires:

Du point du programmeur d’application, l’objectif du contrôle de concurrence est de garantir la cohérence des données et d’assurer la recouvrabilité des transactions. Ces bonnes propriétés sont obtenues en choisissant un niveau d’isolation approprié qui garantit qu’aucune interaction avec un autre utilisateur ne viendra perturber le déroulement d’une transaction, empêcher son annulation ou sa validation.

Une option possible est de toujours choisir un niveau d’isolation maximal, garantissant la sérialisabilité des transactions, mais le mode serializable a l’inconvénient de ralentir le débit transactionnel pour des applications qui n’ont peut-être pas besoin de contrôles aussi stricts. On peut chercher à obtenir de meilleures performances en choisissant explicitement un niveau d’isolation moins élevé, soit parce que l’on sait qu’un programme ne posera jamais de problème de concurrence, soit parce les problèmes éventuels sont considérés comme peu importants par rapport au bénéfice d’une fluidité améliorée.

On considère dans ce qui suit deux exemples. Le premier consiste en deux exécutions concurrentes du programme Réservation, désignées respectivement par \(T_1\) et \(T_2\).

Exemple: concurrence entre mises à jour

Chaque exécution consiste à réserver des places pour le même spectacle, mais pour deux clients distincts \(c_1\) et \(c_2\). L’ordre des opérations reçues par le serveur est le suivant:

\[r_1(s) r_1(c_1) r_2(s) r_2(c_2) w_2(s) w_2(c_2) w_1(s) w_1(c_1)\]

Au départ nous sommes dans la situation suivante:

il reste 50 places libres pour le spectacle s, \(c_1\) et \(c_2\) n’ont pour l’instant réservé aucune place;

\(T_1\) veut réserver 5 places pour s;

\(T_2\) veut réserver 2 places pour s.

Donc on effectue d’abord les lectures pour \(T_1\), puis les lectures pour \(T_2\) enfin les écritures pour \(T_2\) et \(T_1\) dans cet ordre. Aucun client n’a réservé de place.

Le second exemple prend le cas de la procédure effectuant un contrôle de cohérence sur notre base de données, uniquement par des lectures.

Exemple: concurrence entre lectures et mises à jour

La procédure Contrôle s’effectue en même temps que la procédure Réservation qui réserve 2 places pour le client \(c_2\). L’ordre des opérations reçues par le serveur est le suivant (\(T_1\) désigne le contrôle, \(T_2\) la réservation):

\[r_1(c_1) r_1(c_2) r_2(s) r_2(c_2) w_2(s) w_2(c_2) r_1(s)\]

Au départ le client \(c_1\) a réservé 5 places. Il reste donc 45 places libres pour le spectacle. La base est dans un état cohérent.

Les modes d’isolation SQL¶

La norme SQL ANSI (SQL92) définit quatre modes d’isolation correspondant à quatre compromis différents entre le degré de concurrence et le niveau d’interblocage des transactions. Ces modes d’isolation sont définis par rapport aux trois types d’anomalies que nous avons rencontrés dans les exemples qui précèdent:

Lectures sales: une transaction \(T_1\) lit un nuplet mis à jour par une transaction \(T_2\), avant que cette dernière ait validé;

Lectures non répétables: une transaction \(T_1\) accède, en lecture ou en mise à jour, à un nuplet qu’elle avait déjà lu auparavant, alors que ce nuplet a été modifié entre temps par une autre transaction \(T_2\);

Tuples fantômes: une transaction \(T_1\) lit un nuplet qui a été créé par une transaction \(T_2\) après le début de \(T_1\).

Tableau 6 Niveaux d’isolation de la norme SQL¶

Lectures sales

Lectures non répétables

Tuples fantômes

read uncommitted

Possible

Possible

Possible

read committed

Impossible

Possible

Possible

repeatable read

Impossible

Impossible

Possible

serializable

Impossible

Impossible

Impossible

Tableau 6 Niveaux d’isolation de la norme SQL¶
	Lectures sales	Lectures non répétables	Tuples fantômes
`read uncommitted`	Possible	Possible	Possible
`read committed`	Impossible	Possible	Possible
`repeatable read`	Impossible	Impossible	Possible
`serializable`	Impossible	Impossible	Impossible

Il existe un mode d’isolation par défaut qui varie d’un système à l’autre, le plus courant semblant être read committed.

Le premier mode (read uncommitted) correspond à l’absence de contrôle de concurrence. Ce mode peut convenir pour des applications non transactionnelles qui se contentent d’écrire « en vrac » dans des fichiers sans se soucier des interactions avec d’autres utilisateurs.

Avec le mode read committed, on ne peut lire que les nuplets validés, mais il peut arriver que deux lectures successives donnent des résultats différents. Le résultat d’une requête est cohérent par rapport à l’état de la base au début de la requête, Il peut arriver que deux lectures successives donnent des résultats différents si une autre transaction a modifié les données lues, et validé ses modifications. C’est le mode par défaut dans ORACLE par exemple.

Il faut bien être conscient que ce mode ne garantit pas l’exécution sérialisable. Le SGBD garantit par défaut l’exécution correcte des commit et rollback (recouvrabilité), mais pas la sérialisabilité. L’hypothèse effectuée implicitement est que le mode serializable est inutile dans la plupart des cas, ce qui est sans doute justifié, et que le programmeur saura le choisir explicitement quand c’est nécessaire, ce qui en revanche est loin d’être évident.

Le mode repeatable read (le défaut dans MySQL/InnoDB par exemple) garantit que le résultat d’une requête est cohérent par rapport à l’état de la base au début de la transaction. La réexécution de la même requête donne toujours le même résultat. La sérialisabilité n’est pas assurée, et des nuplets peuvent apparaître s’ils ont étés insérés par d’autres transactions (les fameux « nuplets fantômes »).

Enfin le mode serializable assure les bonnes propriétés (sérialisabilité et recouvrabilité) des transactions et une isolation totale. Tout se passe alors comme si on travaillait sur une « image » de la base de données prise au début de la transaction. Bien entendu cela se fait au prix d’un risque assez élevé de blocage des autres transactions.

Le mode est choisi au début d’une transaction par la commande suivante.

set transaction isolation level <option>

Une autre option parfois disponible, même si elle ne fait pas partie de la norme SQL, est de spécifier qu’une transaction ne fera que des lectures. Dans ces conditions, on peut garantir qu’elle ne soulèvera aucun problème de concurrence et le SGBD peut s’épargner la peine de poser des verrous. La commande est:

set transaction read only

Il devient alors interdit d’effectuer des mises à jour jusqu’au prochain commit ou rollback: le système rejette ces instructions.

Le mode `read committed`¶

Le mode read committed, adopté par défaut dans ORACLE par exemple, amène un résultat incorrect pour nos deux exemples! Ce mode ne pose pas de verrou en lecture, et assure simplement qu’une donnée lue n’est pas en cours de modification par une autre transaction. Voici ce qui se passe pour l’exemple ex-conc-rw.

On commence par la procédure de contrôle qui lit le premier client, r_1[c]. Ce client a réservé 5 places. La procédure de contrôle lit \(c_2\) qui n’a réservé aucune place. Donc le nombre total de places réservées est de 5.

Puis c’est la réservation qui s’exécute, elle lit le spectacle, le client 2 (aucun de ces deux nuplets n’est en cours de modification). Le client \(c_2\) réserve 2 places, donc au moment où la réservation effectue un commit, il y a 43 places libres pour le spectacle, 2 places réservées pour \(c_2\).

La session 1 (le contrôle) reprend son exécution et lit s. Comme s est validée on lit la valeur mise à jour juste auparavant par Res, et on trouve donc 43 places libres. La procédure de contrôle constate donc, à tort, une incohérence.

Le mode read committed est particulièrement inadapté aux longues transactions pour lesquelles le risque est fort de lire des données modifiées et validées après le début de la transaction. En contrepartie le niveau de verrouillage est faible, ce qui évite les bloquages.

Le mode `repeatable read`¶

Dans le mode repeatable read, chaque lecture effectuée par une transaction lit les données telles qu’elles étaient au début de la transaction. Cela donne un résultat correct pour l’exemple ex-conc-rw, comme le montre le déroulement suivant.

On commence par la procédure de contrôle qui lit le premier client, r_1[c]. Ce client a réservé 5 places. La procédure de contrôle lit \(c_2\) qui n’a réservé aucune place. Donc le nombre total de places réservées est de 5.

Puis c’est la réservation qui s’exécute, elle lit le spectacle, le client 2 (aucun de ces deux nuplets n’est en cours de modification). Le client \(c_2\) réserve 2 places, donc au moment où la réservation effectue une commit, il y a 43 places libres pour le spectacle, 2 places réservées pour \(c_2\).

La session 1 (le contrôle) reprend son exécution et lit s. Miracle! La mise à jour de la réservation n’est pas visible car elle a été effectuée après le début de la procédure de contrôle. Cette dernière peut donc conclure justement que la base, telle qu’elle était au début de la transaction, est cohérente.

Ce niveau d’isolation est suffisant pour que les mises à jour effectuées par une transaction T” pendant l’exécution d’une transaction T ne soient pas visibles de cette dernière. Cette propriété est extrêmement utile pour les longues transactions, et elle a l’avantage d’être assurée sans aucun verrouillage.

En revanche le mode repeatable read ne suffit toujours pas pour résoudre le problème des mises à jour perdues. Reprenons une nouvelle fois l’exemple ex-conc-trans. Voici un exemple concret d’une session sous MySQL/InnoDB, SGBD dans lequel le mode d’isolation par défaut est repeatable read.

Note

Vous pouvez répéter ce déroulement avec notre interface en ligne.

C’est la première session qui débute, avec des lectures.

Session 1> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

Session 1> select * from Spectacle where id_spectacle=1;
+--------------+--------------------+------------------+-------+
| id_spectacle | nb_places_offertes | nb_places_libres | tarif |
+--------------+--------------------+------------------+-------+
|            1 |                 50 |               50 | 10.00 |
+--------------+--------------------+------------------+-------+
1 row in set (0,01 sec)

Session 1> select * from Client where id_client=1;
+-----------+---------------------+-------+
| id_client | nb_places_reservees | solde |
+-----------+---------------------+-------+
|         1 |                   0 |   100 |
+-----------+---------------------+-------+

La session 1 constate donc qu’aucune place n’est réservée. Il reste 50 places libres. La session 2 exécute à son tour les lectures.

Session 2> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

Session 2> select * from Spectacle where id_spectacle=1;
+--------------+--------------------+------------------+-------+
| id_spectacle | nb_places_offertes | nb_places_libres | tarif |
+--------------+--------------------+------------------+-------+
|            1 |                 50 |               50 | 10.00 |
+--------------+--------------------+------------------+-------+
1 row in set (0,00 sec)

Session 2> select * from Client where id_client=2;
+-----------+---------------------+-------+
| id_client | nb_places_reservees | solde |
+-----------+---------------------+-------+
|         2 |                   0 |    60 |
+-----------+---------------------+-------+

Maintenant la session 2 effectue sa réservation de 2 places. Pensant qu’il en reste 50 avant la mise à jour, elle place le nombre 48 dans la table Spectacle.

Session 2> update Spectacle set nb_places_libres=48
       where id_spectacle=1;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Session 2> update Client set solde=40, nb_places_reservees=2
         where id_client=2;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Session 2> commit;
Query OK, 0 rows affected (0,00 sec)

Pour l’instant InnoDB ne dit rien. La session 1 continue alors. Elle aussi pense qu’il reste 50 places libres. La réservation de 5 places aboutit aux requêtes suivantes.

Session 1> update Spectacle set nb_places_libres=45 where id_spectacle=1;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Session 1> update Client set solde=50, nb_places_reservees=5 where id_client=1;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Session 1> commit;
Query OK, 0 rows affected (0,01 sec)

Session 1> select * from Spectacle where id_spectacle=1;
+--------------+--------------------+------------------+-------+
| id_spectacle | nb_places_offertes | nb_places_libres | tarif |
+--------------+--------------------+------------------+-------+
|            1 |                 50 |               45 | 10.00 |
+--------------+--------------------+------------------+-------+
1 row in set (0,00 sec)

Session 1> select * from Client;
+-----------+---------------------+-------+
| id_client | nb_places_reservees | solde |
+-----------+---------------------+-------+
|         1 |                   5 |    50 |
|         2 |                   2 |    40 |
+-----------+---------------------+-------+

La base est incohérente! les clients ont réservé (et payé) en tout 7 places, mais le nombre de places libres n’a diminué que de 5. L’utilisation de InnoDB ne garantit pas la correction des exécutions concurrentes, du moins avec le niveau d’isolation par défaut.

Ce point est très souvent ignoré, et source de problèmes récurrents chez les organisations qui croient s’appuyer sur un moteur transactionnel assurant une cohérence totale, et constatent de manière semble-t-il aléatoire l’apparition d’incohérences et de déséquilibres dans leurs bases.

Note

La remarque est valable pour de nombreux autres SGBD, incluant ORACLE, dont le niveau d’isolation par défaut n’est pas maximal.

On soupçonne le plus souvent les programmes, à tort puisque c’est l’exécution concurrente qui, parfois, est fautive, et pas le programme. Il est extrêmement difficile de comprendre, et donc de corriger, ce type d’erreur.

Le mode `serializable`¶

Si on analyse attentivement l’exécution concurrente de l’exemple ex-conc-trans, on constate que le problème vient du fait que les deux transactions lisent, chacune de leur côté, une information (le nombre de places libres pour le spectacles) qu’elles s’apprêtent toutes les deux à modifier. Une fois cette information transférée dans l’espace mémoire de chaque processus, il n’existe plus aucun moyen pour ces transactions de savoir que cette information a changé dans la base, et qu’elles s’appuient donc sur une valeur incorrecte.

La seule chose qui reste à faire pour obtenir une isolation maximale est de s’assurer que cette situation ne se produit pas. C’est ce que garantit le mode serializable, au prix d’un risque de blocage plus important. On obtient ce niveau avec la commande suivante:

set transaction isolation level serializable;

Reprenons une dernière fois l’exemple ex-conc-trans, en mode sérialisable, avec MySQL/InoDB.

Note

Vous pouvez aussi choisir le niveau sérialisable et reproduire le scénario avec l’application en ligne

La session 1 commence par ses lectures.

Session 1> set TRANSACTION ISOLATION LEVEL serializable;
Query OK, 0 rows affected (0,04 sec)

Session 1> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

Session 1> select * from Spectacle where id_spectacle=1;
+--------------+--------------------+------------------+-------+
| id_spectacle | nb_places_offertes | nb_places_libres | tarif |
+--------------+--------------------+------------------+-------+
|            1 |                 50 |               50 | 10.00 |
+--------------+--------------------+------------------+-------+
1 row in set (0,00 sec)

Session 1> select * from Client where id_client=1;
+-----------+---------------------+-------+
| id_client | nb_places_reservees | solde |
+-----------+---------------------+-------+
|         1 |                   0 |   100 |
+-----------+---------------------+-------+

Voici le tour de la session 2. Elle effectue ses lectures, et cherche à effectuer la première mise à jour.

Session 2> set TRANSACTION ISOLATION LEVEL serializable;
Query OK, 0 rows affected (0,00 sec)

Session 2> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

Session 2> select * from Spectacle where id_spectacle=1;
+--------------+--------------------+------------------+-------+
| id_spectacle | nb_places_offertes | nb_places_libres | tarif |
+--------------+--------------------+------------------+-------+
|            1 |                 50 |               48 | 10.00 |
+--------------+--------------------+------------------+-------+
1 row in set (0,00 sec)

Session 2> select * from Client where id_client=2;
+-----------+---------------------+-------+
| id_client | nb_places_reservees | solde |
+-----------+---------------------+-------+
|         2 |                   0 |    60 |
+-----------+---------------------+-------+
1 row in set (0,00 sec)

Session 2> update Spectacle set nb_places_libres=48 where id_spectacle=1;

La transaction 2 est mise en attente car, en mode sérialisable, MySQL/InnoDB pose un verrou en lecture sur les lignes sélectionnées. La transaction 1 a donc verrouillé, en mode partagé, le spectacle et le client. La transaction 2 a pu lire le spectacle, et placer à son tour un verrou partagé, mais elle ne peut pas le modifier car cela implique la pose d’un verrou exclusif.

Que se passe-t-il alors du côté de la transaction 1? Elle cherche à faire la mise à jour du spectacle. Voici la réaction de InnoDB.

Session 1> update Spectacle set nb_places_libres=45 where id_spectacle=1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
       try restarting transaction

Un interblocage (deadlock) a été détecté. La transaction 2 était déjà bloquée par la transaction 1. En cherchant à modifier le spectacle, la transaction 1 se trouve bloquée à son tour par les verrous partagées posés par la transaction 2.

En cas d’interblocage, les deux transactions peuvent s’attendre indéfiniment l’une l’autre. Le SGBD prend donc la décision d’annuler par rollback l’une des deux (ici, la transaction 1), en l’incitant à recommencer. La transaction 2 est libérée (elle garde ses verrous) et peut poursuivre son exécution.

Le mode serializable garantit la correction des exécutions concurrentes, au prix d’un risque de blocage et de rejet de certaines transactions. Ce risque, et ses effets désagréables (il faut resoumettre la transaction rejetée) expliquent qu’il ne s’agit pas du mode d’isolation par défaut. Pour les applications transactionnelles, il vaut sans doute mieux voir certaines transactions rejettées que courir un risque d’anomalie.

Verrouillage explicite¶

Certains systèmes permettent de poser explicitement des verrous, ce qui permet pour le programmeur averti de choisir un niveau d’isolation relativement permissif, tout en augmentant le niveau de verrouillage quand c’est nécessaire. ORACLE, PostgreSQL et MySQL proposent notamment une clause FOR update qui peut se placer à la fin d’une requête SQL, et dont l’effet est de réserver chaque nuplet lu en vue d’une prochaine modification.

Verrouillage des tables¶

Reprenons notre programme de réservation, et réécrivons les deux premières requêtes de la manière suivante:

...
select * into v_spectacle
from Spectacle
where id_spectacle=v_id_spectacle
FOR update;
...
select * into v_client from Client
where id_client=v_id_client
FOR update;
..

On annonce donc explicitement, dans le code, que la lecture d’un nuplet (le client ou le spectacle) sera suivie par la mise à jour de ce même nuplet. Le système pose alors un verrou exclusif qui réserve l’accès au nuplet, en lecture ou en mise à jour, à la transaction qui a effectué la lecture avec FOR update. Les verrous posés sont libérés au moment du commit ou du rollback.

Voici le déroulement de l’exécution pour l’exécution de l’exemple ex-conc-trans:

\(T_1\) lit s, après l’avoir verrouillé exclusivement;

\(T_1\) lit \(c_1\), et verrouille exclusivement;

\(T_2\) veut lire s, et se trouve mise en attente;

\(T_1\) continue, écrit s, écrit \(c_1\), valide et libère les verrous;

\(T_2\) est libéré et s’excéute.

On obtient l’exécution en série suivante.

\[r_1(s) r_1(c_1) w_1(s) w_1(c_1) C_1 r_2(s) r_2(c_2) w_2(s) w_2(c_2) C_2\]

La déclaration, avec FOR update de l’intention de modifier un nuplet revient à le réserver et donc à empêcher un entrelacement avec d’autres transactions menant soit à un rejet, soit à une annulation autoritaire du SGBD.

Les SGBDs fournissent également des commandes de verrouillage explicite. On peut réserver, en lecture ou en écriture, une table entière. Un verrouillage en lecture est partagé: plusieurs transactions peuvent détenir un verrou en lecture sur la même table. Un verrouillage en écriture est exclusif: il ne peut y avoir aucun autre verrou, partagé ou exclusif, sur la table.

Voici un exemple avec MySQL dont un des moteurs de stockage, MyISAM, ne gère pas la concurrence. Il faut dont appliquer explicitement un verrouillage si on veut obtenir des exécutions concurrentes sérialisables. En reprenant l’exemple ex-conc-trans avec verrouillage exclusif (WRITE), voici ce que cela donne. La session 1 verrouille (en écriture), lit le spectacle puis le client 1.

Session 1> LOCK TABLES Client WRITE, Spectacle WRITE;
Query OK, 0 rows affected (0,00 sec)

Session 1> select * from Spectacle where id_spectacle=1;
+--------------+--------------------+------------------+-------+
| id_spectacle | nb_places_offertes | nb_places_libres | tarif |
+--------------+--------------------+------------------+-------+
|            1 |                 50 |               50 | 10.00 |
+--------------+--------------------+------------------+-------+
1 row in set (0,00 sec)

Session 1> select * from Client where id_client=1;
+-----------+---------------------+-------+
| id_client | nb_places_reservees | solde |
+-----------+---------------------+-------+
|         1 |                   0 |   100 |
+-----------+---------------------+-------+

La session 2 tente de verrouiller et est mise en attente.

Session 2> LOCK TABLES Client WRITE, Spectacle WRITE;

La session 1 peut finir ses mises à jour, et libère les tables avec la commande UNLOCK TABLES.

Session 1> update Spectacle set nb_places_libres=45
        where id_spectacle=1;
Query OK, 1 row affected (0,00 sec)

Session 1> update Client set solde=50, nb_places_reservees=5
         where id_client=1;
Query OK, 1 row affected (0,00 sec)

Session 1> UNLOCK TABLES;

La session 2 peut alors prendre le verrou, effectuer ses lectures et mises à jour, et libérer le verrou. Les deux transactions se sont effectuées en série, et le résultat est donc correct.

La granularité du verrouillage explicite avec LOCK est la table entière, ce qui est généralement considéré comme mauvais car un verrouillage au niveau de lignes permet à plusieurs transactions d’accéder à différentes lignes de la table.

Le verrouillage des tables est une solution de « concurrence zéro » qui est rarement acceptable car elle revient à bloquer tous les processus sauf un. Dans un système où de très longues transactions (par exemple l’exécution d’un traitement lourd d’équilibrage de comptes) cohabitent avec de très courtes (des saisies interactives), les utilisateurs sont extrêmement pénalisés. Pour ne rien dire du cas où on oublie de relâcher les verrous…

De plus, dans l’exemple ex-conc-trans, il n’existe pas de conflit sur les clients puisque les deux transactions travaillent sur deux lignes différentes \(c_1\) et \(c_2\). quand seules quelques lignes sont mises à jour, un verrouillage total n’est pas justifié.

Le verrouillage de tables peut cependant être envisagé dans le cas de longues transactions qui vont parcourir toute la table et souhaitent en obtenir une image cohérente. C’est par exemple typiquement le cas pour une sauvegarde. De même, si une longue transaction effectuant des mises à jour est en concurrence avec de nombreuses petites transactions, le risque d’interblocage, temporaire ou définitif (voir plus loin) est important, et on peut envisager de précéder la longue transaction par un verrouillage en écriture.

Verrouillage d’une ligne avec `FOR update`¶

Une alternative au mode serializable est la pause explicite de verrous sur les lignes que l’on s’apprête à modifier. La clause FOR update place un verrou exclusif sur les nuplets sélectionnés par un ordre select. Ces nuplets sont donc réservés pour une future modification: aucune autre transaction ne peut placer de verrou en lecture ou en écriture. L’intérêt est de ne pas réserver les nuplets qui sont simplement lues et non modifiées ensuite. Notez qu’en mode serializable toutes les lignes lues sont réservées, car le SGBD, contrairement au programmeur de l’application, ne peut pas deviner ce qui va se passer ensuite.

Voici l’exécution de l’exemple ex-conc-trans, en veillant à verrouiler les lignes que l’on va modifier.

C’est la transaction 1 qui commence. Elle lit le spectacle et le client \(c_1\) en posant un verrou exclusif avec la clause FOR update.

Ensuite c’est la seconde transaction qui transmet ses commandes au serveur. Elle aussi cherche à placer des verrous (c’est normal, il s’agit de l’exécution du même code). Bien entendu elle est mise en attente puisque la session 1 a déjà posé un verrou exclusif.

La session 1 peut continuer de s’exécuter. Le commit libère les verrous, et la transaction 2 peut alors conclure.

Au final les deux transactions se sont exécutées en série. La base est dans un état cohérent. L’utilisation de FOR update est un compromis entre l’isolation assurée par le système, et la déclaration explicite, par le programmeur, des données lues en vue d’être modifiées. Elle assure le maximum de fluidité pour une isolation totale, et minimise le risque d’interblocages. Le principal problème est qu’elle demande une grande discipline pendant l’écriture d’une application puisqu’il faut se poser la question, à chaque requête, des lignes que l’on va ou non modifier.

En résumé, il est de la responsabilité du programmeur, sur un SGBD n’adoptant pas le mode SERIALISABLE par défaut, de prendre lui-même les mesures nécessaires pour les transactions qui risquent d’aboutir à des incohérences en cas de concurrence sur les mêmes données. Ces mesures peuvent consister soit à passer en mode serializable pour ces transactions, soit à poser explicitement des verrous, en début de transaction, sur les données qui vont être modifiées ensuite.

Atelier: réservons des places pour Philippe¶

Le but de cet atelier est de simuler des exécutions concurrentes avec un utilitaire de commandes SQL. Les instructions données ci-dessous ont été testées avec MySQL, elles fonctionnent probablement avec tout système relationnel, au prix éventuel de quelques ajustements.

Préparation¶

Il faut utiliser une application cliente qui permet de soumettre des commandes SQL au serveur. Pour MySQL, vous disposez de l’utilitaire mysql, ou du client graphique MySQL workbench, ou encore du client web phpMyAdmin. À vous de l’installer, de créer la base et le compte utilisateur.

Comme le support de cours montre comment réserver un même spectacle pour deux clients, nous allons simplement étudier une variante: réservation de deux spectacles pour le même client ! En d’autres termes, il s’agit d’une simple transposition de ce qui a été abondamment démontré ci-dessus, la mise en pratique en plus.

Voici tout d’abord les commandes de création des tables

create table Client (id_client integer not null,
                     nom varchar(30) not null,
                     nb_places_reservees integer not null,
                     solde integer not null,
                     primary key (id_client))
                      ;

create table Spectacle (id_spectacle integer not null,
                        titre varchar(30) not null,
                        nb_places_offertes integer not null,
                        nb_places_libres integer not null,
                        tarif decimal(10,2) not null,
                        primary key (id_spectacle))
                     ;

Insérez des données dans la base (et refaites cette insertion quand vous souhaitez la réinitialiser). Vous pouvez exécuter en bloc les commandes suivantes:

set autocommit = 0;
delete from Client;
delete from Spectacle;
insert intoClient values (1, 'Philippe', 0, 2000);
insert intoClient values (2, 'Julie', 0, 350);
insert intoSpectacle values (1, 'Ben hur', 250, 50, 50);
insert intoSpectacle values (2, 'Tartuffe', 120, 30, 30);
commit;

Vous pouvez alors ouvrir deux connexions simultanées à votre base. La première chose à faire est de s’assurer que l’on n’est pas en mode autocommit (mode où chaque mise à jour se termine par un commit, autrement dit un mode anti-transactionnel). Pour MySQL la commande est:

set autocommit = 0;

Nous appellerons les deux connexions Session1 et Session2. Le second réglage est le niveau d’isolation. Choisissez une des commandes ci-dessous.

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

Et pour finir, il faudra, pour simuler une réservation, exécuter pas à pas les commandes de sélection et de mise à jour. Voici quelques exemple à reprendre et adapter.

select * from Client where id_client=1;
select * from Spectacle where id_spectacle=2;

update Client set nb_places_reservees = 0 + 2 where id_client=2;
update Spectacle set nb_places_libres = 50 - 2 where id_spectacle=1;

Déroulement¶

Le but est de réserver des places pour Philippe: 2 places pour Ben Hur, et 4 pour Tartuffe. On a donc deux exécutions de la procédure de réservation, l’une à dérouler dans la session 1, l’autre dans la session 2.

Commencez par écrire les requêtes effectuées par chaque transaction

Effectuez les deux transactions l’une après l’autre (en série). Quel est l’état de la base à la fin? Cela vous satisfait-il?

Maintenant, en mode read committed ou repeatable read, déroulez des deux transactions dans l’ordre suivant: la transaction 1 fait ses lectures; la transaction 2 fait ses lectures; la transaction 1 fait ses écritures, la transaction 2 fait ses écritures.

Quel est l’état de la base à la fin? Conclusion?

Recommencez en mode serialisable.

Vous devriez arriver à des conclusions déjà largement détaillées dans le cours. À vous de faire le lien!

Transactions¶

S1: Transactions¶

Notions de base¶

Exécutions concurrentes¶

Propriétés ACID des transactions¶

Isolation¶

Garantie de la commande commit (durabilité)¶

Garantie de la commande rollback (atomicité)¶

Cohérence des transactions¶

Quiz¶

S2: Pratique des transactions¶

L’application en ligne « Transactions »¶

Structure et contenu de la base¶

Variables¶

Actions¶

Historique¶

Quelques expériences avec l’interface en ligne¶

Leçon 1: isolation des transactions¶

Leçon 2: commit et rollback¶

Leçon 3: les écritures concurrentes posent des verrous¶

Leçon 4: isolation incomplète = incohérence possible¶

Leçon 5: isolation complète = blocages possibles¶

Mise en pratique directe avec un SGBD¶

Quiz¶

S3: effets indésirables des transactions concurrentes¶

Défauts de sérialisabilité¶

Les mises à jour perdues¶

Lectures non répétables¶

Défauts de recouvrabilité¶

Lectures sales¶

Ecriture sale¶

Quiz¶

S4: choisir un niveau d’isolation¶

Les modes d’isolation SQL¶

Le mode read committed¶

Le mode repeatable read¶

Le mode serializable¶

Verrouillage explicite¶

Verrouillage des tables¶

Verrouillage d’une ligne avec FOR update¶

Quiz¶

Exercices¶

Atelier: réservons des places pour Philippe¶

Préparation¶

Déroulement¶

Table Of Contents

Recherche

Garantie de la commande `commit` (durabilité)¶

Garantie de la commande `rollback` (atomicité)¶

Leçon 2: `commit` et `rollback`¶

Le mode `read committed`¶

Le mode `repeatable read`¶

Le mode `serializable`¶

Verrouillage d’une ligne avec `FOR update`¶