Livelli di isolamento delle transazioni e anomalie tipiche

Quando più utenti lavorano sullo stesso database nello stesso momento, le loro transazioni si sovrappongono. Se ogni transazione aspettasse la fine delle altre, il sistema sarebbe correttissimo ma lentissimo. I database cercano quindi un compromesso, e il livello di isolamento è la manopola che regola quanto una transazione può essere disturbata dalle altre. Conoscere le anomalie possibili aiuta a capire quando il livello predefinito basta e quando no.

Le anomalie da conoscere

Lettura sporca

Una transazione legge un dato modificato da un'altra transazione che non ha ancora fatto commit. Se quest'ultima viene annullata, la prima ha lavorato su un valore che non è mai esistito davvero.

Lettura non ripetibile

Una transazione legge una riga, un'altra la modifica e conferma, la prima rilegge la stessa riga e trova un valore diverso. All'interno della stessa transazione, la stessa domanda ha due risposte.

Lettura fantasma

Una transazione esegue una query con un filtro, per esempio «ordini di oggi», e ottiene 10 righe. Un'altra inserisce un nuovo ordine di oggi e conferma. Rieseguendo la query, compare una riga in più: il fantasma.

Aggiornamento perso

Due transazioni leggono lo stesso saldo, 100. Entrambe calcolano un nuovo valore nel codice applicativo, una aggiungendo 10 e l'altra 20, e lo scrivono. Il risultato finale è 110 o 120, non 130: una delle due modifiche è andata persa.

Distorsione di scrittura

È l'anomalia più sottile. Due medici sono di turno e la regola dice che almeno uno deve restare reperibile. Ciascuno, in una transazione separata, controlla che l'altro sia di turno e poi si toglie. Nessuna delle due transazioni modifica la stessa riga, eppure alla fine nessuno è di turno. In inglese si chiama write skew.

I quattro livelli dello standard SQL

Lo standard SQL definisce quattro livelli, dal più permissivo al più rigoroso.

La tabella descrive i requisiti minimi dello standard. Le implementazioni reali spesso fanno di più: per esempio, in PostgreSQL il livello Repeatable Read impedisce anche le letture fantasma, e Read Uncommitted si comporta come Read Committed. Per questo conviene sempre consultare la documentazione del proprio database invece di affidarsi alla sola tabella.

I valori predefiniti

I database più diffusi non partono tutti dallo stesso livello. PostgreSQL, Oracle e SQL Server usano Read Committed come impostazione predefinita, mentre MySQL con il motore InnoDB usa Repeatable Read. Lo stesso codice può quindi comportarsi in modo diverso su sistemi diversi.

Serializable: correttezza al prezzo dei tentativi

Il livello Serializable garantisce che il risultato sia uguale a quello che si otterrebbe eseguendo le transazioni una dopo l'altra, in un qualche ordine. Elimina anche la distorsione di scrittura. Il prezzo è che il database, quando rileva un conflitto, annulla una delle transazioni con un errore di serializzazione. L'applicazione deve essere pronta a ripeterla:

for tentativo in range(3):
    try:
        esegui_transazione()
        break
    except ErroreSerializzazione:
        continue  # si riprova da capo

Senza questa logica di ripetizione, gli utenti vedrebbero errori casuali nei momenti di maggior traffico.

Come evitare l'aggiornamento perso senza Serializable

Nella pratica la maggior parte delle applicazioni lavora in Read Committed e risolve i casi critici con tecniche mirate.

Aggiornamento atomico

Invece di leggere il valore, calcolare nel codice e riscriverlo, si lascia fare il calcolo al database:

UPDATE conti SET saldo = saldo + 20 WHERE id = 7;

L'istruzione è atomica e due esecuzioni concorrenti producono il risultato corretto.

Blocco esplicito in lettura

Quando la logica è troppo complessa per una singola istruzione, si blocca la riga al momento della lettura:

BEGIN;
SELECT saldo FROM conti WHERE id = 7 FOR UPDATE;
-- calcoli nel codice applicativo
UPDATE conti SET saldo = 130 WHERE id = 7;
COMMIT;

Una seconda transazione che tenta lo stesso SELECT ... FOR UPDATE aspetta che la prima finisca.

Controllo ottimistico con versione

Si aggiunge alla tabella una colonna versione. L'aggiornamento avviene solo se la versione non è cambiata dalla lettura:

UPDATE documenti
SET testo = 'nuovo testo', versione = versione + 1
WHERE id = 3 AND versione = 12;

Se l'istruzione modifica zero righe, qualcun altro è arrivato prima: si avvisa l'utente o si riprova. È la tecnica ideale per i moduli web, dove tra lettura e salvataggio possono passare minuti e tenere un blocco aperto non è accettabile.

Come scegliere

1. Per la maggior parte delle operazioni, il livello predefinito del proprio database è adeguato.
2. Per i contatori e i saldi, usare aggiornamenti atomici o FOR UPDATE.
3. Per le modifiche fatte dagli utenti tramite interfaccia, preferire il controllo ottimistico con versione.
4. Per regole che coinvolgono più righe, come il turno minimo, valutare Serializable con logica di ripetizione oppure un vincolo nel database.

In sintesi

• Le anomalie principali sono lettura sporca, non ripetibile, fantasma, aggiornamento perso e distorsione di scrittura.
• Lo standard SQL definisce quattro livelli; le implementazioni reali variano, quindi va letta la documentazione.
• Il livello predefinito non è uguale in tutti i database.
• Serializable è il più sicuro ma richiede di ripetere le transazioni in conflitto.
• Aggiornamenti atomici, FOR UPDATE e versioni risolvono la maggior parte dei casi pratici.