Transactions and Isolation

The problem: two transfers, one lost update

Ana's account holds $100. Two transfers into it run at the same moment: transfer A adds $50 and transfer B adds $30. Each runs in its own transaction: read the balance, add its amount in the application code, write the result back.

step  transaction  does                 balance
1     A            reads 100            100
2     B            reads 100            100
3     A            writes 150, commits  150
4     B            writes 130, commits  130

Both transactions commit without an error, and the balance is $130 instead of $180: A's $50 is gone. This is a lost update. Each transfer was right on its own, but B's write was built on a read from before A's. At read committed, the default level in PostgreSQL, nothing in the database stops it. The six moves of the two transfers can interleave in 20 orders, and 18 of them lose an update at that level: only the 2 where one transfer finishes before the other starts are safe.

ACID: four promises

• Atomicity: all or nothing. If a transaction doesn't commit (a crash, a lost connection), the database undoes the part that ran.
• Consistency: rules kept. A change that breaks a rule you declared, such as a balance of at least 0 or one account per email, is refused. Rules that live only in your code, your code must keep.
• Isolation: transactions don't step on each other. Ideally, transactions that run at the same moment give the same result as if they ran one after another.
• Durability: committed means saved. The change is on disk before the database answers "committed", so a crash or a power cut doesn't lose it.

Atomicity, consistency and durability guard one transaction on its own. Isolation is about many transactions at once, and full isolation is expensive, so databases let you choose how much of it you get: the isolation level.

Fixing the lost update

Wrapping the read and the write in a transaction doesn't help: they already were. Close the gap between reading and writing instead.

One atomic statement. Let the database do the arithmetic:

UPDATE accounts SET balance = balance + 50 WHERE id = 'ana';

The database runs updates to one row one after another. If A's update hasn't committed when B's arrives, B's waits for the row lock, then adds $30 to the row as it is: 100 becomes 150, then 180. Your code never holds an old copy.

Lock the row first. When your code must work out the new value, read the row with SELECT … FOR UPDATE inside the transaction. The row stays locked until the transaction ends, so B's locked read waits; once A commits, B reads $150 and writes $180. This is pessimistic locking (the Concurrency and Locks lesson); keep the locked work short.

Isolation levels and what they stop

The SQL standard names four levels, each keeping the promises of the one below and adding one.

Dirty read. At read uncommitted, a transaction may see changes that aren't committed. A $50 deposit writes Ana's balance as $150; a loan check that needs $120 reads $150 and approves; then the card behind the deposit is declined, and the deposit rolls back to $100. The loan was approved on money that never existed. Read committed stops it: a read sees only committed data, so the check reads $100 and declines.

Non-repeatable read. Read committed shows each commit the moment it happens. Ana's monthly statement reads her balance for its top line ($100), a $50 transfer commits, and the statement reads again for its last line: $150. One row, read twice in one transaction, gave two values. Repeatable read stops it. It's often built as snapshot isolation: the database keeps old versions of rows, and each transaction reads the data as it was when it started (in practice, from its first query), so both lines say $100 while the transfer still commits.

Write skew. A hospital needs at least one doctor on call. Ana and Ben are on call, and both ask to go off at the same moment, each in a snapshot transaction:

step  transaction  does                       on call
1     Ana          counts on call: 2          Ana, Ben
2     Ben          counts on call: 2          Ana, Ben
3     Ana          sets her row off, commits  Ben
4     Ben          sets his row off, commits  nobody

Each check was true in its snapshot, and they changed different rows, so nothing clashed. Two transactions read the same data, then wrote different rows based on it: that's write skew. Snapshot isolation allows it; serializable stops it, because the result must be the same as running the transactions one at a time, in some order.

level             dirty read  non-repeatable read  write skew
read uncommitted  possible    possible             possible
read committed    stopped     possible             possible
repeatable read   stopped     stopped              possible
serializable      stopped     stopped              stopped

Repeatable read here means the snapshot kind; a database that builds it with locks behaves differently. The lost update depends on the database: at repeatable read, some detect the clash and abort one transaction, others let the second write win. And the defaults differ: PostgreSQL's is read committed, MySQL's (InnoDB) is repeatable read.

The cost of stronger levels

PostgreSQL's serializable level uses serializable snapshot isolation (SSI): transactions run side by side, and the database watches what each reads and writes. Run the doctors at serializable, and both count 2 and set their rows; Ana's commits, and Ben's fails with a serialization error and is rolled back. Ben's app runs the transaction again, the new snapshot counts 1 on call, and Ben stays on.

Stronger levels mean more locking (where the database uses locks, transactions wait) or more aborted transactions, and your code must catch the error and run the whole transaction again. So most applications keep the default level and fix the hot spots explicitly: one atomic statement, a row lock, a constraint the database enforces, or serializable for the one transaction that needs it.

Transactions across services

When an order lives in one service and its payment in another, no single database transaction spans both. The usual tools are idempotency keys, so a retried step doesn't run twice, and a saga: a chain of steps, each with an undo. A later design lesson on payments builds them.

Where it shows up in design interviews

In any design where money or counts change: wallets, payments, likes, inventory, bookings. Interviewers listen for where a change happens and how it stays correct when two requests arrive at once. A strong answer names the guard: "A transfer is one atomic update, balance = balance + amount, that only runs if the balance stays at least 0. Booking a room checks for overlapping bookings, so that one transaction runs at serializable and retries on a conflict."

Common interview questions

What does ACID mean?

Atomicity: a transaction happens fully or not at all. Consistency: it can't break the rules you declared. Isolation: transactions running at once don't interfere, to the degree the isolation level promises. Durability: once committed, it survives a crash.

What's a lost update, and how do you prevent it?

Two transactions read the same value, each computes a new one, and the second write overwrites the first. Prevent it with one atomic statement (SET balance = balance + 50), a row lock (SELECT … FOR UPDATE), or a level that detects it.

What's the difference between repeatable read and serializable?

Repeatable read promises a row reads the same all through a transaction, often through a snapshot. Serializable promises a result equal to some one-at-a-time order, so it also stops write skew, at the cost of waits or retries.

Summary

• ACID: all or nothing, rules kept, no stepping on each other, committed means saved.
• Lost update: both read $100; A wrote $150, B wrote $130.
• Fixes: one atomic statement, or lock the row first.
• Read committed: no dirty reads; PostgreSQL's default.
• Repeatable read: a snapshot, so a row reads the same twice.
• Serializable: no write skew; transactions that clash abort and retry.
• The cost: keep the default, guard the hot spots.