Transaction Isolation Levels: From Dirty Reads to Write Skew

Both bookings go through. Each request asks the database whether the hall is free, each sees an empty calendar, and each saves its booking. No line of code is wrong. The bug is in what each transaction was allowed to see, and transaction isolation levels are the setting that controls it.

Picture a common setup. A booking site for banquet and wedding halls lets families pick a hall, a date and a slot: lunch, dinner or the full day. They pay an advance of Rs 50,000 online. The setup is made up, but the bug is real.

On a popular wedding date, one hall gets two confirmed bookings. One family took the dinner slot and another took the full day, in two requests about 40 milliseconds apart (made up). The logs show that each request checked the calendar and found it empty.

The booking code never chose an isolation level, so it ran on the database default. This guide follows that booking site from the simplest anomaly to the double booking. We ran each database behaviour in it on PostgreSQL 16.15 and MySQL 8.0.46 in October 2026. The manual for MySQL 8.4, a long-term support (LTS) series, describes the same rules.

Transaction isolation levels are settings that control how much concurrent transactions see of each other's work. A stronger level hides more and prevents more anomalies, but it can make transactions wait or fail.

Five words make the rest of this guide easy. Each one comes with a booking-site example.

• Transaction: a group of database steps that succeed or fail together. Example: check the slot, save the booking and record the advance as one unit.
• Commit and rollback: commit makes a transaction's changes permanent and visible to others. Rollback throws its changes away, as if they never happened.
• Concurrent: two transactions are concurrent when their lifetimes overlap, even by a few milliseconds. The two wedding-date requests were concurrent.
• Anomaly: a result that could never happen if the transactions had run one after another. The double booking is one, because a request running alone would have seen the first booking.
• Snapshot: a frozen view of the committed data at one moment. Some levels give a transaction one snapshot, so it reads a steady picture while others keep writing.

The strongest promise is called serializable. It means the final result matches some order in which the transactions ran one at a time. The SQL standard defines three weaker levels by listing the anomalies each one may allow.

• Read Uncommitted: may even see another transaction's unsaved changes.
• Read Committed: sees only committed data, but each statement may see newer data than the last.
• Repeatable Read: a row you have read keeps its value until you finish.
• Serializable: the result matches some one-at-a-time order, with no anomalies at all.

A 1995 paper, A Critique of ANSI SQL Isolation Levels, showed that these definitions miss real problems. It defined the lost update and write skew anomalies. It also defined snapshot isolation, where each transaction reads from its own snapshot, and showed it is not serializable.

A dirty read happens when one transaction reads data that another transaction has written but not yet committed. If that other transaction rolls back, the reader acted on data that never really existed.

On the booking site, a booking first writes a hold row, then waits for the payment gateway. Suppose the availability page could read uncommitted data. It would show the hall as taken while the payment is still pending.

Follow the steps in the diagram below. The key moment is the third box, where a family leaves because of a hold that is about to vanish.

The hall is free again, but the second family has already gone elsewhere. A dirty write is worse: one transaction overwrites another's uncommitted change. In our tests, both PostgreSQL and MySQL made the second writer wait, even at Read Uncommitted.

Read Committed stops dirty reads, and it is PostgreSQL's default. PostgreSQL accepts Read Uncommitted as a setting, but its documentation says it behaves like Read Committed. In our PostgreSQL test, a Read Uncommitted transaction never saw the unsaved price.

A non-repeatable read is when you read the same row twice and get different values. A phantom read is when you run the same search twice and get a different set of rows.

Both come from the same cause. Under Read Committed, each statement sees everything committed before that statement started. So two statements in one transaction can see two different versions of the world.

A non-repeatable read on an invoice

The invoice job reads the hall's price, Rs 1.5 lakh (150,000), to print the base amount. An admin then raises the price to Rs 1.65 lakh and commits. The job reads the price again to work out GST, the tax on the booking.

The invoice now shows a base of Rs 1.5 lakh with tax worked out on Rs 1.65 lakh. Each read was correct when it ran. Together they are nonsense.

A phantom row in the night report

The night report counts the bookings for 12 December and finds 3. A new booking commits a moment later. The report then lists the bookings and prints 4 rows under a heading that says 3.

The new row is called a phantom because it appeared inside a search the transaction had already run. No existing row changed, so locking the rows you already read cannot stop it.

Repeatable Read fixes both in PostgreSQL, because the whole transaction reads from one snapshot taken at its first statement. MySQL's InnoDB, the part of MySQL that stores tables by default, does the same for plain reads. In our tests on both, the price and the row count stayed the same inside the transaction.

A lost update happens when two transactions read the same value, both change it in application code, and both write it back. The second write silently wipes out the first.

The booking site runs a winter offer: the first 50 bookings get free decoration. Two families book when 12 offers are left. Both requests read 12, both subtract 1, and both write 11. One offer was given away but never counted.

The naive fix is to wrap the read and the write in one transaction. That is not enough. At Read Committed, both PostgreSQL and MySQL still ended at 11 in our test: the second writer waited for the first, then overwrote it.

The cleanest fix is to let the database do the maths in one statement. The row stays locked from read to write, so the second request waits and then works on the new value.

• Atomic update: UPDATE offers SET left_count = left_count - 1 WHERE id = 7 AND left_count > 0. The check and the change happen together.
• Row lock: SELECT ... FOR UPDATE locks the row you read, so the other request waits until you commit.
• Version check: store a version number, and update only WHERE version equals the one you read, adding 1 to it. If no row changes, read again.
• Stronger level: at PostgreSQL's Repeatable Read, if the first writer commits, the second fails with error 40001, could not serialize access due to concurrent update. Your code then retries.

The 1995 paper calls this rule first-committer-wins: when two snapshot transactions write the same row, only one may commit. PostgreSQL's version of this rule makes the second writer wait for the first. If the first rolls back instead, the second carries on.

Do not assume every database does this at Repeatable Read. MySQL's Repeatable Read let the lost update through in our test, matching Hermitage, an open set of anomaly tests for real databases. The atomic update ended at 10 in both databases.

Write skew happens when two transactions read the same data, each decides a write is safe, and each writes a different row. Each write is fine alone, but together they break a rule.

Picture a factory with two night watchmen and one rule: at least one must stay on duty. At 2 a.m., each looks at the roster, sees the other marked on duty, and signs off. Both checks were honest. The gate is now empty.

Where the picture breaks: a roster on a wall can only be read. A database at Serializable can notice the pattern and cancel one of the two transactions before the gate is left empty.

Nobody overwrote anybody, which is why locks on the rows being written do not help. The clash lives in the rule that links two rows.

It will not. Follow both transactions in the diagram below and notice that they never touch the same row.

Each request reads from its own snapshot, taken before the other committed. Snapshot isolation only stops two transactions that write the same row, and these wrote different rows.

You can watch the same logic in plain Python. The code copies the committed list once per transaction, which is what a snapshot is. The clashes helper says whether a new slot overlaps any slot in a list.

committed = []                      # bookings for one hall on 12 December

def clashes(existing, slot):
    return any(s == slot or "full" in (s, slot) for s in existing)

snap_a = list(committed)            # A takes its snapshot
snap_b = list(committed)            # B takes its snapshot

if not clashes(snap_a, "dinner"):
    committed.append("dinner")      # A commits
if not clashes(snap_b, "full"):
    committed.append("full")        # B commits, no shared row

print(committed)

['dinner', 'full']

Real databases agree. In our tests, a check-then-insert pair saved two bookings at Read Committed and at Repeatable Read, in both PostgreSQL and MySQL. At PostgreSQL's Serializable, the second commit failed with error 40001.

The Hermitage results say the same: Repeatable Read prevents neither form of write skew in PostgreSQL or MySQL. That includes the form where the check is a search, like the clash query.

Only Serializable prevented every anomaly in this guide, in both databases. Each weaker level allows some of them, and the list depends on the database, not just the level's name.

The table comes from our runs on PostgreSQL 16.15 and MySQL 8.0.46, with two sessions stepped by hand. It matches the PostgreSQL documentation and the Hermitage results. Yes means the anomaly happened; phantoms here mean plain reads.

Notice the error codes. PostgreSQL's stronger levels mostly let transactions run, then cancel one with error 40001 when a conflict appears. MySQL's Serializable takes locks instead.

Those locks can end in a deadlock, where two transactions each wait for the other. MySQL then cancels one with deadlock error 1213. Either way, your code must catch the error and run the whole transaction again.

The SQL standard itself is looser than PostgreSQL. It allows phantoms at Repeatable Read and dirty reads at Read Uncommitted. PostgreSQL gives you more than that minimum.

PostgreSQL defaults to Read Committed, and MySQL's InnoDB defaults to Repeatable Read. As of 19 October 2026, the PostgreSQL 18 manual says so, as do the manuals for MySQL 8.4 and 9.7, both LTS series. Neither default stopped the double booking in our tests.

Check what your own session runs. The commands below print the current level, and the comments show the output to expect on a default install.

-- PostgreSQL, in psql
SHOW transaction_isolation;
-- expected on a default install:
--  transaction_isolation
-- -----------------------
--  read committed

-- MySQL
SELECT @@transaction_isolation;
-- expected on a default install: REPEATABLE-READ

The two defaults also take their snapshots differently. PostgreSQL's Read Committed takes a fresh snapshot for every statement. MySQL's Repeatable Read keeps the snapshot made by the transaction's first plain read.

MySQL gap locks block inserts, then deadlock

MySQL has one more tool. A locking read over a range, such as SELECT ... FOR UPDATE with a date condition, also locks the gaps between index entries at Repeatable Read. These gap locks block other sessions from inserting new rows into that range.

There is a catch. The MySQL manual says gap locks do not conflict with each other, so both booking requests can lock the same empty day. In our test, with an index on hall and date, both then tried to insert, and MySQL cancelled one with deadlock error 1213.

So the locking clash query does stop the double booking at Repeatable Read, but only through an error you must retry. At Read Committed, InnoDB turns gap locking off for searches, and both bookings went through.

PostgreSQL's FOR UPDATE takes no gap locks. It locks only rows that already exist, and an empty day has none. In our test, the locking clash query at Read Committed did not block, and both bookings were saved.

You prevent double booking by making the clash visible to the database as a real conflict. Use a constraint, lock one shared row, or run the check and insert under Serializable with retries.

Go back to the watchmen. The fix is not a sharper pair of eyes. It is one shared register that only one guard can sign at a time, and every fix below is a version of that register.

Use the diagram to pick a fix. Start at the top and answer each question about your own rule.

Fix 1: an exclusion constraint

A plain unique key on hall, date and slot is not enough, because dinner and full day are different values. So store each booking as a time range instead. Full day covers 8 a.m. to midnight, and dinner covers 6 p.m. to midnight.

PostgreSQL supports an exclusion constraint, a rule that no two rows may match in a given way. The one below rejects any booking whose range overlaps another booking for the same hall. A tsrange is a range of timestamps, and && means overlaps.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE hall_bookings (
    hall_id     int NOT NULL,
    during      tsrange NOT NULL,
    customer_id int NOT NULL,
    EXCLUDE USING GIST (hall_id WITH =, during WITH &&)
);

-- dinner, 6 p.m. to midnight
INSERT INTO hall_bookings VALUES (7, '[2026-12-12 18:00, 2026-12-13 00:00)', 1);
-- full day, 8 a.m. to midnight: overlaps dinner
INSERT INTO hall_bookings VALUES (7, '[2026-12-12 08:00, 2026-12-13 00:00)', 2);

USING GIST tells PostgreSQL to check the rule with a GiST index, an index type that can test overlaps. Expected result: the first insert prints INSERT 0 1. The second fails with conflicting key value violates exclusion constraint, error code 23P01. A lunch from 11 a.m. to 4 p.m. is still accepted, because it does not overlap dinner.

The btree_gist extension is needed for the hall_id WITH = part. Without it, PostgreSQL 16 refused the table: data type integer has no default operator class. With it, two concurrent overlapping inserts gave one booking at every isolation level: the second waited, then failed.

Fix 2: lock one shared row

If no constraint can express the rule, create a row both requests must touch. Add a hall_days table with one row per hall per date. Every booking first runs SELECT ... FOR UPDATE on that row, then its clash check.

At Read Committed this works. In our test the second request waited at the lock, then ran its clash check as a new statement with a fresh snapshot, and saw the first booking. This trick is called materializing the conflict: you turn an invisible clash into a real row.

The fix at Repeatable Read is to change the shared row, not just lock it, for example with UPDATE hall_days SET event_date = event_date. The waiting request then fails with error 40001, and its retry, a new transaction, sees the booking.

The PostgreSQL documentation hints at this. It raises the error only when the first transaction actually updated or deleted the row, not just locked it.

Fix 3: Serializable with a retry loop

Serializable needs no schema change, only a setting and a retry loop. PostgreSQL implements it as Serializable Snapshot Isolation, or SSI. It records which rows and searches each transaction read, using special read locks that never block anyone. Two writes to the same row still wait as usual.

When it spots a pattern that no one-at-a-time order could produce, it cancels one transaction with error 40001, a serialization failure. The PostgreSQL documentation says applications must be prepared to retry these, from the beginning of the transaction.

Here is the booking function with that loop. It uses psycopg 3, a Python driver for PostgreSQL, and we ran it on psycopg 3.3.6. Open the connection with autocommit on, where each statement commits by itself, so each conn.transaction() block is exactly one transaction.

import random
import time

import psycopg
from psycopg import errors

CLASH_SQL = """
    SELECT 1 FROM bookings
    WHERE hall_id = %(hall)s AND event_date = %(day)s
      AND (slot = %(slot)s OR slot = 'full' OR %(slot)s = 'full')
"""
INSERT_SQL = """
    INSERT INTO bookings (hall_id, event_date, slot, customer_id)
    VALUES (%(hall)s, %(day)s, %(slot)s, %(customer)s)
"""

def book_slot(conn, hall, day, slot, customer):
    # conn was opened with autocommit=True, so each
    # conn.transaction() block below is one whole transaction
    conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
    args = {"hall": hall, "day": day, "slot": slot, "customer": customer}
    for attempt in range(3):
        try:
            with conn.transaction():
                if conn.execute(CLASH_SQL, args).fetchone():
                    return "taken"
                conn.execute(INSERT_SQL, args)
            return "booked"
        except errors.SerializationFailure:
            # the other booking may still be committing:
            # wait a little, then check again from the start
            time.sleep(random.uniform(0.01, 0.05) * (attempt + 1))
    return "busy, please try again"

Three lines matter. Setting isolation_level makes each new transaction block serializable. The except line catches only error 40001, never other failures. On the retry, the clash check sees the winner's booking and returns taken.

The short random wait is not decoration. Without it, our test of 1,000 rounds of two simultaneous requests gave up with busy 216 and 254 times in two runs. An instant retry often started before the winner had committed, so it hit the same conflict.

With a random wait of 10 to 50 milliseconds that grows on each attempt, every losing request returned taken, in 2,000 rounds. Retry the whole transaction, never just the failed statement, because everything it read may be out of date.

Serializable is not free. The 2012 paper on PostgreSQL's SSI measured a cost of less than 7% against snapshot isolation in its benchmarks. Busy rows still mean more cancellations, and each one repeats work, so keep serializable transactions short.

Use your database's default for most work, and protect each risky rule with the narrowest fix that works. Raise the level only for the transactions that need it.

After the incident, the team sorted every write path on the booking site into five groups.

• Simple reads and single-row writes, like a profile edit, stay on Read Committed.
• Counters and stock, like the decoration offer, use atomic updates.
• Rules across many rows, like hall slots, get an exclusion constraint where one fits.
• Rules no constraint can express use Serializable with a retry loop and a short random wait.
• Reports that must add up, like the night report, run as one read-only Repeatable Read transaction.

Then test the fix the way it failed. Our script fired a dinner request and a full-day request for the same hall at the same moment, 1,000 times. With the plain check-then-insert at Read Committed, 980 and 978 rounds ended in a double booking in two runs.

With the Serializable retry loop, none did. Your numbers will differ with your machine, but the gap between hundreds and zero is the check that matters.

Name the rule that concurrent requests could break, then pick the lightest tool that protects it. Interviewers want to hear the anomaly by name and the trade-off of your fix.

1. 1

   State the invariant

   Say the rule in one line, such as one booking per hall slot. An invariant is a rule that must always stay true.

2. 2

   Show the race

   Walk through two requests that both check, then both write. Name the anomaly: lost update for one row, write skew for a rule across rows.

3. 3

   Say what the default allows

   Mention that PostgreSQL defaults to Read Committed and MySQL to Repeatable Read, and that with plain reads neither stops write skew.

4. 4

   Pick the fix

   Prefer an atomic update or a constraint. Use a lock on a shared row or Serializable when the rule is more complex.

5. 5

   Plan for retries

   Say that PostgreSQL's Serializable cancels some transactions with error 40001, and MySQL's locks can deadlock with 1213. Retry the whole transaction a few times, with a short random wait.

6. 6

   Mention scale

   If bookings are split across database shards, a constraint on one shard cannot see the others. Keep each hall's data on one shard, so one database sees every booking for it.