Blocchi advisory di PostgreSQL per flussi di lavoro sicuri in concorrenza

Il problema reale: due processi fanno lo stesso lavoro

Il doppio-processing accade quando lo stesso elemento viene gestito due volte perché due attori diversi pensano entrambi di essere responsabili. Nelle app reali si manifesta come un cliente che viene addebitato due volte, un’approvazione applicata due volte o una email “fattura pronta” inviata due volte. In test tutto può sembrare a posto, poi fallare sotto traffico reale.

Succede di solito quando i tempi diventano stretti e più cose possono agire contemporaneamente:

Due worker prendono lo stesso job nello stesso momento. Un retry parte perché una chiamata di rete è lenta, ma il primo tentativo è ancora in esecuzione. Un utente clicca due volte Approva perché l’interfaccia si è bloccata un secondo. Due scheduler si sovrappongono dopo un deploy o per drift dell’orologio. Anche un solo tap può diventare due richieste se un’app mobile ritenta dopo un timeout.

La parte dolorosa è che ogni attore si comporta “ragionevolmente” singolarmente. Il bug è il gap tra loro: nessuno sa che l’altro sta già processando lo stesso record.

L’obiettivo è semplice: per un dato elemento (un ordine, una richiesta di approvazione, una fattura) deve esserci un solo attore che esegue il lavoro critico alla volta. Gli altri dovrebbero attendere brevemente o rinunciare e riprovare.

I blocchi advisory di PostgreSQL possono aiutare. Offrono un modo leggero per dire “sto lavorando sull’elemento X” usando il database che già usi per la consistenza.

Metti però delle aspettative: un lock non è un sistema di code completo. Non pianifica job per te, non garantisce l’ordinamento né conserva messaggi. È una sbarra di sicurezza intorno alla parte del workflow che non deve mai essere eseguita due volte.

Cosa sono (e non sono) i blocchi advisory di PostgreSQL

I blocchi advisory di PostgreSQL servono a garantire che un solo worker esegua una parte di lavoro alla volta. Scegli una chiave di lock (per esempio “fattura 123”), chiedi al database di bloccarla, fai il lavoro e poi la rilasci.

La parola “advisory” è importante. Postgres non capisce il significato della tua chiave e non protegge nulla automaticamente. Tiene solo traccia di un fatto: la chiave è bloccata o non lo è. Il tuo codice deve mettersi d’accordo sul formato della chiave e deve prendere il lock prima di eseguire la parte rischiosa.

Conviene anche confrontare i blocchi advisory con i row lock. I row lock (come SELECT ... FOR UPDATE) proteggono righe reali di una tabella. Sono ottimi quando il lavoro mappa chiaramente a una riga. Gli advisory lock proteggono una chiave scelta da te, utile quando il workflow tocca molte tabelle, chiama servizi esterni o parte prima che una riga esista.

I blocchi advisory sono utili quando hai bisogno di:

• Azioni one-at-a-time per entità (una approvazione per richiesta, un addebito per fattura)
• Coordinamento tra più server senza aggiungere un servizio di locking separato
• Protezione intorno a uno step di workflow più grande di un singolo aggiornamento di riga

Non sono un sostituto per altri strumenti di sicurezza. Non rendono le operazioni idempotenti, non applicano regole di business e non impediranno duplicati se un percorso di codice dimentica di prendere il lock.

Spesso si chiamano “leggeri” perché puoi usarli senza modifiche allo schema o infrastrutture extra. In molti casi puoi risolvere il doppio-processing aggiungendo una chiamata di lock intorno a una sezione critica mantenendo il resto del design uguale.

Tipi di lock che userai davvero

Quando si parla di “blocchi advisory di PostgreSQL” ci si riferisce solitamente a poche funzioni. Scegliere quella giusta cambia cosa succede in caso di errori, timeout e retry.

Session vs transaction locks

Un lock a livello di sessione (pg_advisory_lock) dura finché la connessione al database è aperta. Questo può essere comodo per worker long-running, ma significa anche che un lock può rimanere se la tua app crasha lasciando una connessione pooled appesa.

Un lock a livello di transazione (pg_advisory_xact_lock) è legato alla transazione corrente. Quando fai commit o rollback, PostgreSQL lo rilascia automaticamente. Per la maggior parte dei workflow request-response (approvazioni, click di fatturazione, azioni admin) questa è la scelta più sicura perché è difficile dimenticarsi di rilasciarlo.

Blocking vs try-lock

Le chiamate bloccanti aspettano che il lock sia disponibile. Semplice, ma può far sembrare una richiesta web bloccata se un’altra sessione tiene il lock.

Le try-lock ritornano immediatamente:

• pg_try_advisory_lock (session-level)
• pg_try_advisory_xact_lock (transaction-level)

La try-lock è spesso migliore per azioni UI. Se il lock è preso, puoi restituire un messaggio chiaro come “Già in elaborazione” e chiedere all’utente di riprovare.

Shared vs exclusive

I lock esclusivi sono “uno alla volta”. I lock condivisi permettono più holder ma bloccano un esclusivo. La maggior parte dei problemi di doppio-processing usa lock esclusivi. I lock condivisi sono utili quando molti lettori possono procedere, ma uno scrittore raro deve eseguire da solo.

Come vengono rilasciati i lock

Il rilascio dipende dal tipo:

• Session locks: rilasciati alla disconnessione o esplicitamente con pg_advisory_unlock
• Transaction locks: rilasciati automaticamente alla fine della transazione

Scegliere la chiave giusta

Un advisory lock funziona solo se ogni worker prova a bloccare esattamente la stessa chiave per la stessa unità di lavoro. Se un percorso blocca “fattura 123” e un altro blocca “cliente 45”, puoi comunque ottenere duplicati.

Inizia definendo la “cosa” da proteggere. Rendila concreta: una fattura, una richiesta di approvazione, una run schedulata, o il ciclo di fatturazione mensile di un cliente. Quella scelta decide quanta concorrenza permetti.

Scegli uno scope che corrisponda al rischio

La maggior parte dei team finisce con uno di questi approcci:

• Per record: più sicuro per approvazioni e fatture (lock su invoice_id o request_id)
• Per cliente/account: utile quando le azioni devono essere serializzate per cliente (fatturazione, variazioni di credito)
• Per step di workflow: quando step diversi possono correre in parallelo, ma ogni step deve essere one-at-a-time

Considera lo scope come una decisione di prodotto, non un dettaglio di database. “Per record” previene doppio-click che addebitano due volte. “Per cliente” previene che due job background generino statement sovrapposti.

Scegli una strategia di chiave stabile

Hai generalmente due opzioni: due interi a 32 bit (spesso namespace + id), o un intero a 64 bit (bigint), talvolta creato hashando una stringa ID.

Le chiavi a due interi sono facili da standardizzare: scegli un numero di namespace fisso per workflow (per esempio approvazioni vs fatturazione) e usa l’ID del record come secondo valore.

L’hashing è utile quando l’identificatore è un UUID, ma devi accettare un piccolo rischio di collisione e essere coerente ovunque.

Qualunque scelta fai, documenta il formato e centralizzalo. “Quasi la stessa chiave” in due posti è un modo comune per reintrodurre duplicati.

Passo dopo passo: un pattern sicuro per l’elaborazione one-at-a-time

Un buon workflow con advisory lock è semplice: lock, verifica, agisci, registra, commit. Il lock non è la regola di business da solo. È una guardia che rende la regola affidabile quando due worker colpiscono lo stesso record contemporaneamente.

Un pattern pratico:

1. Apri una transazione quando il risultato deve essere atomico.
2. Acquisisci il lock per l’unità di lavoro specifica. Preferisci un lock a livello di transazione (pg_advisory_xact_lock) così si rilascia automaticamente.
3. Ricontrolla lo stato nel database. Non dare per scontato di essere il primo. Conferma che il record è ancora idoneo.
4. Esegui il lavoro e scrivi un marcatore “fatto” durevole nel database (aggiornamento stato, voce di ledger, riga di audit).
5. Commit e lascia che il lock venga rilasciato. Se hai usato un lock di sessione, fai l’unlock prima di restituire la connessione al pool.

Esempio: due server ricevono “Approva fattura #123” nello stesso secondo. Entrambi iniziano, ma solo uno ottiene il lock per 123. Il vincitore controlla che la fattura #123 sia ancora pending, la marca approved, scrive l’audit/pagamento e fa commit. Il secondo server o fallisce velocemente (try-lock) o aspetta e poi ricontrolla vedendo che è già approvata ed esce senza duplicare nulla. In entrambi i casi eviti il doppio-processing mantenendo l’UI reattiva.

Per il debugging, registra abbastanza informazioni per tracciare ogni tentativo: request id, approval id e chiave di lock calcolata, actor id, esito (lock_busy, already_approved, approved_ok) e tempi.

Dove si inseriscono i blocchi advisory: approvazioni, fatturazione, scheduler

I blocchi advisory funzionano meglio quando la regola è semplice: per una cosa specifica, solo un processo può fare il lavoro “vincente” alla volta. Mantieni il database e il codice applicativo esistenti, ma aggiungi una piccola sbarra che rende le race condition molto più difficili da innescare.

Approvazioni

Le approvazioni sono trappole classiche di concorrenza. Due revisori (o la stessa persona che clicca due volte) possono premere Approva nel giro di millisecondi. Con un lock keying sull’ID della richiesta, solo una transazione esegue la modifica di stato. Gli altri apprenderanno rapidamente l’esito e potranno mostrare un messaggio chiaro come “già approvato” o “già rifiutato”.

Questo è comune nei portali clienti e nelle console admin dove molte persone osservano la stessa coda.

Fatturazione

La fatturazione spesso richiede regole più rigide: un tentativo di pagamento per fattura, anche quando ci sono retry. Un timeout di rete può spingere l’utente a cliccare Pay di nuovo, o un retry background può partire mentre il primo tentativo è ancora in corso.

Un lock sulla invoice ID garantisce che solo un percorso parli con il provider di pagamento alla volta. Il secondo tentativo può ritornare “pagamento in corso” o leggere lo stato di pagamento più aggiornato. Questo evita lavoro duplicato e riduce il rischio di addebiti doppi.

Scheduler e worker background

In setup multi-instance, gli scheduler possono accidentalmente eseguire la stessa finestra in parallelo. Un lock su nome job più finestra temporale (per esempio daily-settlement:2026-01-29) garantisce che solo un’istanza la esegua.

Lo stesso approccio funziona per worker che prelevano elementi da una tabella: blocca sull’ID dell’item così solo un worker lo processa.

Chiavi comuni includono l’ID di una richiesta di approvazione, l’ID di una fattura, il nome del job più la finestra, l’ID cliente per “una esportazione alla volta” o una chiave di idempotenza unica per i retry.

Un esempio realistico: fermare le doppie approvazioni in un portale

Immagina una richiesta di approvazione in un portale: un ordine d’acquisto è in attesa e due manager cliccano Approva nello stesso secondo. Senza protezione, entrambe le richieste possono leggere “pending” e scrivere “approved”, creando voci di audit duplicate, notifiche duplicate o lavoro downstream scatenato due volte.

I blocchi advisory di PostgreSQL offrono un modo diretto per rendere quell’azione one-at-a-time per ogni approvazione.

Il flusso

Quando l’API riceve un’azione di approvazione, prima prende un lock basato sull’id di approvazione (così approvazioni diverse possono essere processate in parallelo).

Un pattern comune è: lock su approval_id, leggi lo stato corrente, aggiorna lo stato, poi scrivi un record di audit, tutto in una transazione.

BEGIN;

-- One-at-a-time per approval_id
SELECT pg_try_advisory_xact_lock($1) AS got_lock;  -- $1 = approval_id

-- If got_lock = false, return "someone else is approving, try again".

SELECT status FROM approvals WHERE id = $1 FOR UPDATE;

-- If status != 'pending', return "already processed".

UPDATE approvals
SET status = 'approved', approved_by = $2, approved_at = now()
WHERE id = $1;

INSERT INTO approval_audit(approval_id, actor_id, action, created_at)
VALUES ($1, $2, 'approved', now());

COMMIT;

Cosa vive il secondo click

La seconda richiesta o non ottiene il lock (quindi risponde rapidamente “già in elaborazione”) o ottiene il lock dopo che il primo ha finito, poi vede che lo stato è già approved ed esce senza cambiare nulla. In entrambi i casi eviti il doppio-processing mantenendo l’UI reattiva.

Per il debugging, logga request id, approval id e chiave calcolata, actor id, esito (lock_busy, already_approved, approved_ok) e tempi.

Gestire attese, timeout e retry senza bloccare l’app

Aspettare un lock sembra innocuo finché non si trasforma in un bottone che gira, un worker bloccato o un arretrato che non si smaltisce. Quando non riesci a ottenere il lock, fai fail-fast dove c’è un umano in attesa e aspetta solo dove l’attesa è sicura.

Per azioni utente: try-lock e risposte chiare

Se qualcuno clicca Approva o Paga, non bloccare la richiesta per secondi. Usa la try-lock così l’app può rispondere subito.

Un approccio pratico: prova a prendere il lock e, se fallisce, restituisci una chiara risposta “occupato, riprova” (o aggiorna lo stato dell’elemento). Questo riduce i timeout e scoraggia i clic ripetuti.

Mantieni corta la sezione bloccata: valida lo stato, applica la modifica, fai commit.

Per job background: bloccare va bene, ma metti un limite

Per scheduler e worker, bloccare può andare bene perché nessun umano aspetta. Ma servono comunque limiti, altrimenti un job lento può bloccare tutta la flotta.

Usa timeout così un worker può rinunciare e andare avanti:

SET lock_timeout = '2s';
SET statement_timeout = '30s';
SELECT pg_advisory_lock(123456);

Imposta anche un runtime massimo atteso per il job. Se billing di solito finisce in meno di 10 secondi, considera 2 minuti come incidente. Traccia tempo di inizio, job id e quanto tempo i lock vengono tenuti. Se il tuo runner supporta la cancellazione, cancella i task che superano il limite così la sessione termina e il lock viene rilasciato.

Pianifica i retry intenzionalmente. Quando non si acquisisce il lock, decidi cosa succede dopo: riprogrammare con backoff (e un po’ di random), saltare lavoro best-effort per questo ciclo, o segnare l’item come conteso se i fallimenti si ripetono e richiedono attenzione.

Errori comuni che causano lock bloccati o duplicati

La sorpresa più comune sono i lock di sessione che non vengono mai rilasciati. I connection pool mantengono connessioni aperte, quindi una sessione può vivere più a lungo di una richiesta. Se acquisisci un lock di sessione e dimentichi di fare l’unlock, il lock può rimanere fino al riciclo della connessione. Altri worker aspetteranno (o falliranno) ed è difficile capire perché.

Un’altra fonte di duplicati è prendere il lock ma non ricontrollare lo stato. Un lock assicura solo che un worker esegua la sezione critica alla volta. Non garantisce che il record sia ancora idoneo. Ricontrolla sempre dentro la stessa transazione (per esempio conferma pending prima di passare a approved).

Le chiavi di lock confondono spesso i team. Se un servizio blocca su order_id e un altro su una chiave calcolata diversamente per la stessa risorsa reale, ora hai due lock. Entrambi i percorsi possono correre contemporaneamente, creando una falsa sensazione di sicurezza.

Tenere i lock a lungo è di solito autoinflitto. Se fai chiamate di rete lente mentre tieni il lock (payment provider, email/SMS, webhook), una piccola guardia diventa un collo di bottiglia. Mantieni la sezione bloccata focalizzata su lavoro veloce sul database: valida lo stato, scrivi il nuovo stato, registra cosa succede dopo. Poi attiva gli effetti collaterali dopo il commit.

Infine, i blocchi advisory non sostituiscono idempotenza o vincoli di database. Trattali come un semaforo, non come una prova assoluta. Usa vincoli unici dove possibile e chiavi di idempotenza per chiamate esterne.

Checklist rapida prima di mandare in produzione

Tratta i blocchi advisory come un piccolo contratto: tutti nel team dovrebbero sapere cosa significa il lock, cosa protegge e cosa è permesso fare mentre è tenuto.

Una checklist breve che intercetta la maggior parte dei problemi:

• Una chiave di lock chiara per risorsa, documentata e riutilizzata ovunque
• Acquisire il lock prima di qualsiasi azione irreversibile (pagamenti, email, chiamate API esterne)
• Ricontrollare lo stato dopo aver acquisito il lock e prima di scrivere cambiamenti
• Tenere la sezione bloccata breve e misurabile (logga attesa del lock e tempo di esecuzione)
• Decidere cosa significa lock busy per ogni percorso (messaggio UI, retry con backoff, saltare)

Prossimi passi: applica il pattern e mantienilo gestibile

Scegli un posto in cui i duplicati fanno più male e inizia da lì. Buoni obiettivi iniziali sono azioni che costano soldi o cambiano stato in modo permanente, come “addebita fattura” o “approva richiesta”. Racchiudi solo quella sezione critica con un advisory lock, poi estendi ad altri step quando ti fidi del comportamento.

Aggiungi osservabilità di base presto. Logga quando un worker non ottiene il lock e quanto durano i lavori bloccati. Se le attese aumentano, di solito significa che la sezione critica è troppo grande o che una query lenta si nasconde dentro.

I lock funzionano meglio sopra la sicurezza dei dati, non al posto sua. Mantieni campi di stato chiari (pending, processing, done, failed) e supportali con vincoli quando possibile. Se un retry capita nel momento peggiore, un vincolo unico o una chiave di idempotenza può essere la seconda linea di difesa.

Se stai costruendo workflow in AppMaster (appmaster.io), puoi applicare lo stesso pattern mantenendo la modifica di stato critica dentro una transazione e aggiungendo un piccolo step SQL per prendere un advisory lock a livello di transazione prima del passo di “finalizzazione”.

I blocchi advisory sono adatti finché non hai davvero bisogno di funzionalità di coda (priorità, job ritardati, dead-letter), hai forte contention e ti serve parallelismo più intelligente, devi coordinare tra database senza un Postgres condiviso, o necessiti regole di isolamento più stringenti. L’obiettivo è affidabilità noiosa: mantieni il pattern piccolo, coerente, visibile nei log e supportato da vincoli.