PostgreSQL: Transazioni, Livelli di Isolamento e Race Condition

C'è un momento preciso in cui si scopre che la concorrenza è difficile: quando due utenti fanno la stessa cosa nello stesso secondo e il sistema, che fino a un minuto prima sembrava funzionare, produce un risultato assurdo. Due posti venduti per un'unica disponibilità, un contatore più basso di quanto dovrebbe, un saldo scalato due volte.

Il codice è quasi sempre lineare e leggibile. Il problema è che la logica applicativa vive in un mondo concorrente, mentre il database è il punto in cui le due richieste si incontrano davvero. Questo articolo prova a mettere in fila le cose che servono per ragionarci sopra: come funziona una transazione, cosa significano i livelli di isolamento, e quali pattern usare quando l'atomicità di una singola query non basta.

Gli esempi sono costruiti a mano e i numeri servono solo a illustrare il meccanismo.

Una transazione è un'unità di lavoro

Una transazione raggruppa più operazioni in un blocco che o riesce per intero o non lascia traccia. Si apre con BEGIN, si chiude con COMMIT (le modifiche diventano visibili) o ROLLBACK (si torna indietro).

Le proprietà classiche sono quattro:

• Atomicità: o tutto o niente.
• Consistenza: i vincoli del database restano validi.
• Isolamento: due transazioni concorrenti non si vedono a metà strada (con le dovute sfumature, che sono il tema di questo articolo).
• Durabilità: a commit avvenuto, i dati sopravvivono a un crash.

L'isolamento è il punto delicato, perché non esiste una risposta unica: esiste un compromesso tra correttezza e concorrenza. Ed è esattamente quello che regolano i livelli di isolamento.

I livelli di isolamento nello standard SQL

Lo standard SQL definisce quattro livelli, ciascuno dei quali dovrebbe escludere certe anomalie ("fenomeni"). La tabella qui sotto è quella canonica, con le anomalie principali.

Vale la pena tradurre le anomalie con parole concrete:

• Dirty read: leggo dati scritti da una transazione che potrebbe ancora fare rollback. Non dovrei vederli, mai.
• Non-repeatable read: leggo la stessa riga due volte nella stessa transazione e ottengo valori diversi, perché qualcun altro ha committato in mezzo.
• Phantom read: rieseguo la stessa query con lo stesso filtro e compaiono righe nuove, come fantasmi.

A queste se ne aggiungono altre due che lo standard non elenca ma che contano nella pratica:

• Lost update: due transazioni leggono lo stesso valore, entrambe lo modificano, la seconda sovrascrive la prima.
• Write skew: due transazioni leggono un insieme di dati, ognuna modifica una parte diversa dell'insieme, e il risultato combinato viola un invariante che nessuna delle due ha violato da sola.

Cosa fa davvero PostgreSQL

Qui la teoria si allinea in parte con l'implementazione. PostgreSQL usa MVCC (Multi-Version Concurrency Control): quando aggiorni una riga, non la sovrascrivi, ne crei una nuova versione. I lettori non bloccano gli scrittori e viceversa. Questa è la ragione per cui un database con tante letture e scritture miste resta fluido senza che le query si accodino.

I livelli, in pratica:

• Read Uncommitted è accettato come parola ma trattato come Read Committed. Le dirty read in PostgreSQL non esistono.
• Read Committed è il default. Ogni statement vede uno snapshot del database preso al momento in cui lo statement inizia. Due SELECT nella stessa transazione possono quindi vedere dati diversi.
• Repeatable Read in PostgreSQL è più forte dello standard: lo snapshot è preso una volta sola, all'inizio della transazione, e resta lo stesso per tutto il suo corso. In più, se una transazione tenta di aggiornare una riga modificata da un'altra transazione committata dopo il proprio inizio, PostgreSQL non la lascia proseguire: solleva un errore di serializzazione.
• Serializable implementa SSI (Serializable Snapshot Isolation): le transazioni si comportano come se fossero eseguite una dopo l'altra, e quando questo non è possibile una delle due viene abortita con un errore che il chiamante deve gestire.

Il punto cruciale: Serializable e Repeatable Read non eliminano i conflitti, li rendono visibili. Al posto di un risultato sbagliato e silenzioso, ottieni un errore che devi saper gestire. È un miglioramento enorme, ma solo se il codice è pronto a ritentare.

Lost update: il caso del contatore

Immaginiamo una tabella con un contatore e un codice che legge, calcola e riscrive.

CREATE TABLE contatori (
    id     int PRIMARY KEY,
    valore int NOT NULL
);

INSERT INTO contatori (id, valore) VALUES (1, 10);

Il codice applicativo fa tre passi: legge, incrementa in memoria, riscrive.

-- Sessione A
BEGIN;
SELECT valore FROM contatori WHERE id = 1;   -- legge 10

-- Sessione B (nel frattempo)
BEGIN;
SELECT valore FROM contatori WHERE id = 1;   -- legge 10
UPDATE contatori SET valore = 10 + 1 WHERE id = 1;
COMMIT;                                       -- valore ora 11

-- Sessione A riprende
UPDATE contatori SET valore = 10 + 1 WHERE id = 1;
COMMIT;                                       -- valore ancora 11

Il risultato finale è 11, non 12. Nessun errore, nessun avviso. Un aggiornamento è andato perso.

Il motivo è sottile e vale la pena capirlo bene. Con Read Committed, quando la sessione A esegue l'UPDATE, PostgreSQL si accorge che la riga è stata modificata da una transazione più recente, attende il commit di B e poi rilegge la versione aggiornata per valutare la clausola WHERE. Ma l'espressione SET valore = 10 + 1 contiene una costante calcolata prima, quindi il risultato resta 11. Se invece avessi scritto SET valore = valore + 1, PostgreSQL avrebbe rivalutato l'espressione sulla riga nuova, ottenendo 12. Il comportamento dipende da quanto l'espressione è relativa alla riga.

Le tre soluzioni pratiche

1. Query atomica relativa. Se puoi esprimere l'operazione come trasformazione della riga, fallo. È la soluzione più semplice e quasi sempre la migliore.

UPDATE contatori SET valore = valore + 1 WHERE id = 1;

Un solo statement, nessuna finestra temporale tra lettura e scrittura.

2. Lock esplicito. Se devi leggere il valore per decidere qualcosa in applicazione, prendi il lock sulla riga.

BEGIN;
SELECT valore FROM contatori WHERE id = 1 FOR UPDATE;
-- ora nessun altro puo modificare la riga finche non chiudi la transazione
UPDATE contatori SET valore = valore + 1 WHERE id = 1;
COMMIT;

La seconda transazione resta in attesa sul SELECT ... FOR UPDATE finché la prima non committa. Attenzione: la lettura avviene dopo il lock, quindi vedrà il valore aggiornato.

3. Livello di isolamento più alto. Con Repeatable Read o Serializable, la sessione A otterrebbe un errore invece di un risultato sbagliato.

ERROR:  could not serialize access due to concurrent update

Sta al codice decidere cosa fare: quasi sempre riprovare.

Phantom read e overbooking

Il lost update è un conflitto sulla stessa riga. Il phantom read è più insidioso perché riguarda righe che non esistono ancora: un lock non può bloccare qualcosa che non c'è.

Scenario tipico: controllare che ci sia posto e poi inserire.

-- Sessione A
BEGIN;
SELECT count(*) FROM prenotazioni
 WHERE camera_id = 7 AND data = DATE '2025-06-01';
-- risultato: 2, il limite e 3, quindi procedo
INSERT INTO prenotazioni (camera_id, data, ospite)
VALUES (7, DATE '2025-06-01', 'A');
COMMIT;

Se la sessione B esegue la stessa sequenza nello stesso istante, entrambe vedono 2, entrambe inseriscono, e il limite di 3 posti viene superato. Con Read Committed, Repeatable Read e Serializable, PostgreSQL rileva il conflitto di scrittura e fa fallire una delle due transazioni con un errore di serializzazione. Ma affidarsi solo al livello di isolamento significa spostare il problema sulla gestione degli errori, senza aver espresso chiaramente quale sia l'invariante.

La mossa più solida è rendere l'invariante una regola del database, non una speranza del codice applicativo.

-- Versione semplice: una prenotazione per camera e data
ALTER TABLE prenotazioni
  ADD CONSTRAINT una_prenotazione_per_data
  UNIQUE (camera_id, data);

Se il vincolo non è esprimibile con UNIQUE (per esempio un numero massimo di righe per gruppo), le alternative sono:

• Lock su una riga "risorsa": una tabella camere con una riga per camera, bloccata con FOR UPDATE all'inizio della transazione. Tutte le prenotazioni sulla stessa camera si serializzano lì.
• Advisory lock: pg_advisory_xact_lock(hashtext('camera:7')) protegge una sezione critica arbitraria. Utile quando la risorsa logica non ha una riga fisica. Il lock viene rilasciato automaticamente a fine transazione.
• Vincoli di esclusione: EXCLUDE USING gist con un range di date risolve elegantemente i conflitti su intervalli (prenotazioni sovrapposte, turni, tariffe).

Il retry non è un dettaglio

Se scegli Repeatable Read o Serializable, la gestione degli errori diventa parte della logica, non un caso limite. L'errore da intercettare è lo SQLSTATE 40001 (serialization failure) e, in alcuni casi, 40P01 (deadlock detected).

Uno schema minimo in Python con psycopg:

import time
import psycopg

RETRYABLE = {"40001", "40P01"}

def esegui_con_retry(conn, funzione, tentativi=5):
    attesa = 0.05
    for tentativo in range(tentativi):
        try:
            with conn.transaction():
                return funzione(conn)
        except psycopg.errors.Error as errore:
            if errore.sqlstate not in RETRYABLE:
                raise
            time.sleep(attesa)
            attesa *= 2
    raise RuntimeError("troppi conflitti di serializzazione")

Due regole che evitano parecchi dolori:

• La funzione ritentata deve essere idempotente rispetto alla transazione, cioè non deve avere effetti collaterali fuori dal database (email, chiamate HTTP, code). Se ne ha, vanno spostati fuori o resi idempotenti a loro volta.
• Le transazioni devono essere corte. Una transazione che aspetta una risposta di rete tiene lock aperti e fa crescere le versioni morte delle righe, con costi di vacuum che si accumulano.

Diagnostica: capire chi blocca chi

Quando il sistema si impianta, PostgreSQL ha gli strumenti per raccontare cosa sta succedendo.

-- Transazioni in attesa di lock
SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Lock attualmente concessi
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE NOT granted;

Utile anche impostare un limite sano per non far crescere le code all'infinito:

SET lock_timeout = '3s';
SET idle_in_transaction_session_timeout = '30s';
SET statement_timeout = '15s';

Il primo interrompe un'attesa di lock troppo lunga, il secondo chiude una transazione aperta e dimenticata, il terzo mette un tetto alle query fuori controllo.

Errori comuni

Alcuni schemi ricorrenti, tutti visti abbastanza spesso:

• Pensare che il database risolva una logica applicativa scritta male. Il livello di isolamento non sa nulla delle tue regole di business.
• Fare check-then-act senza vincolo né lock. È la forma più diffusa di race condition in applicazioni CRUD.
• Usare Serializable ovunque come scorciatoia. Funziona, ma sposta il costo sui retry e sulle connessioni; va scelto con cognizione.
• Serializable senza retry. Un errore non gestito è, di fatto, un rifiuto di servizio occasionale.
• Transazioni che contengono chiamate di rete. Il lock vive mentre il mondo esterno risponde, magari in due secondi.
• Dimenticare che count(*) non è una prenotazione. Il conteggio è un'istantanea, non una riserva.

Conclusione

I punti che valgono la pena di portarsi dietro:

• Read Committed è il default di PostgreSQL e va bene per la maggior parte del codice, a patto di non fare read-modify-write applicativo su dati condivisi.
• L'update relativo è la prima arma: SET x = x + 1 invece di leggere, calcolare e riscrivere.
• Quando serve leggere per decidere, usa SELECT ... FOR UPDATE o un advisory lock, tenendo la transazione breve.
• Repeatable Read e Serializable non eliminano i conflitti: li trasformano in errori 40001, che il codice deve saper ritentare.
• L'invariante va scritto nel database (vincoli UNIQUE, CHECK, EXCLUDE) ogni volta che è esprimibile: è l'unica garanzia che vale anche se un domani arriva un secondo servizio a scrivere sulla stessa tabella.
• Misura con pg_stat_activity e pg_locks prima di dichiarare che il database è lento: spesso sta solo aspettando qualcuno.

La concorrenza non si risolve con una riga di configurazione, ma con la consapevolezza di dove si trovano i punti di contatto tra richieste diverse. Una volta individuati, le soluzioni sono poche e ripetibili.

Foto di copertina di Albert Stoynov su Unsplash.