🐘 Cas 1 verrous & deadlocks

Ce que PostgreSQL verrouille

Niveaux d’isolation

• READ COMMITTED (par défaut) : verrouillage ligne à la demande, cohérent pour OLTP.
• REPEATABLE READ : snapshot stable, plus de risque de conflits d’écriture.
• SERIALIZABLE : détection de conflits par abort, peut générer beaucoup de rollbacks.

                        -- Isolation par défaut
                        SHOW default_transaction_isolation;

                        -- Forcer un niveau pour une transaction critique
                        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
                    

Paramètres qui jouent sur le locking

• max_locks_per_transaction : nombre de verrous distincts par transaction.
• lock_timeout : délai max avant d’abandonner une attente de verrou.
• deadlock_timeout : délai avant que le détecteur de deadlock ne s’active.
• log_lock_waits : loguer les attentes supérieures à deadlock_timeout.

                        -- Exemple de politique plus « safe » (session ou global)
                        SET lock_timeout = '3s';
                        SET deadlock_timeout = '500ms';
                        SET log_lock_waits = on;
                    

Idées reçues

• PostgreSQL ne fait pas de « lock escalation » brutal façon SQL Server ; mais un DDL ou un VACUUM FULL sur une grosse table peut agir comme un verrou global.
• 90 % des problèmes viennent de transactions trop longues ou de mise à jour en masse sans découpage.

Pattern 1 – Mise à jour croisée

                        -- TX1
                        BEGIN;
                        UPDATE compte SET solde = solde - 100 WHERE id = 1;
                        -- attend...
                    

                        -- TX2
                        BEGIN;
                        UPDATE compte SET solde = solde - 50 WHERE id = 2;
                        UPDATE compte SET solde = solde + 50 WHERE id = 1;  -- bloque sur TX1
                    

                        -- TX1 (suite)
                        UPDATE compte SET solde = solde + 100 WHERE id = 2; -- bloque sur TX2
                    

Chaque transaction attend un verrou détenu par l’autre → cycle de dépendances → deadlock detected.

Pattern 2 – FK sans index

• Table enfant avec contrainte FK mais sans index sur la colonne référencée.
• DELETE/UPDATE sur la table parent → scan complet de la table enfant avec verrous en cascade.
• Combinaison avec d’autres transactions → deadlocks difficiles à expliquer.

Pattern 3 – Triggers & ordres d’accès

• Deux modules métier accèdent aux mêmes tables mais pas dans le même ordre (A→B→C vs B→A→C).
• Un trigger fait des UPDATE/INSERT sur d’autres tables dans un ordre différent.
• Avec beaucoup de concurrence, les cycles de verrous se multiplient.

Prévention

• Imposer un ordre d’accès strict aux tables pour toute l’application.
• Indexer systématiquement les colonnes de FK.
• Segmenter les gros traitements en lots (batchs de quelques milliers de lignes).
• Limiter la durée des transactions : pas de dialogues métier interactifs à l’intérieur du BEGIN.

Paramétrage des logs

                        ALTER SYSTEM SET deadlock_timeout = '500ms';
                        ALTER SYSTEM SET log_lock_waits   = on;
                        SELECT pg_reload_conf();
                    

On obtient ainsi des entrées de log exploitables par pgbadger / observabilité pour rejouer le scénario.

Vue « qui bloque qui ? »

                        SELECT
                        now(),
                        bl.pid      AS blocked_pid,
                        a_bl.usename AS blocked_user,
                        a_bl.query   AS blocked_query,
                        lk.pid      AS blocking_pid,
                        a_lk.usename AS blocking_user,
                        a_lk.query   AS blocking_query
                        FROM pg_locks bl
                        JOIN pg_stat_activity a_bl ON a_bl.pid = bl.pid
                        JOIN pg_locks lk
                        ON bl.locktype = lk.locktype
                        AND bl.database IS NOT DISTINCT FROM lk.database
                        AND bl.relation IS NOT DISTINCT FROM lk.relation
                        AND bl.page IS NOT DISTINCT FROM lk.page
                        AND bl.tuple IS NOT DISTINCT FROM lk.tuple
                        AND bl.transactionid IS NOT DISTINCT FROM lk.transactionid
                        AND bl.classid IS NOT DISTINCT FROM lk.classid
                        AND bl.objid IS NOT DISTINCT FROM lk.objid
                        AND bl.objsubid IS NOT DISTINCT FROM lk.objsubid
                        AND bl.pid <> lk.pid
                        JOIN pg_stat_activity a_lk ON a_lk.pid = lk.pid
                        WHERE NOT bl.granted;
                    

Sessions en attente de verrou

                        SELECT pid, usename, state, wait_event_type, wait_event,
                        now() - xact_start AS xact_age,
                        query
                        FROM pg_stat_activity
                        WHERE wait_event_type = 'Lock'
                        ORDER BY xact_start;
                    

Checklist d’analyse rapide

• Combien de sessions bloquées ? Sur quelles tables / index ?
• Le blocage vient-il d’un DDL, d’un VACUUM FULL, d’un batch UPDATE/DELETE ?
• La transaction bloquante est-elle ancienne, idle in transaction, ou très active ?

Décision : tuer ou patienter ?

• Transaction très ancienne, souvent idle, non critique → candidate pour pg_terminate_backend().
• Transaction courte mais critique (paiement, commande) → on stoppe plutôt le batch qui la bloque.

                        -- Exemple : tuer les sessions qui tiennent un verrou sur une table donnée
                        SELECT pg_terminate_backend(pid)
                        FROM pg_locks
                        JOIN pg_stat_activity USING (pid)
                        WHERE relation = 'orders'::regclass
                        AND mode LIKE 'Exclusive%'
                        AND pid <> pg_backend_pid();
                    

Toutes ces requêtes sont à adapter aux conventions de nommage (schémas, tables sensibles), d’où l’intérêt d’un runbook documenté.