Cours : Les transactions en SQL (MySQL)

Objectifs

• Comprendre à quoi servent les transactions dans une base de données.
• Savoir comment elles fonctionnent : BEGIN, COMMIT, ROLLBACK.
• Identifier les problèmes de cohérence que les transactions permettent d’éviter.

1. Qu’est-ce qu’une transaction ?

Une transaction est un ensemble d’opérations SQL qui doivent être exécutées ensemble ou pas du tout.

Principe : soit toutes les opérations réussissent, soit la base revient à son état initial.

-- Exemple : transfert d’argent entre deux comptes
UPDATE compte SET solde = solde - 100 WHERE id = 1;
UPDATE compte SET solde = solde + 100 WHERE id = 2;

Si la première requête réussit mais pas la deuxième (panne, erreur…), la base devient incohérente (100 € ont disparu). Les transactions évitent ce type de problème.

⚙️ 2. Principe de fonctionnement

Une transaction regroupe plusieurs requêtes SQL dans un bloc contrôlé :

START TRANSACTION;  -- ou BEGIN;
  -- Requêtes SQL
COMMIT;             -- Validation définitive

Si une erreur survient, on peut tout annuler :

ROLLBACK;  -- Annule toutes les modifications depuis BEGIN

Cycle d’une transaction :

3. Le principe ACID

Les transactions reposent sur quatre propriétés fondamentales :

4. Exemple concret en MySQL

Exemple : un emprunt et la mise à jour de disponibilité d’un livre doivent être faits ensemble.

START TRANSACTION;

INSERT INTO emprunt (id_adherent, id_livre, date_emprunt)
VALUES (1, 3, CURRENT_DATE());

UPDATE livre SET disponible = FALSE WHERE id = 3;

COMMIT;

Si une erreur se produit avant le COMMIT, on peut annuler :

ROLLBACK;

5. Exemple avec erreur

START TRANSACTION;

UPDATE compte SET solde = solde - 500 WHERE id = 1;
UPDATE compte SET solde = solde + 500 WHERE id = 99; -- id inexistant !

ROLLBACK; -- On annule tout

➡️ Sans transaction, la première requête aurait été enregistrée, rendant la base incohérente.
➡️ Avec transaction, tout est annulé.

6. Utilisation en PHP (PDO)

<?php
try {
  $pdo = new PDO('mysql:host=localhost;dbname=biblio_tp;charset=utf8mb4', 'root', '');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $pdo->beginTransaction(); // Démarre la transaction

  $pdo->exec("INSERT INTO emprunt (id_adherent, id_livre) VALUES (1, 2)");
  $pdo->exec("UPDATE livre SET disponible = FALSE WHERE id = 2");

  $pdo->commit(); // Valide toutes les requêtes
  echo "Transaction validée avec succès.";
} catch (Exception $e) {
  $pdo->rollBack(); // Annule tout si erreur
  echo "Erreur : transaction annulée → " . htmlspecialchars($e->getMessage());
}
?>

7. Isolation des transactions

Quand plusieurs utilisateurs accèdent en même temps à la base, MySQL doit éviter les interférences.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

8. A retenir

• Une transaction = plusieurs requêtes indivisibles.
• On contrôle explicitement avec BEGIN, COMMIT, ROLLBACK.
• En cas d’erreur → ROLLBACK.
• Les transactions garantissent la cohérence et la fiabilité des données.
• Elles suivent les règles ACID.

9. Important

• Les transactions ne fonctionnent qu’avec les tables InnoDB (pas MyISAM).
• On peut combiner transactions, triggers et procédures pour créer des systèmes robustes.
• Dans les applications, toujours prévoir un bloc try/catch pour gérer les erreurs SQL.

Contexte

Vous travaillez sur une application de gestion de bibliothèque. Lorsqu’un lecteur emprunte un livre :

1. Un enregistrement est ajouté dans la table emprunt.
2. Le livre devient indisponible (disponible = FALSE).

Ces deux opérations doivent être indivisibles : si l’une échoue, l’autre doit être annulée. C’est le rôle des transactions.

Base de données de départ (vous devez déjà l’avoir normalement !)

CREATE DATABASE IF NOT EXISTS biblio_tp;
USE biblio_tp;

CREATE TABLE IF NOT EXISTS livre (
  id INT AUTO_INCREMENT PRIMARY KEY,
  titre VARCHAR(100) NOT NULL,
  disponible BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS adherent (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(50),
  prenom VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS emprunt (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_adherent INT,
  id_livre INT,
  date_emprunt DATE DEFAULT (CURRENT_DATE()),
  FOREIGN KEY (id_adherent) REFERENCES adherent(id),
  FOREIGN KEY (id_livre) REFERENCES livre(id)
);

INSERT INTO livre (titre) VALUES 
('Dune'), 
('1984'),
('Le Meilleur des mondes');

INSERT INTO adherent (nom, prenom) VALUES 
('Martin', 'Paul'),
('Durand', 'Alice');

Étape 1 – Création d’une classe de connexion POO

Commencez par une classe BiblioDB qui établit la connexion à MySQL de manière sécurisée.

<?php
class BiblioDB {
  private PDO $pdo;

  public function __construct() {
    try {
      $this->pdo = new PDO(
        'mysql:host=localhost;dbname=biblio_tp;charset=utf8mb4',
        'root', '', // identifiants à adapter
        [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
      );
    } catch (PDOException $e) {
      die("<p style='color:red'>Erreur de connexion : " . htmlspecialchars($e->getMessage()) . "</p>");
    }
  }

  /* ---------- MÉTHODES MÉTIER ---------- */

  // Version sans transaction
  public function emprunterSansTransaction(int $idAdherent, int $idLivre): void {
    try {
      // Étape 1 : insertion dans emprunt
      $this->pdo->exec("INSERT INTO emprunt (id_adherent, id_livre) VALUES ($idAdherent, $idLivre)");

      // Étape 2 : erreur volontaire (colonne inexistante)
      $this->pdo->exec("UPDATE livre SET disponiblex = FALSE WHERE id = $idLivre");

      echo "<p style='color:green'>Emprunt effectué sans transaction.</p>";
    } catch (PDOException $e) {
      echo "<p style='color:red'>Erreur SQL : " . htmlspecialchars($e->getMessage()) . "</p>";
    }
  }

  // Version avec transaction
  public function emprunterAvecTransaction(int $idAdherent, int $idLivre): void {
    try {
      $this->pdo->beginTransaction(); // Démarre la transaction

      $this->pdo->exec("INSERT INTO emprunt (id_adherent, id_livre) VALUES ($idAdherent, $idLivre)");
      // Erreur volontaire à nouveau
      $this->pdo->exec("UPDATE livre SET disponiblex = FALSE WHERE id = $idLivre");

      $this->pdo->commit(); // Valide tout
      echo "<p style='color:green'>Transaction validée.</p>";
    } catch (PDOException $e) {
      $this->pdo->rollBack(); // Annule tout
      echo "<p style='color:red'>Erreur, transaction annulée : " . htmlspecialchars($e->getMessage()) . "</p>";
    }
  }
}

// ----------- TEST DU PROGRAMME -----------
$db = new BiblioDB();

// 1) Sans transaction
$db->emprunterSansTransaction(1, 1);

// 2) Avec transaction
$db->emprunterAvecTransaction(2, 2);
?>

Observez le résultat dans phpMyAdmin après exécution :

• Dans la première méthode, le livre est ajouté dans emprunt malgré l’erreur : incohérence.
• Dans la seconde, tout est annulé : cohérence préservée.

Étape 2 – Corrigez la requête et validez la transaction

Corrigez la ligne fautive :

$this->pdo->exec("UPDATE livre SET disponible = FALSE WHERE id = $idLivre");

Relancez le test : cette fois, la transaction se valide et le livre devient indisponible.

Étape 3 – À vous de jouer

Créez une méthode rendreLivre(int $idLivre) qui :

1. Démarre une transaction.
2. Met à jour emprunt.date_retour (si présent).
3. Rend le livre à nouveau disponible (disponible = TRUE).
4. Annule tout en cas d’erreur.

Testez votre méthode en introduisant une erreur (ex. mauvaise colonne) puis en la corrigeant.

Questions de réflexion

1. Pourquoi la méthode sans transaction crée-t-elle une incohérence dans la base ?
2. Que se passe-t-il si l’erreur survient après un COMMIT ?
3. Quels autres cas d’usage nécessitent l’utilisation de transactions (ex. paiement, virement bancaire) ?

Bilan

• Une transaction regroupe plusieurs opérations SQL en une unité logique indivisible.
• COMMIT valide tout, ROLLBACK annule tout.
• En PHP, on utilise beginTransaction(), commit(), et rollBack().
• En POO, la connexion et la gestion des erreurs doivent être centralisées dans le constructeur via un try/catch.

Ce TP illustre l’importance d’intégrer la gestion transactionnelle dès la conception d’une application métier.

Cours : Les verrous (Locks) en SQL / MySQL

Objectifs

• Comprendre ce qu’est un verrou dans une base de données relationnelle.
• Voir comment les verrous garantissent la cohérence et l’isolation des transactions.
• Savoir utiliser les commandes MySQL pour verrouiller et libérer des lignes ou tables.

1. Définition

Un verrou (ou lock) est un mécanisme qui empêche plusieurs transactions de modifier simultanément les mêmes données.

But : éviter les incohérences quand plusieurs utilisateurs accèdent ou modifient les mêmes enregistrements en même temps.

Les verrous fonctionnent main dans la main avec les transactions : ils assurent la propriété d’isolation du modèle ACID.

2. Pourquoi les verrous sont-ils nécessaires ?

Sans verrouillage, deux utilisateurs pourraient modifier la même donnée en même temps, entraînant des résultats incohérents ou perdus.

Exemple :
- Utilisateur A lit le solde du compte (100 €)
- Utilisateur B lit le même solde (100 €)
- A retire 30 €, B retire 50 €
→ Résultat final sans verrou : 100 - 50 = 50 €
→ Résultat attendu : 20 €

Les verrous empêchent ces conflits en forçant les transactions à attendre leur tour.

3. Types de verrous dans MySQL

3.1. Verrous implicites (automatiques)

MySQL (InnoDB) applique automatiquement des verrous quand une transaction lit ou modifie des données.

• Shared lock (verrou partagé) : autorise la lecture, mais bloque l’écriture.
• Exclusive lock (verrou exclusif) : bloque lecture et écriture par d’autres transactions.

Exemples implicites :

-- Verrou exclusif automatique :
UPDATE compte SET solde = solde - 100 WHERE id = 1;

-- Verrou partagé automatique :
SELECT * FROM compte WHERE id = 1 FOR SHARE;

3.2. Verrous explicites (manuels)

Le développeur peut verrouiller manuellement des lignes ou tables pour contrôler précisément l’accès concurrent.

a) Verrouillage d’une table

LOCK TABLES compte WRITE;
-- Aucune autre session ne peut lire ni écrire dans cette table.

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

UNLOCK TABLES; -- Libère tous les verrous manuels

b) Verrouillage d’enregistrements (row-level)

START TRANSACTION;
SELECT * FROM compte WHERE id = 1 FOR UPDATE;  -- Verrou exclusif sur cette ligne
-- Autres transactions devront attendre la fin (COMMIT/ROLLBACK)
UPDATE compte SET solde = solde - 50 WHERE id = 1;
COMMIT;

FOR UPDATE crée un verrou exclusif, tandis que FOR SHARE crée un verrou partagé.

4. Granularité des verrous

Les verrous peuvent s’appliquer à différents niveaux :

• Ligne (row-level) : le plus courant dans InnoDB, plus efficace pour la concurrence.
• Page : groupe de plusieurs lignes (rarement utilisé directement).
• Table : utilisé dans MyISAM ou via LOCK TABLES.

InnoDB privilégie les verrous de ligne, ce qui permet à plusieurs utilisateurs de travailler sur des enregistrements différents en parallèle.

5. Exemple complet de verrouillage concurrent

Session 1 :

START TRANSACTION;
SELECT * FROM compte WHERE id = 1 FOR UPDATE; -- Verrou exclusif
-- Modification en attente
UPDATE compte SET solde = solde - 50 WHERE id = 1;

Session 2 (autre utilisateur) :

START TRANSACTION;
SELECT * FROM compte WHERE id = 1 FOR UPDATE; -- ❌ Bloquée tant que la transaction 1 n’est pas terminée

Quand la session 1 fait COMMIT; ou ROLLBACK;, la session 2 est libérée et reprend l’exécution.

6. Les problèmes de verrouillage

a) Deadlock (interblocage)

Deux transactions se bloquent mutuellement : chacune attend un verrou détenu par l’autre.

Transaction 1 verrouille A, attend B
Transaction 2 verrouille B, attend A → blocage

MySQL détecte automatiquement les deadlocks et annule une des transactions :

ERROR 1213 (40001): Deadlock found when trying to get lock

Solution : toujours verrouiller les ressources dans le même ordre logique.

b) Verrouillage trop large

Utiliser LOCK TABLES bloque toute la table → perte de performance.
Préférer les verrous de ligne (FOR UPDATE).

7. Exemple en PHP avec PDO

<?php
try {
  $pdo = new PDO('mysql:host=localhost;dbname=biblio_tp;charset=utf8mb4', 'root', '');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $pdo->beginTransaction();

  // Verrou exclusif sur le livre 1
  $stmt = $pdo->query("SELECT * FROM livre WHERE id = 1 FOR UPDATE");
  $livre = $stmt->fetch();

  if (!$livre['disponible']) {
    throw new Exception("Livre déjà réservé !");
  }

  $pdo->exec("UPDATE livre SET disponible = FALSE WHERE id = 1");

  $pdo->commit();
  echo "<p style='color:green'>Livre réservé avec succès !</p>";
} catch (Exception $e) {
  $pdo->rollBack();
  echo "<p style='color:red'>Erreur : " . htmlspecialchars($e->getMessage()) . "</p>";
}
?>

Ici, le FOR UPDATE empêche un autre utilisateur de réserver le même livre simultanément.

8. Observation des verrous actifs

Pour voir quels verrous sont en cours dans InnoDB :

SHOW ENGINE INNODB STATUS\G

On peut aussi consulter la table système :

SELECT * FROM performance_schema.data_locks;

9. Points clés à retenir

• Les verrous empêchent les accès concurrents conflictuels.
• Ils garantissent la cohérence des transactions (isolation).
• FOR UPDATE → verrou exclusif, FOR SHARE → verrou partagé.
• Éviter de verrouiller trop large pour ne pas bloquer d’autres utilisateurs.
• InnoDB gère automatiquement la plupart des verrous (niveau ligne).
• Attention aux deadlocks : verrouiller toujours dans le même ordre.