Isolation Levels, Locking and Deadlocks

Two sessions run the same read-modify-write and one update disappears. A check that the system has at least one admin on call passes twice and leaves none. A batch job and a web request take the same two rows in opposite orders and the database kills one of them with an error nobody expected. All three are the same subject: what concurrent transactions are allowed to see, and what they hold while they look.

Every example ran on MySQL 9.6, the engine whose defaults surprise people most. PostgreSQL behaviour is quoted where it differs.

Five read phenomena have precise definitions, and two are missing from the standard

The names matter: "race condition" is not actionable and "non-repeatable read" tells you which level to reach for.

Phenomenon	What happens
Dirty read	A transaction reads data written by a concurrent transaction that has not committed.
Non-repeatable read	A transaction re-reads a row it already read and finds it changed, because another transaction committed in between.
Phantom read	A transaction re-runs a query with a search condition and the set of matching rows has changed.
Lost update	Two transactions read the same value, both compute a new one from it, and the second write erases the first.
Write skew	Two transactions read overlapping data, each writes a different row, and the pair breaks a rule neither broke alone.

The first three are the ones the standard names and assigns to levels. The last two are absent from its table, which is why isolation levels do not rescue you from them.

MySQL defaults to REPEATABLE READ, PostgreSQL to READ COMMITTED

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

PostgreSQL's manual states that Read Committed is the default there, and that Read Uncommitted behaves as Read Committed, because that is the only sensible mapping onto its architecture. MySQL implements all four, including a genuine Read Uncommitted, which is how the dirty read below is possible.

The standard's table, read as a minimum and not a promise:

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Not possible	Possible	Possible
REPEATABLE READ	Not possible	Not possible	Allowed by the standard
SERIALIZABLE	Not possible	Not possible	Not possible

MySQL's REPEATABLE READ is stronger than the name suggests in one direction and weaker in another. Every plain SELECT gets the snapshot established by the first read, which is more than the standard requires, so a plain read never sees a phantom. It does not make a locking read consistent, and the seam shows below.

A dirty read needs READ UNCOMMITTED, and MySQL will give you one

A row starts at 10. Session B opens a transaction, writes 999 and holds it open, while session A reads at READ UNCOMMITTED:

+----------+-----+
| step     | qty |
+----------+-----+
| A read 1 |  10 |
+----------+-----+
+------------------------------------------+-----+
| step                                     | qty |
+------------------------------------------+-----+
| A read 2 (uncommitted writer still open) | 999 |
+------------------------------------------+-----+

--- after session B rolled back ---
+----------------------+
| value_after_rollback |
+----------------------+
|                   10 |
+----------------------+

Session A read a value that never existed in any committed state of the database. At READ COMMITTED or above the same read returns 10, and the only way to get a dirty read is to ask for one.

Non-repeatable reads and phantoms change with the level

The same test at both levels, with session B committing qty = 20 while session A is open:

READ COMMITTED
| A first read  |  10 |
| A second read |  20 |

REPEATABLE READ
| A first read  |  10 |
| A second read |  10 |

At READ COMMITTED each statement gets a fresh snapshot, so the second read sees the committed change. At REPEATABLE READ the snapshot was fixed by the first read, so the second returns the old value. That decides what a report running several queries in one transaction shows you. Now the seam: session B inserts a matching row, and session A reads three ways.

REPEATABLE READ
| plain read 1 | 2 |
| plain read 2 | 2 |
| locking read | 3 |

READ COMMITTED
| plain read 1 | 2 |
| plain read 2 | 3 |
| locking read | 3 |

At REPEATABLE READ the two plain reads agree on 2 and the SELECT ... FOR UPDATE returns 3. The locking read is not reading the snapshot; it reads the latest committed version, because its job is to hand you a row you are about to change. MySQL's REPEATABLE READ prevents phantoms only for reads that take no lock, which is a narrower claim than the level name suggests.

Raising the level does not fix a lost update

Two sessions read a counter of 100, each adds one, each writes its own answer:

--- session A ---
| step    | views |
| A reads |   100 |
--- session B ---
| step    | views |
| B reads |   100 |
--- final value ---
+-------------+
| final_views |
+-------------+
|         101 |
+-------------+

Both ran at REPEATABLE READ, MySQL's default, and the result is 101 instead of 102. Neither transaction did anything the level forbids: each read a consistent value, each wrote a legal value, and the second write overwrote the first because the value it computed came from a read taken before the other.

The fix is not a higher level. It is to stop splitting the read from the write. Either let the statement do the arithmetic, so the increment happens against the row the write will lock:

UPDATE page SET views = views + 1 WHERE id = 1;

or read the row behind a lock so the second session cannot proceed until the first has finished:

BEGIN;
SELECT views FROM page WHERE id = 1 FOR UPDATE;
UPDATE page SET views = 101 WHERE id = 1;
COMMIT;

The first form is better whenever the new value can be expressed from the old one: one lock, held for one statement, the same code on every engine. The second is for when the new value depends on something the database cannot compute. Either way, a read that a write depends on belongs in the same statement as the write, or behind a lock.

Write skew survives REPEATABLE READ because the rows do not overlap

Two doctors are on call, and the rule is that at least one always is. Each checks the count and, seeing two, takes themselves off:

--- session A ---
| step           | n |
| A sees on call | 2 |
--- session B ---
| step           | n |
| B sees on call | 2 |
--- final state ---
+-----------------+
| doctors_on_call |
+-----------------+
|               0 |
+-----------------+
+-----------+------+---------+
| doctor_id | name | on_call |
+-----------+------+---------+
|         1 | ana  |       0 |
|         2 | bo   |       0 |
+-----------+------+---------+

Both ran at REPEATABLE READ. Each read the count through the same snapshot and saw 2, which was true when it read, then updated a different row, so no lock conflicted. A rule about the collection was checked by two transactions that each saw a valid collection.

No constraint catches this. A CHECK sees one row and a foreign key sees two, and neither can express "at least one row in this table has this flag". PostgreSQL's manual is blunt about the general case: enforcing business rules at REPEATABLE READ is not likely to work without explicit locks, because even with a stable snapshot there may be no serial order matching what happened.

Three repairs, in the order worth trying. Lock the decision with SELECT ... FOR UPDATE over the rows being counted. Change the model so the invariant is a row you can lock, such as a single roster row every change must update. Or run at SERIALIZABLE, which on PostgreSQL means Serializable Snapshot Isolation and an abort with SQLSTATE 40001 for one of the two, with a retry.

Row locks block writers, table locks block readers, and SKIP LOCKED builds a queue

What matters about a row lock is what it does not do. With session A holding one row open, session B tries two updates:

| step                      |
| B updated a different row |
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The other row updated immediately. Only the update of the locked row waited, until innodb_lock_wait_timeout (50 seconds by default) expired. PostgreSQL's manual puts the same point precisely: row-level locks do not affect data querying, because they block only writers and lockers to the same row. A plain SELECT never waits for one.

Table locks are the other shape. In PostgreSQL only an ACCESS EXCLUSIVE lock blocks a plain SELECT, and that is what TRUNCATE, DROP TABLE, VACUUM FULL and most ALTER TABLE take. In MySQL the same effect comes from LOCK TABLES and from the metadata lock every DDL statement takes, which is why an ALTER TABLE on a busy table queues behind every open transaction that touched it and then blocks new queries behind itself. That is the mechanism behind a table going unavailable because a schema change waited on a long-running read.

The row lock becomes a queue when you add SKIP LOCKED. Six pending jobs, two workers, two jobs each:

FOR UPDATE SKIP LOCKED
--- session A ---            --- session B ---
| A claims |  1 |            | B claims |  3 |
| A claims |  2 |            | B claims |  4 |

FOR UPDATE (no SKIP LOCKED)
--- session A ---            --- session B ---
| A claims |  1 |            | B claims |  1 |
| A claims |  2 |            | B claims |  2 |

Without SKIP LOCKED, session B waited for session A to finish and claimed the same two rows, so two workers did the same job twice. With it, session B skipped the locked rows and took the next two. The claim has to be one statement:

BEGIN;
SELECT job_id FROM job
WHERE status = 'pending'
ORDER BY job_id
LIMIT 2
FOR UPDATE SKIP LOCKED;
UPDATE job SET status = 'running' WHERE job_id IN (1, 2);
COMMIT;

Three details make it work. The ORDER BY keeps the claim order deterministic. The FOR UPDATE holds the rows until the transaction ends, so the status update and the release of the lock are one event. And SKIP LOCKED returns an incomplete result set by design, so it belongs in a queue claim and never in a report where a skipped row changes the answer. Both engines support it; SQLite does not.

Deadlocks are a cycle the engine detects and breaks

Session A updates account 1 then account 2; session B updates account 2 then account 1. Where each waits on the other, the lock graph has a cycle:

--- session A ---
| result      |
| A committed |

--- session B ---
ERROR 1213 (40001) at line 5: Deadlock found when trying to get lock;
try restarting transaction

--- final balances ---
+----+---------+
| id | balance |
+----+---------+
|  1 |      90 |
|  2 |     110 |
+----+---------+

Session A committed and session B was rolled back entirely, including the update it had already made and thought was safe. InnoDB watches for cycles and breaks them by aborting one. PostgreSQL does the same, and its manual notes that which transaction gets aborted is difficult to predict and should not be relied on.

The engine reports the cycle in full, which is the part worth reading rather than skipping:

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
UPDATE account2 SET balance = balance + 10 WHERE id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `m3lab`.`account2` ... lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
UPDATE account2 SET balance = balance + 5 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `m3lab`.`account2` ... lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

Read it as two columns: what each transaction holds and what it waits for. Every deadlock report says the same thing in a different format, and the rows under HOLDS are the ones to trace back to a statement.

Two code rules prevent most of these. Take locks in a consistent order: if every code path touching accounts 1 and 2 touches them lowest id first, the cycle cannot form, because the second session waits instead of holding. Keep transactions short and out of network calls, since the longer a transaction holds its first lock, the larger the window for another to take the lock it will want second. PostgreSQL's manual gives both rules as the defence. Handle the rest by catching error 1213 and SQLSTATE 40001, rolling back, and retrying the whole transaction, because a deadlock the engine cannot detect surfaces as a lock wait timeout instead, which is slower and harder to diagnose.

Show the solution and the reasoning

One session gets ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction and the other commits. Which one is not predictable and not stable across runs, which is why the retry belongs in the code. Both manuals say the choice should not be relied on.

The status output has two blocks, one per transaction, each with a HOLDS THE LOCK(S) section and a WAITING FOR THIS LOCK TO BE GRANTED section. The rows under HOLDS for transaction 1 are the rows transaction 2 waits on, and the other way round. Following each set of rows back to the statement that took it is the diagnostic technique.

Booking in ascending order would have prevented the deadlock. Session B would block on the first seat rather than taking the second, so it would hold nothing while waiting and no cycle could close. The rule costs nothing and removes most deadlocks in practice, which is why "touch rows in primary key order" belongs in a review checklist.

The count query does not prevent the seats from exceeding the limit. At REPEATABLE READ each session reads the same snapshot, both see one seat booked, both conclude a second is allowed, and both insert. They insert different rows, so no lock conflicts and no cycle forms; the limit is broken by two transactions that each saw a legal state. A constraint cannot express the rule either. The repairs are the ones from the write skew section: lock the slot with SELECT ... FOR UPDATE before counting, or run at SERIALIZABLE and retry the loser.