Transactions and isolation levels

A transaction is all of it or none of it

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

If anything fails between BEGIN and COMMIT — a constraint, a crash, a lost connection — neither update survives. That is the A in ACID, and it is the easy part. The four letters promise different things, and only two of them are usually what a bug is about:

Isolation is the one with a dial on it, and the one people are surprised by.

The anomalies the levels are named after

• Dirty read — you see a change another transaction has not committed. Practically no engine you will meet allows it by default.
• Non-repeatable read — you read a row twice in one transaction and get two different values, because someone committed in between.
• Phantom read — you run the same WHERE twice and the second run returns a row that was not there before.

* PostgreSQL's repeatable read uses snapshot isolation, which happens to prevent phantoms too. This is the kind of detail that makes "what does repeatable read do" a question with a per-engine answer. The defaults differ as well: read committed in PostgreSQL and Oracle, repeatable read in MySQL's InnoDB.

The bug this causes in real code

BEGIN;
SELECT stock FROM products WHERE id = 1;     -- 1 left
-- application decides: enough stock, proceed
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

Two requests run that at once and both read 1. Both decide there is enough. Stock ends at -1. Nothing errored, no isolation level below serializable prevented it, and the transaction did its job — the read and the write were simply not atomic together.

Three ways out, in increasing cost:

-- 1. Do the check in the write itself. No read, no race.
UPDATE products SET stock = stock - 1
WHERE id = 1 AND stock > 0;
-- then check how many rows were affected

-- 2. Lock the row for the duration of the transaction.
SELECT stock FROM products WHERE id = 1 FOR UPDATE;

-- 3. Let the database refuse the conflict.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

The first is the one to reach for: a conditional update is one statement, and one statement is atomic by definition. Serializable is correct but pushes the problem onto you — transactions can now fail with a serialization error, and the application must retry them.

Deadlocks are a lock-ordering problem

Two transactions, each holding what the other wants:

T1: UPDATE accounts id=1   T2: UPDATE accounts id=2
T1: UPDATE accounts id=2   T2: UPDATE accounts id=1
        both wait, forever

The engine detects the cycle and kills one with a deadlock error. The fix is not a retry loop, it is ordering: if every transaction touches rows in the same order — by primary key, say — the cycle cannot form. Keep a retry as a backstop, not as the strategy.

Keep them short

A transaction holds locks and a snapshot until it ends. Two habits cause most production incidents here: doing an HTTP call inside a transaction, and opening one at the start of a request handler and committing at the end. Both hold a database resource for as long as something unrelated takes. Open late, commit early, and never wait on a network inside one.