Concurrence : transactions, verrous, SKIP LOCKED

MVCC : pourquoi les lecteurs ne bloquent pas les écrivains

Postgres utilise le MVCC (Multi-Version Concurrency Control) : chaque transaction voit un instantané cohérent de la base. Une écriture crée une nouvelle version de la ligne sans détruire l'ancienne — les lecteurs continuent de voir leur version. Conséquence pratique : les lectures ne bloquent jamais les écritures et inversement. Les conflits n'arrivent qu'entre écrivains sur la même ligne.

Corollaire opérationnel : les versions mortes s'accumulent → VACUUM (auto en général) les recycle. Une table très écrite mal vacuumée « gonfle » (bloat) — un classique de prod à connaître.

Les niveaux d'isolation (ce qu'ils garantissent)

• Read Committed (défaut) : chaque requête voit ce qui est committé à son démarrage — suffisant pour 95 % des cas ;
• Repeatable Read : l'instantané est figé pour toute la transaction (utile pour un rapport cohérent multi-requêtes) ;
• Serializable : comme si les transactions s'exécutaient en série — la base détecte les anomalies et fait échouer une transaction (à toi de la rejouer). Le prix de la garantie maximale.

La règle : rester en Read Committed, monter d'un cran seulement quand une anomalie précise le justifie — et savoir gérer le serialization_failure (retry) si tu montes à Serializable.

Les verrous applicatifs et le pattern file d'attente

Le cours Stack IA a montré FOR UPDATE SKIP LOCKED pour le polling email et le reconciler de jobs — voici le pourquoi :

-- N workers se partagent une file, sans coordinateur externe :
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED       -- verrouille MES lignes, SAUTE celles des autres
LIMIT 10;
-- … traiter … UPDATE status = 'done' … COMMIT (libère les verrous)

• FOR UPDATE : verrouille les lignes lues (les autres transactions attendent) ;
• SKIP LOCKED : au lieu d'attendre, saute les lignes déjà verrouillées → chaque worker prend un lot disjoint, zéro contention. C'est ce qui transforme une table en file d'attente concurrente sans Redis ni RabbitMQ ;
• NOWAIT : variante qui échoue immédiatement au lieu de sauter (quand tu veux savoir que c'est verrouillé).

Et les advisory locks (pg_advisory_lock) pour un verrou applicatif nommé (« un seul worker exécute cette tâche cron ») — un mutex distribué gratuit.