Transactions & isolation levels

A transaction starts with BEGIN, runs statements, and ends with COMMIT (make every change permanent) or ROLLBACK (undo all of them). ACID describes the guarantees: Atomicity (all or nothing), Consistency (constraints hold before and after), Isolation (concurrent transactions do not interfere in forbidden ways) and Durability (once committed, the change survives a crash, thanks to a write-ahead log: WAL in PostgreSQL, the redo log in InnoDB).

Isolation is a trade-off between correctness and concurrency. The SQL standard defines four levels by which anomalies they allow: Read Uncommitted (dirty reads possible), Read Committed (no dirty reads, but a re-read can change: non-repeatable reads), Repeatable Read (rows you read do not change, but new matching rows can appear: phantoms) and Serializable (the result is as if transactions ran one at a time).

Real engines differ from the textbook. PostgreSQL defaults to Read Committed; its Read Uncommitted behaves like Read Committed, its Repeatable Read is snapshot isolation that also prevents phantoms, and its Serializable uses SSI, which aborts a transaction with a serialization failure rather than allow an anomaly. MySQL InnoDB defaults to Repeatable Read, using a consistent snapshot for plain SELECTs and next-key locks for locking reads and writes.

1. 1Most modern engines use MVCC (multi-version concurrency control): an update writes a new version of the row instead of overwriting it, so readers see a consistent snapshot without blocking writers, and writers do not block readers.
2. 2At Read Committed, each statement sees a fresh snapshot of data committed before that statement began. Two SELECTs in the same transaction can therefore see different values.
3. 3At Repeatable Read (PostgreSQL) or with consistent reads in InnoDB, the snapshot is taken at the first read of the transaction and reused, so plain reads never change during it.
4. 4Writes to the same row are still serialised with row locks: a second transaction updating a row that another transaction has updated but not committed waits. In PostgreSQL Read Committed, after the first commits, the waiting UPDATE re-checks its WHERE clause against the new row version; at Repeatable Read or Serializable it fails with a serialization error instead.
5. 5SELECT ... FOR UPDATE locks the rows it reads so no one else can change them until you commit; FOR SHARE allows other readers to lock too but blocks writers.
6. 6If two transactions each wait for a lock the other holds, the database detects the deadlock and aborts one of them. Applications must catch serialization failures and deadlocks (SQLSTATE 40001 and 40P01 in PostgreSQL) and retry the whole transaction.

-- accounts(id, balance): transfer 80 from account 1 to account 2
BEGIN;

-- Lock both rows in a fixed order (lowest id first) to avoid deadlocks
SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

-- Debit only if funds are sufficient; app checks that 1 row was updated
UPDATE accounts SET balance = balance - 80
WHERE id = 1 AND balance >= 80;

-- If 0 rows were updated: ROLLBACK and report insufficient funds
UPDATE accounts SET balance = balance + 80
WHERE id = 2;

COMMIT;

-- Stricter isolation for one transaction (PostgreSQL / standard syntax):
-- BEGIN ISOLATION LEVEL SERIALIZABLE;   -- retry on SQLSTATE 40001
-- MySQL: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION;

Starting from balances (1, 100) and (2, 0), one transfer leaves (1, 20) and (2, 80). A concurrent second transfer waits on the FOR UPDATE lock, then sees balance 20, its debit updates 0 rows, and the app rolls it back. Either both updates happen or neither does.

Cost: Higher isolation means more locking or more aborted and retried transactions, which lowers throughput under contention. MVCC keeps reads non-blocking but leaves old row versions to clean up (VACUUM in PostgreSQL, purge in InnoDB).