Transazioni Database: ACID e Livelli di Isolamento Spiegati

Perché un bonifico bancario richiede più di due UPDATE

Sposta 50€ da un conto a un altro. Nella versione naive bastano due istruzioni: sottrai dal conto A, aggiungi al conto B. Eseguile separatamente e, se il processo va in crash o la connessione cade tra le due, il denaro è sparito da A e non arriva mai su B. Il database ha fatto esattamente quello che gli è stato chiesto, due volte. Il business ha perso 50€ comunque.

Una transazione racchiude entrambe le istruzioni così che il database le tratti come un’unica unità: o accadono entrambe, o nessuna delle due. Questa garanzia, insieme ad altre tre, è quello che indica l’acronimo ACID. È il motivo per cui puoi fidarti di un database per gestire denaro o scorte di magazzino, dove “a metà” è peggio di “non fatto”.

Cosa garantiscono le quattro proprietà ACID

• Atomicità. Tutte le istruzioni di una transazione vanno a commit insieme, o nessuna lo fa. Se il processo va in crash dopo il debito ma prima dell’accredito, il database annulla anche il debito. Non c’è nessuno stato parziale da sistemare a mano.
• Consistenza. Una transazione può portare il database solo da uno stato valido a un altro. Se un vincolo CHECK dice che un saldo non può essere negativo, nessuna transazione può fare commit di una riga che lo viola, indipendentemente da cosa ha provato a fare il codice dell’applicazione.
• Isolamento. Le transazioni in esecuzione contemporaneamente non vedono il lavoro a metà delle altre. Quanto rigidamente viene applicato è una manopola, non una regola fissa: quella manopola è il livello di isolamento, trattato più sotto.
• Durabilità. Una volta che il database dichiara che una transazione ha fatto commit, quel dato sopravvive anche a un crash un secondo dopo. È a questo che serve il write-ahead log: la modifica è su disco (o replicata) prima che il commit ritorni.

Atomicità e durabilità sono compito del database, e i motori più diffusi le gestiscono correttamente. L’isolamento è quello su cui decidi tu. Un isolamento più rigido costa concorrenza, e quel costo si vede nei grafici di latenza in produzione.

Come eseguire una transazione con BEGIN, COMMIT e ROLLBACK

In PostgreSQL (e nella maggior parte dei database SQL, con piccole differenze di sintassi), una transazione è l’insieme delle istruzioni tra BEGIN e COMMIT:

1
2
3
4
5
6

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

COMMIT;

Se qualcosa va storto prima del COMMIT (una violazione di vincolo, o un controllo di business nel codice dell’applicazione), ROLLBACK annulla tutto quello che è successo da BEGIN in poi:

1
2
3
4
5

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- il controllo di business nel codice dell'applicazione trova il saldo negativo
ROLLBACK;

Dopo il rollback, il saldo del conto 1 è esattamente quello che era prima che BEGIN venisse eseguito. Per una transazione lunga in cui vuoi annullare solo una parte del lavoro, un savepoint ti dà un rollback parziale:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

ROLLBACK TO before_credit;  -- annulla solo l'accredito
UPDATE accounts SET balance = balance + 50 WHERE id = 3; -- accredita un conto diverso

COMMIT;

Fuori da un BEGIN esplicito, la maggior parte dei driver esegue ogni istruzione nella sua transazione auto-commit. Ogni istruzione è atomica di per sé, ma niente lega due istruzioni insieme. Salta BEGIN nel bonifico bancario e sei di nuovo al punto di partenza: 50€ persi.

Cosa prevengono i livelli di isolamento delle transazioni

Due transazioni concorrenti possono interferire tra loro in tre modi specifici, e i livelli di isolamento si definiscono in base a quali di questi permettono:

Lo standard SQL definisce quattro livelli attorno a queste tre anomalie:

Raramente vorrai usare READ UNCOMMITTED. PostgreSQL non lo implementa nemmeno e silenziosamente esegue Read Committed al suo posto; MySQL invece lo rispetta e ti mostrerà dirty read. In pratica i due livelli che contano sono Read Committed e Repeatable Read, e i due principali database open source hanno default diversi:

• PostgreSQL usa come default Read Committed. Ogni istruzione dentro una transazione vede uno snapshot fresco preso all’avvio di quella istruzione, quindi due SELECT nella stessa transazione possono restituire dati diversi se un’altra transazione ha fatto commit nel frattempo.
• MySQL (InnoDB) usa come default Repeatable Read. Lo snapshot viene preso una sola volta, alla prima lettura della transazione, e ogni SELECT successivo in quella transazione vede lo stesso snapshot, anche se un’altra transazione ha fatto commit di un aggiornamento nel frattempo.

In PostgreSQL, imposta il livello subito dopo BEGIN, prima che venga eseguita qualsiasi query:

1
2
3
4
5
6
7

-- PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- ... più avanti nella stessa transazione ...
SELECT balance FROM accounts WHERE id = 1; -- garantito lo stesso valore di sopra
COMMIT;

In MySQL, la forma SESSION cambia il livello per ogni transazione successiva su quella connessione:

1
2

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Come Repeatable Read blocca un phantom read in PostgreSQL

Apri due sessioni psql sulla stessa tabella ed esegui questo. Vederlo una volta resta in mente meglio che memorizzare la tabella.

Sessione A:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

Sessione B, mentre la transazione di A è ancora aperta:

1
2
3

BEGIN;
INSERT INTO accounts (id, balance) VALUES (9, 2000);
COMMIT;

Sessione A, stessa transazione aperta, ri-eseguendo la query identica:

1
2
3
4

SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

La riga inserita e committata da B non appare. Il Repeatable Read di PostgreSQL prende il suo snapshot alla prima query della transazione di A e lo mantiene fino al COMMIT, quindi le righe nuove di altre transazioni non si vedono mai. Questo è più rigido di quanto richiesto dallo standard: la tabella sopra permette phantom a questo livello, ma l’implementazione di PostgreSQL non li produce. Esegui la stessa sequenza con A in Read Committed e la seconda SELECT restituisce la riga nuova, perché Read Committed prende uno snapshot fresco a ogni istruzione.

Serializable va oltre. Rileva quando due transazioni concorrenti hanno prodotto un risultato che non sarebbe potuto accadere in nessun ordine sequenziale, e ne annulla una con un errore di serializzazione (PostgreSQL restituisce lo SQLSTATE 40001) invece di lasciare che l’anomalia vada a commit. È il livello che chiude la classica race condition dove due transazioni leggono entrambe un valore, decidono in base a quello, e scrivono entrambe. Il codice che usa SERIALIZABLE deve essere pronto a ritentare:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... istruzioni ...
COMMIT;
-- in caso di errore 40001 ("could not serialize access due to read/write dependencies"):
-- fai rollback e ritenta l'intera transazione

Cosa costano i livelli di isolamento più rigidi

Il prezzo di un isolamento più rigido è la concorrenza:

• Le scritture restano bloccate fino al commit, a ogni livello. Un UPDATE prende un lock sulla riga nel momento in cui viene eseguito e lo mantiene fino a COMMIT o ROLLBACK, indipendentemente dal livello di isolamento. Quello che Repeatable Read e Serializable aggiungono è mantenere lo stesso snapshot (o, per Serializable, predicate lock sulle righe esaminate dalle tue query) per tutta la transazione. Fai una chiamata API esterna lenta tra BEGIN e COMMIT e ogni riga che hai scritto resta bloccata, e ogni riga che hai letto sotto Serializable resta tracciata, per tutto il tempo che richiede quella chiamata.
• I retry non sono opzionali con Serializable. Rifiuterà transazioni apparentemente valide sotto carico concorrente: è un comportamento voluto, non un difetto. Se la tua applicazione non intercetta gli errori di serializzazione e non ritenta, quelle transazioni falliscono a intermittenza, sotto carico, che è il momento peggiore per scoprirlo. Repeatable Read può generare lo stesso errore 40001 su un semplice aggiornamento concorrente, quindi il codice di retry non serve solo per Serializable — ogni transazione sopra Read Committed ne ha bisogno.
• Le transazioni lunghe bloccano le modifiche allo schema e gonfiano lo storage. L’MVCC di PostgreSQL mantiene le vecchie versioni delle righe per tutto il tempo in cui una transazione aperta potrebbe ancora averne bisogno. Una transazione lasciata aperta per ore (un bug comune: apri una transazione in una richiesta web e non fai mai commit né rollback) impedisce a VACUUM di liberare quello spazio, e un ALTER TABLE su una tabella che ha toccato aspetta che finisca.
• Non tutte le scritture hanno bisogno di una transazione esplicita. Una singola istruzione UPDATE è già atomica. Racchiuderne una in BEGIN/COMMIT aggiunge un round trip senza nessuna garanzia in più. Usa una transazione esplicita quando la correttezza dipende dal successo o fallimento congiunto di due o più istruzioni: il bonifico bancario, una registrazione che scrive sia una riga in users che una in accounts, un ordine che decrementa l’inventario e inserisce il record dell’ordine.

Gli ORM (Entity Framework, SQLAlchemy, ActiveRecord) di solito aprono una transazione per unità di lavoro e fanno commit per te. Non andare contro questo comportamento. Il bug da tenere d’occhio è l’opposto: codice che chiama .save() o .commit() separatamente per due scritture collegate, buttando via l’atomicità che l’ORM ti avrebbe dato.

Quale livello di isolamento usare

Parti da Read Committed e sali solo per le transazioni specifiche dove sai nominare la race che stai chiudendo. È la stessa disciplina di quando aggiungere un indice solo dopo aver trovato la query lenta. Se non riesci a scrivere quella race in una frase, non hai ancora bisogno di un livello più rigido.

Articoli correlati

• Come Funzionano gli Indici Database e Quando Usarli
• Tipi di JOIN in SQL Spiegati con Esempi
• Cos’è una Race Condition e Come Risolverla