Transaction Control Language (TCL)

Transaction Control Language (TCL)

Le langage de transaction de données (TCL) est le sous-langage responsable de la gestion des transactions dans une base de données.

Définition

Une transaction est une séquence d’opérations qui doit être exécutée dans son intégralité ou pas du tout.

Une transaction est un ensemble de requêtes qui sont exécutées par blocs.

Si une des requêtes du bloc échoue, on peut décider d’annuler tout le bloc de requêtes (ou de quand même valider les requêtes qui ont réussies).

Les transactions sont utilisées pour garantir l’intégrité des données lorsque l’on veut effectuer des modifications importantes.

Par exemple, lorsque vous transférez de l’argent d’un compte bancaire à un autre, l’argent doit quitter votre compte et doit être ajouté au compte tiers. Vous ne pouvez pas dire que la transaction est terminée sans que les deux étapes soient effectuées.

Vocabulaire

Lorsque l’on valide les requêtes d’une transaction, on dit aussi que l’on commit les changements.

Lorsque l’on annule les requêtes d’une transaction, on dit aussi que l’on rollback les changements.

Auto-commit vs Transactions

Le mode autocommit est le mode par défaut dans la plupart des SGBD-R, chaque requête est exécutée comme une transaction indépendante.

Le mode autocommit implique que toute requête sans erreur sera exécutée et il ne sera pas possible de revenir en arrière

Il est tout de même possible de démarrer une transaction contenant plusieurs requêtes lorsque le SGBD est configuré en mode autocommit avec les instructions START TRANSACTION; ou BEGIN;

ACID vs BASE

ACID et BASE sont des modèles de transactions de base de données qui déterminent la manière dont une base de données organise et manipule les données.

• ACID : Atomicité, Cohérence, Isolation, Durabilité : concerne plutot les bases de données relationnelles
• BASE : Basically Available, Soft state, Eventually consistent : concerne plutot les bases de données NoSQL

Les bases de données ACID donnent la priorité à la cohérence plutôt qu’à la disponibilité : l’ensemble de la transaction échoue si une erreur survient à n’importe quelle étape de la transaction.

Les bases de données BASE donnent la priorité à la disponibilité plutôt qu’à la cohérence. Au lieu de faire échouer la transaction, il est possible que les utilisateurs accèdent temporairement à des données incohérentes. La cohérence des données sera éventuellement atteinte, mais pas immédiatement.

Les propriétés ACID

Les transactions doivent respecter les propriétés ACID :

• Atomicité : Une transaction est une opération unique qui doit être exécutée dans son intégralité ou pas du tout.
• Cohérence : Une transaction doit laisser la base de données dans un état cohérent.
• Isolation : Les transactions doivent être isolées les unes des autres.
• Durabilité : Les modifications apportées par une transaction doivent être persistantes.

Les commandes du TCL

Les commandes TCL (Transaction Control Language) permettent de gérer les transactions dans une base de données.

Les commandes les plus courantes sont :

• START TRANSACTION ou BEGIN : Démarre une transaction
• COMMIT : Valide une transaction
• ROLLBACK : Annule une transaction
• SAVEPOINT : Crée un point de sauvegarde dans une transaction

Exemple de transaction

BEGIN TRANSACTION;

UPDATE Comptes SET solde = solde - 100 WHERE id = 1;
UPDATE Comptes SET solde = solde + 100 WHERE id = 2;

COMMIT;

Dans cet exemple, si la deuxième mise à jour échoue, la transaction entière sera annulée et les données seront restaurées dans leur état initial.

Jalons (Savepoints)

Les jalons (savepoints) permettent de créer des points de sauvegarde dans une transaction.

Cela permet de revenir à un état antérieur de la transaction en cas d’erreur.

Exemple de transaction avec jalon

BEGIN TRANSACTION;

UPDATE Comptes SET solde = solde - 100 WHERE id = 1;

SAVEPOINT point1;

UPDATE Comptes SET solde = solde + 100 WHERE id = 2;

ROLLBACK TO SAVEPOINT point1;

COMMIT;

Dans cet exemple, si la deuxième mise à jour échoue, on revient à l’état de la transaction avant la création du jalon point1.

Conclusion

Le langage de transaction de données (TCL) permet de gérer les transactions dans une base de données.

Les transactions sont utilisées pour garantir l’intégrité des données et respecter les propriétés ACID.

Les commandes TCL les plus courantes sont START TRANSACTION, COMMIT, ROLLBACK et SAVEPOINT.

Exercice

Soit la table Comptes suivante :

CREATE TABLE Comptes (
    id INT PRIMARY KEY,
    solde INT NOT NULL DEFAULT 0 CHECK (solde >= 0)
);

INSERT INTO Comptes (id, solde) VALUES (1, 1000);
INSERT INTO Comptes (id, solde) VALUES (2, 500);
INSERT INTO Comptes (id, solde) VALUES (3, 100);
INSERT INTO Comptes (id, solde) VALUES (4, 300);

Rédiger une transaction SQL avec un jalon entre les deux mises à jour suivantes :

1. Transfert de 100€ du compte 1 au compte 2.
2. Transfert de 150€ du compte 3 au compte 4.

Quel sera le solde des comptes après l’exécution de la transaction ? Pourquoi ?

Solution

BEGIN TRANSACTION;

UPDATE Comptes SET solde = solde - 100 WHERE id = 1;
UPDATE Comptes SET solde = solde + 100 WHERE id = 2;

SAVEPOINT point1;

UPDATE Comptes SET solde = solde - 150 WHERE id = 3;
UPDATE Comptes SET solde = solde + 150 WHERE id = 4;

SAVEPOINT point2;

COMMIT;

La table Comptes sera mise à jour comme suit :

Comptes
id	solde
1	900
2	600
3	100
4	300

La deuxième mise à jour va échouer et on reviendra à l’état de la transaction avant la création du jalon point2 (soit au jalon point1).