Comment identifier et résoudre les blocages et la contention de verrous dans PostgreSQL

Les blocages et la contention de verrous dans PostgreSQL se manifestent généralement par une application qui semble soudainement bloquée. Les requêtes s'accumulent, les workers restent dans l'état active ou idle in transaction, et la base de données elle-même peut encore avoir beaucoup de CPU disponible. Le problème n'est pas une question de capacité brute. Une session attend qu'une autre session libère un verrou, et parfois toute une file d'attente se forme derrière elle.

La façon la plus rapide de gérer cela est de séparer deux cas. La contention de verrous signifie qu'une session attend, mais elle peut éventuellement continuer. Un blocage signifie que deux sessions ou plus s'attendent mutuellement dans un cycle, donc PostgreSQL doit annuler une transaction. Vous déboguez les deux avec les mêmes outils de base, mais la correction est souvent différente.

Principes de base du verrouillage PostgreSQL

PostgreSQL utilise des verrous pour protéger les tables, les lignes, les transactions et d'autres objets internes lorsque plusieurs sessions travaillent en même temps. Il utilise également MVCC, de sorte que les lecteurs et les rédacteurs ordinaires ne se bloquent souvent pas mutuellement. C'est pourquoi PostgreSQL peut bien gérer une forte concurrence, mais c'est aussi pourquoi les problèmes de verrouillage peuvent être déroutants : le problème est généralement un modèle d'instruction spécifique, pas "trop d'utilisateurs" en général.

Types de verrous

PostgreSQL utilise différents niveaux de verrouillage, chacun offrant un degré de protection différent. Comprendre cela est essentiel pour diagnostiquer les problèmes :

AccessShareLock : Utilisé par les SELECT ordinaires. Il entre en conflit principalement avec ACCESS EXCLUSIVE, c'est pourquoi de nombreuses lectures peuvent s'exécuter pendant que des écritures ont lieu.
RowExclusiveLock : Courant pour INSERT, UPDATE et DELETE sur une table. Le nom est facile à mal interpréter ; il ne signifie pas que chaque ligne de la table est verrouillée de manière exclusive.
ShareUpdateExclusiveLock : Utilisé par des opérations telles que VACUUM sans FULL, ANALYZE et certaines opérations d'index. Il autorise les lectures et écritures normales mais entre en conflit avec plusieurs opérations de maintenance.
ShareLock / ShareRowExclusiveLock / ExclusiveLock : Modes de verrouillage de table plus forts utilisés par des DDL spécifiques et des opérations liées aux contraintes.
AccessExclusiveLock : Le verrou de table le plus restrictif. ALTER TABLE, DROP TABLE, TRUNCATE et VACUUM FULL peuvent prendre ce type de verrou. Il bloque les lectures ordinaires ainsi que les écritures.

Les verrous au niveau des lignes sont distincts des modes de verrouillage au niveau des tables. Une UPDATE prend un RowExclusiveLock au niveau de la table et des verrous de ligne sur les lignes qu'elle modifie. Lorsque les gens disent "cette ligne est verrouillée", ils veulent généralement dire qu'une autre transaction a modifié ou sélectionné cette ligne FOR UPDATE et n'a pas encore validé.

Modes de verrouillage

Les modes de verrouillage indiquent le type d'accès dont une transaction a besoin. Ils sont souvent représentés par des noms comme RowExclusiveLock, ShareLock, ExclusiveLock, etc.

Identifier la contention de verrous et les sessions bloquantes

La contention de verrous se produit lorsque plusieurs transactions attendent des verrous détenus par d'autres transactions. Cela peut considérablement ralentir votre application. La vue système pg_locks est votre outil principal pour diagnostiquer ces problèmes.

Utilisation de `pg_locks`

La vue pg_locks fournit des informations sur tous les verrous actifs dans le système de base de données. Elle est inestimable pour comprendre quelles sessions détiennent des verrous et lesquelles attendent.

Voici une requête courante pour identifier les sessions bloquantes :

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    blocking_activity.query AS blocking_statement
FROM
    pg_catalog.pg_locks blocked_locks
JOIN
    pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN
    pg_catalog.pg_locks blocking_locks
ON
    blocking_locks.locktype = blocked_locks.locktype AND
    blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND
    blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND
    blocking_locks.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

Explication de la requête :

Nous joignons pg_locks avec pg_stat_activity deux fois : une fois pour le processus bloqué et une fois pour le processus bloquant.
La clause WHERE NOT blocked_locks.granted filtre les verrous qui sont actuellement en attente.
La condition blocking_locks.pid != blocked_locks.pid garantit que nous ne signalons pas une session se bloquant elle-même.
Les conditions de jointure sur pg_locks correspondent aux verrous sur la même ressource.

Interprétation de la sortie

blocked_pid / blocking_pid : Les identifiants de processus (PID) des sessions impliquées.
blocked_user / blocking_user : Les utilisateurs associés à ces PID.
blocked_statement / blocking_statement : Les requêtes SQL qui sont actuellement en cours d'exécution ou en attente.
blocked_mode / blocking_mode : Les modes de verrouillage demandés et détenus.

Si cette requête renvoie des lignes, vous avez une contention de verrous. Le blocking_pid détient un verrou que le blocked_pid attend.

Comprendre et résoudre les blocages

Un blocage se produit lorsque deux transactions ou plus attendent chacune un verrou détenu par une autre transaction dans le cycle, créant une dépendance circulaire qu'aucune ne peut résoudre par elle-même. PostgreSQL détecte les blocages et les résout automatiquement en abandonnant l'une des transactions, généralement celle qui provoque le blocage et a effectué le moins de travail.

Scénarios de blocage courants

Deux transactions mettant à jour différentes lignes dans différentes tables dans un ordre inverse :
- Transaction A : Met à jour la ligne X dans la table 1, puis tente de mettre à jour la ligne Y dans la table 2.
- Transaction B : Met à jour la ligne Y dans la table 2, puis tente de mettre à jour la ligne X dans la table 1. Si la transaction A verrouille la ligne X et la transaction B verrouille la ligne Y, elles se bloqueront mutuellement lorsqu'elles tenteront d'acquérir le verrou détenu par l'autre.
UPDATE suivi de SELECT ... FOR UPDATE :
- Transaction A : Met à jour une ligne.
- Transaction B : Exécute SELECT ... FOR UPDATE sur la même ligne. Si l'UPDATE détient toujours un verrou exclusif de ligne lorsque le SELECT FOR UPDATE tente d'acquérir un verrou partagé, et que d'autres dépendances existent, un blocage peut se produire.

Détection des blocages

PostgreSQL enregistre les informations de blocage dans son journal serveur. Vous verrez généralement des messages comme :

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

PostgreSQL choisit automatiquement une transaction à abandonner afin que les autres puissent continuer. Ne vous fiez pas à pg_stat_activity après coup pour obtenir l'histoire complète ; au moment où vous regardez, l'instruction annulée peut avoir disparu. Le journal serveur est généralement le meilleur enregistrement du cycle.

Résolution des blocages

Lorsqu'un blocage est détecté et que PostgreSQL le résout en abandonnant une transaction :

Identifier la victime : Vérifiez les journaux PostgreSQL pour le message deadlock detected. Il spécifiera quel processus a été abandonné.
Réessayer la transaction abandonnée : L'application recevant SQLSTATE 40P01 (deadlock_detected) doit réessayer l'ensemble de la transaction lorsque l'opération peut être réessayée en toute sécurité. Réessayer uniquement la dernière instruction peut laisser l'état de l'application incohérent.
Analyser la cause : La clé de la résolution est de prévenir les futurs blocages. Cela implique de comprendre pourquoi le blocage s'est produit (comme décrit dans les scénarios courants) et d'ajuster la logique de l'application ou la conception de la base de données.

Techniques pour prévenir la contention de verrous et les blocages

Mieux vaut prévenir que guérir. La mise en œuvre de stratégies pour minimiser la contention de verrous et éviter les situations de blocage est cruciale pour une base de données PostgreSQL haute performance.

1. Ordonnancement cohérent des transactions

Règle : Accédez et modifiez toujours les ressources (tables, lignes) dans le même ordre dans toutes les transactions. Si plusieurs transactions doivent mettre à jour TableA et TableB, assurez-vous qu'elles mettent toujours à jour TableA avant TableB, ou vice-versa, de manière cohérente.
Exemple : Si une transaction doit mettre à jour des enregistrements dans users et orders, effectuez toujours les opérations sur users en premier, puis sur orders. Évitez les scénarios où une transaction met à jour users puis orders, tandis qu'une autre met à jour orders puis users.

2. Minimiser la durée des transactions

Règle : Gardez les transactions aussi courtes que possible. Plus une transaction est ouverte longtemps, plus elle détient de verrous, augmentant ainsi le risque de contention.
Action : Effectuez uniquement les opérations de base de données nécessaires dans une transaction. Déplacez le travail non lié à la base de données (par exemple, les appels API externes, les calculs complexes ne dépendant pas de l'état de la transaction) en dehors des limites de la transaction.

3. Utiliser des niveaux d'isolation appropriés

Règle : Comprenez et choisissez le niveau d'isolation des transactions correct. PostgreSQL propose :
- READ UNCOMMITTED (simulé par READ COMMITTED dans PostgreSQL)
- READ COMMITTED (par défaut)
- REPEATABLE READ
- SERIALIZABLE
Action : Le READ COMMITTED par défaut offre de bonnes performances tout en empêchant les lectures sales. REPEATABLE READ et SERIALIZABLE offrent une cohérence plus forte mais peuvent entraîner plus d'erreurs serialization_failure (qui sont essentiellement des blocages pour l'isolation d'instantané) et potentiellement plus de contention de verrous. Utilisez-les uniquement lorsque cela est absolument nécessaire.

4. Optimiser les requêtes et les index

Règle : Les requêtes lentes détiennent les verrous plus longtemps. Assurez-vous que vos requêtes sont efficaces et bien indexées.
Action : Utilisez EXPLAIN ANALYZE pour identifier les requêtes lentes. Ajoutez des index appropriés pour accélérer la récupération des données, en particulier pour les clauses WHERE et les conditions JOIN.

5. Utiliser `SELECT ... FOR UPDATE` avec parcimonie

Règle : SELECT ... FOR UPDATE verrouille les lignes pour la durée de la transaction. C'est puissant pour éviter les conditions de concurrence, mais cela peut aussi être une source majeure de contention.
Action : Utilisez-le uniquement lorsque vous avez vraiment besoin de verrouiller des lignes pour empêcher leur modification par d'autres transactions avant que votre transaction ne termine son travail. Considérez si les verrous consultatifs pourraient être mieux adaptés à certains scénarios.

6. Verrous consultatifs

Règle : Pour le verrouillage au niveau de l'application ou des besoins de synchronisation plus complexes qui ne correspondent pas directement aux verrous d'objets de base de données, les verrous consultatifs de PostgreSQL peuvent être un outil puissant.
Action : Utilisez des fonctions comme pg_advisory_lock(), pg_advisory_lock_shared() et pg_advisory_unlock() pour implémenter des mécanismes de verrouillage personnalisés. Ces verrous ne sont pas automatiquement détectés par le mécanisme de détection des blocages, donc la logique de l'application doit les gérer avec soin.

7. Regroupement des opérations

Règle : Au lieu d'émettre de nombreuses instructions UPDATE ou DELETE individuelles, envisagez de les regrouper en une seule instruction ou d'utiliser COPY pour le chargement/mise à jour en masse lorsque cela est possible.
Action : Une seule instruction UPDATE peut acquérir des verrous plus efficacement qu'une boucle d'UPDATE individuelles. Analysez le comportement de verrouillage de vos opérations par lots.

Un flux de triage pratique

Lorsqu'un incident est actif, commencez par les sessions en attente, pas par la théorie la plus ancienne dans votre tête :

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

Si un PID bloquant apparaît encore et encore, inspectez-le :

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

L'expression à surveiller est idle in transaction. Cette session n'effectue pas activement un travail utile sur la base de données, mais elle peut encore détenir des verrous. Cela provient souvent d'un code d'application qui ouvre une transaction, effectue une requête, appelle une API externe et ne valide qu'après le retour de l'API. Déplacez l'appel externe en dehors de la transaction si vous le pouvez.

Annulez avec précaution. SELECT pg_cancel_backend(pid) demande à la requête en cours de s'arrêter. SELECT pg_terminate_backend(pid) tue la session et annule sa transaction ouverte. Dans un incident de production, terminer le bloqueur peut être la bonne décision, mais capturez d'abord la requête et l'âge de la transaction afin de pouvoir corriger le chemin de code plus tard.

Une autre habitude aide après l'incident : enregistrez la requête bloquante, la requête bloquée et les limites de la transaction à partir des journaux d'application. La seule instruction SQL ne suffit souvent pas. Une UPDATE users SET last_seen_at = now() inoffensive peut devenir un bloqueur si elle se trouve dans une transaction qui attend également une API de paiement. Les corrections de blocage vivent généralement au niveau du flux de transaction, pas à l'intérieur d'une instruction isolée.

Les corrections durables sont généralement simples : gardez les transactions courtes, touchez les ressources partagées dans un ordre cohérent, ajoutez des index qui empêchent les mises à jour de trop analyser, et faites en sorte que les tentatives d'application gèrent proprement 40P01 et les échecs de sérialisation. PostgreSQL peut détecter un blocage, mais il ne peut pas reconcevoir le modèle de transaction pour vous. Cette partie appartient à la révision de l'application et du schéma une fois l'incident calmé.