Una transazione raggruppa più istruzioni in modo che abbiano effetto tutte o nessuna. La avvii con BEGIN, la rendi permanente con COMMIT, oppure annulli tutto ciò che è successo dopo BEGIN con ROLLBACK. Finché non confermi, le altre sessioni non vedono le tue modifiche.
Alice finisce con 70 e Bob con 80. Se il server si bloccasse tra i due update, non sopravviverebbe nessuno dei due: i soldi non possono lasciare un conto senza arrivare all'altro.
Annullare le modifiche con ROLLBACK
ROLLBACK butta via ogni modifica fatta dopo BEGIN:
Il DELETE ha rimosso tutte e tre le righe dentro la transazione, e il rollback le ha riportate indietro: il conteggio è 3. In psql, racchiudi allo stesso modo un UPDATE o un DELETE manuale rischioso. Eseguilo dopo BEGIN, controlla il risultato con una SELECT, poi scegli COMMIT o ROLLBACK.
Senza BEGIN, PostgreSQL esegue ogni istruzione in una transazione tutta sua e la conferma quando finisce (autocommit). I driver si comportano in modo diverso: psycopg in Python avvia una transazione alla prima istruzione e aspetta conn.commit().
Annullare parte di una transazione con SAVEPOINT
Un savepoint segna un punto dentro una transazione. ROLLBACK TO SAVEPOINT name annulla solo il lavoro fatto dopo quel punto, e la transazione continua:
Il log contiene "import users" e "import products". Il passo degli ordini è stato annullato senza perdere il primo insert. RELEASE SAVEPOINT name dimentica un savepoint che non ti serve più.
Risolvere "current transaction is aborted"
Quando un'istruzione fallisce dentro una transazione, PostgreSQL segna l'intera transazione come fallita e rifiuta ogni istruzione successiva finché non la chiudi:
BEGIN;
INSERT INTO accounts VALUES ('alice', 10); -- duplicate key: error
UPDATE accounts SET balance = 0;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
La soluzione è ROLLBACK. Un COMMIT su una transazione interrotta non salva niente; PostgreSQL risponde ROLLBACK. Per andare avanti dopo un errore previsto, imposta un savepoint prima dell'istruzione rischiosa e torna a quel punto. I driver e gli ORM che offrono le "transazioni annidate" funzionano così.
Annullare anche le modifiche alle tabelle
CREATE TABLE, ALTER TABLE, DROP TABLE e la maggior parte del DDL sono transazionali, quindi una migrazione che fallisce a metà non lascia niente dietro di sé:
to_regclass restituisce NULL: per tutti gli altri la tabella non è mai esistita. Alcuni comandi si rifiutano di girare dentro un blocco di transazione, tra cui VACUUM, CREATE DATABASE e CREATE INDEX CONCURRENTLY.
Scegliere un livello di isolamento
Il livello di isolamento decide quali commit delle altre sessioni vede una transazione. Lo imposti con BEGIN ISOLATION LEVEL ....
| Livello | Cosa vede ogni istruzione | Quando usarlo |
|---|---|---|
READ COMMITTED (predefinito) | I dati confermati prima che l'istruzione iniziasse | La maggior parte delle applicazioni |
REPEATABLE READ | Un'unica istantanea presa alla prima query della transazione | Report ed esportazioni che devono essere coerenti |
SERIALIZABLE | Come REPEATABLE READ, e PostgreSQL annulla le transazioni il cui risultato potrebbe differire da quello ottenuto eseguendole una alla volta | Regole di business che leggono e poi scrivono |
READ UNCOMMITTED viene accettato ma si comporta come READ COMMITTED: PostgreSQL non mostra mai dati non confermati. Con REPEATABLE READ e SERIALIZABLE, aspettati errori con SQLSTATE 40001 ("could not serialize access") e quando ne ricevi uno ripeti l'intera transazione.
Bloccare una riga che stai per modificare
Per impedire a un'altra sessione di modificare una riga tra la tua lettura e la tua scrittura, bloccala mentre la leggi con FOR UPDATE. Gli altri che scrivono aspettano finché non confermi:
BEGIN;
SELECT balance FROM accounts WHERE name = 'alice' FOR UPDATE;
UPDATE accounts SET balance = balance - 30 WHERE name = 'alice';
COMMIT;
Tenere brevi le transazioni
Una transazione aperta tiene i suoi lock sulle righe finché non finisce, e impedisce a VACUUM di rimuovere le vecchie versioni delle righe. Una sessione lasciata "idle in transaction" da uno script andato in crash può bloccare gli altri che scrivono per ore. Imposta idle_in_transaction_session_timeout per chiudere in automatico queste sessioni.
Dentro una transazione, now() restituisce la stessa ora per ogni istruzione: l'ora in cui la transazione è iniziata (vedi funzioni di data). Il tutorial ufficiale sulle transazioni ha altri esempi.
Domande frequenti
PostgreSQL usa l'autocommit?
Sì. Un'istruzione inviata fuori da BEGIN gira in una transazione tutta sua e viene confermata quando finisce. Alcuni driver fanno il contrario: psycopg in Python apre una transazione per te e aspetta commit(), a meno che tu non attivi l'autocommit.
Qual è il livello di isolamento predefinito in PostgreSQL?
READ COMMITTED. Ogni istruzione vede i dati confermati prima che l'istruzione iniziasse. Usa BEGIN ISOLATION LEVEL REPEATABLE READ o SERIALIZABLE quando una transazione deve vedere un'unica istantanea coerente.
Posso fare il rollback di CREATE TABLE o ALTER TABLE in PostgreSQL?
Sì. Quasi tutto il DDL in PostgreSQL è transazionale, quindi un CREATE, ALTER o DROP dentro una transazione sparisce con ROLLBACK. Alcuni comandi, come CREATE DATABASE, VACUUM e CREATE INDEX CONCURRENTLY, non possono proprio girare dentro un blocco di transazione.
Che differenza c'è tra COMMIT ed END in PostgreSQL?
Nessuna. END è un'estensione di PostgreSQL che fa lo stesso di COMMIT, e START TRANSACTION è la forma standard di BEGIN.