Transactions: all or nothing

A transaction is a group of operations that the database treats as a single unit: either they all succeed, or none of them leaves a trace. It's how you protect yourself from inconsistencies when a sequence of UPDATE/INSERT/DELETE must stay consistent.

The syntax

BEGIN;             -- apri la transazione

UPDATE …;
INSERT …;
DELETE …;
SELECT …;          -- puoi anche solo leggere

COMMIT;            -- conferma tutte le modifiche
-- oppure
ROLLBACK;          -- annulla tutte le modifiche, come se nulla fosse

Everything between BEGIN and COMMIT is atomic: no other connection sees the intermediate state. If the program crashes or executes ROLLBACK, the database stays exactly as it was before BEGIN.

The classic scenario: the transfer

The canonical example is a stock transfer between products (or a money transfer between accounts): two UPDATEs that must succeed together, never one yes and the other no.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
UPDATE products SET stock = stock + 1 WHERE id = 2;
COMMIT;

If the second UPDATE fails (e.g. a CHECK constraint error), a ROLLBACK restores the stock of product 1 to its original value.

ROLLBACK: the DBA's debugger

BEGIN;
UPDATE customers SET city = 'X' WHERE id = 1;
SELECT first_name, city FROM customers WHERE id = 1;   -- "vedo come è cambiato"
ROLLBACK;

A very useful pattern for trying out an UPDATE on real data: you run it, inspect the result with a SELECT in the same transaction, and if it doesn't convince you, you ROLLBACK — the database is exactly as it was before.

SAVEPOINT: partial rollback

Inside a transaction you can create intermediate points:

BEGIN;
UPDATE customers SET city = 'A' WHERE id = 1;
SAVEPOINT step1;
UPDATE customers SET city = 'B' WHERE id = 2;
-- mi sono accorto che il secondo UPDATE è sbagliato:
ROLLBACK TO SAVEPOINT step1;
COMMIT;     -- solo il primo UPDATE viene confermato

ACID in 30 seconds

Transactions guarantee the ACID properties:

• Atomicity — all or nothing;
• Consistency — constraints stay satisfied at the start and end of the transaction;
• Isolation — concurrent transactions don't see intermediate states (at various "levels");
• Durability — a successful COMMIT is not lost even in case of a crash.

Your turn

Review exercise