Une transaction représente une séquence d'opérations traitées comme une unité logique de travail. Ces opérations sont soumises au système collectivement : soit toutes réussissent, soit aucune n'est appliquée. Une transaction est une unité logique de travail indivisible.

Simuler un virement bancaire

Considérons une table banque pour simuler des opérations bancaires :

CREATE TABLE banque (
    id NUMBER(6) PRIMARY KEY,
    solde NUMBER(6) CHECK(solde >= 0)
);

INSERT INTO banque (id, solde) VALUES (1, 100);
INSERT INTO banque (id, solde) VALUES (2, 100);

Un virement de 50 de la banque 1 vers la banque 2 impliquerait deux instructions SQL distinctes :

-- Tentative de virement
UPDATE banque SET solde = solde - 50 WHERE id = 1;
UPDATE banque SET solde = solde + 50 WHERE id = 2;

Un problème survient si la première mise à jour échoue (par exemple, si le solde devient négatif, violant la contrainte), tandis que la seconde réussit. Dans ce scénario, les données deviennent incohérentes.

Résolution avec les transactions

Les transactions garantissent que l'ensemble des opérations est exécuté avec succès ou annulé. Voici comment implémenter le virement avec une transaction :

BEGIN
    -- Débit du compte source
    UPDATE banque SET solde = solde - 50 WHERE id = 1;

    -- Crédit du compte destination
    UPDATE banque SET solde = solde + 50 WHERE id = 2;

    -- Valider la transaction si tout s'est bien passé
    COMMIT;
EXCEPTION
    -- Annuler la transaction en cas d'erreur
    WHEN OTHERS THEN
        ROLLBACK;
END;
/

Les quatre propriétés ACID des transactions

• Atomicité : Toutes les opérations d'une transaction réussissent, ou aucune ne réussit.
• Cohérence : Une transaction amène la base de données d'un état valide à un autre état valide. Les invariants sont préservés.
• Isolation : Les transactions concurrentes n'interfèrent pas les unes avec les autres. Chaque transaction semble s'exécuter séquentiellement.
• Durabilité : Une fois qu'une transaction est validée (commmit), ses modifications sont permanentes, même en cas de défaillance du système.

Cycle de vie d'une transaction

Le comportement de soumission des transactions varie selon les systèmes de gestion de base de données (SGBD) :

• MySQL : Soumission automatique par défaut (chaque instruction DML est soumise individuellement).
• Oracle : Soumission manuelle requise.

Une transaction commence avec la première instruction DML (Data Manipulation Language - INSERT, UPDATE, DELETE) après la fin de la transaction précédente. Elle se termine par une validation (COMMIT) ou une annulation (ROLLBACK).

Validation (COMMIT)

• Explicite : COMMIT;
• Implicite : Sortie normale de la session (EXIT), commandes DCL (GRANT, REVOKE), commandes DDL (CREATE, DROP).

Annulation (ROLLBACK)

• Explicite : ROLLBACK;
• Implicite : Arrêt anormal du système (panne de courant, crash).

Points de sauvegarde (SAVEPOINT)

Les points de sauvegarde permettent d'annuler partiellement une transaction. Vous pouvez définir un point de sauvegarde, effectuer d'autres opérations, puis revenir à ce point de sauvegarde sans annuler toute la transaction.

-- Création d'une table d'exemple
CREATE TABLE exemples (
    id NUMBER(10) PRIMARY KEY,
    nom VARCHAR2(10)
);

INSERT INTO exemples (id, nom) VALUES (1, 'Alice');
INSERT INTO exemples (id, nom) VALUES (2, 'Bob');

-- Définir un point de sauvegarde
SAVEPOINT debut_insertion;

INSERT INTO exemples (id, nom) VALUES (3, 'Charlie');

-- Afficher toutes les données actuelles
SELECT * FROM exemples;

-- Revenir au point de sauvegarde 'debut_insertion'
ROLLBACK TO SAVEPOINT debut_insertion;

-- Afficher à nouveau pour voir le résultat de l'annulation partielle
SELECT * FROM exemples;

Niveaux d'isolation des transactions

Les transactions concurrentes peuvent entraîner des problèmes :

• Lecture sale (Dirty Read) : Une transaction lit des données modifiées par une autre transaction qui n'a pas encore été validée et pourrait être annulée.
• Lecture non répétable (Non-Repeatable Read) : Une transaction lit plusieurs fois la même ligne et obtient des valeurs différentes car une autre transaction a modifié et validé la ligne entre les lectures.
• Lecture fantôme (Phantóm Read) : Une transaction lit un ensemble de lignes, puis une autre transaction insère ou supprime des lignes qui correspondent aux critères de recherche. La transaction initiale, relue, renvoie un ensemble de lignes différent.

Différence entre Lecture non répétable et Lecture fantôme :

• Lecture non répétable concerne les modifications apportées à une ligne existante (souvent via UPDATE).
• Lecture fantôme concerne l'apparition ou la disparition de lignes (souvent via INSERT ou DELETE).

Les niveaux d'isolation SQL99, par ordre croissant de protection contre les problèmes de concurrecne (et de diminution des performances) :

1. Read Uncommitted : Permet les lectures sales, non répétables et fantômes.
2. Read Committed : Empêche les lectures sales. Oracle utilise ce niveau par défaut.
3. Repeatable Read : Empêche les lectures sales et non répétables. Peut encore permettre les lectures fantômes.
4. Serializable : Empêche tous les problèmes mentionnés (lectures sales, non répétables, fantômes). Chaque transaction s'exécute comme si elle était la seule à s'exécuter. Les performances peuvent être réduites. Oracle supporte également ce niveau.

Changement du niveau d'isolation :

Assurez-vous de valider les transactions précédentes avant de changer le niveau d'isolation.

-- Valider la transaction en cours pour terminer les opérations précédentes
COMMIT;

-- Définir le niveau d'isolation pour les transactions suivantes (exemple pour Oracle)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Ou pour une lecture seule (souvent une forme de Serializable)
-- SET TRANSACTION READ ONLY;

-- Pour MySQL, tous les niveaux sont supportés :
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Opérations typiques de transaction

1. Démarrage : Automatique avec la première instrcution DML après la fin de la transaction précédente.
2. Validation : COMMIT; pour rendre les changements permanents.
3. Annulation : ROLLBACK; pour annuler les changements depuis le début de la transaction ou depuis un point de sauvegarde.

Exemple d'annulation :

-- Insérer des données
INSERT INTO banque (id, solde) VALUES (3, 100);
INSERT INTO banque (id, solde) VALUES (4, 100);
INSERT INTO banque (id, solde) VALUES (5, 100);

-- Annuler toutes les insertions
ROLLBACK;

-- Vérifier que les enregistrements n'ont pas été ajoutés
SELECT * FROM banque WHERE id IN (3, 4, 5);

Exemple : Gestion des dossiers d'étudiants

Scénario : Archiver et supprimer les résultats d'examens pour les étudiants d'un certain niveau ("Argent") avant de finaliser leur dossier.

1. Vérifier les données à archiver :

-- Supposons que les 'subjectid' 5 à 10 correspondent au niveau "Argent"
SELECT * FROM result WHERE subjectid BETWEEN 5 AND 10;

2. Créer la table d'historique et y insérer les données :

CREATE TABLE historyResult (
    Id NUMBER(4) PRIMARY KEY,
    StudentNo VARCHAR2(50) NOT NULL,
    SubjectId NUMBER(2) NOT NULL,
    StudentResult NUMBER(4) NULL,
    ExamDate DATE NOT NULL
);

-- Insérer les résultats à archiver
INSERT INTO historyResult (Id, StudentNo, SubjectId, StudentResult, ExamDate)
SELECT Id, StudentNo, SubjectId, StudentResult, ExamDate
FROM result
WHERE subjectid BETWEEN 5 AND 10;

-- Valider l'archivage
COMMIT;

-- Vérifier les données archivées
SELECT * FROM historyResult;

3. Supprimer les résultats originaux après confirmation :

-- Supprimer les résultats du niveau "Argent" de la table principale
DELETE FROM result
WHERE subjectid BETWEEN 5 AND 10;

-- Valider la suppression
COMMIT;

-- Vérifier que les données ont été supprimées de la table principale
SELECT * FROM result WHERE subjectid BETWEEN 5 AND 10;