Transactions & Isolation Levels

Transactions & Isolation Levels

A transaction groups multiple SQL statements into a single atomic unit. Either all statements succeed and are committed, or all are rolled back. This is the foundation of ACID compliance.

-- Transfer money between accounts atomically
BEGIN;

UPDATE accounts SET balance = balance - 100.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE id = 2;

-- Verify before committing
SELECT id, balance FROM accounts WHERE id IN (1, 2);

COMMIT;
-- or ROLLBACK; to undo everything

Postgres also supports SAVEPOINT for partial rollbacks within a transaction — useful in complex stored procedures.

BEGIN;

INSERT INTO audit_log (event) VALUES ('start');

SAVEPOINT before_risky_op;

UPDATE large_table SET status = 'processed' WHERE condition;

-- If something looks wrong:
ROLLBACK TO SAVEPOINT before_risky_op;

-- Continue with something else
INSERT INTO audit_log (event) VALUES ('aborted risky op');

COMMIT;

Isolation Levels

SQL defines four isolation levels that control how concurrent transactions see each other's changes. Postgres implements all four.

• READ UNCOMMITTED — Postgres treats this as READ COMMITTED; it never allows dirty reads.
• READ COMMITTED (default) — each statement sees only data committed before it started. A second statement in the same transaction may see new data committed by others.
• REPEATABLE READ — all statements in the transaction see a snapshot taken at the first statement. Prevents non-repeatable reads and phantom reads (in Postgres).
• SERIALIZABLE — the strictest level. Transactions behave as if they ran one at a time. May abort with a serialization error if a conflict is detected.

-- Set isolation level for a transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT sum(balance) FROM accounts;  -- snapshot is taken here
-- ... long calculation ...
SELECT sum(balance) FROM accounts;  -- same snapshot, same result

COMMIT;