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

  1. BEGIN starts a transaction. Changes are made, but other users can't see them yet (isolation).
  2. The database writes each change to a write-ahead log on disk.
  3. 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).
  4. ROLLBACK, or a crash before COMMIT: every change in the transaction is undone (atomicity).
  5. Rules such as 'balance must not go below 0' are checked, so an invalid state is never committed (consistency).
  6. 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

Common mistakes

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.