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.

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

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

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 :

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 :

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 :

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 :

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 :

Maintenant, on va implémenter le transfert d'argent de notre exemple, à l'aide des variables 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 :

On répète la procédure, mais en simulant une panne de courant :

/* 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;

docker compose down

Quand on relance notre Container docker, et on regarde notre table, on verra des valeurs inconsistantes :

On a détruit 100 € ! La seule façon serait de manuellement détecter le problème, et le corriger à la main avec :

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.

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 :

Succès !! La modification qu'on a apportée avant la coupure a été annulée !

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.

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 :

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 :

rollback;

Cette commande annule toutes les opérations depuis start transaction. Vérifions, en re-visionnant la table account :

La notion de responsabilité partagée et illustrée dans l'image suivante :

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 :

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 :

/* 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 :

/* 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 */

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 :

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 !

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 :

/* 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 !

/* 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 :

/* 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 !

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.

Créons un deadlock pour s'amuser !

/* 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 :

/* 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;

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 !

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 !