Transazioni Postgres per workflow a più passaggi: pattern pratici

Perché gli aggiornamenti multi-step spesso diventano incoerenti

La maggior parte delle funzionalità reali non sono una singola query al database. Sono una breve catena: inserire una riga, aggiornare un saldo, segnare uno stato, scrivere un record di audit, magari accodare un job. Una scrittura parziale avviene quando solo alcuni di questi passaggi arrivano al database.

Questo si manifesta quando qualcosa interrompe la catena: un errore del server, un timeout tra la tua app e Postgres, un crash dopo il passo 2 o un retry che riesegue il passo 1. Ogni istruzione va bene da sola. Il workflow si rompe quando si ferma a metà.

Di solito lo noti rapidamente:

• Una riga esiste, ma una riga correlata manca (ordine creato, nessun articolo)
• I soldi sono stati mossi, ma lo stato non è cambiato (pagato, ancora segnato come non pagato)
• Due record che dovrebbero essere uno (sottoscrizioni duplicate dopo un retry)
• Flag che non concordano (l'utente è "active" ma non ha un piano)
• Stati che compaiono solo sotto carico o errori

Un esempio concreto: un upgrade di piano aggiorna il piano del cliente, aggiunge un record di pagamento e aumenta i crediti disponibili. Se l'app va in crash dopo aver salvato il pagamento ma prima di aggiungere i crediti, il supporto vede "pagato" in una tabella e "nessun credito" in un'altra. Se il client ritenta, potresti persino registrare il pagamento due volte.

L'obiettivo è semplice: tratta il workflow come un singolo interruttore. O ogni passo riesce, o nessuno riesce, così non salvi mai lavoro mezzi fatto.

Le transazioni, in parole semplici

Una transazione è il modo del database di dire: tratta questi passi come un'unità di lavoro. O tutte le modifiche avvengono, o nessuna. Questo è importante ogni volta che il tuo workflow richiede più di un aggiornamento, come creare una riga, aggiornare un saldo e scrivere un record di audit.

Pensa al trasferimento di denaro tra due conti. Devi sottrarre dal Conto A e aggiungere al Conto B. Se l'app va in crash dopo il primo passo, non vuoi che il sistema "ricordi" solo la sottrazione.

Commit vs rollback

Quando fai commit, dici a Postgres: mantieni tutto quello che ho fatto in questa transazione. Tutte le modifiche diventano permanenti e visibili alle altre sessioni.

Quando fai rollback, dici a Postgres: dimentica tutto quello che ho fatto in questa transazione. Postgres annulla le modifiche come se la transazione non fosse mai esistita.

Cosa garantisce Postgres (e cosa no)

Dentro una transazione, Postgres garantisce che non esporrai risultati a metà lavoro alle altre sessioni prima del commit. Se qualcosa fallisce e fai rollback, il database pulisce le scritture di quella transazione.

Una transazione non risolve una cattiva progettazione del workflow. Se sottrai l'importo sbagliato, usi l'user ID sbagliato o salti un controllo necessario, Postgres committerà comunque il risultato errato. Le transazioni inoltre non prevengono automaticamente ogni conflitto a livello di business (come la vendita eccessiva di inventario) a meno che non le affianchi ai vincoli giusti, lock o al giusto livello di isolamento.

Workflow che dovrebbero essere raggruppati

Ogni volta che aggiorni più di una tabella (o più righe) per completare una singola azione del mondo reale, hai un candidato per una transazione. Il punto resta: o tutto è fatto, o niente lo è.

Il flusso di un ordine è il caso classico. Potresti creare una riga ordine, riservare inventario, prendere un pagamento e poi segnare l'ordine come pagato. Se il pagamento riesce ma l'aggiornamento dello stato fallisce, hai denaro catturato con un ordine che sembra ancora non pagato. Se la riga ordine viene creata ma lo stock non è riservato, puoi vendere articoli che in realtà non hai.

L'onboarding utente si rompe nello stesso modo. Creare l'utente, inserire un record profilo, assegnare ruoli e registrare che va inviato un'email di benvenuto sono un'unica azione logica. Senza raggruppamento puoi ritrovarti con un utente che può effettuare il login ma non ha permessi, o un profilo che esiste senza utente.

Le operazioni di back-office spesso richiedono comportamento rigoroso "traccia + cambio di stato". Approvare una richiesta, scrivere una voce di audit e aggiornare un saldo devono avere successo insieme. Se il saldo cambia ma il log di audit manca, perdi la prova di chi ha cambiato cosa e perché.

Anche i job in background ne beneficiano, soprattutto quando processi un item con più passaggi: rivendica l'item per evitare che due worker lo facciano, applica l'aggiornamento di business, registra un risultato per report e retry, poi marca l'item come fatto (o fallito con motivo). Se questi passi si separano, retry e concorrenza generano confusione.

Progetta il workflow prima di scrivere SQL

Le funzionalità multi-step si rompono quando le tratti come un insieme di aggiornamenti indipendenti. Prima di aprire un client del database, scrivi il workflow come una breve storia con un chiaro traguardo: cosa conta esattamente come "fatto" per l'utente?

Inizia elencando i passi in linguaggio semplice, poi definisci una singola condizione di successo. Per esempio: "L'ordine è creato, l'inventario è riservato e l'utente vede un numero di conferma d'ordine." Qualsiasi cosa al di sotto di questo non è successo, anche se alcune tabelle sono state aggiornate.

Poi separa nettamente il lavoro sul database dal lavoro esterno. I passi di database sono quelli che puoi proteggere con transazioni. Le chiamate esterne come pagamenti con carta, invio email o chiamate a API di terze parti possono fallire in modo lento e imprevedibile, e generalmente non puoi annullarle.

Un approccio semplice alla pianificazione: separa i passi in (1) devono essere tutto-o-nulla, (2) possono avvenire dopo il commit.

Decidi cosa appartiene alla transazione

All'interno della transazione, mantieni solo i passi che devono essere coerenti insieme:

• Creare o aggiornare righe core (ordine, fattura, saldo conto)
• Riservare risorse condivise (inventario, posti, quota)
• Registrare un evento durevole "cosa fare dopo" (tabella outbox)
• Applicare regole con vincoli (chiavi uniche, foreign key)

Sposta gli effetti collaterali fuori. Ad esempio, fai il commit dell'ordine prima, poi invia l'email di conferma basandoti su un record outbox.

Scrivi le aspettative di rollback per ogni passo

Per ogni passo, descrivi cosa deve succedere se il passo successivo fallisce. "Rollback" può significare un rollback del database, oppure una azione compensativa.

Esempio: se il pagamento riesce ma la riserva dell'inventario fallisce, decidi in anticipo se rimborsare immediatamente, o segnare l'ordine come "pagamento catturato, in attesa di stock" e gestirlo in modo asincrono.

Passo dopo passo: avvolgere un workflow in una transazione

Una transazione dice a Postgres: tratta questi passi come un'unità. O tutti accadono, o nessuno. Questo è il modo più semplice per prevenire scritture parziali.

Il flusso di base

Usa una sola connessione al database (una sessione) dall'inizio alla fine. Se distribuisci i passi su connessioni diverse, Postgres non può garantire il risultato tutto-o-nulla.

La sequenza è semplice: begin, esegui le letture e le scritture necessarie, fai commit se tutto va bene, altrimenti rollback e restituisci un errore chiaro.

Ecco un esempio minimo in SQL:

BEGIN;

-- reads that inform your decision
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;

-- writes that must stay together
UPDATE accounts SET balance = balance - 50 WHERE id = 42;
INSERT INTO ledger(account_id, amount, note) VALUES (42, -50, 'Purchase');

COMMIT;

-- on error (in code), run:
-- ROLLBACK;

Mantienila breve (e facilmente debugabile)

Le transazioni tengono lock mentre girano. Più le tieni aperte, più blocchi altro lavoro e più è probabile incorrere in timeout o deadlock. Fai dentro alla transazione l'essenziale e sposta fuori i task lenti (invio email, chiamate ai provider di pagamento, generazione PDF).

Quando qualcosa fallisce, registra contesto sufficiente per riprodurre il problema senza esporre dati sensibili: nome del workflow, order_id o user_id, parametri chiave (importo, valuta) e il codice di errore Postgres. Evita di loggare payload completi, dati di carta o dettagli personali.

Nozioni base di concorrenza: lock e isolamento senza il gergo

La concorrenza sono semplicemente due cose che succedono nello stesso momento. Immagina due clienti che cercano di comprare l'ultimo biglietto del concerto. Entrambi vedono "1 rimasto", entrambi cliccano Pay, e ora la tua app deve decidere chi lo prende.

Senza protezione, entrambe le richieste possono leggere lo stesso valore vecchio e scrivere un aggiornamento. Così ottieni inventario negativo, prenotazioni duplicate o un pagamento senza ordine.

I lock di riga sono il guardrail più semplice. Blocchi la riga specifica che stai per modificare, fai i controlli, poi la aggiorni. Le altre transazioni che toccano la stessa riga devono aspettare fino al tuo commit o rollback, evitando aggiornamenti doppi.

Un pattern comune: inizia una transazione, seleziona la riga inventario con FOR UPDATE, verifica che ci sia stock, decrementalo, poi inserisci l'ordine. Quello "tiene la porta" mentre completi i passi critici.

I livelli di isolamento controllano quanta sovrapposizione di risultati strani permetti dalle transazioni concorrenti. Il compromesso è solitamente sicurezza vs velocità:

• Read Committed (default): veloce, ma puoi vedere cambiamenti commessi da altri tra le istruzioni.
• Repeatable Read: la tua transazione vede uno snapshot stabile, buono per letture coerenti, può richiedere più retry.
• Serializable: massima sicurezza, Postgres può abortire una transazione per mantenere i risultati come se fossero eseguiti uno per volta.

Mantieni i lock brevi. Se una transazione resta aperta mentre chiami un'API esterna o aspetti un'azione dell'utente, creerai attese lunghe e timeout. Preferisci una chiara via di fallimento: imposta un lock timeout, cattura l'errore e rispondi "per favore ritenta" invece di lasciare richieste in sospeso.

Se devi fare lavoro fuori dal database (come addebitare una carta), spezza il workflow: riserva rapidamente, committa, poi fai la parte lenta e finalizza con un'altra breve transazione.

Retry che non creano duplicati

I retry sono normali nelle app con Postgres. Una richiesta può fallire anche quando il codice è corretto: deadlock, statement timeout, brevi cadute di rete o un errore di serializzazione con livelli di isolamento più alti. Se riesegui semplicemente lo stesso handler, rischi di creare un secondo ordine, addebitare due volte o inserire righe "evento" duplicate.

La soluzione è l'idempotenza: l'operazione dovrebbe essere sicura da eseguire due volte con lo stesso input. Il database dovrebbe essere in grado di riconoscere "questa è la stessa richiesta" e rispondere in modo coerente.

Un pattern pratico è allegare una chiave di idempotenza (spesso un request_id generato dal client) a ogni workflow multi-step e memorizzarla sul record principale, poi aggiungere un vincolo unico su quella chiave.

Per esempio: nel checkout, genera request_id quando l'utente clicca Pay, poi inserisci l'ordine con quel request_id. Se avviene un retry, il secondo tentativo urta il vincolo unico e restituisci l'ordine esistente invece di crearne uno nuovo.

Ciò che conta di solito:

• Usa un vincolo unico su (request_id) o (user_id, request_id) per bloccare duplicati.
• Quando il vincolo è violato, recupera la riga esistente e ritorna lo stesso risultato.
• Fai sì che gli effetti collaterali seguano la stessa regola: un solo payment intent per ordine, un solo evento "ordine confermato" per ordine.
• Registra il request_id così il supporto può tracciare cosa è successo.

Tieni il loop di retry fuori dalla transazione. Ogni tentativo dovrebbe aprire una nuova transazione e rieseguire l'unità di lavoro dall'inizio. Ritentare dentro una transazione fallita non aiuta perché Postgres la marca come aborted.

Un piccolo esempio: la tua app cerca di creare un ordine e riservare inventario, ma va in timeout subito dopo il COMMIT. Il client ritenta. Con una chiave di idempotenza, la seconda richiesta restituisce l'ordine già creato e salta una seconda prenotazione invece di raddoppiare il lavoro.

Usa il database per far rispettare le regole, non solo il codice

Le transazioni tengono insieme un workflow multi-step, ma non rendono automaticamente i dati corretti. Un modo solido per evitare gli effetti delle scritture parziali è rendere gli stati "sbagliati" difficili o impossibili nel database, anche se un bug si infiltra nel codice.

Inizia con le protezioni di base. Le foreign key assicurano che i riferimenti siano reali (una riga di dettaglio non può puntare a un ordine mancante). NOT NULL impedisce righe mezze compilate. I vincoli CHECK catturano valori che non hanno senso (per esempio, quantity > 0, total_cents >= 0). Queste regole girano ad ogni scrittura, qualunque servizio o script tocchi il database.

Per workflow più lunghi, modella esplicitamente i cambi di stato. Invece di molte flag booleane, usa una sola colonna status (pending, paid, shipped, canceled) e permetti solo transizioni valide. Puoi far rispettare questo con vincoli o trigger così il database rifiuta salti illegali come shipped -> pending.

L'unicità è un'altra forma di correttezza. Aggiungi vincoli unici dove i duplicati rompono il workflow: order_number, invoice_number o un idempotency_key usato per i retry. Allora, se l'app ritenta la stessa richiesta, Postgres blocca il secondo insert e puoi restituire "già processato" invece di creare un secondo ordine.

Quando hai bisogno di tracciabilità, registrala esplicitamente. Una tabella di audit (o history) che registra chi ha cambiato cosa e quando trasforma "aggiornamenti misteriosi" in fatti che puoi interrogare durante gli incidenti.

Errori comuni che causano scritture parziali

La maggior parte delle scritture parziali non è causata da "SQL sbagliato." Vengono da decisioni sul workflow che rendono facile committare solo metà storia.

Le trappole che compaiono nelle app reali

• Eseguire lavoro esterno lento mentre la transazione è aperta. Chiamare un provider di pagamento, inviare un'email o caricare un file dentro la transazione mantiene i lock più a lungo del necessario. Se l'API è lenta o va in timeout, altri utenti si accodano dietro la tua transazione aperta.
• Leggere fuori dalla transazione, poi scrivere dopo. Esempio: leggi il saldo di un utente, lo mostri in UI, poi più tardi deduci basandoti su quel valore vecchio. Un'altra sessione potrebbe aver cambiato il saldo nel frattempo.
• Catturare un errore ma comunque committare qualcosa. Un pattern comune è "prova passo 1, prova passo 2, registra l'errore, ritorna successo comunque." Se il codice raggiunge COMMIT dopo un fallimento, hai appena reso il database incoerente apposta.
• Aggiornare tabelle in ordini diversi attraverso percorsi di codice differenti. Se una richiesta aggiorna accounts poi orders, ma un'altra fa l'inverso, aumenti la probabilità di deadlock sotto carico.
• Tenere transazioni aperte troppo a lungo. Le transazioni lunghe possono bloccare scritture, ritardare la pulizia del vacuum e creare timeout confusi.

Un esempio concreto: nel checkout riservi inventario, crei un ordine e poi addebiti una carta. Se addebiti la carta dentro la stessa transazione, potresti tenere un lock inventario mentre aspetti la rete. Se l'addebito riesce ma la transazione poi fa rollback, hai addebitato il cliente senza ordine.

Un pattern più sicuro è: tieni la transazione focalizzata sullo stato del database (riserva inventario, crea ordine, registra pagamento in pending), committa, poi chiama l'API esterna e scrivi il risultato in una nuova breve transazione. Molti team implementano questo con uno status pending e un job di background.

Checklist rapida per operazioni tutto-o-nulla

Quando un workflow ha più passi (insert, update, charge, send), l'obiettivo è semplice: o tutto viene registrato, o niente.

Confini di transazione

Tieni tutte le scritture database richieste dentro una transazione. Se un passo fallisce, fai rollback e lascia i dati esattamente come erano.

Rendi esplicita la condizione di successo. Per esempio: "L'ordine è creato, lo stock è riservato e lo stato pagamento è registrato." Qualsiasi altra cosa è un percorso di fallimento che deve abortire la transazione.

• Tutte le scritture richieste avvengono dentro un singolo blocco BEGIN ... COMMIT.
• C'è un unico stato di done nel database (non solo in memoria dell'app).
• Qualsiasi errore porta a ROLLBACK, e il chiamante riceve un risultato di fallimento chiaro.

Parafiamme di sicurezza (così i retry non fanno danni)

Assumi che la stessa richiesta possa essere ritentata. Il database dovrebbe aiutarti a far rispettare azioni "una volta sola".

• Supporta azioni one-only con vincoli unici (una riga di pagamento per ordine, una prenotazione per item per ordine).
• Rendi i retry sicuri e ripetibili (stesso input produce lo stesso stato finale, non duplicati).

Mantieni le transazioni brevi

Fai il minimo indispensabile dentro la transazione ed evita di aspettare chiamate di rete tenendo lock.

• Mantieni le transazioni brevi e imposta un timeout così non rimangono appese.
• Fai il lavoro lento (chiamate a provider di pagamento) fuori dalla transazione, poi registra il risultato in una nuova breve transazione.

Osserva i fallimenti

Se non vedi dove si rompe, continuerai a indovinare.

• Logga il passo del workflow e un request id per ogni fallimento.
• Monitora i tassi di rollback e i lock timeout per catturare i rischi di scritture parziali in anticipo.

Esempio: un flusso di checkout che resta consistente sotto errore

Un checkout ha diversi passi che dovrebbero muoversi insieme: creare l'ordine, riservare inventario, registrare il tentativo di pagamento, quindi segnare lo stato dell'ordine.

Immagina che un utente clicchi Buy per 1 articolo.

Un flusso sicuro (il lavoro DB è un'unità)

Dentro una transazione, fai solo cambiamenti al database:

• Inserisci una riga orders con status pending_payment.
• Riserva stock (per esempio, decrementa inventory.available o crea una riga reservations).
• Inserisci una riga payment_intents con un idempotency_key fornito dal client (unico).
• Inserisci una riga outbox tipo "order_created".

Se una qualunque istruzione fallisce (esaurimento stock, errore di vincolo, crash), Postgres fa rollback dell'intera transazione. Non ti ritrovi con un ordine senza riserva, o una riserva senza ordine.

E se il pagamento fallisce a metà?

Il provider di pagamento è fuori dal database, quindi trattalo come un passo separato.

Se la chiamata al provider fallisce prima del commit, abortisci la transazione e nulla viene scritto. Se la chiamata fallisce dopo il commit, esegui una nuova transazione che marca il tentativo di pagamento come fallito, rilascia la riserva e imposta lo stato dell'ordine su canceled.

Retry senza creare un secondo ordine

Fai in modo che il client invii un idempotency_key per ogni tentativo di checkout. Applicalo con un indice unico su payment_intents(idempotency_key) (o su orders se preferisci). Al retry, il codice recupera le righe esistenti e continua invece di inserire un nuovo ordine.

Email e notifiche

Non inviare email dentro la transazione. Scrivi un record outbox nella stessa transazione, poi lascia che un worker in background mandi l'email dopo il commit. In questo modo non inoltri email per un ordine che è stato rollbackato.

Prossimi passi: applicalo a un workflow questa settimana

Scegli un workflow che tocchi più di una tabella: signup + enqueue email di benvenuto, checkout + inventario, fattura + voce di ledger, o crea progetto + impostazioni di default.

Scrivi prima i passi, poi le regole che devono essere sempre vere (le tue invarianti). Esempio: "Un ordine è o completamente pagato e riservato, o non pagato e non riservato. Mai mezzo riservato." Trasforma quelle regole in un'unità tutto-o-nulla.

Un piano semplice:

• Elenca le esatte operazioni SQL in ordine (letture, insert, update, delete).
• Aggiungi prima i vincoli mancanti al database (chiavi uniche, foreign key, check constraint).
• Aggiungi una chiave di idempotenza per la richiesta così i retry non creano duplicati.
• Avvolgi i passi in una transazione e rendi esplicito il punto di successo (committa solo quando tutti i controlli passano).
• Decidi come deve comportarsi un retry sicuro (stessa idempotency key, stesso esito).

Poi testa apposta i casi brutti. Simula un crash dopo il passo 2, un timeout appena prima del commit e un double-submit dalla UI. L'obiettivo sono risultati noiosi: niente righe orfane, nessun addebito doppio, niente in sospeso per sempre.

Se stai prototipando velocemente, aiuta disegnare il workflow in uno strumento di planning prima di generare handler e schema. Per esempio, Koder.ai (koder.ai) ha una Planning Mode e supporta snapshot e rollback, il che può essere utile mentre iteri sui confini di transazione e sui vincoli.

Fallo per un workflow questa settimana. Il secondo sarà molto più veloce.