Database Transaction Isolation: 5 Concurrency Bugs and How to Fix Them

Your code works perfectly when you test it alone. Then two users act at the same moment, and an account ends up with the wrong balance, or a hospital ends up with no doctor on call.

These bugs are hard to find because they depend on timing. Every statement is correct. The problem is how two transactions interleave.

Isolation levels are the database setting that decides which interleavings are allowed. This post shows five classic bugs as timelines, explains what the four standard levels promise, and covers what PostgreSQL and MySQL actually do. It ends with fixes that work at any level and a lost update you can reproduce yourself in a few lines of Python.

What isolation means

A transaction is a group of statements that succeed or fail together. Isolation is the promise that transactions running at the same time don’t trip over each other.

The ideal is serializable behavior: the result is the same as if the transactions had run one at a time. Databases can deliver that, but it limits concurrency. So most offer weaker levels that allow some odd results in exchange for speed.

Your job is to know which odd results your level allows, and whether your code can survive them.

In the timelines below, A and B are two transactions talking to one database. Time runs downward, and the numbers show the order of the steps.

Five bugs, one at a time

1. Dirty read

A dirty read means you read a change that another transaction has not committed yet. If that transaction rolls back, you acted on a value that never existed.

sequenceDiagram
    autonumber
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: UPDATE balance = 50 (not committed yet)
    B->>DB: SELECT balance
    DB-->>B: 50
    A->>DB: ROLLBACK
    Note over B: B acted on a value that never existed

You only see dirty reads at the lowest level, Read uncommitted, which few applications use.

2. Non-repeatable read

You read the same row twice in one transaction and get different values, because another transaction committed a change in between.

sequenceDiagram
    autonumber
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: SELECT price
    DB-->>A: 100
    B->>DB: UPDATE price = 120, COMMIT
    A->>DB: SELECT price (same transaction)
    DB-->>A: 120
    Note over A: Same query, different answer

This breaks reports and calculations that read a value more than once and expect it to stay put.

3. Phantom read

This is the same problem for a set of rows. You run the same query twice, and the set of matching rows changes because another transaction inserted or deleted one.

sequenceDiagram
    autonumber
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: SELECT count(*) WHERE status = 'pending'
    DB-->>A: 3
    B->>DB: INSERT a pending row, COMMIT
    A->>DB: SELECT count(*) WHERE status = 'pending'
    DB-->>A: 4
    Note over A: A new row appeared mid-transaction

Counts, totals and “does any row exist?” checks are the usual victims.

4. Lost update

Two transactions read the same value, each computes a new one, and each writes it back. The second write silently overwrites the first.

sequenceDiagram
    autonumber
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: SELECT balance
    DB-->>A: 100
    B->>DB: SELECT balance
    DB-->>B: 100
    A->>DB: UPDATE balance = 150 (100 + 50), COMMIT
    B->>DB: UPDATE balance = 130 (100 + 30), COMMIT
    Note over A,B: Final balance is 130, so A's deposit is lost

Both deposits were valid, but the balance should be 180, not 130. This is probably the most common concurrency bug in application code.

5. Write skew

Two transactions read the same data, then each updates a different row based on what it read. Each decision was valid on its own, but together they break a rule.

sequenceDiagram
    autonumber
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: SELECT count(*) FROM on_call
    DB-->>A: 2
    B->>DB: SELECT count(*) FROM on_call
    DB-->>B: 2
    A->>DB: Take Alice off call, COMMIT
    B->>DB: Take Bob off call, COMMIT
    Note over A,B: Nobody is on call, yet each check was true when it ran

The rule here is that at least one doctor must stay on call.

Because the two transactions wrote different rows, there is no row-level conflict to detect. That is why many databases miss it.

The four standard levels

The SQL standard defines four levels by which of the first three bugs they forbid. This table is from the PostgreSQL documentation:

Notice what is missing: lost updates and write skew don’t appear in the table. A well-known 1995 paper, A Critique of ANSI SQL Isolation Levels, pointed out this gap.

The standard also sets only minimum protections. A database may do better than the table says, or behave differently, at the same level name. So a level’s name alone can’t tell you whether your code is safe.

What PostgreSQL and MySQL actually do

Here is how the two most popular open-source databases implement each level:

These details come from the PostgreSQL and MySQL documentation.

To see the biggest difference, replay the lost update from earlier at Repeatable Read:

sequenceDiagram
    autonumber
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: BEGIN (Repeatable Read), SELECT balance
    DB-->>A: 100
    B->>DB: UPDATE balance = 130, COMMIT
    A->>DB: UPDATE balance = 150 (100 + 50)
    alt PostgreSQL
        DB-->>A: ERROR could not serialize access due to concurrent update
        Note over A: A rolls back and retries from the start
    else MySQL (InnoDB)
        DB-->>A: OK, the row is updated
        A->>DB: COMMIT
        Note over A,B: Final balance is 150, so B's deposit is lost
    end

The timeline is the same, but the outcome isn’t. PostgreSQL stops A, so your code must be ready to catch the error and retry. MySQL lets A overwrite B’s change with a value computed from an old snapshot.

Write skew survives Repeatable Read in both databases. In PostgreSQL only Serializable stops it, and in MySQL, Serializable stops it by locking.

Other databases such as Oracle and SQL Server differ again. Hermitage, a test suite by Martin Kleppmann, records how many databases behave at each level, so it’s the place to check if you use something else.

The fixes

Raising the isolation level is one fix, but it isn’t always the best one. These tools work at any level, and they often cost less.

1. Let the database do the math. Instead of reading a balance and writing back a new one, send one statement:

UPDATE accounts SET balance = balance + 50 WHERE id = 1;

The read and the write happen together, so a concurrent change can’t slip in between. This fixes lost updates even at Read Committed.

2. Lock what you read. SELECT ... FOR UPDATE makes other transactions wait until you finish. It fixes lost updates, and it fixes write skew if you lock every row your decision depends on. It can’t lock a row that doesn’t exist yet, so it doesn’t cover phantoms.

With a lock, the two deposits from the lost update example come out right (shown at Read Committed):

sequenceDiagram
    autonumber
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: SELECT balance FOR UPDATE
    DB-->>A: 100 (row is now locked)
    B->>DB: SELECT balance FOR UPDATE
    Note over B,DB: B waits for A to finish
    A->>DB: UPDATE balance = 150 (100 + 50), COMMIT
    DB-->>B: 150 (lock released)
    B->>DB: UPDATE balance = 180 (150 + 30), COMMIT
    Note over A,B: Final balance is 180, nothing is lost

3. Check before you write. Add a version column, and make the update conditional on it:

UPDATE accounts SET balance = 150, version = version + 1
WHERE id = 1 AND version = 7;

If this changes zero rows, someone else got there first, so reload and try again. This approach works in any database and holds no locks while a user is thinking.

4. Let the database enforce the rule. Unique, foreign key and check constraints are correct under any timing. PostgreSQL also has exclusion constraints, which can stop two bookings from overlapping. If the rule is a constraint, there is no race to lose.

5. Use Serializable, and retry. Write each transaction as if it were alone, and let the database abort the ones that conflict. Your code must catch the error and rerun the whole transaction. In PostgreSQL the error is a serialization failure (SQLSTATE 40001).

Here is the doctors example again at Serializable in PostgreSQL, where the database aborts one of the two transactions:

sequenceDiagram
    autonumber
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B
    A->>DB: SELECT count(*) FROM on_call
    DB-->>A: 2
    B->>DB: SELECT count(*) FROM on_call
    DB-->>B: 2
    A->>DB: Take Alice off call, COMMIT
    DB-->>A: OK
    B->>DB: Take Bob off call, COMMIT
    DB-->>B: ERROR serialization failure (40001)
    B->>DB: Retry: SELECT count(*) FROM on_call
    DB-->>B: 1
    Note over B: B now sees one doctor on call and keeps Bob on duty

6. Keep transactions short. Never wait for user input or a slow network call inside a transaction. Long transactions hold locks and snapshots, which leads to more conflicts.

Which fix to reach for depends on the bug:

Reproduce a lost update

You can trigger a lost update on your laptop. This script uses SQLite and Python threads. SQLite runs writers one at a time, so a barrier forces the unlucky timing from the lost update diagram above:

import sqlite3, threading, os

DB = 'bank.db'

def reset():
    if os.path.exists(DB):
        os.remove(DB)
    con = sqlite3.connect(DB)
    con.execute('CREATE TABLE accounts(id INTEGER PRIMARY KEY, balance INTEGER)')
    con.execute('INSERT INTO accounts VALUES (1, 100)')
    con.commit()
    con.close()

def balance():
    con = sqlite3.connect(DB)
    return con.execute('SELECT balance FROM accounts WHERE id = 1').fetchone()[0]

def unsafe_deposit(amount, both_have_read, a_has_written, is_b):
    con = sqlite3.connect(DB, timeout=5, isolation_level=None)  # autocommit
    current = con.execute('SELECT balance FROM accounts WHERE id = 1').fetchone()[0]
    both_have_read.wait()           # force the bad timing: both have seen 100
    if is_b:
        a_has_written.wait()        # B writes last, after A has finished
    con.execute('UPDATE accounts SET balance = ? WHERE id = 1', (current + amount,))
    if not is_b:
        a_has_written.set()

def safe_deposit(amount):
    con = sqlite3.connect(DB, timeout=5, isolation_level=None)
    con.execute('UPDATE accounts SET balance = balance + ? WHERE id = 1', (amount,))

def run(*jobs):
    threads = [threading.Thread(target=job) for job in jobs]
    for t in threads:
        t.start()
    for t in threads:
        t.join()

reset()
both_have_read, a_has_written = threading.Barrier(2), threading.Event()
run(lambda: unsafe_deposit(50, both_have_read, a_has_written, False),
    lambda: unsafe_deposit(30, both_have_read, a_has_written, True))
print('read, compute, write in app code:', balance())   # 130, not 180

reset()
run(lambda: safe_deposit(50), lambda: safe_deposit(30))
print('single UPDATE statement         :', balance())   # 180

Running it prints:

read, compute, write in app code: 130
single UPDATE statement         : 180

The database did nothing wrong here. The bug is in the application’s read, compute, write pattern. I ran the script 20 times, and the app-code version always ended at 130.

Wrapping up

Most concurrency bugs come from a short window between reading a value and writing a decision based on it. Each fix above closes that window in a different way, and Serializable with a retry loop is the fallback when the logic is too complex for the others.

Whatever you choose, test it with two real sessions to see what your database actually does.