SAVEPOINT in SQLite: transazioni annidate con ROLLBACK TO

Un savepoint è un segnalibro con nome dentro una transazione

Una transazione normale è tutto o niente: tutto ciò che sta tra BEGIN e COMMIT viene salvato insieme oppure scartato insieme. Di solito è quello che vuoi, ma a volte ti serve una granularità più fine. "Prova questo gruppo di modifiche; se va storto, annulla solo quel gruppo e tieni vivo il resto della transazione."

È questo un savepoint. Lasci un segnalibro con nome, fai del lavoro e poi tieni il lavoro (RELEASE) oppure torni indietro al segnalibro (ROLLBACK TO).

CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 100), ('Boris', 100);

BEGIN;
  UPDATE accounts SET balance = balance - 30 WHERE name = 'Ada';

  SAVEPOINT risky;
    UPDATE accounts SET balance = balance + 30 WHERE name = 'Nobody';
  ROLLBACK TO risky;

  UPDATE accounts SET balance = balance + 30 WHERE name = 'Boris';
COMMIT;

SELECT * FROM accounts;

L'addebito di Ada e l'accredito di Boris restano entrambi. L'aggiornamento sbagliato su Nobody è stato annullato senza perdere il resto della transazione.

I tre comandi

Tre istruzioni sono tutta l'API:

• SAVEPOINT name: imposta un segnalibro.
• RELEASE SAVEPOINT name: tiene il lavoro fatto dal segnalibro in poi e rimuove il segnalibro.
• ROLLBACK TO SAVEPOINT name: annulla tutto ciò che è stato fatto dal segnalibro in poi; il segnalibro resta al suo posto, così puoi riprovare.

La parola SAVEPOINT dopo RELEASE e ROLLBACK TO è facoltativa: funzionano sia RELEASE risky sia ROLLBACK TO risky.

CREATE TABLE log (msg TEXT);

BEGIN;
  INSERT INTO log VALUES ('inizio');

  SAVEPOINT step1;
    INSERT INTO log VALUES ('passo 1 completato');
  RELEASE step1;

  SAVEPOINT step2;
    INSERT INTO log VALUES ('tentativo passo 2');
  ROLLBACK TO step2;
    INSERT INTO log VALUES ('nuovo tentativo passo 2');
  RELEASE step2;
COMMIT;

SELECT * FROM log;

Per il database finale, la riga tentativo passo 2 non è mai esistita. Tutto il resto viene salvato.

Savepoint senza una transazione esterna

Ecco una piccola sorpresa: puoi usare SAVEPOINT senza un BEGIN prima. SQLite apre una transazione per te senza dirlo, e il savepoint più esterno fa la parte della transazione stessa. RELEASE su quel savepoint fa il commit, ROLLBACK TO torna indietro senza fare il commit.

CREATE TABLE notes (text TEXT);

SAVEPOINT outer;
  INSERT INTO notes VALUES ('ciao');
RELEASE outer;

SELECT * FROM notes;

Ecco perché a volte i savepoint vengono descritti come "transazioni con nome". Ma mescolare i due stili nel codice reale crea confusione: scegline uno. La maggior parte delle persone usa BEGIN ... COMMIT esplicito per il confine esterno e i savepoint solo per i punti interni di annullamento parziale.

Savepoint annidati

I savepoint si impilano. Puoi impostarne uno dentro un altro e annullare quello interno senza toccare quello esterno:

CREATE TABLE items (name TEXT);

BEGIN;
  INSERT INTO items VALUES ('a');

  SAVEPOINT outer;
    INSERT INTO items VALUES ('b');

    SAVEPOINT inner;
      INSERT INTO items VALUES ('c');
    ROLLBACK TO inner;
    -- 'c' non c'è più; 'a' e 'b' sono ancora qui

    INSERT INTO items VALUES ('d');
  RELEASE outer;
COMMIT;

SELECT * FROM items;

Contenuto finale: a, b, d. Tornare a inner ha rimosso c ma ha lasciato intatto il lavoro fatto prima di inner (l'insert di b), e la transazione è andata avanti.

Tornare a un savepoint esterno scarta anche tutto ciò che è stato fatto ai livelli interni: l'intera pila sopra quel nome si srotola in un colpo:

CREATE TABLE items (name TEXT);

BEGIN;
  SAVEPOINT outer;
    INSERT INTO items VALUES ('b');
    SAVEPOINT inner;
      INSERT INTO items VALUES ('c');
  ROLLBACK TO outer;
COMMIT;

SELECT * FROM items;

Sia b sia c sono spariti. ROLLBACK TO outer annulla tutto da quando è stato impostato outer, compresi inner e l'insert di c.

Perché usare i savepoint?

Il caso classico è l'elaborazione di un lotto in cui i singoli elementi possono fallire senza buttare via tutto il lotto. Racchiudi ogni elemento in un savepoint; se fallisce, torna al savepoint e prosegui:

CREATE TABLE imports (id INTEGER PRIMARY KEY, value INTEGER CHECK(value > 0));

BEGIN;
  SAVEPOINT row1;
    INSERT INTO imports(value) VALUES (10);
  RELEASE row1;

  SAVEPOINT row2;
    INSERT INTO imports(value) VALUES (-5);  -- violerebbe il vincolo CHECK
  ROLLBACK TO row2;

  SAVEPOINT row3;
    INSERT INTO imports(value) VALUES (20);
  RELEASE row3;
COMMIT;

SELECT * FROM imports;

Nel codice di un'applicazione reale, l'insert sbagliato solleva un errore e l'applicazione lo intercetta, esegue ROLLBACK TO e continua. Due righe buone vengono salvate; quella sbagliata non avvelena il lotto.

È anche così che ORM e strumenti di migrazione implementano le transazioni annidate: non annidano davvero i blocchi BEGIN (SQLite non lo permette), ma trasformano le chiamate annidate in savepoint.

Alcune cose a cui fare attenzione

Qualche dettaglio che frega chi usa i savepoint per la prima volta:

• COMMIT fa sempre il commit dell'intera transazione. Non importa quanti savepoint aperti hai: COMMIT (o il suo alias END) chiude tutta la transazione esterna. Non pensare a RELEASE come a un commit parziale; niente è durevole finché la transazione che lo contiene non fa il commit.
• ROLLBACK (senza TO) annulla tutto. Chiude la transazione e scarta tutti i savepoint aperti. Usa ROLLBACK TO name quando vuoi tenere viva la transazione.
• Un savepoint resta aperto finché non viene rilasciato o superato da un rollback. Dimenticare RELEASE non fa perdere dati, ma il segnalibro resta lì finché la transazione non finisce.
• I nomi non devono essere unici. Se imposti SAVEPOINT s due volte, ROLLBACK TO s trova il più recente. Utile per la ricorsione; fonte di confusione se capita per sbaglio.

Prossimo passo: le viste

I savepoint ti danno un controllo più fine sulle scritture. Il passo successivo è dare forma alle letture: salvare una query come oggetto riutilizzabile con un nome, da cui fare SELECT come da una tabella. È una vista, ed è il prossimo argomento.