TP 3.04 — Systèmes transactionnels

Commit et rollback sous psql, ACID en pratique, verrouillage entre deux sessions, Transac1 et la course au dernier article — avec les deux solutions et les sorties réelles des programmes.

Objectif du TP : comprendre l'intérêt et le fonctionnement d'un système transactionnel — l'usage de begin transaction, commit et rollback, les niveaux d'isolation, et les verrous explicites — en le mettant les mains dans le cambouis : deux sessions psql en parallèle, puis des programmes JDBC qui font (ou évitent) des catastrophes.

Numérotation

Sur le sujet distribué en cours, ce TP s'appelle « TP 3.05 — Systèmes Transactionnels ». Ici il porte le numéro 3.04, dans la continuité des TP 3.01 à 3.03 de la ressource. Le contenu est inchangé.

Avant de commencer

par défaut, le client psql est en mode AUTOCOMMIT : chaque ordre est automatiquement exécuté et enregistré durablement ;
le niveau d'isolation par défaut est READ COMMITTED ;
le prompt de psql indique l'état : base=* pendant une transaction active, base=! quand elle contient une erreur (que l'on termine alors par un rollback) ;
la table du TP :

create table comptes (pers text, solde int);

1. Commit et Rollback manuels

1. Une transaction qui n'aboutit pas… un rollback implicite. On veut insérer dupont, mais une erreur se glisse dans l'ordre SQL — regardez bien ce qui se passe :

$ psql -d but2
BEGIN TRANSACTION;
INSERT 0 1
SELECT * FROM comptes;
  pers  | solde
--------+-------
 dupont |   100
(1 row)
INSERT INTO comptes value('dupont',100);
ERROR:  syntax error at or near "value"
END;
ROLLBACK
SELECT * FROM comptes;
  pers | solde
-------+-------
(0 rows)

Le SELECT intermédiaire montrait bien dupont… mais END (ou COMMIT) sur une transaction avortée se comporte comme un ROLLBACK : tout est annulé, y compris le premier ordre. D'ailleurs, après l'erreur, plus aucun ordre n'est accepté jusqu'à la fin du bloc :

INSERT INTO comptes value('aborted_demo',2);
ERROR:  syntax error at or near "value"
SELECT * FROM comptes;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

2. Une transaction qui n'aboutit pas… un rollback explicite. Cette fois aucun doute :

BEGIN TRANSACTION;
INSERT 0 1
SELECT * FROM comptes;   -- montre dupont | 100  (1 row)
ROLLBACK;
SELECT * FROM comptes;   -- 0 row… dupont n'a jamais existé

3. Une transaction qui est validée. Même scénario, conclusion différente :

BEGIN TRANSACTION;
INSERT 0 1
SELECT * FROM comptes;   -- montre dupont | 100  (1 row)
COMMIT;
SELECT * FROM comptes;
  pers  | solde
--------+-------
 dupont |   100
(1 row)

La différence entre les cas 2 et 3 ne tient qu'au dernier ordre : ROLLBACK efface, COMMIT rend définitif. C'est toute l'atomicité d'une transaction : soit tout, soit rien.

2. A.C.I.D. en pratique

ACID — les quatre garanties attendues d'un « bon » SGBD. Les trois premières se jouent pour une transaction seule ; l'isolation devient la question centrale dès que plusieurs transactions s'exécutent en même temps. PostgreSQL s'appuie sur le journal (WAL : Write-Ahead Logging).

Atomicité — tout est exécuté, ou rien ne l'est

On tente d'insérer deux lignes en une transaction, la seconde contient une erreur de donnée (le texte 'paul' dans la colonne entière solde) :

BEGIN TRANSACTION;
INSERT INTO comptes VALUES('duchemin',40);
SELECT * FROM comptes;
   pers   | solde
----------+-------
 dupont   |   100
 duchemin |    40
(2 rows)
INSERT INTO comptes VALUES('durand','paul');
ERROR:  invalid input syntax for type integer: "paul"
COMMIT;      -- (transaction avortée : se comporte en ROLLBACK)
SELECT * FROM comptes;
  pers  | solde
--------+-------
 dupont |   100
(1 row)

Même le premier ordre n'est pas conservé ! Et sans erreur, les deux lignes entrent correctement :

BEGIN TRANSACTION;
INSERT INTO comptes VALUES('duchemin',40);
INSERT INTO comptes VALUES('durand',500);
COMMIT;
SELECT * FROM comptes;
 dupont   |   100
 duchemin |    40
 durand   |   500
(3 rows)

Cohérence — toutes les contraintes doivent être respectées

C'est le 'paul' ci-dessus (contrainte de type), mais c'est vrai de toutes : clé primaire, UNIQUE, NOT NULL, CHECK… Une contrainte violée empêche la validation de la transaction — c'est exactement ce que nous verrons échouer « trop tard » en section 6.

Isolation — rien n'est visible des autres sessions tant que tout n'est pas terminé

Ouvrez deux fenêtres de terminal et lancez psql dans chacune. Session 1 insère leclercq, mais ne valide pas tout de suite :

-- session 1
BEGIN TRANSACTION;
INSERT 0 1              -- leclercq, 200 € — pas encore validé

-- session 2, pendant ce temps
SELECT * FROM comptes WHERE pers = 'leclercq';
 pers | solde
------+-------
(0 rows)                -- invisible pour les autres !

-- session 1
COMMIT;

-- session 2
SELECT * FROM comptes WHERE pers = 'leclercq';
 leclercq |   200
(1 row)                 -- maintenant tout le monde la voit

Durabilité — une transaction terminée est effective

On quitte les deux psql, on relance une nouvelle session, et on demande la table : tout est là, y compris après la fermeture complète des clients.

$ psql -d but2
SELECT * FROM comptes ORDER BY pers;
      pers       | solde
-----------------+-------
 autocommit_test |    42
 duchemin        |    40
 dupont          |   100
 durand          |   500
 leclercq        |   230
(5 rows)

3. Le verrouillage entre deux sessions

Autre illustration de l'isolation : par défaut, PostgreSQL isole les transactions (chacune travaille sur une copie des données jusqu'au commit) et verrouille les données par ligne. Deux sessions, une même ligne — regardez la seconde s'arrêter net :

-- session 1                                    -- session 2
BEGIN;
UPDATE comptes SET solde = solde + 10
  WHERE pers = 'leclercq';
UPDATE 1

                                                BEGIN;
                                                UPDATE comptes SET solde = solde + 20
                                                  WHERE pers = 'leclercq';
                                                -- bloquée : l'ordre ATTEND

COMMIT;
                                                UPDATE 1   -- le verrou est levé, ça repart
                                                COMMIT;

-- session 1
SELECT * FROM comptes WHERE pers = 'leclercq';
 leclercq |   230
(1 row)   -- 200 + 10 + 20 : les DEUX modifications ont été prises en compte

Dans notre test, la seconde session est restée bloquée 3 secondes (le temps que la première valide). Point crucial en READ COMMITTED : quand le verrou se lève, la seconde transaction travaille sur la version à jour de la ligne (210 €) et applique son + 20 : 230 €. Rien n'est perdu — la mise à jour du voisin n'a pas été écrasée.

4. L'autocommit

Par défaut, chaque ordre SQL est immédiatement validé. On peut passer en commit manuel :

\set AUTOCOMMIT off
INSERT INTO comptes VALUES('autocommit_test',42);
SELECT * FROM comptes WHERE pers = 'autocommit_test';
 autocommit_test |    42        -- visible de NOTRE session
(1 row)
ROLLBACK;
SELECT * FROM comptes WHERE pers = 'autocommit_test';
 pers | solde
------+-------
(0 rows)                       -- finalement, rien
INSERT INTO comptes VALUES('autocommit_test',42);
COMMIT;
SELECT * FROM comptes WHERE pers = 'autocommit_test';
 autocommit_test |    42        -- cette fois, c'est définitif
(1 row)
\set AUTOCOMMIT on

Quelle est la différence avec le select d'avant le rollback ?

Les deux selects montraient la ligne ! Une session voit ses propres modifications non validées — c'est normal. La différence se joue après : avec ROLLBACK, la ligne disparaît (elle n'a jamais existé pour les autres sessions) ; avec COMMIT, elle reste, pour tout le monde et durablement. C'est exactement ce que démontrent les sections 1 et 2.

À noter : les options du client psql (dont le mode autocommit) peuvent être rangées dans le fichier .psqlrc, joué au démarrage de chaque session.

5. Transactions en JDBC — Transac1

Assurez-vous que la table client possède une clé primaire :

create table client (cno int primary key, nom varchar(10), prenom varchar(10), age int);

a. Le programme classique. Transac1 vidange la table, insère les clés 1 à 1 000, puis — c'est là que ça coince — encore des clients avec des clés allant de 990 à 1990 :

Transac1.java — le cœur

Statement st = con.createStatement();
st.executeUpdate("truncate table client");
for (int i = 1; i <= 1000; i++) {
    st.executeUpdate("insert into client values(" + i + ",'nom" + i
            + "','prenom" + i + "'," + (i % 100) + ")");
}
for (int i = 990; i <= 1990; i++) {          // clés déjà prises !
    st.executeUpdate("insert into client values(" + i + ",'nom" + i
            + "','prenom" + i + "'," + (i % 100) + ")");
}

Exécution : le programme s'arrête brutalement sur la clé 990 :

Exception in thread "main" org.postgresql.util.PSQLException:
  ERROR: duplicate key value violates unique constraint "client_pkey"
  Detail: Key (cno)=(990) already exists.

Que s'est-il passé dans la base ? select count(*) from client; → 1 000 lignes. En autocommit, la première boucle a été validée insert par insert ; l'échec de la seconde n'a donc rien annulé. La table reste dans un état intermédiaire.

b. La version transactionnelle. On désactive l'autocommit, on met un commit à la fin du try et un rollback dans le catch :

Transac1T.java — le cœur

con.setAutoCommit(false);        // c'est nous qui décidons quand valider
Statement st = con.createStatement();
try {
    st.executeUpdate("truncate table client");
    for (int i = 1; i <= 1000; i++) {
        st.executeUpdate("insert into client values(" + i + ",'nom" + i
                + "','prenom" + i + "'," + (i % 100) + ")");
    }
    for (int i = 990; i <= 1990; i++) {
        st.executeUpdate("insert into client values(" + i + ",'nom" + i
                + "','prenom" + i + "'," + (i % 100) + ")");
    }
    con.commit();                // succès : on valide tout d'un coup
    System.out.println("Transaction validée");
} catch (Exception e) {
    con.rollback();              // échec : on annule TOUT
    System.out.println("Échec, transaction annulée : " + e.getMessage());
}

Exécution :

Échec, transaction annulée : ERROR: duplicate key value violates unique constraint "client_pkey"
  Detail: Key (cno)=(990) already exists.

Et dans la base ? select count(*) → 0 lignes. Tout a été annulé — y compris le truncate : c'est la définition de l'atomicité. (Et si la table avait contenu des lignes avant le programme, le rollback les aurait laissées intactes : on retrouve exactement l'état d'avant.)

c. La correction. On modifie la seconde boucle pour viser des clés libres — de 3000 à 4000 :

Transaction validée
select count(*) from client;  → 2 001 lignes

Les deux boucles du sujet font chacune 1 001 insertions (bornes incluses : 990 à 1990, puis 3000 à 4000) — d'où 1 000 + 1 001 = 2 001.

d. Testez votre compréhension. Entre les deux boucles, on ajoute un insert d'une clé à 3500. Combien de lignes restent dans la table, sans exécuter le programme ?

Version	Lignes restantes	Pourquoi
autocommit `true`	1 001	la boucle 1 (1 000 lignes) et l'insert 3500 ont été validés chacun au passage ; la boucle 2 échoue avant d'insérer quoi que ce soit
autocommit `false`	0	le `rollback` annule tout le bloc : boucle 1, insert 3500, boucle 2 — tout

Vérifié en exécutant les deux variantes : 1 001 et 0. Le réflexe est désormais : tout programme qui exécute plusieurs ordres devrait être en mode transactionnel.

6. Comment se faire facturer un produit qu'on n'a pas eu

Une petite application de commerce électronique : si l'article est en stock, on facture le client et on réduit le stock — 100 € par article, pour simplifier :

create table produits (prod text, qute int);
update comptes set solde = 0;
update produits set qute = 1;

1. Le programme classique. Transac2 prend deux paramètres (java Transac2 Dupont Chaise), vérifie le stock, puis — entre la facturation et la décrémentation — laisse une fenêtre de tir de 10 secondes (Thread.sleep(10000), dans le sujet, pour lancer un second processus en concurrence) :

Transac2.java — les lignes qui comptent

String r1 = "select qute from produits where prod = '" + prod + "'";
ResultSet rs = st.executeQuery(r1);
rs.next();
if (rs.getInt("qute") > 0) {
    String r3 = "update comptes set solde = solde + 100 where pers = '" + pers + "'";
    st.executeUpdate(r3);
    Thread.sleep(10000);   // la fenêtre de tir
    String r2 = "update produits set qute = qute - 1 where prod = '" + prod + "'";
    st.executeUpdate(r2);
    System.out.println("Achat réalisé");
} else {
    System.out.println("Achat impossible");
}

2. En fonctionnement normal (stock de 3, quatre achats successifs) — tout va bien :

--- achat 1   Achat réalisé
--- achat 2   Achat réalisé
--- achat 3   Achat réalisé
--- achat 4   Achat impossible

3. La course. Stock remis à 1, et deux clients arrivent « au même moment » : on lance Dupont, puis Durand pendant le sommeil du premier :

$ java Transac2 Dupont Chaise     # t = 0
$ java Transac2 Durand Chaise     # t = 2 s — Dupont n'a pas encore décrémenté
Achat réalisé
Achat réalisé

 Dupont |   100        ← les DEUX clients sont facturés
 Durand |   100
 Chaise |    -1        ← et le stock est négatif !

La course de Transac2, en une image : les deux programmes lisent « qute = 1 » avant que le premier n'ait décrémenté le stock. Les deux passent le test « il reste un article », les deux facturent leur client… et l'article unique est vendu deux fois. Le stock finit négatif.

Les deux programmes ont lu qute = 1 avant que le premier ne le décrémente : les deux ont passé le test « il reste un article » et les deux ont vendu le même. Un client a été facturé d'un produit qui n'existe pas. Ce type de problème existe sur des milliers de sites web.

4. Solution 1 — les verrous. On transforme le programme (Transac2_sol1) pour verrouiller la ligne dès la lecture, avec SELECT … FOR UPDATE :

Transac2_sol1.java — le cœur

String r1 = "select qute from produits where prod = '" + prod + "' for update";
ResultSet rs = st.executeQuery(r1);
rs.next();
if (rs.getInt("qute") > 0) {
    // … facturation, Thread.sleep, décrémentation …
    con.commit();
    System.out.println("Achat réalisé");
} else {
    con.commit();
    System.out.println("Achat impossible");
}

Course relancée, résultats réels :

Achat réalisé        (Dupont)
Achat impossible     (Durand — après avoir ATTENDU 8 s que le verrou se lève)
 Dupont |   100
 Durand |     0      ← pas facturé, et le stock s'arrête à 0

SELECT … FOR UPDATE : la transaction T1 verrouille les lignes qu'elle lit ; la transaction T2 qui veut les mêmes lignes attend la fin de T1 (COMMIT ou ROLLBACK). C'est le verrou explicite, sûr même en read committed.

5. Solution 2 — isolation renforcée + stratégie RETRY. Sans verrou explicite, on monte au niveau SERIALIZABLE et on réessaie proprement quand le SGBD rejette (SQLState 40001) :

Transac2_sol2.java — la boucle de retry

for (int tentative = 1; tentative <= maxTentatives; tentative++) {
    try (Connection con = DriverManager.getConnection(url, "votrelogin", "votremdp")) {
        con.setAutoCommit(false);
        con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        try (Statement st = con.createStatement()) {
            // … select, test du stock, facturation, décrémentation …
            con.commit();
            return;   // terminé : succès ou stock vide
        } catch (SQLException e) {
            con.rollback();
            if (!"40001".equals(e.getSQLState())) {
                throw e;   // ce n'est pas un conflit de concurrence : on arrête là
            }
            System.out.println("Conflit de concurrence (40001) — tentative "
                    + tentative + " annulée, on recommence…");
        }
    }
}

Résultats réels de la course :

Achat réalisé (tentative 1)                              (Dupont)
Conflit de concurrence (40001) — tentative 1 annulée…    (Durand)
Achat impossible                                         (après retry : le stock est bien à 0)
 Dupont |   100
 Durand |     0

Le SGBD a détecté le conflit et rejeté la transaction fautive ; le RETRY a alors rejoué l'achat sur des données fraîches et constaté… qu'il n'y avait plus d'article. Pas de double facturation, pas d'attente.

La leçon du TP

« Tous les programmes qui possèdent plusieurs ordres devraient toujours être en mode transactionnel ! (y compris ceux des précédents TP) » — c'est la conclusion du sujet, et les sections 5 et 6 en sont la démonstration.

7. Retour au TP 3.03 : Speed et transactions

Dernier volet : reprenez Speed2 (PreparedStatement, 10 000 inserts un par un — 1 042 ms dans le TP 3.03) et passez-le en mode transactionnel : setAutoCommit(false) avant la boucle, un seul commit() après, hors de la boucle :

con.setAutoCommit(false);
// … la boucle de 10 000 inserts …
con.commit();   // un seul commit, hors de la boucle

Résultats mesurés :

Version	Temps
Speed2 (autocommit)	1 042 ms
Speed2 transactionnel	324 ms
Speed4 (batch) — pour mémoire	151 ms

Pourquoi ? En autocommit, les 10 000 inserts déclenchent 10 000 commits : chaque commit est une opération lourde (validation du journal, WAL). En mode transactionnel, il n'y a plus qu'un seul commit pour tout le lot — le temps chute d'un facteur ≈3. Et le batch, qui réduit les allers-retours réseau, et la transaction, qui réduit les validations, se cumulent : c'est le combo gagnant des insertions en masse.

A retenir

BEGIN / COMMIT / ROLLBACK : une transaction est une unité « tout ou rien ». Une erreur avorte la transaction, et END/COMMIT sur une transaction avortée se comporte en ROLLBACK ;
par défaut, psql et JDBC sont en autocommit : pour grouper des ordres, désactivez-le (\set AUTOCOMMIT off, setAutoCommit(false)) et validez explicitement ;
ACID vérifié à la main : l'atomicité annule même un truncate ; l'isolation cache les modifications non validées aux autres sessions ; la durabilité se vérifie après reconnexion ;
verrou de ligne : la deuxième mise à jour attend, puis se réapplique sur la version à jour (200 → 210 → 230 : les deux modifications comptent) ;
la course au dernier article se produit pour de vrai : deux facturations, un stock à −1. Deux remèdes : SELECT … FOR UPDATE (on attend) ou SERIALIZABLE + RETRY sur SQLState 40001 (on recommence) ;
et une transaction, sur un gros lot d'insertions, n'est pas seulement plus sûre : elle est aussi plus rapide (324 ms contre 1 042 ms).

tp4-transactionnel.pdf

TP 3.04 — Systèmes transactionnels · 5 pages

Consultation seule — téléchargement désactivé

Chargement du document…

TP 3.04 — Systèmes transactionnels

Sur cette page