Transactions

Une transaction est une série d'actions (requêtes) indivisibles permettant de passer d'un état cohérent de la base à un autre état cohérent de la base.

Propriétés ACID

Cycle de vie d'une transaction :

ROLLBACK; annule toutes les données de la transaction sauf tout ce qui est modification du schéma de la base.

Sous MySQL les transactions ne sont possibles que sur les tables en InnoDB.

Isolation

Il y a 4 niveaux d'isolation :

  1. serializable est le niveau maximal d'isolation. Si deux transactions à ce niveau se lancent en même temps le résultat sera équivalent à lancer les transactions l'une après l'autre.
  2. reapatable read (lecture reproductible) permet juste que la transaction t2 en parallèle de t1 renvoie les mêmes valeurs
  3. read committed permet de voir les différences dès qu'une transaction est finie
  4. read uncommitted est un niveau équivalent à n'avoir aucune isolation

Plus les niveaux sont hauts plus les verrous sont hauts mais plus les performances sont basses. Le niveau d'isolation par défaut est reapatable read.

Pour afficher le niveau d'isolation :

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.01 sec)

Pour modifier la valeur courante d'isolation : SET TRANSACTION ISOLATION LEVEL niveauniveau est une valeur prise parmis SERIALIZABLE, REPEAT READ, READ COMMITTED ou READ UNCOMMITTED.

Erreurs

Trois types d'erreurs :

Lectures impropres Lectures non reproductibles Lectures fantômes
Serializable NonNonNon
Reapatable read NonNonOui
Read committed NonOuiOui
Read uncommitted OuiOuiOui

Verrous

Mais le principe d'isolation pose un nouveau problème si on édite une des valeurs alors qu'une transaction a déjà édité une valeur (ex. des réservations de places).

Dès qu'il y a une modification dans une transaction un verrou est pausé avec un timeout. Toutes les modifications futures resteront en pause en attendant la libération du verrou.

Interblocage (Deadlock)

Il peut arriver que des opérations s'attendent mutuellement, et donc là c'est le drame, c'est ce qu'on appelle l'interblocage. Pour résoudre ces problèmes il y a deux méthodes.

À priori

On pose des règles de priorité sur les transactions. Les transactions avec la priorité la plus basse se suicident. La règle de priorité peut être le temps (la plus récente, la plus agée, ...) ou des priorités données selon l'importance de la requète.

= suicide de transactions = estampillage

À posteriori

L'administrateur de la base va devoir intervenir manuellement pour assassiner les transactions.

= assassinat de transactions

Save points

On ne peut pas imbriquer des transactions. Néamoins on peut mettre des points intermédiaires dans des grandes transactions.

START TRANSACTION;
...
SAVEPOINT nom;
...
ROLLBACK TO SAVEPOINT nom;
...
COMMIT;

Le ROLLBACK TO SAVEPOINT revient au niveau du SAVEPOINT mais ne termine pas toute la transaction et laisse les verrous toujours en place.

Autocommit

Par défaut MySQL commit automatiquement les transactions seules. En mode SET AUTOCOMMIT = 0 toutes les requêtes sont des transactions, il faut donc COMMIT; après toutes les transactions. Pour voir le niveau d'autocommit :

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

Exemples SQL

CREATE TABLE Compte (
	idCompte INT PRIMARY KEY auto_increment,
	idClient INT NOT NULL,
	solde REAL NOT NULL,
	INDEX (idClient)
) engine=InnoDB;
CREATE TABLE Operation (
	idOperation INT PRIMARY KEY auto_increment,
	idCompte INT NOT NULL REFERENCES Compte(idCompte),
	libelle VARCHAR(255),
	montant REAL NOT NULL,
	INDEX (idCompte)
) engine=InnoDB;
INSERT INTO Compte (idCompte, idClient, solde) VALUES (1, 5, 1500.10);
INSERT INTO Compte (idCompte, idClient, solde) VALUES (2, 5, 100.50);

# Virement de 100 euros du Compte 1 vers Compte 2

START TRANSACTION;
UPDATE Compte SET solde = solde + 100 WHERE idCompte = 1;
	# À ce moment là sans la transaction l'état de la base serait incohérent
UPDATE Compte SET solde = (solde - 100) WHERE idCompte = 2;
INSERT INTO Operation (idCompte, libelle, montant) VALUES (1, 'Virement', -100);
INSERT INTO Operation (idCompte, libelle, montant) VALUES (2, 'Virement', 100);
COMMIT;