Livello di isolamento REPEATABLE READ: prevenzione del Non-Repeatable Read

Immagina di giocare a un videogioco online e nel frattempo qualche cheater si infila nel codice e potenzia il suo personaggio, oppure stai leggendo un libro in biblioteca e qualcuno nel frattempo cambia le pagine, inserisce nuovi capitoli o addirittura sostituisce il libro. Situazione piuttosto fastidiosa, vero? Ecco, proprio da queste "sorprese" ti protegge il livello di isolamento REPEATABLE READ.

REPEATABLE READ ti garantisce che i dati che vedi all'interno di una transazione rimarranno invariati fino alla fine di quella transazione. Anche se un'altra transazione prova ad aggiornare quei dati, la tua transazione sarà protetta da questi cambiamenti.

Caratteristiche chiave:

• Previene il Dirty Read (lettura di dati che non sono ancora stati confermati).
• E, soprattutto, previene il Non-Repeatable Read. Questo significa che se hai letto un set di dati all'inizio della transazione, quando li rileggi otterrai gli stessi dati, anche se qualcun altro li ha modificati.

Tuttavia, REPEATABLE READ non protegge dal Phantom Read. Se un'altra transazione aggiunge nuove righe, queste potrebbero comparire nella tua query successiva. Per eliminare anche questa anomalia, serve il livello SERIALIZABLE, ma di questo parleremo più avanti.

Come impostare il livello di isolamento REPEATABLE READ

Prima di passare agli esempi, vediamo come attivare questo livello di isolamento in PostgreSQL. Ci sono due modi principali:

1. Impostare il livello di isolamento per una singola transazione:

   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   BEGIN;
   -- Le tue query
   COMMIT;
   

2. Impostare il livello di isolamento per la sessione corrente:

   SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   

Nel secondo caso, tutte le transazioni della sessione useranno REPEATABLE READ.

Esempio: prevenzione del Non-Repeatable Read

Supponiamo di avere una tabella accounts con la seguente struttura:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'in attesa'
);

INSERT INTO orders (customer_name, status)
VALUES ('Alice', 'in attesa'), ('Bob', 'in attesa');

Partiamo da uno scenario base, dove una transazione modifica i dati e un'altra li legge.

Scenario senza REPEATABLE READ (livello READ COMMITTED)

Transazione 1 inizia:

BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Otteniamo: 100

Nel frattempo la Transazione 2 modifica i dati:

BEGIN;
UPDATE accounts SET balance = 150 WHERE account_id = 1;
COMMIT;

Transazione 1 continua:

SELECT balance FROM accounts WHERE account_id = 1;
-- Otteniamo: 150 (i dati sono cambiati!)
COMMIT;

Come vedi, con il livello READ COMMITTED i dati possono cambiare tra due letture nella stessa transazione. Questo è il Non-Repeatable Read.

Scenario con REPEATABLE READ

Ora proviamo lo stesso esempio, ma con il livello di isolamento REPEATABLE READ.

Transazione 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- Otteniamo: 100

Transazione 2:

BEGIN;
UPDATE accounts SET balance = 150 WHERE account_id = 1;
COMMIT;

Transazione 1 continua:

SELECT balance FROM accounts WHERE account_id = 1;
-- Ancora otteniamo: 100 (i dati sono invariati!)
COMMIT;

Indipendentemente dai cambiamenti fatti da un'altra transazione, la transazione 1 vede i dati come erano all'inizio. Così il Non-Repeatable Read viene evitato.

Come funziona REPEATABLE READ

PostgreSQL usa il meccanismo MVCC (Multi-Version Concurrency Control) per implementare il livello di isolamento REPEATABLE READ. Il principio base di MVCC è che ogni transazione riceve uno "snapshot" stabile del database, che non cambia fino alla fine della transazione. Questo si ottiene creando e gestendo più versioni delle righe.

Quando una transazione parte, vede i dati nello stato in cui erano al momento dell'avvio. Se un'altra transazione fa delle modifiche, PostgreSQL crea una nuova versione della riga, ma la versione precedente resta per tutte le transazioni che la stanno usando.

Proprio per questo le transazioni possono essere lente e richiedere molta memoria. Ed è anche il motivo per cui pochi usano il livello di isolamento più alto: è il più affidabile, ma rallenta di più il lavoro col database.

Limiti di REPEATABLE READ: Phantom Read

Come già detto, REPEATABLE READ non protegge dal Phantom Read. Per capire cosa significa, vediamo un esempio con query che lavorano su intervalli di dati.

Supponiamo di avere una tabella orders:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    amount NUMERIC NOT NULL
);

INSERT INTO orders (amount)
VALUES (50), (100), (150);

Transazione 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT COUNT(*) FROM orders WHERE amount > 50;
-- Otteniamo: 2

Transazione 2:

BEGIN;
INSERT INTO orders (amount) VALUES (200);
COMMIT;

Transazione 1 continua:

SELECT COUNT(*) FROM orders WHERE amount > 50;
-- Otteniamo: 3 (una nuova riga è apparsa nel risultato!)
COMMIT;

In questo caso, una nuova riga (con amount = 200) è stata aggiunta da un'altra transazione e "fantasiosamente" è apparsa nel risultato della query della transazione 1, nonostante il livello di isolamento REPEATABLE READ.

Se vuoi evitare il Phantom Read, devi usare il livello SERIALIZABLE, ma questo richiede sempre un compromesso sulle performance.

Vantaggi e svantaggi di REPEATABLE READ

Il livello di isolamento REPEATABLE READ è una soluzione ottima quando vuoi essere sicuro che i dati non cambino durante l'esecuzione della transazione. Una volta che hai letto qualcosa, quel valore resterà lo stesso fino al COMMIT, anche se qualcun altro in un'altra transazione prova a modificarlo.

Questo approccio previene sia le letture sporche (dirty read), sia le letture non ripetibili (non-repeatable read). Lavori sempre con gli stessi dati che avevi all'inizio — nessun aggiornamento a sorpresa "al volo". È particolarmente utile quando gestisci report o prendi decisioni dove la consistenza è importante.

D'altra parte, REPEATABLE READ non gestisce i cosiddetti "fantasmi" (phantom read) — quando nuove righe appaiono nel risultato di una query che hai già fatto nella stessa transazione. Inoltre, sotto carico elevato, questo livello può causare conflitti tra transazioni, soprattutto se accedono spesso agli stessi dati. Questo può portare a lock e rollback, anche se le query erano corrette.

Insomma, REPEATABLE READ è un buon compromesso tra affidabilità e performance, ma in scenari con alta concorrenza può richiedere qualche attenzione e tuning extra.

Consigli utili ed errori comuni

• Ricorda che la scelta del livello di isolamento influisce sulle performance. Usa REPEATABLE READ solo quando ti serve davvero la certezza che i dati non cambino.
• Confondere REPEATABLE READ e SERIALIZABLE è un errore comune. Se vedi nuove righe dopo una query ripetuta, è il comportamento previsto per REPEATABLE READ.
• Quando lavori con transazioni lunghe, fai attenzione ai possibili conflitti di lock. Le transazioni lunghe possono bloccare altre operazioni.

PostgreSQL offre diversi strumenti per gestire l'isolamento delle transazioni. Il livello REPEATABLE READ è perfetto quando vuoi essere sicuro che i dati già letti non cambino all'interno della stessa transazione.