System designCore15 min

Transactions & isolation levels

Moving $100 between two accounts takes two writes. A crash between them loses money, and another request running at the same moment can see, or break, the half-done state. A transaction is the database's promise about both, and the isolation level is the fine print.

1 · The problem

A transfer is two writes, and anything can happen between them

A small bank's app lets a customer move money between checking and savings. Our customer has $500 in each, $1,000 in total. Moving $100 to savings takes two writes: take 100 from checking, then add 100 to savings. Now the server crashes between them:

Bank server

Checking row

Savings row

subtract 100: 500 → 400

server crashes

still 500

The first write landed and the second never ran. Checking says 400 and savings says 500: $100 is gone, and nothing in the data says where it went.

Crashes are only half of it. A statement job adding up the balances between the two writes sees $900, even if nothing crashes. And two requests changing the same balance at the same moment can erase each other's work.

A transaction is the database's answer. It groups several reads and writes into one unit with only two possible endings:

Commit: every write takes effect, together.
Abort, also called rollback: none of them does, as if the transaction never ran. The database aborts on its own after a crash or a conflict, and your code can abort on purpose.

The transfer as one transaction

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'checking-7';
UPDATE accounts SET balance = balance + 100 WHERE id = 'savings-7';
COMMIT;

In PostgreSQL and MySQL, a statement run without BEGIN is still its own small transaction (autocommit). That protects one statement, not the pair. A transaction also stops at the edge of one database: a transfer between two banks, or between two services with their own databases, needs Distributed transactions: 2PC & sagas.

2 · The promise

ACID is four promises, and they are not equally strong

Databases describe what a transaction promises with four letters, ACID. They sound like one guarantee. They are four different ones, kept by different parts of the system:

Letter	The promise at the bank	Who keeps it
Atomicity	The transfer happens completely or not at all; an aborted one leaves no trace	The database, by undoing every write of an aborted transaction
Consistency	Rules such as "the total never goes below zero" hold after every transaction	Mostly your code; the database checks only the rules you declare to it
Isolation	Transactions running at once don't trip over each other	The database, as far as the isolation level you choose
Durability	A committed transfer survives a crash or a power cut	A log on disk, and copies on other machines

Atomicity is easiest to picture as abortability. If anything fails before the commit, the database throws away every write the transaction made, so your code can simply try again. The one gap: if the commit succeeded but the reply was lost, a retry runs the transfer twice. Idempotency covers that case.

Consistency is the odd letter out. The database can enforce CHECK (balance >= 0), a unique username or a foreign key. A rule it doesn't know about, like "checking plus savings never below zero", is your code's job. The C in ACID is also not the C in CAP; Consistency models untangles the meanings.

Durability comes from a write-ahead log. The database first appends the transaction's changes to a log file and forces that file onto disk. Only then does it reply "committed". The data files themselves are updated later:

Only the log has to reach disk before the reply, and it is only ever appended at its end, which disks do quickly. One flush can commit many transactions at once. After a crash the database redoes committed changes from the log; a transaction with no commit record never happened.

Durability is never absolute. A disk can die with the log on it, which is why databases also copy the log to other machines (Database replication). And it can be traded away on purpose: PostgreSQL's asynchronous commit replies before the log reaches disk, so a crash can lose the last few transactions, although it never corrupts the data.

One misconception to drop: "our database is ACID" says less than it sounds. The I comes with a dial, and the most widely used databases ship with that dial turned down.

3 · The bugs

Weak isolation lets six kinds of bugs through

Perfect isolation would mean every transaction behaves as if it had the database to itself. That costs waiting or retries, so databases offer weaker levels, and each weaker level lets certain bugs through. Here is the full list, all at our bank. Each one needs two transactions running at the same time:

Bug	What happens at the bank
Dirty write	Two transfers overwrite each other's uncommitted writes to the same balance, mixing them into one result
Dirty read	The app shows $600 in savings from a transfer that then rolls back
Read skew, or non-repeatable read	A statement reads savings before a transfer and checking after it, and prints $900
Lost update	Two deposits both read $500, each adds its amount and writes, and one deposit vanishes
Write skew	Two withdrawals each check a rule over both accounts, then each lower a different one; the rule breaks
Phantom	Two withdrawals each count two free ones this month, and each inserts a third; the customer gets four. This is write skew through a new row; a phantom read, where the same query returns a new row the second time, is the milder form

The first two involve data that isn't committed yet. The other four happen with nothing but committed data, which is why they surprise people. Read skew is the easiest to watch. The statement job reads one balance, the transfer commits, and the job reads the other:

Statement job

Database

Transfer

read savings

500

checking 500 → 400

savings 500 → 600

commit

read checking

400

total: $900

Each value the job read was committed and correct at the moment it read it. Taken from two different moments, they add up to a total that never existed. Run the job again and it prints $1,000, so the bug is hard to reproduce.

Write skew and phantoms are the subtle ones, and they get their own section below. First, which level stops which bug.

4 · The levels

Each isolation level is a list of bugs it still lets through

Four levels are in common use. Read the table from the right: whatever a level still allows is what your code has to handle.

Level	Stops	Still allows
Read uncommitted	Dirty writes	Dirty reads, plus everything read committed allows
Read committed	Dirty writes and dirty reads	Read skew, lost updates, write skew, phantoms
Snapshot isolation	Also read skew, lost updates and phantom reads (the same query sees the same rows)	Write skew, including the phantom kind: write skew through newly inserted rows
Serializable	All six	Nothing; you pay in waiting or retries instead

Read committed gives each statement a fresh view of committed data, so the statement job's two reads can come from two different moments. Snapshot isolation gives the whole transaction one view, frozen when it starts. The job would print $1,000, and keep printing it while transfers commit around it.

Snapshots are cheap because of multiversion concurrency control (MVCC). A write doesn't destroy the old value. The database keeps earlier versions of the row, each labeled with the transaction that wrote it, and each reader sees the newest version committed before its own snapshot:

Two readers, two versions of the same row, and no waiting: readers never block writers, and writers never block readers. The cost is old versions piling up until no snapshot needs them and the database cleans them away. A transaction left open for hours holds that cleanup back.

Now the trap: the names databases give these levels don't match the table. The SQL standard describes its weaker levels by three bugs only (dirty reads, non-repeatable reads and phantoms). It says nothing about lost updates or write skew, so each database filled the gaps its own way:

Database	Default	What the names mean
PostgreSQL	Read committed	"Repeatable read" is snapshot isolation; "serializable" is truly serializable
MySQL (InnoDB)	Repeatable read	Plain reads use a snapshot, but writes see the latest data, so lost updates and write skew get through
Oracle	Read committed	"Serializable" is snapshot isolation, so write skew gets through
SQL Server	Read committed	Uses locks by default; snapshot isolation is a separate level, switched on per database

So "we run at repeatable read" means different things on different databases. Check what your database actually prevents, in its documentation or with a published test suite, never by the name alone.

5 · The catch

Two writers can each be right and the result still be wrong

Two deposits arrive at the same moment: $100 from a paycheck and $50 from a refund. Each one reads the balance, adds its amount in the app, and writes the result back:

Step	Deposit A (+$100)	Deposit B (+$50)
Reads the balance	500	500
Adds its amount	500 + 100 = 600	500 + 50 = 550
Writes	600	550, a moment later

The balance ends at $550, because B wrote last. The right answer is $650. This lost update has four standard fixes. The first two:

Let the database do the arithmetic. SET balance = balance + 100 reads and writes in one statement. Under read committed, the second deposit waits for the first to commit, then adds its 50 to 600.
Lock what you read. SELECT ... FOR UPDATE locks the row, so the other deposit waits until this transaction ends.

The other two:

Compare and set. Keep a version number on the row and write ... WHERE id = 7 AND version = 12. If someone else got there first, no row matches, so you read again and retry.
Let the database notice. Snapshot isolation aborts the second of two transactions that write the same row. PostgreSQL's repeatable read and Oracle's serializable both do; MySQL's repeatable read does not.

Write skew is harder, because the two transactions never write the same row. The bank lets checking go below zero as long as checking plus savings stays at or above zero. An $800 ATM withdrawal from checking and an $800 bill payment from savings happen at the same moment:

ATM withdrawal

Database

Bill payment

read both: 500 + 500

1,000 − 800 ≥ 0: ok

checking 500 → −300

savings 500 → −300

commit

total: −$600

Each transaction checked the rule on a consistent snapshot and was right about what it saw. They wrote different rows, so snapshot isolation found no conflict and committed both. Run one after the other, and the second would have been refused.

Snapshot isolation lets this through because it compares only what transactions write. The fix has to cover what they read:

Lock the rows the rule reads. SELECT ... WHERE customer = 7 FOR UPDATE locks both accounts, so the second withdrawal waits until the first finishes and can't act on the old balances.
Use serializable, which tracks reads too (next section).

The phantom version is worse, because the row to lock doesn't exist yet. Take "three free withdrawals a month". Two withdrawals each count the two already made, each decides the next one is free, and each inserts a new row. Neither touched a row the other wrote, and there was no third row to lock.

Three ways out:

Serializable.
A constraint the database checks. Number free withdrawals 1 to 3 with a unique index, and two transactions can't both take number 3.
A row made to be locked, such as one per customer per month, which every withdrawal locks first.

6 · The strongest

Serializable stops every bug, and you pay in waiting or retries

Serializable promises that the result equals some order of running the transactions one at a time. Not necessarily the order in which they started, and with no promise about real time; that stronger guarantee is called strict serializable. Databases reach serializable in three ways:

Method	How it works	What it costs
Actual serial execution	One thread runs one transaction at a time, so nothing can interleave	Transactions must be short and in memory; one core's speed is the limit
Two-phase locking	Reads take shared locks, writes exclusive ones, plus locks on ranges; all held until commit	Readers and writers wait for each other; deadlocks, where the database aborts one side
Serializable snapshot isolation	Snapshots as before, plus tracking what each transaction read; aborts when a dangerous pattern appears	No extra waiting, but some aborts, a few of them needless

Redis transactions are the first kind. Commands queued with MULTI run back to back when EXEC arrives, and no other client's command runs in the middle. But Redis doesn't roll back: if one command fails, the rest still run. "Transaction" doesn't mean ACID everywhere.

MySQL's and SQL Server's serializable levels use locking. PostgreSQL's uses serializable snapshot isolation, which in published benchmarks ran only slightly slower than plain snapshot isolation, and much faster than locking on read-heavy work. Here is the write skew from the last section, run again under it:

ATM withdrawal

Database

Bill payment

read both: 1,000

checking → −300

savings → −300

commit

abort: serialization failure

retry: read both

−300 + 500 = 200

200 − 800 < 0: refuse

The database noticed that each transaction had read a row the other then wrote. It let the first commit and aborted the second. The retry reads fresh balances, −300 and 500, and refuses the payment.

That retry is not optional. Locking databases abort a transaction to break each deadlock, and serializable snapshot isolation aborts on conflicts, so the application has to be ready to run them again:

Run the whole transaction.
Begin, do every read and write, commit.
Catch the right errors.
A serialization failure (code 40001 in PostgreSQL) or a deadlock. Anything else is a real error.
Start again from the reads.
Values read in the aborted attempt may be stale. Never reuse them.
Cap the attempts.
A few retries with backoff, then report the failure (Timeouts, retries, backoff & jitter).
Keep side effects out.
Send the email or call the payment provider after the commit, or make the call idempotent; a retry would repeat it.

7 · In practice

Use the weakest level that still keeps your rules

There's no right level for a whole system, only for each rule. Write down the rules your data must keep. For each one, ask which transactions read it and which write it:

Bank feature	Bug to fear	Enough to stop it
Showing a balance	Seeing a transfer that later rolls back	Read committed
Monthly statement	Read skew between the two accounts	One snapshot, such as PostgreSQL's repeatable read
Deposit	Lost update	`balance = balance + amount`, at any level
Joint overdraft rule	Write skew	Serializable, or FOR UPDATE on both rows
Three free withdrawals	Write skew through new rows	Serializable, or a unique slot number

Whatever the level, keep transactions short:

No waiting on people. Never hold a transaction open while a user decides. Read and show, then check again inside a short transaction when they confirm.
No slow calls inside. A network call in the middle holds locks or a snapshot for its whole duration.
Mark reads as reads. Declaring a transaction read-only lets PostgreSQL's serializable level skip some of its checking.

And remember where transactions stop. They protect the data inside one database. Holding a lock across machines is Distributed locks; keeping the databases of two services in step is Distributed transactions: 2PC & sagas.

Check yourself

5 questions

1. The server crashes after subtracting $100 from checking but before adding it to savings, inside one transaction. What does the customer see after the restart?

2. At read committed, a statement job reads savings, a transfer commits, and the job then reads checking. Which bug can it hit?

3. Two deposits each run SELECT balance, add their amount in the app, then UPDATE with the result. What fixes it, even at read committed?

4. Under snapshot isolation, two withdrawals each check "checking + savings ≥ 0" and each lower a different account. Why does the database commit both?

5. Your database aborts a transaction with a serialization failure at the serializable level. What should the application do?

Takeaways

Remember this

A transaction makes several writes inside one database take effect together or not at all.
Durability comes from a write-ahead log forced to disk before the database replies "committed".
Each isolation level is defined by what it still allows: read committed allows read skew, lost updates and write skew; snapshot isolation still allows write skew.
Level names differ between databases: PostgreSQL's repeatable read and Oracle's serializable are both snapshot isolation.
Serializable stops every anomaly but aborts some transactions, so the application must retry the whole transaction.