Transazioni e Livelli di Isolamento — Cheat Sheet

Tag: database, sql, acid, concurrency, postgresql, mysql, backend

Livello: intermedio

TL;DR: I livelli di isolamento bilanciano prestazioni e consistenza gestendo tre anomalie classiche: Dirty Read, Non-Repeatable Read e Phantom Read.

Concetti chiave

ACID definisce Atomicità, Consistenza, Isolamento e Durabilità nelle transazioni dei database relazionali.

I 4 livelli ANSI SQL standard sono Read Uncommitted, Read Committed, Repeatable Read e Serializable.

Dirty Read: lettura di dati scritti da un'altra transazione non ancora confermata via commit.

Non-Repeatable Read: rileggere la stessa riga produce valori differenti modificati e confermati altrove.

Phantom Read: rileggere un range di righe restituisce nuovi record inseriti da un'altra transazione.

Read Committed impedisce le Dirty Read ed è il default in PostgreSQL e SQL Server.

Repeatable Read blocca le Non-Repeatable Read ed è il default di InnoDB in MySQL.

Serializable garantisce l'equivalenza all'esecuzione sequenziale delle transazioni tramite lock o controlli ottimistici (SSI).

MVCC (Multi-Version Concurrency Control) mantiene versioni multiple delle righe evitando lock in lettura.

Domande tipiche e risposte pronte

Q: Qual è la differenza sostanziale tra Non-Repeatable Read e Phantom Read?R: La Non-Repeatable Read riguarda la modifica o cancellazione di una riga già esistente; la Phantom Read riguarda l'inserimento di nuove righe che soddisfano il predicato di ricerca.

Q: Perché non usiamo sempre il livello Serializable in produzione?R: Serializable riduce il throughput, aumenta la latenza e provoca abort frequenti per conflitti di serializzazione, richiedendo logica di retry.

Q: In che modo MVCC riduce le contenzioni rispetto al locking tradizionale?R: Consente ai lettori di non bloccare gli scrittori e agli scrittori di non bloccare i lettori, leggendo uno snapshot consistente del database.

Insidie e errori comuni

Dare per scontato che Repeatable Read prevenga sempre le Phantom Read: dipende dall'implementazione specifica del database engine.

Assumere che Read Committed protegga da Lost Update: update concorrenti basati su letture precedenti possono sovrascriversi a vicenda.

Dimenticare di gestire esplicitamente gli errori di serializzazione a livello applicativo quando si usa Serializable.

Snippet e comandiSQL

```-- Impostare il livello di isolamento per la transazione corrente (SQL standard / PostgreSQL)

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 42;

UPDATE accounts SET balance = balance - 100 WHERE id = 42;

COMMIT;

-- Locking pessimistico per evitare Lost Update a livello Read Committed

SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;

```

Da ripassare

[ ] Meccanismo di Snapshot Isolation e Write Skew anomaly

[ ] Implementazione specifica di Repeatable Read in InnoDB vs PostgreSQL

[x] Matrice dei 4 livelli di isolamento ANSI SQL e anomalie corrispondenti