Transaction Isolation Levels Explained: Dirty Reads, Lost Updates, Write Skew and What Postgres and MySQL Really Do

It is 10:00:00 and tickets for a sold-out band go on sale. Seat A12 in the front row is still free. Two fans tap Buy at the same moment, two requests land on two app servers, and both check the database: "Is A12 taken?" Both hear "no". Both insert a booking. Both get a confirmation email. On the night, two people walk up to one seat. Nothing crashed, no query failed, and every line of code did what it said. The bug lives in the gap between two transactions, and that gap is what isolation levels are about.

For this blog, we'll stay inside one database: one PostgreSQL or MySQL primary with many concurrent connections. Replication lag, cross-shard transactions and MVCC internals get their own parts later.

01What a transaction promises, and why the "I" is slippery

You have probably met ACID before. Here it is in one line per letter:

• Atomicity: all of the transaction happens, or none of it does.
• Consistency: the database moves from one valid state to another, as far as your constraints describe "valid".
• Isolation: transactions running at the same time don't see or trample each other's work, to the degree the isolation level promises.
• Durability: once COMMIT returns, the data survives a crash.

Three of those are close to yes-or-no. Isolation is a dial, and the default setting is not the strongest one. That is why "we use transactions" does not mean "we have no race conditions".

Picture a concert hall box office before computers: one whiteboard of seats, several clerks on the phones. The safest rule is only one clerk at the whiteboard at a time. Nobody double-books, but callers wait. Faster rules let every clerk use the board at once: some read it live, some work from a photo of it, some stick a "mine" note on a seat first. Each rule is an isolation level, and each lets a different mistake slip through.

02The anomaly catalogue, one timeline each

An anomaly is a result you could never get if the transactions had run one after another. We'll use one small ticketing app for all of them (PostgreSQL syntax; MySQL needs small changes):

CREATE TABLE ticket_tiers (
  id      int PRIMARY KEY,
  show_id int NOT NULL,
  name    text,          -- 'Standing', 'Balcony'
  price   int,
  stock   int,           -- tickets left
  version int DEFAULT 0  -- used later for optimistic locking
);
CREATE TABLE bookings (
  id      bigserial PRIMARY KEY,
  show_id int, seat text, fan_id int
);

In every diagram below, Session A is the top row, Session B the bottom row, and the numbered badges give the order in which statements reach the database.

Dirty read: reading work that might be undone

A dirty read happens when a transaction reads data that another transaction has written but not committed. If that writer rolls back, the reader acted on a value that never officially existed.

The last standing ticket is in someone's checkout. Their transaction sets stock to 0, then the card is declined and it rolls back. In between, another fan loads the page.

-- Session A                                   -- Session B (READ UNCOMMITTED)
BEGIN; UPDATE ticket_tiers SET stock = 0 WHERE id = 3;
                                               SELECT stock FROM ticket_tiers WHERE id = 3;  -- 0
ROLLBACK;  -- stock is 1 again

You rarely meet this one. The PostgreSQL docs say its Read Uncommitted behaves like Read Committed, so dirty reads never happen. MySQL InnoDB allows them only if you choose READ UNCOMMITTED.

Non-repeatable read: same row, two answers

A non-repeatable read happens when a transaction reads the same row twice and gets two different committed values, because someone committed a change in between. A receipt job reads the Standing price for the line item, then again for the total. In between, the promoter raises the price.

-- Session A (receipt)                         -- Session B (promoter)
BEGIN; SELECT price FROM ticket_tiers WHERE id = 3;  -- 80
                                               UPDATE ticket_tiers SET price = 95 WHERE id = 3; COMMIT;
SELECT price FROM ticket_tiers WHERE id = 3;   -- 95 under READ COMMITTED

Its cousin is read skew: you read two related rows at slightly different moments and see a combination that never existed. Read Standing, the promoter moves 50 tickets from Standing to Balcony, then read Balcony: your report counts those 50 twice.

Phantom read: a new row joins the party

A phantom read happens when you rerun a query with the same WHERE clause and a different set of rows comes back, because another transaction inserted or deleted a matching row. Our app limits each fan to four tickets per show, so it counts what the fan holds before adding one. Meanwhile the same fan buys from a laptop too.

-- Session A (limit check)                     -- Session B (fan's laptop)
BEGIN; SELECT count(*) FROM bookings WHERE show_id = 7 AND fan_id = 42;  -- 3
                                               INSERT INTO bookings (show_id, seat, fan_id) VALUES (7, 'C3', 42); COMMIT;
SELECT count(*) FROM bookings WHERE show_id = 7 AND fan_id = 42;          -- 4

Phantoms matter because row locks can't easily stop them. You can lock a row that exists, but locking "every row that might match" needs a range or predicate lock, which is much harder. That comes back in write skew.

Lost update: the classic overselling bug

A lost update happens when two transactions read a value, compute a new one in application code, and both write it back. The second write silently overwrites the first. This read-modify-write race is behind most "we sold more than we had" incidents. The Standing tier has 10 left; fan 1 buys 2 and fan 2 buys 3, at the same time.

-- both sessions, interleaved as in the diagram
SELECT stock FROM ticket_tiers WHERE id = 3;          -- 10 for both
UPDATE ticket_tiers SET stock = 8 WHERE id = 3;       -- A: 10 - 2, computed in the app
UPDATE ticket_tiers SET stock = 7 WHERE id = 3;       -- B: 10 - 3, overwrites A

The bug is in the shape of the code: arithmetic outside the database, a literal written back. The database had no idea 8 and 7 both came from 10.

Write skew: the double booking from the opening

Now the hard one. Write skew happens when two transactions read the same data, each decides it is safe, and each writes a different row. Nothing was overwritten, so nothing looks like a conflict, yet together they break a rule both checked. This is the seat A12 story: the rule is "one booking per seat", checked before inserting.

-- each session, at the same time
SELECT 1 FROM bookings WHERE show_id = 7 AND seat = 'A12';   -- no rows
INSERT INTO bookings (show_id, seat, fan_id) VALUES (7, 'A12', 7);  -- B inserts fan 9
COMMIT;

The per-fan limit is write skew too: both devices count 3, both insert, and the fan ends with five. The shape is always the same: check a condition, then write something that changes it, with the check and the write not protected together. When the check is "does a matching row exist?", there is no row yet to lock.

03The four SQL-standard levels

The SQL standard names four isolation levels. It defines the three weaker ones by which phenomena they must prevent, and it only defines three phenomena: dirty read, non-repeatable read and phantom read. Serializable is defined differently: the result must equal some one-at-a-time order of the transactions. The PostgreSQL docs add a fourth column, "serialization anomaly", for everything else that a serial order would forbid.

Look at the last column. The two anomalies that do the most real damage are not in the standard's list. The standard only sets a floor, and each database builds something different on top. Jepsen's write-up points to A Critique of ANSI SQL Isolation Levels by Berenson and colleagues, which formalised snapshot isolation partly because the standard's wording was ambiguous.

So the name tells you less than you think. The real question is: what does my database actually do at this level, for this anomaly?