Transazioni in SQLite: BEGIN, COMMIT e ROLLBACK spiegati

Una transazione è un pacchetto tutto o niente

Una transazione raggruppa più istruzioni in modo che abbiano effetto tutte oppure nessuna. Se qualcosa va storto a metà, puoi fare rollback e il database torna esattamente com'era all'inizio.

L'esempio classico è spostare denaro:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

I due UPDATE vanno insieme. Se il database si bloccasse tra l'uno e l'altro, Ada avrebbe 2000 centesimi in meno e Boris niente in più. Racchiuderli in BEGIN ... COMMIT rende la coppia atomica: succedono entrambi, oppure nessuno dei due.

Autocommit: il comportamento predefinito che usi già

Ogni istruzione SQL che hai eseguito finora era una transazione. Per impostazione predefinita SQLite è in modalità autocommit: ogni istruzione riceve un proprio BEGIN e COMMIT impliciti attorno.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('first');
INSERT INTO notes (body) VALUES ('second');
INSERT INTO notes (body) VALUES ('third');

SELECT * FROM notes;

Tre insert, tre transazioni separate, tre accessi al disco per fare fsync della modifica. Va bene per scritture singole, ma è lento per i caricamenti massivi, e significa che non puoi annullare un gruppo di istruzioni come un'unica unità. BEGIN disattiva l'autocommit fino al successivo COMMIT o ROLLBACK.

ROLLBACK: fare finta che non sia mai successo

ROLLBACK scarta tutto quello che è stato fatto dal BEGIN corrispondente. Il database torna allo stato precedente alla transazione.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

Sia l'UPDATE sia il DELETE spariscono: la tabella è com'era prima del BEGIN. È questa la rete di sicurezza che permette al codice dell'applicazione di interrompersi in modo pulito quando incontra un errore a metà di un'operazione composta da più istruzioni.

Tra l'altro, una violazione di vincolo dentro una transazione non fa automaticamente il rollback di tutto. Annulla l'istruzione che ha causato il problema e lascia la transazione aperta, in attesa che decida tu. Se vuoi il tutto o niente, è l'applicazione che deve eseguire ROLLBACK quando vede un errore.

Velocizzare gli insert massivi

Siccome ogni istruzione in autocommit fa il proprio fsync, racchiudere un blocco in un'unica transazione è spesso 100 volte più veloce:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

Una sola sincronizzazione su disco al COMMIT invece di una per riga. Se stai importando migliaia di righe e ti chiedi perché vada a passo di lumaca, la risposta è quasi sempre questa.

DEFERRED, IMMEDIATE, EXCLUSIVE

BEGIN accetta una modalità che controlla quando SQLite prende i lock:

• BEGIN DEFERRED (il predefinito): nessun lock finché non leggi o scrivi. Il lock di scrittura viene preso in modo pigro, alla prima istruzione di scrittura.
• BEGIN IMMEDIATE: prende subito il lock di scrittura. Le altre connessioni possono ancora leggere, ma nessun'altra connessione può iniziare a scrivere.
• BEGIN EXCLUSIVE: come IMMEDIATE, e in più nessun'altra connessione può nemmeno leggere. In modalità WAL si comporta come IMMEDIATE; la differenza conta solo nella vecchia modalità rollback journal.

BEGIN DEFERRED;     -- uguale a un BEGIN normale
BEGIN IMMEDIATE;    -- riserva subito il lock di scrittura
BEGIN EXCLUSIVE;    -- riserva tutto (modalità rollback journal)

La scelta conta per la concorrenza. Con un semplice BEGIN, due connessioni possono avviare entrambe una transazione, leggere tranquillamente e poi entrare in competizione quando provano a scrivere: la seconda che chiede il lock di scrittura riceve SQLITE_BUSY e, peggio ancora, ha già fatto delle letture che ora deve buttare via.

BEGIN IMMEDIATE risolve il problema: se sai che scriverai, chiedi prima il lock di scrittura. La seconda connessione si blocca (o fallisce subito) immediatamente, prima di fare lavoro che dovrebbe scartare.

Regola pratica: se la tua transazione scriverà, usa BEGIN IMMEDIATE.

Le letture dentro una transazione vedono uno snapshot

Finché una transazione è aperta, le tue letture vedono uno snapshot coerente del database com'era quando la transazione è iniziata (in modalità WAL) o quando hai letto la prima volta (in modalità rollback journal). Le modifiche confermate da altre connessioni non compariranno all'improvviso nelle tue query.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, la tua scrittura è visibile
COMMIT;

SELECT n FROM counter;

Vedi le tue scritture non ancora confermate; le altre connessioni no. Quando fai COMMIT, il nuovo valore diventa visibile a tutti. È questo che si intende quando si dice che SQLite è serializzabile: non c'è un'opzione READ COMMITTED da regolare, perché il comportamento predefinito è già il livello più forte.

Una transazione nel codice dell'applicazione

In un programma reale lo schema di solito è un try/except (o try/catch) attorno al corpo, con un ROLLBACK nel ramo di errore:

-- Pseudocodice per qualsiasi libreria client
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

La maggior parte delle librerie client (sqlite3 di Python, better-sqlite3, ecc.) lo gestisce per te con un blocco with o un helper transaction(). Vale la pena controllare la documentazione della tua libreria: i valori predefiniti non sono sempre quelli che ti aspetteresti. In particolare sqlite3 di Python ha avuto storicamente un comportamento strano con l'autocommit; le versioni recenti hanno aggiunto un vero parametro autocommit per sistemarlo.

Cose che fanno inciampare

• Il DDL dentro le transazioni funziona. CREATE TABLE, ALTER TABLE e perfino DROP TABLE possono essere annullati con un rollback. In questo SQLite è insolito: molti database fanno il commit automatico del DDL.
• VACUUM non può essere eseguito dentro una transazione. Nemmeno alcuni altri comandi di manutenzione. Eseguili in modalità autocommit.
• Un COMMIT fallito è un fallimento vero. Se COMMIT restituisce SQLITE_BUSY (raro ma possibile), la transazione non è confermata. Il tuo codice deve gestirlo, di solito riprovando.
• Le transazioni lunghe bloccano chi scrive. Una transazione che resta aperta per minuti blocca gli altri scrittori per minuti. Aprile il più tardi possibile e confermale in fretta.

Prossimo passo: savepoint

BEGIN e COMMIT sono tutto o niente. A volte vuoi annullare solo una parte di una transazione, per esempio abbandonare un passaggio rischioso ma tenere il resto. È a questo che servono i savepoint, e sono il prossimo argomento.