CS Fundamentals Trainer · Guides

Transaction isolation levels

Dirty reads to write skew, the four standard levels versus what PostgreSQL and MySQL really do, and how to fix each problem.

20 min read

When two transactions touch the same data at the same time, the database has to decide what each of them is allowed to see of the other. That decision is the isolation level, the "I" in ACID. Choose too weak a level and you get bugs that appear once a month under load and vanish when you try to reproduce them. Choose too strong a level and you pay in speed and in transactions that must be retried. This guide shows the problems that can happen, the four standard levels, what real databases actually do, and the tools you have to fix each problem.

1. What isolation promises

A transaction groups statements so that they succeed or fail together. Isolation adds a promise about concurrency: each transaction behaves as if it ran alone, even though many run at once.

The strongest form of that promise is serializability: the outcome of running transactions concurrently is the same as if they had run one after the other, in some order. Real systems often weaken the promise to go faster, and the SQL standard names the weaker levels by the odd behaviours they still allow. Those behaviours are called anomalies, and you need to be able to recognise each.

2. The anomalies

Each example shows two transactions, T1 and T2, with time running downwards.

Dirty read

T2 reads data that T1 has written but not committed. If T1 rolls back, T2 acted on something that never existed.

TimeT1T2
1UPDATE accounts SET balance = 0 WHERE id = 1
2SELECT balance FROM accounts WHERE id = 1 → 0
3ROLLBACK(decides, based on 0, to refuse an order)

Non-repeatable read

T1 reads a row twice and gets different answers, because T2 changed and committed it in between.

TimeT1T2
1SELECT price FROM items WHERE id = 7 → 10
2UPDATE items SET price = 12 WHERE id = 7, COMMIT
3SELECT price FROM items WHERE id = 7 → 12

Phantom read

T1 runs the same query twice and gets a different set of rows, because T2 inserted (or deleted) a row that matches.

TimeT1T2
1SELECT count(*) FROM orders WHERE total > 100 → 5
2INSERT INTO orders (total) VALUES (150), COMMIT
3SELECT count(*) FROM orders WHERE total > 100 → 6

The difference from the non-repeatable read: no row T1 had already seen changed. A new row appeared. Locking the rows you read cannot stop that, because the row did not exist yet.

Lost update

Two transactions read a value, calculate a new one, and write it back. The second write overwrites the first, which is silently lost.

Diagram: Lost update: two deposits of 10, only one counts

This is the bug behind "read, change in application code, save" patterns, and the classic case of two people editing the same record.

Whether the database stops it depends on the engine and the level. In my test on PostgreSQL 16, Read Committed let it happen (final balance 110), while Repeatable Read failed the second write with could not serialize access due to concurrent update (SQLSTATE 40001), so the application could retry. That check is PostgreSQL's; MySQL's InnoDB at Repeatable Read does not raise an error here, because its updates always act on the latest committed version of the row. I could not run MySQL, so that last point rests on its documentation [1]. Do not rely on the level to save you: use one of the fixes in section 5.

Write skew

Two transactions each read the same set of rows, each decide the rule still holds, and each write to a different row. Neither overwrote the other, yet together they break the rule.

The standard example: a hospital needs at least one doctor on call. Alice and Bob are both on call, and both ask to go off call at the same moment.

Diagram: Write skew: both doctors leave, nobody is on call

Write skew is the anomaly people forget, and it is the one that separates "snapshot" levels from truly serializable ones. It appears in bookings (two people reserve the last room), unique usernames without a unique constraint, and any "check, then act" across several rows [2][3].

Read skew

A cousin of the non-repeatable read, seen across two rows that belong together. T1 reads the first row, T2 moves money between the two rows and commits, then T1 reads the second row, and T1's picture of the data never existed. Take two accounts with 100 each (200 in total). T1 reads account 1 (100). T2 moves 50 from account 1 to account 2 and commits. T1 reads account 2:

Level (PostgreSQL 16)What T1 sawTotal
Read Committed100 + 150250 (money appeared)
Repeatable Read100 + 100200 (a consistent snapshot)

Backups and reports are the classic victims, which is why a long read needs a snapshot (section 6).

Try write skew yourself

You can watch the doctors example happen in two psql windows (A and B). First, once:

CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

Then, step by step (the number of the step is the order in which you press Enter):

StepWindow AWindow B
1BEGIN ISOLATION LEVEL REPEATABLE READ;BEGIN ISOLATION LEVEL REPEATABLE READ;
2SELECT count(*) FROM doctors WHERE on_call; → 2SELECT count(*) FROM doctors WHERE on_call; → 2
3UPDATE doctors SET on_call = false WHERE name = 'alice';
4UPDATE doctors SET on_call = false WHERE name = 'bob';
5COMMIT;COMMIT;

Both commits succeed, and SELECT count(*) FROM doctors WHERE on_call; now returns 0. Reset (UPDATE doctors SET on_call = true;) and repeat with ISOLATION LEVEL SERIALIZABLE in both windows. The first commit goes through; the second fails with ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001, and the hint says it might succeed if retried). Once more at Read Committed with SELECT name FROM doctors WHERE on_call FOR UPDATE; as the first query in each window: B now waits at that query until A commits, then sees only one doctor on call and can refuse.

3. The four standard levels

The SQL standard defines the levels by which of the first three anomalies they must prevent:

LevelDirty readNon-repeatable readPhantom read
Read Uncommittedpossiblepossiblepossible
Read Committedpreventedpossiblepossible
Repeatable Readpreventedpreventedpossible
Serializablepreventedpreventedprevented

That table is the textbook answer, and it is where many people stop. It is also incomplete: it says nothing about lost update or write skew, and the 1995 paper "A Critique of ANSI SQL Isolation Levels" showed that the standard's definitions are ambiguous and miss several anomalies, including snapshot isolation, which many databases offer under the name "Repeatable Read" [2].

What you really get

The names mean different things in different databases. Always read your database's documentation, not the textbook table.

DatabaseDefault"Repeatable Read" is really..."Serializable" is really...
PostgreSQLRead CommittedSnapshot isolation: no dirty reads, non-repeatable reads, or phantoms; write skew is possibleSerializable Snapshot Isolation (SSI): truly serializable, by aborting transactions that could cause an anomaly [4][5]
MySQL (InnoDB)Repeatable ReadReads use a snapshot taken at the first read; locking reads and writes use next-key locks, which also block phantoms [1]Plain reads also take shared locks, so readers can block writers [1]
SQL ServerRead Committed, which uses locks unless READ_COMMITTED_SNAPSHOT is on (it is on by default in Azure SQL Database, off in SQL Server)Lock-based. A separate SNAPSHOT level gives a transaction-wide snapshot, once ALLOW_SNAPSHOT_ISOLATION is switched on for the databaseLock-based; see the Microsoft page for the lock details [6]
OracleRead Committed(not offered; Oracle offers Read Committed, Serializable and a read-only mode)Read consistency for the whole transaction, with ORA-08177: Cannot serialize access when a row it wants to change was changed after it began. This is generally understood to be snapshot isolation, so write skew would still be possible; Oracle's page does not use that term or discuss write skew [7]

SQL Server has two switches that change a lot. With READ_COMMITTED_SNAPSHOT on, the ordinary Read Committed level reads row versions (each statement sees a snapshot taken at its start) instead of taking shared locks, so readers stop blocking writers; it is a database-wide setting, and applications that relied on the blocking can behave differently. SNAPSHOT isolation gives a snapshot for the whole transaction and has to be allowed per database first [6].

Two facts from PostgreSQL's documentation are worth remembering: asking for Read Uncommitted gives you Read Committed (dirty reads are not possible at all), and Repeatable Read there is stronger than the standard requires because it also prevents phantoms [4].

4. How databases do it

There are two main techniques, and many systems mix them.

With MVCC, the isolation level is mostly about when the snapshot is taken:

Level (PostgreSQL)The snapshot is taken...So a repeated read
Read Committedat the start of each statementcan see new commits between statements
Repeatable Readat the start of the first statement of the transactionalways sees the same data
Serializablelike Repeatable Read, plus tracking of read/write dependenciessame, and the database aborts a transaction when a cycle could form

Snapshots explain both the good and the bad of "Repeatable Read": reports see a stable picture of the data (good), but two transactions each work on their own picture and can both act on facts that the other is changing (write skew).

5. Preventing the problems

You have more tools than just "raise the level". Often a smaller change is better.

Do the change in the database, in one statement

A lost update needs a read, calculate, write in the application. Make it a single statement and the database does the arithmetic on the current value:

-- unsafe: the application read the balance earlier
UPDATE accounts SET balance = 110 WHERE id = 1;

-- safe: the database calculates from the current value
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

In PostgreSQL's Read Committed, if another transaction changed the row meanwhile, the update waits for it to commit, then re-reads the new version of the row and applies the change to that [4]. That is why this simple form is safe.

Lock the rows you decide on

SELECT ... FOR UPDATE locks the rows it returns until the transaction ends, so anyone else who wants to change or lock them waits:

BEGIN;
SELECT * FROM doctors WHERE on_call FOR UPDATE;   -- lock everyone currently on call
-- if the count is 1, refuse; otherwise:
UPDATE doctors SET on_call = false WHERE name = 'alice';
COMMIT;

Now the two doctors serialise: the second transaction waits for the first, then sees the new state (or, at Repeatable Read, receives a serialization error and retries). Use FOR UPDATE when you know which rows matter. It does not help against phantoms (a row that does not exist yet cannot be locked).

Other locking tools worth knowing

UPDATE jobs SET state = 'taken'
WHERE id = (SELECT id FROM jobs WHERE state = 'new' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED)
RETURNING id;

In my test, worker A got job 1 and worker B, running at the same time, got job 2 without waiting. (The result is "some job", not a strict order, so this suits queues where order is not a promise.)

Optimistic locking: a version column

For "read it, edit it for a while, save it" flows such as a form, do not hold a lock while a person types. Keep a version number, and make the update conditional on it:

UPDATE documents
SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;     -- $3 is the version you loaded
-- 0 rows updated?  Someone else saved first: tell the user, or merge.

Let a constraint be the judge

If the rule can be written as a constraint, let the database enforce it. A UNIQUE index stops two transactions both claiming the same username, at any isolation level. A PostgreSQL exclusion constraint can say "no two bookings overlap for the same room". Constraints are checked against the latest data, so they close gaps that snapshots leave open.

Use Serializable, and retry

When the rule is complicated and you cannot lock or constrain it, ask for SERIALIZABLE and let the database detect the problems. The price is that it will occasionally abort a transaction with a serialization failure (SQLSTATE 40001 in PostgreSQL), and your code must retry the whole transaction [4]. A deadlock (40P01, section 7) needs exactly the same treatment, so the wrappers below retry on both. That means a transaction is a function you can run again, without side effects outside the database (no emails sent in the middle, no external calls). Here is the retry wrapper in four languages (pick a language on the tabs; your choice is remembered):

import time
import psycopg
from psycopg import errors

RETRY = (errors.SerializationFailure, errors.DeadlockDetected)   # 40001 and 40P01


def run_serializable(conninfo, work, attempts=5):
    for attempt in range(1, attempts + 1):
        try:
            with psycopg.connect(conninfo) as conn:       # a pool in real code
                conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
                with conn.transaction():
                    return work(conn)
        except RETRY:
            time.sleep(0.01 * attempt)                    # back off a little, then try again
    raise RuntimeError("too many serialization failures")
func RunSerializable(ctx context.Context, pool *pgxpool.Pool, work func(pgx.Tx) error) error {
	for attempt := 1; attempt <= 5; attempt++ {
		tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
		if err != nil {
			return err
		}
		err = work(tx)
		if err == nil {
			err = tx.Commit(ctx) // a serialization failure can also surface here
		}
		if err == nil {
			return nil
		}
		_ = tx.Rollback(ctx)
		var pgErr *pgconn.PgError
		if !errors.As(err, &pgErr) || (pgErr.Code != "40001" && pgErr.Code != "40P01") {
			return err // not a serialization failure or a deadlock: do not retry
		}
		time.Sleep(time.Duration(attempt) * 10 * time.Millisecond) // back off a little
	}
	return errors.New("too many serialization failures")
}
async function runSerializable(pool, work, attempts = 5) {
  for (let attempt = 1; attempt <= attempts; attempt++) {
    const client = await pool.connect();
    try {
      await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await work(client);
      await client.query("COMMIT"); // a serialization failure can also surface here
      return result;
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {});
      if (err.code !== "40001" && err.code !== "40P01") throw err; // neither serialization failure nor deadlock
      await new Promise((resolve) => setTimeout(resolve, 10 * attempt)); // back off a little
    } finally {
      client.release();
    }
  }
  throw new Error("too many serialization failures");
}
using System.Data;
using Npgsql;

public static async Task<T> RunSerializableAsync<T>(
    NpgsqlDataSource db, Func<NpgsqlConnection, Task<T>> work, int attempts = 5)
{
    for (int attempt = 1; ; attempt++)
    {
        await using var conn = await db.OpenConnectionAsync();
        await using var tx = await conn.BeginTransactionAsync(IsolationLevel.Serializable);
        try
        {
            var result = await work(conn);
            await tx.CommitAsync();      // a serialization failure can also surface here
            return result;
        }
        catch (PostgresException e) when ((e.SqlState is PostgresErrorCodes.SerializationFailure or PostgresErrorCodes.DeadlockDetected) && attempt < attempts)
        {
            await tx.RollbackAsync();
            await Task.Delay(10 * attempt);   // back off a little, then try again
        }
    }
}

In PostgreSQL, Repeatable Read can fail the same way (an update of a row that another transaction changed meanwhile raises could not serialize access due to concurrent update), so the same wrapper protects you there.

6. Choosing a level

SituationA sensible choice
Typical web request: load a row, change it, save itRead Committed, with atomic updates, optimistic version columns, and constraints
A report or an export that must see one consistent momentRepeatable Read (read-only), so the numbers add up
A rule that spans several rows and you cannot lock or constrain itSerializable, with retries
Money movementAtomic updates or explicit row locks in a fixed order (to avoid deadlocks), inside Read Committed or stronger; add constraints such as CHECK (balance >= 0)
Counters and statistics where small errors do not matterWhatever is cheapest

For a long report or export that must see one consistent picture and must never be cancelled halfway, PostgreSQL has BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE. The transaction may wait at the start until it can get a snapshot that is guaranteed free of anomalies, and then it runs without any risk of a serialization failure [4].

A higher level is not a free upgrade: more aborted transactions, more memory for tracking, and a need to retry in code that may never have expected failure. Raise it for the specific transactions that need it, not for the whole database. Most PostgreSQL applications run on Read Committed with a handful of FOR UPDATE and unique constraints, and are correct.

7. Deadlocks are a different problem

Deadlocks happen when T1 waits for a lock that T2 holds while T2 waits for one that T1 holds. Locking creates them, at any isolation level. The database detects the cycle and aborts one transaction (40P01 in PostgreSQL). Reduce them by locking rows in a consistent order (always the smaller account id first), by keeping transactions short, and by retrying when one happens.

8. Isolation across more than one database

Everything above is about one database. Once a business operation spans several services, a database transaction cannot cover it, and you use sagas, outbox patterns and idempotent retries instead. Related terms you may hear:

9. Questions interviewers like to add

"What is the difference between a non-repeatable read and a phantom?"

A non-repeatable read is a row you already saw that changed or disappeared. A phantom is a new row that now matches your query. Row locks can stop the first one but not the second.

"Does Repeatable Read prevent phantoms?"

By the standard, no. In PostgreSQL and in InnoDB's consistent reads, yes (through snapshots), but write skew is still possible in PostgreSQL's version. This is why "it depends on the database" is the correct start to the answer.

"How do you avoid overselling the last item in stock?"

UPDATE items SET stock = stock - 1 WHERE id = $1 AND stock > 0, and check that one row was updated. The condition and the change happen atomically; no isolation level needed beyond Read Committed.

"Why not always use Serializable?"

It costs throughput and forces retries. Often a unique constraint or one SELECT ... FOR UPDATE gets the same safety with less cost. Use it where the invariant is hard to express any other way.

"What does MVCC cost?"

Old row versions pile up and must be cleaned (VACUUM in PostgreSQL). A long-running transaction keeps old versions alive and can make tables bloat. Keep transactions short.

10. Things to mention in an interview

Quick recap

  1. Isolation decides what concurrent transactions can see of each other.
  2. Six anomalies: dirty read, non-repeatable read, phantom, read skew, lost update, write skew.
  3. The standard levels are Read Uncommitted, Read Committed, Repeatable Read, Serializable, but each database implements them differently.
  4. PostgreSQL's Repeatable Read is snapshot isolation (no phantoms, but write skew); only Serializable is fully safe.
  5. Fix most problems with atomic updates, SELECT ... FOR UPDATE, version columns and constraints.
  6. Under Serializable (and Repeatable Read), retry the whole transaction on 40001 (and on a deadlock, 40P01).
  7. Keep transactions short, lock in a consistent order, and raise the level only where it is needed.

Sources

  1. MySQL documentation, InnoDB Transaction Isolation Levels.
  2. Berenson et al., A Critique of ANSI SQL Isolation Levels, SIGMOD 1995. The anomalies, and snapshot isolation.
  3. Martin Kleppmann, Designing Data-Intensive Applications, chapter 7 (Transactions). Write skew and the weak isolation levels explained at length.
  4. PostgreSQL documentation, Transaction Isolation. What each level does in PostgreSQL, and the retry advice.
  5. Ports and Grittner, Serializable Snapshot Isolation in PostgreSQL, VLDB 2012.
  6. Microsoft, SET TRANSACTION ISOLATION LEVEL (Transact-SQL). SQL Server's levels, READ_COMMITTED_SNAPSHOT and SNAPSHOT.
  7. Oracle, Data Concurrency and Consistency. Read committed, serializable and read-only modes, and ORA-08177.
  8. Jepsen, Consistency Models. A map of isolation and consistency models, with serializability and linearizability side by side.

Practise this topic: Databases

More guides