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.

16 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.

Non-repeatable read

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

Phantom read

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

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.

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 [1][6].

3. The four standard levels

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

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" [1].

What you really get

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

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 [2].

4. How databases do it

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

• Locking (two-phase locking). A transaction locks what it reads and writes and holds the locks until it ends. Readers block writers and writers block readers. It gives serializability but can be slow under contention and causes deadlocks, which the database resolves by aborting one transaction.
• Multiversion concurrency control (MVCC). The database keeps several versions of each row. A transaction reads from a snapshot, a consistent picture of the data at one moment, so readers do not block writers and writers do not block readers. This is how PostgreSQL, Oracle, and InnoDB's consistent reads work.

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

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 [2]. 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).

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 [2]. 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


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 errors.SerializationFailure:
            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" {
			return err // not a serialization failure: 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") throw err; // not a serialization failure: do not retry
      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 == PostgresErrorCodes.SerializationFailure && 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

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:

• Serializability is about transactions and their order; linearizability is about a single object and real time (once a write finishes, every later read sees it). A database that is both is called strictly serializable. Distributed databases advertise which one they provide, and the difference matters [5].
• Replica reads may return old data even when the primary is fully serializable. Isolation levels describe the primary, not a lagging replica.

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

• Define isolation and name the five anomalies: dirty read, non-repeatable read, phantom, lost update, write skew.
• Give the four standard levels, then say the names differ between databases and name PostgreSQL's real behaviour.
• Explain snapshots (MVCC) and that the level decides when the snapshot is taken.
• Show the cheap fixes first: atomic updates, FOR UPDATE, version columns, constraints.
• Say that Serializable means "retry on failure", and that the transaction must be safe to repeat.
• Mention that replicas and multiple services are a separate problem.

Quick recap

1. Isolation decides what concurrent transactions can see of each other.
2. Five anomalies: dirty read, non-repeatable read, phantom, 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.
7. Keep transactions short, lock in a consistent order, and raise the level only where it is needed.

Sources

1. Berenson et al., A Critique of ANSI SQL Isolation Levels, SIGMOD 1995. The anomalies, and snapshot isolation.
2. PostgreSQL documentation, Transaction Isolation. What each level does in PostgreSQL, and the retry advice.
3. Ports and Grittner, Serializable Snapshot Isolation in PostgreSQL, VLDB 2012.
4. MySQL documentation, InnoDB Transaction Isolation Levels.
5. Jepsen, Consistency Models. A map of isolation and consistency models, with serializability and linearizability side by side.
6. Martin Kleppmann, Designing Data-Intensive Applications, chapter 7 (Transactions). Write skew and the weak isolation levels explained at length.

Practise this topic: SQL & Databases

More guides