Transaction isolation levels and their typical anomalies

When several users work on the same database at the same time, their transactions overlap. If every transaction waited for the others to finish, the system would be perfectly correct but very slow. Databases therefore look for a compromise, and the isolation level is the dial that sets how much one transaction can be disturbed by the others. Knowing the possible anomalies helps you understand when the default level is enough and when it is not.

The anomalies to know

Dirty read

A transaction reads a value changed by another transaction that has not yet committed. If the latter is rolled back, the first one has worked on a value that never really existed.

Non-repeatable read

A transaction reads a row, another changes it and commits, the first reads the same row again and finds a different value. Within the same transaction, the same question has two answers.

Phantom read

A transaction runs a query with a filter, for example «today's orders», and gets 10 rows. Another inserts a new order for today and commits. Running the query again, one extra row appears: the phantom.

Lost update

Two transactions read the same balance, 100. Both compute a new value in application code, one adding 10 and the other 20, and write it back. The final result is 110 or 120, not 130: one of the two changes has been lost.

Write skew

This is the subtlest anomaly. Two doctors are on call and the rule says at least one must remain available. Each, in a separate transaction, checks that the other is on call and then signs off. Neither transaction changes the same row, yet in the end nobody is on call.

The four levels in the SQL standard

The SQL standard defines four levels, from the most permissive to the strictest.

The table describes the minimum requirements of the standard. Real implementations often do more: in PostgreSQL, for example, Repeatable Read also prevents phantom reads, and Read Uncommitted behaves like Read Committed. That is why it is always worth checking your own database's documentation rather than relying on the table alone.

The defaults

The most widely used databases do not all start from the same level. PostgreSQL, Oracle and SQL Server use Read Committed by default, while MySQL with the InnoDB engine uses Repeatable Read. The same code can therefore behave differently on different systems.

Serializable: correctness at the price of retries

The Serializable level guarantees that the result is the same as running the transactions one after another, in some order. It also eliminates write skew. The price is that the database, when it detects a conflict, rolls back one of the transactions with a serialization error. The application must be ready to retry it:

for attempt in range(3):
    try:
        run_transaction()
        break
    except SerializationError:
        continue  # start again from the top

Without this retry logic, users would see random errors at peak traffic times.

Avoiding lost updates without Serializable

In practice most applications work at Read Committed and handle critical cases with targeted techniques.

Atomic update

Instead of reading the value, computing in code and writing it back, let the database do the calculation:

UPDATE accounts SET balance = balance + 20 WHERE id = 7;

The statement is atomic and two concurrent executions produce the correct result.

Explicit locking on read

When the logic is too complex for a single statement, lock the row at the moment of reading:

BEGIN;
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
-- calculations in application code
UPDATE accounts SET balance = 130 WHERE id = 7;
COMMIT;

A second transaction attempting the same SELECT ... FOR UPDATE waits for the first to finish.

Optimistic control with a version

Add a version column to the table. The update only happens if the version has not changed since it was read:

UPDATE documents
SET body = 'new text', version = version + 1
WHERE id = 3 AND version = 12;

If the statement changes zero rows, someone else got there first: warn the user or retry. This is the ideal technique for web forms, where minutes can pass between reading and saving and holding a lock open is not acceptable.

How to choose

1. For most operations, your database's default level is adequate.
2. For counters and balances, use atomic updates or FOR UPDATE.
3. For changes users make through an interface, prefer optimistic control with a version.
4. For rules spanning several rows, such as the minimum on-call rule, consider Serializable with retry logic or a constraint in the database.

In short

• The main anomalies are dirty, non-repeatable and phantom reads, lost updates and write skew.
• The SQL standard defines four levels; real implementations vary, so read the documentation.
• The default level is not the same in every database.
• Serializable is the safest but requires retrying conflicting transactions.
• Atomic updates, FOR UPDATE and versions solve most practical cases.