data / sql

Verrouillage et concurrence : niveaux d'isolation

Leçon 171 exercice

Explication

Ce que vous allez apprendre

Nommer les trois anomalies de concurrence : lecture sale, lecture non répétable, phénomène fantôme
Choisir le bon niveau d'isolation (READ COMMITTED, REPEATABLE READ, SERIALIZABLE)
Verrouiller explicitement des lignes avec SELECT ... FOR UPDATE
Construire une file d'attente concurrente performante avec FOR UPDATE SKIP LOCKED
Éviter un deadlock en verrouillant systématiquement les ressources dans le même ordre

Dans quel contexte ?

Une application de traitement de commandes fait tourner plusieurs workers en parallèle qui piochent des tâches dans une table jobs. Sans précaution, deux workers peuvent récupérer et traiter la même tâche en double. FOR UPDATE SKIP LOCKED, présenté dans cette leçon, est le pattern standard pour garantir qu'une tâche n'est prise en charge que par un seul worker à la fois.

D'abord, le scénario qui pose problème

En production, des dizaines voire des milliers d'utilisateurs lisent et écrivent la même base simultanément. Que se passe-t-il si deux transactions modifient le même compte bancaire au même instant ?

Sans règles, le chaos

Sans règles claires, on obtiendrait des résultats incohérents, imprévisibles, différents à chaque exécution. Il faut définir précisément ce que chaque transaction est autorisée à voir pendant qu'une autre est en cours.

Trois anomalies à connaître par leur nom, une par une

La "lecture sale" (dirty read) consiste à lire une donnée modifiée mais pas encore validée par une autre transaction, un peu comme croire une rumeur non confirmée. La "lecture non répétable" se produit quand relire deux fois la même ligne donne deux résultats différents dans une seule transaction. Le "phénomène fantôme" apparaît quand une requête répétée retourne un nombre de lignes différent, à cause d'insertions concurrentes.

La réponse : des niveaux d'isolation croissants

Ces niveaux forment une échelle : plus on monte, de READ UNCOMMITTED vers SERIALIZABLE, plus on élimine d'anomalies, mais plus les transactions se bloquent ou échouent mutuellement.

Niveau d'isolation	Dirty read	Lecture non répétable	Phénomène fantôme
`READ UNCOMMITTED`	Possible	Possible	Possible
`READ COMMITTED` (défaut PostgreSQL)	Empêché	Possible	Possible
`REPEATABLE READ` (défaut MySQL)	Empêché	Empêché	Possible (selon SGBD)
`SERIALIZABLE`	Empêché	Empêché	Empêché

Le niveau le plus strict a un coût

SERIALIZABLE peut faire échouer une transaction à la validation, obligeant l'application à la retenter. C'est le prix à payer pour la cohérence la plus totale.

Un outil complémentaire : le verrouillage explicite

SELECT ... FOR UPDATE verrouille explicitement des lignes pour empêcher toute modification concurrente pendant qu'on travaille dessus.

Le piège qui en découle : le deadlock

Si deux transactions verrouillent les mêmes ressources dans un ordre différent, elles peuvent s'attendre mutuellement indéfiniment. La parade universelle est de toujours verrouiller les ressources dans le même ordre partout dans le code, ce qui complète directement les transactions vues en leçon 9.

Piège fréquent

La transaction A verrouille le compte 1 puis tente de verrouiller le compte 2, pendant que la transaction B verrouille le compte 2 puis tente de verrouiller le compte 1 : chacune attend l'autre indéfiniment. PostgreSQL détecte ce deadlock et annule automatiquement l'une des deux transactions avec une erreur, mais mieux vaut l'éviter en amont.

Bonne pratique

Verrouille toujours plusieurs ressources dans un ordre déterministe et identique partout dans le code, par exemple par identifiant croissant (ORDER BY id FOR UPDATE), pour éliminer structurellement le risque de deadlock.

Commandes & code

Verrouillage et niveaux d'isolation

-- Problèmes classiques de concurrence :
-- Dirty read      : lire une donnée modifiée mais pas encore committée par une autre transaction
-- Non-repeatable read : relire la même ligne donne un résultat différent dans la même transaction
-- Phantom read    : une requête répétée retourne des lignes différentes (insertions concurrentes)

-- READ UNCOMMITTED : autorise les dirty reads (quasi jamais utilisé en pratique)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- READ COMMITTED : niveau par défaut de PostgreSQL/SQL Server
-- Ne voit que les données committées, mais peut voir des valeurs différentes entre deux SELECT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ : niveau par défaut de MySQL/InnoDB
-- Garantit qu'un SELECT répété dans la même transaction retourne toujours le même résultat
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE : le plus strict, équivaut à une exécution séquentielle des transactions
-- Peut échouer avec une erreur de sérialisation à COMMIT -> il faut retenter la transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT solde FROM comptes WHERE id = 1;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
COMMIT;  -- peut lever "could not serialize access due to concurrent update"

-- Verrou explicite au niveau ligne : empêche une autre transaction de modifier ces lignes
BEGIN;
SELECT * FROM comptes WHERE id = 1 FOR UPDATE;   -- verrouille la ligne jusqu'au COMMIT/ROLLBACK
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
COMMIT;

-- FOR UPDATE SKIP LOCKED : pattern de file d'attente / job queue performante
-- Chaque worker prend une ligne non verrouillée, sans attendre les autres workers
BEGIN;
SELECT * FROM jobs
WHERE statut = 'en_attente'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs SET statut = 'en_cours', worker_id = 'worker-7' WHERE id = 123;
COMMIT;

-- FOR SHARE : verrou partagé, autorise d'autres lectures mais empêche les modifications
BEGIN;
SELECT * FROM produits WHERE id = 5 FOR SHARE;
COMMIT;

-- Détecter un deadlock : PostgreSQL annule automatiquement l'une des deux transactions
-- Transaction A : verrouille compte 1, veut verrouiller compte 2
-- Transaction B : verrouille compte 2, veut verrouiller compte 1
-- -> "deadlock detected" ; bonne pratique : toujours verrouiller dans le MÊME ordre (ex: par id croissant)
BEGIN;
SELECT * FROM comptes WHERE id IN (1, 2) ORDER BY id FOR UPDATE;  -- ordre déterministe -> évite le deadlock

Résumé

Isolation croissante = READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE (moins de concurrence, plus de cohérence).
SELECT ... FOR UPDATE verrouille les lignes lues jusqu'à la fin de la transaction.
FOR UPDATE SKIP LOCKED est le pattern standard pour des files d'attente/job queues concurrentes.
Toujours verrouiller les ressources dans un ordre cohérent entre transactions pour éviter les deadlocks.
SERIALIZABLE peut nécessiter une logique de retry côté application en cas d'échec de sérialisation.

Exercices pratiques

1 disponible

Mission : éviter que deux workers ne traitent la même tâche

Objectif : Construire une file d'attente concurrente sûre avec FOR UPDATE SKIP LOCKED et prévenir un deadlock entre transactions.

Contexte

Plusieurs workers tournent en parallèle et piochent des tâches dans une table jobs(id, statut, worker_id). Sans précaution, deux workers ont déjà récupéré et traité la même tâche en double la semaine dernière. Par ailleurs, deux transactions de virement se sont bloquées mutuellement en environnement de test.

Résoudre l’exercice →