Lo spostamento di 300 rupie da Asha a Ben richiede due aggiornamenti: sottrarre da un conto e aggiungere all’altro. Se il sistema tra loro fallisce, il denaro svanisce. Le transazioni SQL esistono per renderlo impossibile, trattando entrambi i passaggi come una singola unità.

In questa guida esamineremo commit, rollback ed errori su una tabella di piccoli account. Abbiamo eseguito questi esempi in SQLite e i saldi mostrati provengono da quell’esecuzione. I comandi di base sono simili tra i database, ma il comportamento di blocco e isolamento varia molto, quindi indichiamo dove controllare la documentazione del proprio database.

Cos’è una transazione?

Una transazione è una sequenza di una o più istruzioni SQL che il database considera come un’unica operazione logica. Ecco la nostra tabella:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

Il vincolo CHECK è una regola secondo cui un saldo non può mai scendere sotto lo zero, che utilizzeremo in seguito. Si noti che la sintassi per iniziare una transazione varia: SQLite e PostgreSQL accettano BEGIN, anche MySQL ha START TRANSACTION e SQL Server utilizza BEGIN TRANSACTION.

Al di fuori di una transazione esplicita, la maggior parte dei database utilizza autocommit, dove ogni istruzione viene salvata non appena termina. Oracle è un’eccezione notevole, perché una sessione rimane in una transazione fino al commit.

IMPEGNO: renderlo permanente

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

-- Asha | 700
-- Ben  | 500

Fino all’esecuzione di COMMIT, le modifiche sono provvisorie. Una volta restituito con successo, il database garantisce che vengano salvati. Il totale su entrambi i conti è rimasto a 1200, che è il punto centrale dell’esercizio.

ROLLBACK: annulla tutto

ROLLBACK annulla tutto dall’inizio della transazione:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

Un rollback avviene automaticamente anche se la connessione si interrompe o il server si arresta in modo anomalo prima del commit, quindi una transazione completata a metà non sopravvive.

Quando un’affermazione fallisce

Ecco un caso più realistico. Asha tenta di inviare 5000, che il conto non contiene. Il credito a Ben viene eseguito per primo, quindi l’addebito infrange la regola CHECK:

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

Senza la transazione, Ben avrebbe tenuto i 5000 e i libri contabili sarebbero sbagliati. Tieni presente un dettaglio importante: un’istruzione non riuscita non sempre annulla la transazione da sola. In SQLite e MySQL, ad esempio, le istruzioni precedenti possono rimanere in sospeso, quindi il codice deve eseguire il rollback. PostgreSQL invece contrassegna l’intera transazione come interrotta e rifiuta ulteriori istruzioni finché non si esegue il rollback.

Nel codice dell’applicazione, associare sempre la transazione alla gestione degli errori. Ecco l’idea in Python e la nostra guida alla gestione delle eccezioni Python spiega la sintassi:

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

Punti di salvataggio

Un punto di salvataggio contrassegna una posizione all’interno di una transazione, quindi puoi annullare solo una parte del lavoro:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

Il bonus 999 è stato annullato, mentre il resto della transazione è stato portato avanti e impegnato. Ciò è utile per i passaggi facoltativi in ​​un processo lungo.

Spiegazione delle proprietà dell’ACIDO

La coerenza è spesso fraintesa. Il database applica solo le regole dichiarate, come vincoli e chiavi esterne. Ciò non impedirà al tuo codice di trasferire l’importo sbagliato. La durabilità viene solitamente ottenuta con un registro write-ahead, in cui le modifiche vengono registrate in modo sicuro su disco prima della conferma del commit.

Cosa succede con le transazioni simultanee?

Abbiamo aperto due connessioni a un file SQLite. Il primo ha avviato una transazione e aggiornato una riga da 1 a 2, senza ancora impegnarsi. Ecco cosa ha visto la seconda connessione:

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

SQLite consente un solo scrittore alla volta, il che è più semplice dei database dei server, dove molte transazioni possono scrivere righe diverse contemporaneamente. Ma il principio è lo stesso: le altre sessioni non dovrebbero vedere modifiche non confermate con impostazioni normali e le scritture in conflitto devono attendere. Nel nostro test, abbiamo impostato la seconda connessione in modo che si interrompesse immediatamente invece di aspettare, quindi l’errore è apparso immediatamente.

Leggi le anomalie

Quando le transazioni si sovrappongono, possono verificarsi alcuni problemi. Lo standard SQL li definisce in termini di ciò che una transazione può osservare:

Un altro problema è l’aggiornamento perso. Due sessioni leggono un saldo di 100, ciascuna aggiunge 10 e ciascuna scrive 110. Un aggiornamento è effettivamente svanito, quando la risposta dovrebbe essere 120.

Livelli di isolamento SQL

I livelli di isolamento consentono di scambiare la rigidità con la concorrenza. Ecco cosa consente lo standard SQL a ogni livello:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

La sintassi e il comportamento esatti dipendono dal database. Anche le impostazioni predefinite differiscono: PostgreSQL e SQL Server utilizzano la lettura impegnata, mentre InnoDB di MySQL utilizza la lettura ripetibile. Alcuni database forniscono garanzie più forti di quelle richieste dallo standard o trattano un livello come un altro. In PostgreSQL, ad esempio, la lettura senza commit si comporta come la lettura impegnata. Leggi sempre la documentazione del tuo database prima di fare affidamento su un comportamento specifico.

Deadlock

Un deadlock si verifica quando due transazioni detengono ciascuna un blocco di cui l’altra ha bisogno. La transazione A blocca la riga 1 e vuole la riga 2, mentre la transazione B blocca la riga 2 e vuole la riga 1. Nessuna delle due può continuare.

Database come PostgreSQL, MySQL e SQL Server lo rilevano e annullano una transazione con un errore. Questo è un comportamento normale e la tua domanda dovrebbe essere preparata per questo. Per ridurre i blocchi, aggiorna sempre le righe in un ordine coerente, ad esempio inserendo prima l’ID account più basso, mantieni le transazioni brevi e riprova quando ne viene scelto uno come vittima.

Migliori pratiche

• Mantieni le transazioni brevi. Quelle lunghe bloccano e bloccano altri utenti.
• Non aspettare mai un utente o un’API lenta all’interno di una transazione. Esegui questa operazione prima di iniziare o dopo.
• Gestire sempre gli errori. Ogni percorso dovrebbe terminare con un commit o un rollback.
• Aggiungi vincoli. Forniscono al database regole reali da applicare. Anche un buon design della tabella aiuta.
• Utilizza buoni indici.Ricerche più veloci significano che i blocchi vengono mantenuti per meno tempo.
• Fai attenzione ai nuovi tentativi. Riprova in caso di deadlock, ma assicurati che un’operazione non possa essere applicata due volte.
• Testare la concorrenza di proposito. Molti di questi bug compaiono solo sotto carico, quindi i test di integrazione di base da soli potrebbero non rivelarli.

Considerazioni finali

Le transazioni trasformano diversi passaggi rischiosi in un’operazione sicura e ACID descrive le garanzie che la rendono affidabile. Ogni volta che un’azione aziendale tocca più di una riga o tabella, chiediti cosa accadrebbe se si fermasse a metà. Se la risposta ti preoccupa, inseriscila in una transazione. Per esercitarti, apri due sessioni in un database locale e prova a produrre tu stesso le anomalie.

Domande frequenti

Continua a imparare