Transactions et niveaux d'isolement dans PostgreSQL

1. Aperçu

Quand plusieurs requêtes lisent et écrivent dans une même table, le résultat peut être incorrect même si chaque requête SQL est juste. Le niveau d’isolement détermine ce qu’une transaction « voit » parmi les changements d’autres transactions.

Dans cet article, nous verrons comment PostgreSQL implémente les niveaux d’isolement. Vous reproduirez les erreurs de lost update et write skew. À la fin, vous saurez comment choisir le niveau approprié, le configurer dans Spring Boot et écrire correctement la logique de retry.

2. Transactions dans PostgreSQL

Une transaction regroupe plusieurs instructions en une unité. Soit tout réussit, soit aucune instruction n’a d’effet :

CREATE TABLE account (
    id      BIGINT PRIMARY KEY,
    owner   TEXT NOT NULL,
    balance NUMERIC(12, 2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO account VALUES (1, 'An', 1000), (2, 'Binh', 500);

BEGIN;
UPDATE account SET balance = balance - 300 WHERE id = 1;
UPDATE account SET balance = balance + 300 WHERE id = 2;
COMMIT;

Si la deuxième instruction UPDATE échoue, vous appelez ROLLBACK et le solde d’An reste inchangé. Quand il n’y a pas de BEGIN, PostgreSQL fonctionne en mode autocommit : chaque instruction est une transaction séparée.

PostgreSQL utilise MVCC (Multi-Version Concurrency Control). Chaque instruction UPDATE crée une nouvelle version de la ligne au lieu de l’écraser. Chaque transaction lit les données via un snapshot. Par conséquent, la lecture ne bloque pas l’écriture, et vice versa. Le niveau d’isolement détermine quand le snapshot est pris : au début de chaque instruction, ou une seule fois pour toute la transaction.

3. Les anomalies et les quatre niveaux d’isolement

Le standard SQL définit les anomalies suivantes :

• Dirty read : lire des données qu’une autre transaction n’a pas encore validées.
• Non-repeatable read : lire la même ligne deux fois mais obtenir des valeurs différentes.
• Phantom read : réexécuter une même requête avec condition, et voir apparaître ou disparaître des lignes.
• Lost update : deux transactions lisent puis écrivent la même ligne, et une écriture en écrase une autre.
• Write skew : deux transactions lisent des données communes, puis chacune écrit dans une ligne différente, violant une contrainte métier.

Le tableau ci-dessous décrit comment PostgreSQL gère chaque anomalie :

Level              Dirty read  Non-repeatable  Phantom  Lost update  Write skew
READ UNCOMMITTED   Non         Oui             Oui      Oui          Oui
READ COMMITTED     Non         Oui             Oui      Oui          Oui
REPEATABLE READ    Non         Non             Non      Non (*)      Oui
SERIALIZABLE       Non         Non             Non      Non (*)      Non (*)
(*) La transaction est annulée avec SQLSTATE 40001, l'application doit réessayer.

Il y a deux points qui diffèrent du standard SQL dont vous devez vous souvenir :

• READ UNCOMMITTED dans PostgreSQL fonctionne exactement comme READ COMMITTED. Vous ne rencontrerez jamais de dirty read.
• REPEATABLE READ bloque aussi les phantom reads, car il s’agit en fait de snapshot isolation.

Le niveau par défaut est READ COMMITTED. Vous pouvez le vérifier avec :

SHOW default_transaction_isolation;

 default_transaction_isolation
-------------------------------
 read committed

4. READ COMMITTED

À ce niveau, chaque instruction prend un nouveau snapshot. Ouvrez deux sessions psql et exécutez alternativement dans cet ordre :

-- Session A
BEGIN;
SELECT balance FROM account WHERE id = 1;   -- 700.00

-- Session B
UPDATE account SET balance = 900 WHERE id = 1;  -- autocommit

-- Session A
SELECT balance FROM account WHERE id = 1;   -- 900.00
COMMIT;

Dans la même transaction, la session A voit deux valeurs différentes. C’est un non-repeatable read.

4.1. Lost update

L’erreur la plus courante dans les applications est le motif « lire en Java, calculer, puis écrire » :

BigDecimal balance = jdbc.queryForObject(
        "SELECT balance FROM account WHERE id = ?", BigDecimal.class, 1L);
jdbc.update("UPDATE account SET balance = ? WHERE id = ?",
        balance.subtract(new BigDecimal("100")), 1L);

Supposons que deux threads exécutent ce code et lisent tous les deux 1000. Les deux écriront 900. Le résultat est qu’un débit est perdu.

Il y a deux façons de corriger cela tout en gardant READ COMMITTED.

Méthode 1 : laissez la base de données calculer, écrivez en une seule instruction atomique.

UPDATE account SET balance = balance - 100 WHERE id = 1;

Quand PostgreSQL rencontre une ligne en cours de mise à jour par une autre transaction, il attend que cette transaction se valide. Ensuite, il relit la version la plus récente de la ligne et applique la soustraction.

Méthode 2 : verrouillez la ligne dès la lecture avec SELECT ... FOR UPDATE.

BEGIN;
SELECT balance FROM account WHERE id = 1 FOR UPDATE;
-- l'application vérifie la logique métier, calcule...
UPDATE account SET balance = 900 WHERE id = 1;
COMMIT;

La deuxième transaction sera bloquée au SELECT ... FOR UPDATE jusqu’à la validation de la première. Elle lira donc toujours la valeur la plus récente.

5. REPEATABLE READ

À ce niveau, le snapshot est pris une seule fois à la première instruction de la transaction. Chaque lecture ultérieure voit le même état des données.

Si vous tentez de mettre à jour une ligne qu’une autre transaction a modifiée et validée après la prise du snapshot, PostgreSQL refusera :

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE id = 1;          -- 1000.00

-- Session B
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- commit

-- Session A
UPDATE account SET balance = balance - 200 WHERE id = 1;

ERROR:  could not serialize access due to concurrent update

Le lost update ne se produit pas silencieusement. En échange, la session A doit ROLLBACK et réexécuter depuis le début. Le code d’erreur 40001 (serialization_failure) est un signal pour que l’application réessaye.

6. SERIALIZABLE et write skew

REPEATABLE READ laisse toujours passer le write skew. Considérez cet exemple classique : un hôpital exige qu’il y ait toujours au moins un médecin de garde.

CREATE TABLE doctor (
    id      BIGINT PRIMARY KEY,
    name    TEXT NOT NULL,
    on_call BOOLEAN NOT NULL
);
INSERT INTO doctor VALUES (1, 'Lan', true), (2, 'Minh', true);

Les deux médecins demandent à quitter la garde, chacun dans une transaction :

-- A et B exécutent tous deux :
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor WHERE on_call;  -- les deux voient 2

-- A
UPDATE doctor SET on_call = false WHERE id = 1;
-- B
UPDATE doctor SET on_call = false WHERE id = 2;

COMMIT;  -- A : OK
COMMIT;  -- B : OK  -> plus personne en garde !

Les deux transactions écrivent dans deux lignes différentes, donc aucun conflit d’écriture n’est détecté. Si on passe à SERIALIZABLE, PostgreSQL utilise Serializable Snapshot Isolation (SSI). SSI suit les dépendances lecture-écriture entre les transactions et en annule une des deux :

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

L’erreur peut apparaître au UPDATE ou lors du COMMIT. Le code d’erreur est toujours 40001.

Note : SERIALIZABLE ne verrouille pas de ligne supplémentaire. Il détecte simplement les conflits et annule les transactions. Avec SSI, PostgreSQL peut annuler même des transactions qui ne sont pas réellement en conflit. Par conséquent, sous forte charge d’écriture concurrente, le taux de retry peut augmenter considérablement.

7. Utiliser les niveaux d’isolement dans Spring Boot

Ajoutez les dépendances suivantes :

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

La façon la plus simple est de déclarer le niveau d’isolement sur @Transactional :

@Service
public class ReportService {

    private final JdbcTemplate jdbc;

    public ReportService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
    public BigDecimal totalBalance() {
        BigDecimal total = jdbc.queryForObject(
                "SELECT sum(balance) FROM account", BigDecimal.class);
        Long count = jdbc.queryForObject(
                "SELECT count(*) FROM account", Long.class);
        // les deux requêtes utilisent le même snapshot -> données cohérentes
        return total;
    }
}

Spring appelle Connection.setTransactionIsolation(...) avant de démarrer la transaction. Le driver PostgreSQL le traduit en SET TRANSACTION ISOLATION LEVEL ....

7.1. Retry en cas d’échec de sérialisation

Le retry doit être en dehors de la transaction. Faire un retry à l’intérieur est inutile, car la transaction est déjà marquée comme corrompue. Utilisez TransactionTemplate pour éviter le piège de l’auto-invocation avec @Transactional :

@Service
public class OnCallService {

    private static final int MAX_ATTEMPTS = 3;

    private final JdbcTemplate jdbc;
    private final TransactionTemplate tx;

    public OnCallService(JdbcTemplate jdbc, PlatformTransactionManager tm) {
        this.jdbc = jdbc;
        this.tx = new TransactionTemplate(tm);
        this.tx.setIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE);
    }

    public void goOffCall(long doctorId) {
        for (int attempt = 1; ; attempt++) {
            try {
                tx.executeWithoutResult(status -> doGoOffCall(doctorId));
                return;
            } catch (ConcurrencyFailureException e) {
                if (attempt >= MAX_ATTEMPTS) {
                    throw e;
                }
            }
        }
    }

    private void doGoOffCall(long doctorId) {
        Integer onCall = jdbc.queryForObject(
                "SELECT count(*) FROM doctor WHERE on_call", Integer.class);
        if (onCall == null || onCall < 2) {
            throw new IllegalStateException("Il doit y avoir au moins un médecin de garde");
        }
        jdbc.update("UPDATE doctor SET on_call = false WHERE id = ?", doctorId);
    }
}

Ce code fonctionne comme suit :

• setIsolationLevel(ISOLATION_SERIALIZABLE) s’applique à chaque appel de executeWithoutResult.
• Spring traduit SQLSTATE 40001 (et les deadlocks 40P01) en sous-classes de ConcurrencyFailureException. Vous n’avez pas besoin de lire le code d’erreur du driver.
• À chaque retry, une nouvelle transaction prend un nouveau snapshot. Elle voit que l’autre médecin a quitté la garde, donc lève IllegalStateException. La contrainte métier est respectée.

Note : Spring Boot 3 configure automatiquement JdbcTransactionManager. Cette classe traduit aussi les erreurs survenant au moment du commit en DataAccessException. Si vous déclarez vous-même un bean DataSourceTransactionManager, les erreurs de commit deviennent TransactionSystemException et la boucle de retry ci-dessus ne les capturera pas.

8. Tester le write skew avec Testcontainers

Pour reproduire précisément l’ordre d’entrelacement, on utilise directement deux Connection JDBC dans le même thread. Cette approche ne dépend pas du moment d’exécution du thread. Ajoutez la dépendance de test :

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>

La classe de test :

@Testcontainers
class WriteSkewTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");

    @BeforeEach
    void setUp() throws SQLException {
        try (Connection c = connect(); Statement st = c.createStatement()) {
            st.execute("DROP TABLE IF EXISTS doctor");
            st.execute("CREATE TABLE doctor (id BIGINT PRIMARY KEY, name TEXT, on_call BOOLEAN)");
            st.execute("INSERT INTO doctor VALUES (1, 'Lan', true), (2, 'Minh', true)");
        }
    }

    @Test
    void repeatableReadAllowsWriteSkew() throws SQLException {
        runConcurrently(Connection.TRANSACTION_REPEATABLE_READ);
        assertThat(countOnCall()).isZero();
    }

    @Test
    void serializableRejectsWriteSkew() throws SQLException {
        assertThatThrownBy(() -> runConcurrently(Connection.TRANSACTION_SERIALIZABLE))
                .isInstanceOf(SQLException.class)
                .extracting(e -> ((SQLException) e).getSQLState())
                .isEqualTo("40001");
        assertThat(countOnCall()).isEqualTo(1);
    }

    private void runConcurrently(int level) throws SQLException {
        try (Connection a = begin(level); Connection b = begin(level)) {
            assertThat(countOnCall(a)).isEqualTo(2);
            assertThat(countOnCall(b)).isEqualTo(2);
            offCall(a, 1);
            a.commit();
            offCall(b, 2);
            b.commit();
        }
    }

    private Connection begin(int level) throws SQLException {
        Connection c = connect();
        c.setAutoCommit(false);
        c.setTransactionIsolation(level);
        return c;
    }

    private Connection connect() throws SQLException {
        return DriverManager.getConnection(
                postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
    }

    private void offCall(Connection c, long id) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement(
                "UPDATE doctor SET on_call = false WHERE id = ?")) {
            ps.setLong(1, id);
            ps.executeUpdate();
        }
    }

    private int countOnCall(Connection c) throws SQLException {
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery("SELECT count(*) FROM doctor WHERE on_call")) {
            rs.next();
            return rs.getInt(1);
        }
    }

    private int countOnCall() throws SQLException {
        try (Connection c = connect()) {
            return countOnCall(c);
        }
    }
}

Les résultats attendus des deux tests :

repeatableReadAllowsWriteSkew  -> PASSED (les deux commit, 0 médecin de garde)
serializableRejectsWriteSkew   -> PASSED (B est annulée avec 40001, 1 médecin de garde)

Le deuxième test n’affirme pas si l’erreur survient au UPDATE ou au COMMIT de B. Il vérifie simplement le code 40001, car le moment où SSI détecte le conflit peut varier entre les versions de PostgreSQL.

9. Erreurs courantes et quand ne pas utiliser

• Augmenter le niveau sans faire de retry. À REPEATABLE READ et SERIALIZABLE, l’erreur 40001 est un comportement normal, pas un bug. Sans retry, les utilisateurs recevront des erreurs 500 aléatoires.
• Faire un retry à l’intérieur de @Transactional. La transaction corrompue doit être annulée et redémarrée depuis le début. Placez le retry dans la couche appelante à l’extérieur.
• Appeler une API externe dans une transaction avec retry. Les emails ou les ordres de paiement peuvent être envoyés plusieurs fois. Réalisez les effets secondaires après validation réussie.
• Mettre SERIALIZABLE dans tout le système « pour être sûr ». Cela cause des frais de suivi SSI et beaucoup de retry. Utilisez-le uniquement pour les sections avec des contraintes s’étendant sur plusieurs lignes.
• Mélanger les niveaux dans le même cas d’usage métier. SSI ne protège que si toutes les transactions concernées exécutent SERIALIZABLE. Une transaction READ COMMITTED écrivant dans la même table peut toujours violer la contrainte.
• Une transaction REPEATABLE READ s’exécutant trop longtemps. Un vieux snapshot empêche VACUUM de nettoyer les versions mortes des lignes, gonflant la table. Gardez les transactions courtes.

Note : Pour les contraintes exprimables par le schéma, comme CHECK, UNIQUE ou exclusion constraint, privilégiez ces contraintes. Elles sont moins coûteuses et plus fiables que d’augmenter le niveau d’isolement.

10. Conclusion

PostgreSQL exécute par défaut READ COMMITTED. Ce niveau suffit à la plupart des applications, du moment que vous écrivez les mises à jour de façon atomique ou utilisez SELECT ... FOR UPDATE pour éviter les lost updates.

REPEATABLE READ offre un snapshot cohérent tout au long de la transaction, idéal pour les rapports. SERIALIZABLE bloque aussi les write skews. Ces deux niveaux exigent que l’application réessaye quand elle rencontre SQLSTATE 40001.

Dans Spring Boot, déclarez le niveau via @Transactional(isolation = ...) ou TransactionTemplate, capturez ConcurrencyFailureException à l’extérieur de la transaction, et testez le comportement avec Testcontainers en utilisant un ordre d’opérations déterministe.