Transactions & Isolation
ACID in practice, what each isolation level actually permits, and the locking behaviour behind the incident.
All Or Nothing, Under Concurrency
| Property | Guarantees |
|---|---|
| Atomicity | All statements commit, or none do |
| Consistency | Constraints hold before and after |
| Isolation | Concurrent transactions do not corrupt each other |
| Durability | A committed write survives a crash |
Isolation is the one with a dial on it, because perfect isolation means running transactions one at a time. Every level below that trades a specific anomaly for concurrency, and the job is knowing which anomaly you just accepted.
Isolation Levels
| Level | Dirty read | Non-repeatable read | Phantom |
|---|---|---|---|
| Read uncommitted | Possible | Possible | Possible |
| Read committed | No | Possible | Possible |
| Repeatable read | No | No | Possible* |
| Serializable | No | No | No |
| Anomaly | Is |
|---|---|
| Dirty read | Reading data another transaction has not committed |
| Non-repeatable read | The same row read twice returns different values |
| Phantom read | The same query returns a different set of rows |
| Lost update | Two read-modify-writes, and one silently wins |
Defaults differ and it matters: PostgreSQL and Oracle default to read committed, MySQL InnoDB to repeatable read. Code that assumes one behaviour and runs against the other produces bugs that only appear under concurrency.
Optimistic & Pessimistic Locking
Two transactions read a balance of 100, both subtract 30, both write 70. One update is silently lost. Isolation alone does not prevent this at the usual levels — you have to choose a locking strategy.
-- pessimistic: take the lock up front, others wait
BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
UPDATE accounts SET balance = balance - 30 WHERE id = 42;
COMMIT;
-- optimistic: no lock; the version check fails the loser, who retries
UPDATE accounts
SET balance = 70, version = version + 1
WHERE id = 42 AND version = 7; -- 0 rows updated means someone beat you| Pessimistic | Optimistic | |
|---|---|---|
| Cost | Waiting, and deadlock risk | Wasted work on conflict |
| Best when | Contention is common | Conflicts are rare |
| Client must | Keep the transaction short | Handle the retry |
Where the operation is expressible as a single atomic statement — SET balance = balance - 30 — the database does it for you, and neither strategy is needed. Reach for the read-modify-write shape only when the new value genuinely depends on application logic.
Deadlocks & Long Transactions
A deadlock is two transactions each holding what the other needs. The database detects the cycle and kills one, so your application will see a deadlock error and must be able to retry it.
| Practice | Effect |
|---|---|
| Lock rows in a consistent order | Removes the cycle entirely — the real fix |
| Keep transactions short | Less time holding locks, fewer collisions |
| Never wait on a human or an API inside one | A transaction spanning a network call is an outage waiting |
| Retry deadlock errors with backoff | They are expected, not exceptional |
| Set a statement timeout | Bounds the damage of a runaway query |
Interview Questions
What do isolation levels trade?
Concurrency against specific anomalies. Stricter levels prevent dirty, non-repeatable and phantom reads but reduce throughput; every level below serializable accepts a named anomaly.
Dirty, non-repeatable and phantom reads — the difference?
A dirty read sees uncommitted data; a non-repeatable read gets different values for the same row twice; a phantom read gets a different set of rows for the same query.
Why do defaults matter?
PostgreSQL defaults to read committed and MySQL InnoDB to repeatable read. Logic written against one and deployed on the other breaks only under concurrency, which makes it hard to reproduce.
Optimistic or pessimistic locking?
Pessimistic where contention is common — take the lock and make others wait. Optimistic where conflicts are rare — check a version on write and retry the loser.
How do you prevent deadlocks?
Acquire locks in a consistent order across the codebase, keep transactions short, and retry on the deadlock error, which is expected rather than exceptional.
Why are long transactions harmful beyond locking?
They hold locks for longer than the work needs and block vacuum and log truncation, degrading the whole database rather than just the affected rows.