ACID transactions and isolation levels
A transaction groups several operations so they succeed or fail together. ACID stands for Atomic, Consistent, Isolated, Durable.
Why it matters
Real operations change several things at once: take money from A, give it to B. If the system crashes halfway, or two users act at the same moment, money can be created or destroyed.
The idea
Atomic: all or nothing. Consistent: the database moves from one valid state to another, so its rules always hold. Isolated: transactions running at the same time don't see each other's half-finished work. Durable: once committed, the change survives a crash. Isolation comes in levels — read committed, repeatable read, serializable — trading safety for speed.
A bank transfer: the money leaves account A and arrives in account B, or neither happens. You never see the money 'in the air'.
The picture is one bank. Across two databases or two services you usually can't use one ACID transaction, so you need patterns like sagas instead.
How it works
- BEGIN starts a transaction. Changes are made, but other users can't see them yet (isolation).
- The database writes each change to a write-ahead log on disk.
- COMMIT: the log is saved to disk, and only then is success reported. After a crash, the database replays the log, so committed changes survive (durability).
- ROLLBACK, or a crash before COMMIT: every change in the transaction is undone (atomicity).
- Rules such as 'balance must not go below 0' are checked, so an invalid state is never committed (consistency).
- When two transactions touch the same row, locks or version numbers decide who goes first, according to the isolation level.
Example
Transfer £100: begin, take £100 from A (only if A has it), add £100 to B, commit. If anything fails, roll back and both balances are unchanged.
Key points
- Anomalies: dirty reads, non-repeatable reads, phantom reads, lost updates, write skew.
- Read committed (a common default) stops dirty reads; repeatable read or snapshot stops non-repeatable reads; serializable stops all of them but is slowest.
- Lost-update fixes: SELECT … FOR UPDATE (pessimistic lock) or a version column (optimistic locking).
- Durability comes from a write-ahead log saved to disk before the commit is confirmed.
- Distributed transactions (two-phase commit) exist but are slow and fragile; prefer sagas across services.
- Hands-on: Transactions and ACID in the SQL course runs real BEGIN, COMMIT and ROLLBACK examples.
Common mistakes
- Checking a balance in one query and updating it in another without a lock: a lost update.
- Assuming the default isolation level prevents every anomaly.
- Keeping a transaction open for a long time (for example, while calling another service), which holds locks and blocks others.
Not true: Reading a balance in one query and updating it in the next works correctly without a lock.
In fact: Two requests can both read 100, both subtract, and both write: one update is lost. Lock the row with SELECT … FOR UPDATE, or check a version column.
Not true: Long transactions: fine to keep one open while the code calls another service.
In fact: An open transaction holds its locks, so a slow call blocks everyone who needs those rows. Keep transactions short, and call other services outside them.
In the interview
For anything involving money or stock I'd use ACID transactions in a relational database. To stop two users buying the last item, I'd use a row lock with SELECT FOR UPDATE, or optimistic locking with a version number. I'd also pick the isolation level that blocks the anomalies that matter.
Part of Unit 3: Data: databases, storage and scaling data in the free System Design course.