Historique de la question.
Les verrous d’advisoire sont apparus pour la première fois dans PostgreSQL 8.2 pour fournir des primitives de synchronisation légères au niveau de l’application qui fonctionnent en dehors du système de visibilité des tuples MVCC. Ils ont été conçus pour des flux de travail tels que le traitement de files d’attente et l’ingestion idempotente où le verrouillage basé sur des tables serait sémantiquement inapproprié ou prohibitif sur le plan des performances. Contrairement aux verrous au niveau des lignes qui sont liés à des tuples de table spécifiques et enregistrés dans la colonne système xmax, les verrous d’advisoire résident entièrement dans le gestionnaire de verrous en mémoire partagée, offrant un mécanisme pour organiser l’accès à des ressources abstraites sans générer de tuples morts ou de trafic WAL.
Le problème.
Dans des pipelines d’ingestion idempotente à haute concurrence, la garantie d’unicité sur les clés commerciales (par exemple, des UUID externes) via INSERT ... ON CONFLICT ou SELECT FOR UPDATE crée des goulets d’étranglement sévères. Les approches au niveau des lignes nécessitent d’écrire dans le tas pour définir les bits de verrou, ce qui gonfle les tables, accélère la pression sur le VACUUM et provoque des points chauds dans les index uniques pendant la résolution des conflits. Le défi est de fournir une exclusion mutuelle pour des entités logiques – telles qu’une clé commerciale hachée – sans toucher à la couche de stockage, tout en s’assurant que les échecs de verrou ne laissent pas échapper des ressources dans les pools de connexions persistants.
La solution.
La propriété critique est que les verrous d’advisoire sont stockés exclusivement dans la table de hachage LOCKTAG dans la mémoire partagée, en utilisant LOCKMETHOD_ADVISORY, et ne modifient donc jamais les pages de relation sous-jacentes. En utilisant pg_advisory_xact_lock(hashtext(business_key)), l’application acquiert un mutex à portée de transaction qui se libère automatiquement lors d’un COMMIT ou d’un ROLLBACK, empêchant l’évasion de verrou associée aux pg_advisory_lock à portée de session. Cette approche élimine le gonflement des tables et le conflit d’index car le verrou n’existe qu’en tant qu’entrée légère en mémoire, comme démontré ci-dessous :
BEGIN; -- Acquérir un verrou lié à la transaction sur la clé commerciale hachée SELECT pg_advisory_xact_lock(hashtext('a1b2c3d4')); -- Sûr d’insérer ; pas de conflit d’index unique si une autre session détient le verrou INSERT INTO events (business_key, payload) VALUES ('a1b2c3d4', '{"event":"click"}') ON CONFLICT (business_key) DO NOTHING; COMMIT;
L’équipe de la plateforme de données d’une entreprise de télémétrie devait garantir un traitement exactement une fois pour 50 000 événements par seconde ingérés de Kafka dans PostgreSQL, où chaque événement portait un UUID généré par le client servant de clé d'idempotence. Les tests de charge initiaux utilisant INSERT ... ON CONFLICT DO NOTHING sur une colonne UUID unique ont causé une latence sévère en raison de la contention sur le verrou d’index B-tree unique et d’un gonflement rapide dû aux échecs de mise à jour HOT. Le taux de génération de WAL a doublé pendant les heures de pointe, menaçant le retard de réplication et la capacité de stockage.
Une solution proposée consistait à précontrôler l’existence de la clé en utilisant SELECT * FROM events WHERE business_key = $1 FOR UPDATE, puis à insérer uniquement si le résultat était vide. Bien que cela ait empêché les doublons, cela a obligé chaque écrivain à acquérir un verrou de ligne sur soit la ligne existante, soit une ligne de réservation par intérim, créant un hotspot massif dans les pages de la table de réservation. L’approche a généré un gonflement substantiel de la table – nécessitant un VACUUM pour récupérer les tuples morts toutes les quinze minutes – et ne pouvait pas prévenir les conditions de concurrence entre le contrôle et l’insertion sans maintenir le verrou pendant toute la durée de la transaction, limitant sévèrement le débit.
L’équipe d’architecture a suggéré de déplacer la coordination vers un cache Redis externe en utilisant des opérations SETNX pour contrôler les insertions. Cela a éliminé le gonflement de la base de données et réduit la charge de PostgreSQL, mais a introduit des modes de défaillance critiques : des partitions réseau entre le cluster Redis et la base de données pouvaient permettre des insertions en double lorsque le verrou Redis expirait mais que la transaction PostgreSQL n’avait pas encore été validée. De plus, maintenir la cohérence entre deux systèmes distribués a ajouté de la complexité opérationnelle et a nécessité l’implémentation de Redlock ou d’algorithmes similaires, augmentant la latence d’environ 5 millisecondes par opération.
Le design choisi s’est appuyé sur les verrous d’advisoire natifs de PostgreSQL via pg_advisory_xact_lock(hashtext(business_key)), acquérant un verrou lié à la transaction sur le UUID haché avant d’essayer l’insertion. Étant donné que ces verrous résident uniquement en mémoire partagée et ne touchent pas le tas, ils n'imposent aucun surcoût de stockage et se libèrent automatiquement à la fin de la transaction, prévenant l’évasion de verrou observée avec les verrous à portée de session. Pour éviter les blocages indétectables, la couche d’application a trié tous les UUID dans chaque lot par leur valeur entière hachée avant d’acquérir des verrous, assurant un protocole d’ordre global entre tous les travailleurs concurrents.
Les verrous d’advisoire ont été sélectionnés car ils offraient la latence la plus faible (acquisition en moins d'une milliseconde) et aucun effet secondaire de stockage tout en maintenant une correction stricte sans dépendances externes. Contrairement à l’approche Redis, la durée de vie du verrou était liée à la transaction de la base de données, garantissant l’atomicité entre l’acquisition du verrou et la validation de l’insertion. Contrairement à SELECT FOR UPDATE, aucun gonflement de table n’a été généré, et contrairement à ON CONFLICT brut, l’index unique n’a jamais été stressé par des insertions concurrentes conflictuelles car la sérialisation a eu lieu avant l’accès au tas.
Après déploiement, le pipeline d’ingestion a maintenu 80 000 événements par seconde avec une latence p99 de moins de 10 millisecondes, comparé aux pics précédents de 200 ms pendant les pics de contention. Le gonflement des tables a chuté à des niveaux négligeables, permettant au autovacuum de s’exécuter uniquement pendant les heures creuses, et le volume de WAL a diminué de 40 %, réduisant considérablement les coûts de stockage d’archivage et le retard de réplique. Le système a maintenu la sémantique exactement une fois à travers plusieurs redémarrages de base de données et churns de pools de connexions sans un seul événement en double ou un délai d’attente induit par un blocage.
Pourquoi utiliser pg_advisory_lock (à portée de session) au lieu de pg_advisory_xact_lock risque-t-il d’épuiser le pool de connexions et d’entraîner une ingestion de doublons dans une architecture de travail à haut débit ?
Les candidats échouent souvent à reconnaître que pg_advisory_lock persiste jusqu’à ce qu’il soit explicitement déverrouillé ou que la session se déconnecte, même si la transaction échoue. Dans un environnement en pool où les travailleurs réutilisent de longues connexions, une erreur logique ou une exception qui contourne l’appel de déverrouillage laisse le verrou maintenu indéfiniment, faisant attendre sans fin les travailleurs suivants traitant la même clé commerciale. pg_advisory_xact_lock doit être utilisé à la place car il lie la durée de vie du verrou à la limite de la transaction, assurant une libération automatique lors d’un ROLLBACK et évitant l’évasion de mutex qui autrement priverait le pool de travailleurs et bloquerait le pipeline d’ingestion.
Comment l'absence d'une garantie d'ordre total lors de l'acquisition de plusieurs verrous d'advisoire conduit-elle à des blocages indétectables, et quel motif d'application spécifique élimine ce danger ?
Contrairement aux blocages au niveau des lignes que le détecteur de deadlock_timeout de PostgreSQL résout en tuant une transaction victime, les blocages de verrous d'advisoire sont invisibles au moteur car ils se produisent dans des espaces de noms définis par l'utilisateur. Si le travailleur A verrouille la ressource X puis Y, tandis que le travailleur B verrouille Y puis X, les deux sessions attendent indéfiniment sans erreur. Le modèle obligatoire est de trier tous les identifiants de ressources (par exemple, valeurs hashtext(uuid)) dans un ordre strictement monotone (croissant ou décroissant) à travers toute l’application avant de faire des demandes de verrou. Cet ordre global assure que les graphes d'attente restent acycliques, rendant impossible les dépendances circulaires et éliminant le risque de blocages silencieux.
Quelle limitation de la mémoire partagée restreint le nombre de verrous d'advisoire qu'une seule transaction peut détenir, et comment dépasser max_locks_per_transaction se manifeste-t-il par rapport à l'épuisement des verrous au niveau des lignes ?
De nombreux candidats supposent que les verrous d'advisoire sont infinis, mais ils consomment des entrées dans la table de verrou partagé régie par le paramètre de configuration max_locks_per_transaction (par défaut 64). Détenir plus de verrous que cette limite dans une seule transaction déclenche ERROR: out of shared memory (SQLSTATE 53200), annulant immédiatement la transaction. Cela contraste avec les verrous au niveau des lignes, où dépasser les limites déclenche généralement une mise à niveau de verrou ou des attentes selon lock_timeout, mais n’épuise pas un pool de mémoire partagée fixe. L'atténuation consiste à regrouper les opérations en sous-transactions plus petites ou à agréger plusieurs ressources logiques sous une seule clé de verrou d'advisoire via un hachage composite, plutôt que d'essayer de verrouiller simultanément des milliers de clés individuelles.