Advisory lock e pool di connessioni: il leak silenzioso e come evitarlo

Per chi è questo pezzo

Se hai worker che a volte eseguono lo stesso job due volte, o stai scegliendo come serializzare una coda su Postgres, probabilmente sai già cosa sono un advisory lock e un SELECT … FOR UPDATE. Quello che non trovi facilmente in documentazione è quando il meccanismo che hai scelto ti tradisce in produzione e come accorgertene prima che i sintomi siano “dati doppi” o richieste appese.

L’angolo qui è uno solo: lock advisory session-scoped + pool che riusa la stessa sessione Postgres tra transazioni (il caso tipico di PgBouncer in transaction pooling), e perché SKIP LOCKED è l’anti-pattern se il tuo obiettivo è attendere la stessa chiave, non saltarla.

Riferimento di versione: esempi allineati a PostgreSQL 16; su altre major controlla i campi di pg_locks e le note del tuo pooler.

Setup minimo (ledger)

Serve una riga stabile per chiave logica se più avanti confrontiamo con FOR UPDATE:

DROP TABLE IF EXISTS job_ledger;

CREATE TABLE job_ledger (
  job_key     text PRIMARY KEY,
  claim_count bigint NOT NULL DEFAULT 0,
  updated_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO job_ledger (job_key) VALUES ('report-daily')
ON CONFLICT DO NOTHING;

Costante applicativa per advisory (evita collisioni con altri sottosistemi):

-- classid fittizio condiviso da tutti i lock “job queue” dell’app
SELECT 7742 AS app_advisory_class;

Dimostrazione breve: pg_advisory_xact_lock fa quello che ti aspetti

Due finestre psql.

Sessione A

BEGIN;
SELECT pg_advisory_xact_lock(7742, hashtext('report-daily'));
-- … lavoro …
COMMIT;

Sessione B (stesso BEGIN + stessa SELECT pg_advisory_xact_lock(…) mentre A non ha ancora fatto COMMIT): resta in attesa. Dopo COMMIT di A, B prosegue. Il lock è legato alla transazione: a fine transazione non resta nulla sul backend.

Fin qui niente di nuovo rispetto alla doc — serve solo come controllo positivo prima del caso che brucia.

Il caso che brucia: pg_advisory_lock e il pool in transaction mode

pg_advisory_lock (senza xact_) è session-scoped: sopravvive al COMMIT finché non chiami pg_advisory_unlock o non chiudi la connessione.

Riproduzione senza PgBouncer (stesso backend, una sola psql)

In una sessione:

SELECT pg_advisory_lock(7742, hashtext('report-daily'));
COMMIT;

Dopo il COMMIT, chiedi a Postgres se il lock c’è ancora:

SELECT l.pid, l.mode, l.granted, l.classid, l.objid
FROM pg_locks l
WHERE l.locktype = 'advisory'
  AND l.pid = pg_backend_pid();

Atteso: almeno una riga granted = true. Il COMMIT non ha liberato il lock: è normale per la variante session-scoped.

Immagina ora PgBouncer in transaction pooling (o un pool equivalente): dopo il COMMIT la tua applicazione restituisce al pool il socket verso Postgres. Il processo backend (pid sopra) spesso resta lo stesso per il prossimo client che ottiene quella connessione server. Il lock advisory è ancora attaccato a quel pid: il prossimo worker che ottiene quel backend eredita un lock che non ha mai richiesto. Sintomi: serializzazione “fantasma”, starvation, o due team diversi che condividono comportamenti impossibili da riprodurre in locale senza pool.

Perché pg_advisory_xact_lock è la leva pratica: rilascia il lock a fine transazione, quindi il backend torna “pulito” per il prossimo affittuario del pool dopo ogni COMMIT/ROLLBACK, che è esattamente il ciclo di vita che transaction pooling assume.

Nota: in session pooling PgBouncer una connessione client mappa 1:1 su un backend per tutta la vita: il leak cross-client è meno frequente, ma resta il problema del lock dimenticato (pg_advisory_unlock mancante) fino a disconnect. In transaction mode il cross-affitto del backend è il caso d’uso quotidiano.

Documentazione utile: PgBouncer features — pooling mode (transaction vs session).

FOR UPDATE sul ledger: quando lock e stato devono coincidere

Se la serializzazione deve essere la stessa transazione che aggiorna claim_count / stato, spesso si usa una riga di ledger:

BEGIN;

SELECT job_key, claim_count
FROM job_ledger
WHERE job_key = 'report-daily'
FOR UPDATE;

UPDATE job_ledger
SET claim_count = claim_count + 1, updated_at = now()
WHERE job_key = 'report-daily';

COMMIT;

Qui il lock è la riga che stai mutando: niente “prima advisory, poi update” con una finestra concettuale in mezzo.

SKIP LOCKED: perché non è la serializzazione che cerchi

SKIP LOCKED serve a non attendere righe già bloccate: la sessione che legge prosegue subito senza quelle righe. Per un job con chiave nota che deve essere elaborato al più da un worker alla volta, questo è spesso l’opposto di quello che vuoi: vuoi che il secondo worker aspetti, non che “non veda” il lavoro.

Sessione A

BEGIN;
SELECT job_key FROM job_ledger WHERE job_key = 'report-daily' FOR UPDATE;
-- non fare COMMIT ancora

Sessione B — prima senza skip:

BEGIN;
SELECT job_key FROM job_ledger WHERE job_key = 'report-daily' FOR UPDATE;

Comportamento atteso: blocco finché A non committa. Serializzazione classica.

Fai ROLLBACK su B, poi ripeti B con:

BEGIN;
SELECT job_key FROM job_ledger WHERE job_key = 'report-daily' FOR UPDATE SKIP LOCKED;

Comportamento atteso: la query ritorna subito con zero righe (non c’è un’altra riga “libera” da restituire): il worker interpreta “nessun job” mentre in realtà il job c’è ed è solo bloccato. Per idempotenza su una chiave, questo è la trappola.

Lo escludiamo dal pattern “outbox / claim pool” non per snobismo: lì vuoi proprio saltare righe già prese da altri worker su insieme di job; qui stiamo parlando di una chiave nota che deve essere mutuamente esclusa, non di “pesca la prossima riga libera”.

Query di monitoring pronta (advisory in attesa oltre N secondi)

Obiettivo: alimentare un alert (“c’è un backend fermo da troppo tempo in attesa di advisory”). La query unisce pg_locks (lock non concesso) a pg_stat_activity e, tramite le colonne identificative del lock, al bloccante che detiene il lock granted.

Soglia di esempio: 30 secondi — parametrica in produzione.

SELECT
  blocked_a.pid AS blocked_pid,
  now() - blocked_a.query_start AS blocked_since,
  blocked_a.state AS blocked_state,
  blocked_a.wait_event_type,
  blocked_a.wait_event,
  left(blocked_a.query, 200) AS blocked_query_snippet,
  blocking_a.pid AS blocking_pid,
  now() - blocking_a.query_start AS blocking_query_age,
  left(blocking_a.query, 200) AS blocking_query_snippet
FROM pg_catalog.pg_locks blocked_l
JOIN pg_catalog.pg_stat_activity blocked_a ON blocked_a.pid = blocked_l.pid
JOIN pg_catalog.pg_locks blocking_l
  ON blocking_l.locktype = blocked_l.locktype
 AND blocking_l.database IS NOT DISTINCT FROM blocked_l.database
 AND blocking_l.relation IS NOT DISTINCT FROM blocked_l.relation
 AND blocking_l.page IS NOT DISTINCT FROM blocked_l.page
 AND blocking_l.tuple IS NOT DISTINCT FROM blocked_l.tuple
 AND blocking_l.virtualxid IS NOT DISTINCT FROM blocked_l.virtualxid
 AND blocking_l.transactionid IS NOT DISTINCT FROM blocked_l.transactionid
 AND blocking_l.classid IS NOT DISTINCT FROM blocked_l.classid
 AND blocking_l.objid IS NOT DISTINCT FROM blocked_l.objid
 AND blocking_l.objsubid IS NOT DISTINCT FROM blocked_l.objsubid
 AND blocking_l.pid <> blocked_l.pid
JOIN pg_catalog.pg_stat_activity blocking_a ON blocking_a.pid = blocking_l.pid
WHERE NOT blocked_l.granted
  AND blocking_l.granted
  AND blocked_l.locktype = 'advisory'
  AND now() - blocked_a.query_start > interval '30 seconds';

Cosa guardare per colonna (per tuning alert):

• blocked_pid: backend Postgres del worker in attesa.
• blocked_since: da quanto quella query è in esecuzione — utile come proxy della durata dell’attesa sul lock.
• blocked_state / wait_event_type / wait_event: in attesa su lock tipicamente active + Lock + evento advisory / transactionid a seconda del piano; utile per distinguere attese su lock da I/O.
• blocked_query_snippet: testo troncato della query in esecuzione (attenzione a PII nei log).
• blocking_pid: chi detiene il lock advisory concesso.
• blocking_query_snippet: cosa sta facendo il detentore — spesso è la transazione lunga da ottimizzare o da limitare nel timeout.

In ambienti molto rumorosi filtra ulteriormente su classid = 7742 se usi solo quella classe per i job.

Cosa resta fuori

Patroni, fail-over, ORM, LISTEN/NOTIFY, pattern outbox con SKIP LOCKED su tabella di claim — sono pezzi giusti ma altre storie.

Chiusura

La domanda utile non è “advisory o FOR UPDATE?” da tabella astratta, ma: il mio lock sopravvive al COMMIT in modo che il pool lo regali al prossimo client? e il mio worker interpreta “nessuna riga” come “nessun lavoro” quando in realtà il lavoro è solo bloccato? Se porti in produzione pg_advisory_lock con transaction pooling senza esserne consapevole, il bug è silenzioso finché non misuri contention o incroci casuali tra tenant/job.

Riferimenti

• Advisory locks
• Funzioni: pg_advisory_lock / pg_advisory_xact_lock
• pg_locks, pg_stat_activity
• PgBouncer pooling modes