Transactions and ACID
Date: 2026-08-17
A group of operations that either all happen or none do. The atomicity is the famous part and the easy part; isolation is where the subtlety lives, because the default isolation level in most databases permits anomalies people assume are impossible.
A transaction is a unit of work treated as indivisible. ACID names four guarantees:
ATOMICITY all operations, or none
CONSISTENCY constraints hold before
and after
ISOLATION concurrent transactions
don't corrupt each other
DURABILITY committed means committed,
even through a crash
Atomicity, the obvious one
BEGIN;
UPDATE accounts SET balance = balance - 50
WHERE id = 1;
UPDATE accounts SET balance = balance + 50
WHERE id = 2;
COMMIT;A crash between those two statements loses £50 without a transaction. With one, the whole thing rolls back.
The commerce version is more familiar: create the order, decrement stock, record the payment. Any subset of those succeeding is a data problem someone will have to fix by hand.
Isolation, where the difficulty is
Isolation is a dial, not a switch, and the settings permit specific anomalies:
LEVEL PREVENTS
READ UNCOMMITTED nothing
↑ dirty reads: you can see another
transaction's uncommitted changes
READ COMMITTED dirty reads
← the default in Postgres, SQL Server
↑ non-repeatable reads: read the same
row twice, get different values
REPEATABLE READ + non-repeatable reads
← the default in MySQL/InnoDB
↑ phantoms: a range query returns
different rows the second time
SERIALIZABLE everything
behaves as if transactions ran
one after another
The two most popular databases ship different defaults, which is a genuine portability trap and one of the more useful facts on this page.
The anomaly nobody expects
Write skew passes every check and still breaks the rule:
RULE: at least one doctor on call
Alice's txn Bob's txn
reads: 2 on call reads: 2 on call
"fine, I can leave" "fine, I can leave"
sets Alice off sets Bob off
commit commit
↓
ZERO on call
Neither transaction wrote the same row. No lock conflicted. Both read a valid state and made it invalid — and only SERIALIZABLE prevents it.
The commerce shape is the same: two concurrent orders each checking “is stock available”, each seeing 1, each proceeding — Race Conditions.
The practical fixes
1. Let the database do the arithmetic — atomic, with no read-then-write gap. Check rows affected: 0 means it failed.
UPDATE stock SET qty = qty - 1
WHERE id = 5 AND qty >= 1;2. Take an explicit lock, serialising access to those rows.
SELECT ... FOR UPDATE;3. Optimistic locking — 0 rows affected means someone else got there first, so retry.
UPDATE ... WHERE id = 5 AND version = 3;4. Unique constraints — the only reliable way to prevent duplicates.
Option 1 handles most of it, and it’s the one people reach for last because the read-then-write version reads more naturally in application code.
Durability, and its asterisks
Committed data survives a crash — but the guarantee has settings:
synchronous commit off faster, a small
window of loss
replication lag the primary has it,
the replica doesn't
fsync behaviour the OS or disk may
still be buffering
“Committed” on a replica is a weaker claim than on the primary, which is exactly the gap that produces read-your-writes bugs — Eventual Consistency.
Practical guidance
- Keep transactions short. A transaction held open across a network call holds locks for the duration and blocks everyone else
- Never hold one across a user interaction. A multi-step checkout is not one database transaction
- Know your isolation level. Not the level you assume — check it
- Don’t retry blindly. A serialisation failure should be retried; a constraint violation should not
- Distributed transactions across services are best avoided. Two-phase commit is fragile at scale; the usual answer is idempotent steps with compensating actions — Idempotency