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

Comprendre et résoudre les interblocages et les contentions de verrous PostgreSQL

PostgreSQL, une base de données relationnelle open-source puissante et largement utilisée, offre des mécanismes de contrôle de concurrence robustes pour permettre à plusieurs utilisateurs et applications d'accéder et de modifier des données simultanément. Cependant, lorsque ces opérations concurrentes interagissent de manière complexe, cela peut conduire à des situations comme la contention de verrous et, dans les cas plus graves, des interblocages (deadlocks). Comprendre comment les verrous fonctionnent dans PostgreSQL, identifier les causes profondes de la contention et mettre en œuvre des stratégies de résolution efficaces sont essentiels pour maintenir les performances et la disponibilité de la base de données.

Cet article vous guidera à travers les subtilités du verrouillage PostgreSQL. Nous explorerons les différents types de verrous, comment utiliser la vue système pg_locks pour diagnostiquer les problèmes de verrouillage, identifier les sessions bloquantes, analyser les scénarios d'interblocage courants et, surtout, discuter des techniques pratiques pour prévenir et résoudre ces goulots d'étranglement de performance. En maîtrisant ces concepts, vous pourrez assurer des opérations plus fluides et plus efficaces au sein de votre environnement PostgreSQL.

Les bases du verrouillage PostgreSQL

PostgreSQL utilise un mécanisme de verrouillage sophistiqué pour gérer l'accès concurrent aux objets de la base de données, tels que les tables, les lignes et même des colonnes spécifiques. L'objectif principal est d'assurer l'intégrité des données en empêchant les opérations conflictuelles. Cependant, ce mécanisme peut également être une source de problèmes de performance s'il n'est pas géré avec soin.

Types de verrous

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

Verrou d'accès exclusif (Access Exclusive Lock) : Accès exclusif à une ressource. Aucune autre transaction ne peut acquérir de verrou sur la ressource. C'est le verrou le plus restrictif.
Verrou exclusif (Exclusive Lock) : Une seule transaction peut détenir ce verrou. D'autres transactions peuvent lire la ressource mais ne peuvent pas la modifier.
Verrou de mise à jour partagée exclusif (Share Update Exclusive Lock) : Permet à d'autres de lire mais pas d'écrire, et empêche d'autres transactions d'acquérir certains autres verrous.
Verrou de ligne exclusif partagé (Share Row Exclusive Lock) : Permet à plusieurs transactions de détenir des verrous de ligne exclusifs partagés ou des verrous partagés, mais une seule transaction peut détenir un verrou exclusif, de mise à jour partagée exclusif ou de ligne exclusif.
Verrou partagé (Share Lock) : Permet à plusieurs transactions de détenir des verrous partagés simultanément. Cependant, il bloque toute transaction tentant d'acquérir un verrou exclusif, d'accès exclusif ou de mise à jour partagée exclusif.
Verrou de ligne exclusif (Row Exclusive Lock) : Permet à plusieurs transactions de détenir des verrous de ligne exclusifs simultanément. Il empêche les transactions d'acquérir des verrous exclusifs, d'accès exclusifs ou de mise à jour partagée exclusifs. C'est un type de verrou courant pour les opérations UPDATE et DELETE.
Verrou exclusif (Exclusive Lock) : Accorde un accès exclusif à une transaction pour une ligne spécifique. D'autres transactions peuvent lire la ligne mais ne peuvent acquérir aucun verrou de niveau ligne sur celle-ci.
Verrou d'accès exclusif (Access Exclusive Lock) : Le verrou le plus restrictif, empêchant toute autre transaction d'accéder à la ressource à quelque niveau que ce soit.

Modes de verrouillage

Les modes de verrouillage indiquent le type d'accès qu'une transaction requiert. 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 ralentir considérablement 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 quelles sessions sont en attente.

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 qui se bloque 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 (PIDs) des sessions impliquées.
blocked_user / blocking_user : Les utilisateurs associés à ces PIDs.
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 interblocages

Un interblocage 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 seule. PostgreSQL détecte les interblocages et les résout automatiquement en annulant l'une des transactions, généralement celle qui est à l'origine de l'interblocage et qui a effectué le moins de travail.

Scénarios d'interblocage courants

Deux transactions mettant à jour des lignes différentes dans des tables différentes dans l'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 s'interbloqueront 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 de ligne exclusif lorsque le SELECT FOR UPDATE tente d'acquérir un verrou partagé, et que d'autres dépendances existent, un interblocage peut se produire.

Détection des interblocages

PostgreSQL consigne les informations d'interblocage dans son journal de serveur. Vous verrez typiquement 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 un processus victime à annuler. Vous pouvez également utiliser pg_stat_activity pour voir les requêtes impliquées au moment de la détection.

Résolution des interblocages

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

Identifier la victime : Vérifiez les journaux PostgreSQL pour le message deadlock detected. Il spécifiera quel processus a été annulé.
Retenter la transaction annulée : L'application recevant l'erreur d'interblocage doit être conçue pour intercepter cette erreur spécifique (par exemple, le code d'erreur deadlock_detected) et retenter la transaction. C'est le moyen le plus courant et le plus efficace de gérer les interblocages du point de vue de l'application.
Analyser la cause : La clé de la résolution est de prévenir les interblocages futurs. Cela implique de comprendre pourquoi l'interblocage 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 interblocages

La prévention est toujours meilleure que la guérison. La mise en œuvre de stratégies visant à minimiser la contention de verrous et à éviter les situations d'interblocage est cruciale pour une base de données PostgreSQL haute performance.

1. Ordre de transaction cohérent

Règle : Toujours accéder et modifier les ressources (tables, lignes) dans le même ordre à travers 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 les chances de contention.
Action : Effectuez uniquement les opérations de base de données nécessaires au sein d'une transaction. Déplacez le travail non lié à la base de données (par exemple, les appels d'API externes, les calculs complexes non dépendants de l'état de la transaction) en dehors de la limite de la transaction.

3. Utiliser des niveaux d'isolation appropriés

Règle : Comprendre et choisir le niveau d'isolation des transactions correct. PostgreSQL offre :
- 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 davantage d'erreurs de serialization_failure (qui sont essentiellement des interblocages pour l'isolation de snapshot) et potentiellement plus de contention de verrous. Ne les utilisez que lorsque c'est absolument nécessaire.

4. Optimiser les requêtes et les index

Règle : Les requêtes lentes retiennent 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 de JOIN.

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

Règle : SELECT ... FOR UPDATE verrouille les lignes pendant la durée de la transaction. C'est puissant pour prévenir les conditions de concurrence (race conditions) mais peut aussi être une source majeure de contention.
Action : Ne l'utilisez que lorsque vous avez réellement besoin de verrouiller des lignes pour empêcher qu'elles ne soient modifiées par d'autres transactions avant que votre transaction ne termine son travail. Réfléchissez si les verrous consultatifs pourraient être mieux adaptés à certains scénarios.

6. Verrous consultatifs (Advisory Locks)

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 interblocages, la logique de l'application doit donc les gérer avec soin.

7. Opérations par lots

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/la mise à jour en masse lorsque cela est possible.
Action : Une seule instruction UPDATE pourrait acquérir des verrous plus efficacement qu'une boucle d'instructions UPDATE individuelles. Analysez le comportement de verrouillage de vos opérations par lots.

Conclusion

La contention de verrous et les interblocages sont des défis courants dans les environnements de base de données à forte concurrence. En comprenant les concepts fondamentaux du verrouillage PostgreSQL, en utilisant des outils comme pg_locks et pg_stat_activity pour diagnostiquer les problèmes, et en mettant en œuvre des stratégies préventives telles que l'ordonnancement cohérent des transactions, la minimisation de la durée des transactions et l'optimisation des requêtes, vous pouvez améliorer considérablement la stabilité et les performances de votre base de données PostgreSQL. N'oubliez pas qu'une gestion robuste des erreurs dans votre application, en particulier pour la reprise des transactions bloquées, est également une partie essentielle de la gestion efficace de ces situations.