# Transactions La plupart du temps, les modifications à notre base de données sont groupées, selon la logique business : * Plusieurs *lignes* sont modifiées ou supprimées * Plusieurs *tables* sont modifiées Plus que souvent cette logique business s’effectue coté serveur (dans le cas de PHP) ou coté API. Prenons l'exemple d'une opération de banque : Personne Y veut verser une somme à une personne X.

Exemple d'une opération multi-commande

On voit dans l'image que le transfert d'argent s'effectue par plusieurs commandes SQL. Une opération logique, mais multi-commande coté SQL. Le problème est que chaque commande SQL s'effectue quelques millisecondes après la précédente. Dans l'informatique, qui fonctionne au niveau de micro-secondes, il est bien possible qu'un **sinistre arrive en plein milieu de cette opération** ! Il y a quelques exemples possibles : **Une coupure de courant**

Une coupure de courant en plein milieu de l'opération

Dans l'exemple, il y a une coupure de courant entre l'étape 4 et 5. Le problème est qu'on a déjà enlevé de l'argent du compte Y. Après le rétablissement du serveur, on n'a aucune notion de ce qu'il n'est pas encore terminé. On a détruit pour toujours la somme de 100 €. Personne Y ne l'a plus, et Personne X ne l'a pas reçu ! **Une condition de course**

Race condition

Dans l'exemple, dans l'opération 1, on charge le solde (disons 200 €) de la Personne Y en mémoire dans notre API ou appli PHP (par exemple). Dans les quelques millisecondes entre l'étape 1 et 4, il arrive une mise à jour d'une autre appli qui change le solde à 50 €. On peut dire un virement automatique de paiement de loyer. Notre processus, ensuite, procède à effectuer son `update`, en écrasant la valeur actuelle (50 €), par 100 €. Notre solde n'est plus cohérente ni fiable !! Une autre exemple :

Race condition

Dans cet exemple, on aura déjà supprimé la somme du compte de la Personne Y, seulement pour trouver que le compte de la personne X a été suspendu. À ce moment-là, on signale une erreur, sauf la Personne Y a déjà perdu sa somme ! ## Opération `ACID` Nous cherchons alors à éviter tous ces problèmes, avec une règle simple :

Tout ou rien !

Plus formellement, on parle d'une opération **ACID** : * **Atomicité** : Toutes les opérations s’effectuent correctement dans la base de données, sinon aucun changement n’est enregistré * **Cohérence** : La base de données commence dans un état consistant, et se termine dans un état consistant. En partie, implémenté par les contraintes sur les données, mais aussi sous la responsabilité du développeur. * **Isolement** : Les transactions peuvent s’effectuer en parallèle, mais la SGBDR fait une sorte de garantir l’isolement des données touchées par chaque transaction. Si les mêmes données sont touchées par 2 transactions, faire une sorte de les effectuer dans l’ordre * **Durabilité** : Après la fin d’une transaction, tous les changements sont persistants, même en cas de panne ## Transactions Les SGDBR implémente ce qu'on appelle *une transaction*, une technique qui assure les propriétés ACID sur un ensemble de commandes SQL. La syntaxe est le suivant : ```sql start transaction; /* Plusieurs commandes SQL */ update ... set ... insert into ... delete from ... /* Une erreur est rencontré ? Remettre tout à zéro, et quitter ! */ rollback; /* Tout s'est bien passé. Sauvegarder les modifications */ commit; ``` Dans les sections suivantes, on va analyser l'usage des transactions, et tester des différentes caractéristiques ACID. ## Atomicité Créons une table pour notre transaction bancaire, et insérons 2 lignes pour nos deux comptes : ```sql create table account ( client_id int not null auto_increment, balance int not null default 0 check (balance >= 0), primary key(client_id) ); insert into account (balance) values (2000); insert into account (balance) values (5000); ``` Actuellement, notre table est le suivant : | client\_id | balance | | ---------- | ------- | | 1 | 2000 | | 2 | 5000 | Maintenant, on va implémenter le transfert d'argent de notre exemple, à l'aide des variables SQL : ```sql /* Retirer la somme de 100 euro du compte 1 */ select @solde1:=balance from account where client_id=1; set @solde1:=@solde1 - 100; update account set balance=@solde1 where client_id=1; /* Ajouter la somme de 100 euro au compte 2 */ select @solde2:=balance from account where client_id=2; set @solde2:=@solde2 + 100; update account set balance=@solde2 where client_id=2; ``` Tout se passe bien, cette fois-ci, le résultat est : | client\_id | balance | | ---------- | ------- | | 1 | 1900 | | 2 | 5100 | On répète la procédure, mais **en simulant une panne de courant** : ```sql /* Retirer la somme de 100 euro du compte 1 */ select @solde1:=balance from account where client_id=1; set @solde1:=@solde1 - 100; update account set balance=@solde1 where client_id=1; /** ICI, ARRÊTEZ VOTRE CONTAINER DOCKER ! C'est l'équivalent d'une panne qui interrompe votre opération ! **/ /* Ajouter la somme de 100 euro au compte 2 */ select @solde2:=balance from account where client_id=2; set @solde2:=@solde2 + 100; update account set balance=@solde2 where client_id=2; ``` {% hint style="info" %} Comment arrêter votre Container docker ? Si vous avez crée votre SGBDR avec un fichier `docker-compose.yml`, il suffit de naviguer dans le dossier de ce fichier et taper : ```sh docker compose down ``` {% endhint %} Quand on relance notre Container docker, et on regarde notre table, on verra **des valeurs inconsistantes** : | client\_id | balance | | ---------- | ------- | | 1 | 1800 | | 2 | 5100 | On a détruit 100 € ! La seule façon serait de manuellement détecter le problème, et le corriger à la main avec : ```sql update account set balance=1900 where client_id=1; ``` Maintenant, testons avec l'outil `transaction` de notre SGBDR ! Ajoutons la ligne `start transaction` avant notre opération, et `commit` à la fin de notre opération. ```sql start transaction; /* Retirer la somme de 100 euro du compte 1 */ select @solde1:=balance from account where client_id=1; set @solde1:=@solde1 - 100; update account set balance=@solde1 where client_id=1; /** ICI, ARRÊTEZ VOTRE CONTAINER DOCKER ! C'est l'équivalent d'une panne qui interrompe votre opération ! **/ /* Ajouter la somme de 100 euro au compte 2 */ select @solde2:=balance from account where client_id=2; set @solde2:=@solde2 + 100; update account set balance=@solde2 where client_id=2; commit; ``` Testez ! Commencez à taper vos commandes, et arrêtez votre instance en plein milieu, comme indiqué. Relancez votre Container, et analysez la table : | client\_id | balance | | ---------- | ------- | | 1 | 1900 | | 2 | 5100 | Succès !! La modification qu'on a apportée avant la coupure a été annulée ! {% hint style="success" %} **L'atomicité** est donc assurée en utilisant `start transaction` et `commit`. {% endhint %} ## Cohérence La cohérence de nos données est quelque chose qui, en partie, peut être assuré par notre SGBDR à travers des **contraintes** et les transactions. Imaginons l'exemple dans lequel Personne Y essaye de transférer plus d'argent de que ce qui est disponible dans son compte. ```sql start transaction; /* Cette fois-ci on commence par ajouter la somme au compte 2 */ select @solde2:=balance from account where client_id=2; set @solde2:=@solde2 + 2500; update account set balance=@solde2 where client_id=2; /* Ensuite soustraire du compte 1 */ select @solde1:=balance from account where client_id=1; set @solde1:=@solde1 - 2500; update account set balance=@solde1 where client_id=1; ERROR 4025 (23000): CONSTRAINT `account.balance` failed for `myfirstdb`.`account` ``` On voit que la dernière ligne produit une erreur ! En effet, à la création de cette table, nous avons ajouté une contrainte que le solde d'un compte ne peut jamais être inférieur à zéro ! Le SGBDR nous a protégés contre une incohérence ! En revanche, à nous de décider comment réagir. À ce moment, si on fait un `select` de notre table, on verra que le solde du compte 2 a était déjà fait : | client\_id | balance | | ---------- | -------- | | 1 | 1900 | | 2 | **7600** | On pourrait manuellement réduire la somme (compliqué !). Mais, la transaction peut nous aider ! Il suffit de réagir à l'erreur en émettant un `rollback` : ```sql rollback; ``` Cette commande annule toutes les opérations depuis `start transaction`. Vérifions, en re-visionnant la table `account` : | client\_id | balance | | ---------- | -------- | | 1 | 1900 | | 2 | **5100** | {% hint style="success" %} La **cohérence** est assurée par nos soins et le SGBDR : Valider la cohérence de nos données est la responsabilité du développeur : * En mettant en place des contraintes * En faisant des tests de cohérence manuels * Parfois notre logique business et trop complexes pour les contraintes possibles avec SQL Et, en réagissant correctement * Notamment en utilisant `rollback` pour la remise à zéro de nos données {% endhint %} La notion de responsabilité partagée et illustrée dans l'image suivante :

Pseudo-code : Gestion de la consistance en dehors du SGBDR

Dans cet exemple, on implémente l'opération dans du code similaire à Javascript, à travers un *driver*, ou librairie, qui envoie des commandes SQL à notre SGBDR. Il y a deux cas de figure : 1. Le SGBDR retourne une erreur, qui est manifesté en tant qu'exception dans notre code. Dans l'exemple gauche, il suffit d'attraper l'exception et renvoyer la commande `rollback`. 2. Dans l'exemple sur le droit, l'erreur est plutôt détectée dans notre code, et pas par le SGBDR même. C'est l'exemple où la logique business est trop compliquée pour les simples contraintes possibles avec SQL. Dans ce cas, on lance une exception manuellement, et ensuite on réagit avec un `rollback`. ## Isolement Aujourd’hui nos bases de données opèrent de façon parallèle ! Plusieurs connexions peuvent se faire en même temps, et plusieurs commandes traitées en parallèle. Et si on modifiait le solde du compte 1 (on dépose de l’argent) pendant notre transaction de transfert d'argent ? Pour simplifier nos tests, on commence par réinitialiser nos deux comptes : ```sql update account set balance=2000 where client_id=1; update account set balance=5000 where client_id=2; ``` Pour simuler des connexions parallèles, nous allons ouvrir **deux clients** SQL. Dans l'invite de commande 1 on commence à taper l'opération de transfert d'avant : ```sql /* A taper dans l'invite de commande 1 */ start transaction; select @solde1:=balance from account where client_id=1; set @solde1:=@solde1 - 100; update account set balance=@solde1 where client_id=1; /* ARRÊTEZ ICI, ET TAPER L'AUTRE OPERATION DANS L'AUTRE INVITE DE COMMANDE */ select @solde2:=balance from account where client_id=2; set @solde2:=@solde2 + 100; update account set balance=@solde2 where client_id=2; commit; ``` Et dans l'invite de commande 2, on tape l'opération de dépose de 5000 € dans le compte : ```sql /* A taper dans l'invite de commande 2 */ start transaction; select @solde1:=balance from account where client_id=1; set @solde1:=@solde1 + 5000; update account set balance=@solde1 where client_id=1; commit; /* RETOURNER A L'INVITE 1 ET COMPLETER L'OPERATION */ ```

Simulez des opérations simultanées dans deux invites de commandes

Vous allez noter que dans le terminal 2, après avoir omis le `update`, l'invite de commande fige. Le SGBDR attend que la transaction qui a déjà agi sur la ligne soit terminé avec `commit` avant de procéder. De cette manière, **l'isolement** a été assuré pas le SGBDR ! Mais, ce n'est pas suffisant ! Regardons notre table : | client\_id | balance | | ---------- | -------- | | 1 | **7000** | | 2 | 5100 | On a un solde de 7000 €, c'est comme si on n'a pas transféré 100 € à la personne B ! Le problème vient de l'implémentation des transactions : * Chaque mise à jour au sein d'une transaction est stocké dans une table *éphémère*, et donc non visible pour toutes les commandes en dehors de la transaction * Ces modifications sont écrites officiellement à la commande `commit`. Dans notre exemple, dans l'invite de commande 2, on a récupéré le solde pas encore touché par l'opération update (parce qu'elle n'a pas été `commit` encore). Donc le solde est erroné ! Mais, on ne peut pas le savoir !

L'isolement ne garantit pas de la cohérence !

En vrai, le problème de base est qu'on a réussi à lire une valeur qui était en plein de modification. Il faut isoler encore plus les données pour dire au SGBDR de mettre en pause toute opération qui peut agir sur une valeur en cours de modification dans une transaction. Heureusement, il est possible de le faire, notamment en ajoutant `for update` à la fin de notre commande `select` : ```sql /* A taper dans l'invite de commande 1 */ start transaction; select @solde1:=balance from account where client_id=1 for update; set @solde1:=@solde1 - 100; update account set balance=@solde1 where client_id=1; ... ``` La clause `for update` signal au SGBDR qu'on a l'intention de modifier cette ligne, et toute opération de lecture en dehors de cette transaction devrait attendre. Réessayons ! ```sql /* A taper dans l'invite de commande 1 */ start transaction; select @solde1:=balance from account where client_id=1 for update; set @solde1:=@solde1 - 100; update account set balance=@solde1 where client_id=1; /* ARRÊTEZ ICI, ET TAPER L'AUTRE OPERATION DANS L'AUTRE INVITE DE COMMANDE */ select @solde2:=balance from account where client_id=2; set @solde2:=@solde2 + 100; update account set balance=@solde2 where client_id=2; commit; ``` Et dans l'invite de commande 2, on tape l'opération de dépose de 5000 € dans le compte : ```sql /* A taper dans l'invite de commande 2 */ start transaction; select @solde1:=balance from account where client_id=1 for update; set @solde1:=@solde1 + 5000; update account set balance=@solde1 where client_id=1; commit; /* RETOURNER A L'INVITE 1 ET COMPLETER L'OPERATION */ ``` Cette fois-ci, on observe que le deuxième invite de commandes fige plutôt à la ligne `select`. En fait, il sait maintenant que la ligne concernée est en train d'être modifié par une autre transaction, et qu'on devrait attendre. Est-ce que les valeurs sont consistantes ? Oui ! | client\_id | balance | | ---------- | ------- | | 1 | 6900 | | 2 | 5100 | ## Deadlocks La clause `for update` à l'effet de **verrouiller** les lignes concernées. En revanche, il faut l'utiliser avec précaution ! On risque de figer toute notre base si on verrouille trop de lignes en même temps, car on créera ce qu'on appelle des **impasses** ou **deadlocks**.

Impasses

Créons un deadlock pour s'amuser ! ```sql /* A taper dans l'invite de commande 1 */ start transaction; select @solde1:=balance from account where client_id=1 for update; set @solde1:=@solde1 - 100; update account set balance=@solde1 where client_id=1; /* ARRÊTEZ ICI, ET TAPER LES OPERATIONs DANS L'AUTRE INVITE DE COMMANDE, JUSQU'A (1) */ select @solde2:=balance from account where client_id=2 for update; /* (2) ARRÊTEZ ICI, ET TAPER CONTINUER OPERATION DANS L'AUTRE INVITE DE COMMANDE */ set @solde2:=@solde2 + 100; update account set balance=@solde2 where client_id=2; commit; ``` Et dans l'invite de commande 2, on tape l'opération de dépose de 5000 € dans le compte : ```sql /* A taper dans l'invite de commande 2 */ start transaction; select @solde2:=balance from account where client_id=2 for update; set @solde2:=@solde2 + 300; update account set balance=@solde2 where client_id=2; /* (1) RETOURNER A L'INVITE 1 ET CONTINUER L'OPERATION JUSQU'A (2) */ select @solde1:=balance from account where client_id=1 for update; set @solde1:=@solde1 + 5000; update account set balance=@solde1 where client_id=1; commit; ```

Impasse : un exemple

On voit que MariaDB nous détecte tout seul l'impasse : ``` ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction ``` Certains SGBDR aura une manière de détecter des impasses (simples). Ou bien, ils imposent un timeout sur les transactions si une impasse est trop complexe à détecter automatiquement (s'il n'y a aucun mouvement sur la transaction pendant X secondes, on abandonne la transaction). La solution est de mieux gérer les opérations pour éviter l'impasse. Dans notre exemple, notre bêtise était de verrouiller les lignes dans le mauvais ordre. Si, dans l'invite de commandes 2, on avait commencé par récupérer le solde 1, on ne serait immédiatement bloqué, et on ne serait pas tombé dans l'impasse. Je vous laisse comme exercice la résolution de l'impasse ! {% hint style="success" %} On n'est jamais à l'abri des impasses, mais on peut toujours essayer de les éviter : * un schéma bien normalisé va nous permettre de plus facilement isoler les lignes * n’acquérez que les locks strictement nécessaires * attention à l’ordre de récupération de locks ! * implémenter un système *backoff* : quand une transaction échoue à cause d’un deadlock, attendez quelques secondes, puis réessayez. {% endhint %} ## Transactions et le DDL Attention, les transactions sont respectées uniquement entre les requêtes type DML: `update`, `insert` et `delete`. Si, pendant une transaction, on rencontre une requête type DDL (`alter`, `create`, `drop` etc.) : * `commit` est implicitement appelé, juste avant l’instruction DDL * une nouvelle transaction est implicitement créée juste après l’instruction DDL Vous perdez alors les propriétés ACID ! À souvenir notamment pour vos migrations ! --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.glassworks.tech/sgbdr/data-manipulation-dml/010-transactions.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.