14 August 2026

What is a database isolation level?

What is an isolation level, and how does it affect data integrity?

Before exploring database isolation levels, we first need to understand what a transaction is in the context of databases. Let's begin with an example!

When performing daily tasks on a database, you are likely to run simple queries such as SELECT. However, when you need to debug a problem or fix some rows, you may need to run data-modifying queries.

Let's say you are tired, overlook this simple error, and press Enter.

DELETE FROM users;
WHERE id = 42;

Whoops! 10 000 000 rows affected. Good luck fixing that if you do not have a backup. The semicolon after users ends the statement, so it deletes EVERY ROW from the users table. The following WHERE clause will produce a syntax error, but it will not undo the preceding DELETE. If the client executes the statements separately with autocommit enabled, the DELETE is committed before the invalid WHERE statement is attempted. In that case, every row is gone. The exact behavior depends on the database, driver, and client configuration.

This is exactly where a transaction can help. You simply use START TRANSACTION or BEGIN, depending on your database, to make it easy to roll back changes.

BEGIN;

DELETE FROM users;
WHERE id = 42;

ROLLBACK;

ROLLBACK lets you roll back the transaction when you spot an error or do not want to commit it. If you do want to apply the changes, use COMMIT.

Transactions also provide developers with powerful functionality. You can combine multiple statements into one atomic operation. This means that all its changes become permanent as a single unit; if the transaction is rolled back, none of the changes become permanent. For example, let's say you want to hard-delete a user and its related entities:

BEGIN;

DELETE FROM images WHERE owner_id = 42;
DELETE FROM chats WHERE owner_id = 42;
DELETE FROM users WHERE id = 42;

If the final deletion from users fails, we can simply roll back the transaction, leaving the affected rows unchanged.

There is, however, one problem. Transactions take time to complete, so they can overlap with each other. The more traffic the system handles, the more likely such an overlap becomes. Interactions between these transactions might corrupt your data. This is where the database isolation level comes into play.

What is a database isolation level?

A database isolation level controls how transactions interact with each other. It is essentially a way to tell the database engine what data from one transaction is shown to another concurrent transaction. Higher isolation levels rule out more concurrency anomalies. This is important for data safety and consistency.

What can happen to data? — Anomalies

Before we jump to describing standard isolation levels, we need to understand some relevant terms.

Dirty read

A dirty read occurs when one transaction reads data written by another transaction that has not yet been committed. If the writing transaction rolls back, the reader has observed a value that never became part of the committed database state.

It happens as follows:

Transaction B updates data but does not commit it.
Transaction A reads the uncommitted data.

If Transaction B rolls back the change, Transaction A might have invalid data.

Concurrency trace · 1/5

Transaction anomaly

Dirty read

Transaction A reads a balance that Transaction B never commits.

A · Reader

B · Writer

1. Shared database
Initial state
2. Transaction B · Writer
B starts a transfer
3. Transaction B · Writer
B updates without committing
4. Transaction A · Reader
A reads the dirty value
5. Transaction B · Writer
B abandons the change

Active operation

accounts[42].balance = €100

The committed balance is €100, and every transaction sees the same value.

Visible state

Committed balance: €100
Transaction A sees: €100

Non-repeatable read

A non-repeatable read occurs when a transaction reads the same row twice and obtains different results because another transaction modified or deleted that row and committed between the two reads. Unlike a dirty read, the second value is committed.

Concurrency trace · 1/5

Transaction anomaly

Non-repeatable read

Transaction A repeats a query for the same row and gets a different committed value.

A · Long-running reader

B · Order updater

1. Shared database
Initial state
2. Transaction A · Long-running reader
A starts and reads
3. Transaction B · Order updater
B changes the same row
4. Transaction B · Order updater
B commits
5. Transaction A · Long-running reader
A repeats the query

Active operation

orders[7].status = 'pending'

Order 7 has a committed status of pending.

Visible state

Committed status: pending
A's last read: —

Phantom read

It is somewhat similar to a non-repeatable read but spans multiple rows. It happens when a transaction repeats a query and gets a different set of rows because another transaction inserted, updated, or deleted rows and committed between the two queries.

Concurrency trace · 1/5

Transaction anomaly

Phantom read

Transaction A repeats a range query and an additional committed row appears.

A · Range reader

B · Order creator

1. Shared database
Initial state
2. Transaction A · Range reader
A runs a range query
3. Transaction B · Order creator
B inserts a matching row
4. Transaction B · Order creator
B commits
5. Transaction A · Range reader
A repeats the range query

Active operation

open orders = [#41, #42]

Two committed orders currently match the open-order query.

Visible state

Matching rows: #41, #42
Row count: 2

Serialization anomaly

This is a kind of anomaly that occurs when the result of transactions performed concurrently differs from the result that the transactions would produce if performed sequentially.

Concurrency trace · 1/5

Transaction anomaly

Serialization anomaly

Two valid decisions combine into a result that no safe sequential order would allow.

A · Approves Alice

B · Approves Bob

1. Shared database
Initial state
2. Transaction A · Approves Alice
A checks the rule
3. Transaction B · Approves Bob
B checks the same rule
4. Transaction A · Approves Alice
A writes and commits
5. Transaction B · Approves Bob
B writes and commits

Active operation

doctors_on_vacation = 2; maximum = 3

Two doctors are away. One more approval is safe, but two are not.

Visible state

Doctors away: 2
Maximum allowed: 3

Types of isolation levels

The SQL standard defines four basic isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Choosing one involves a trade-off between execution speed and data consistency.

`READ UNCOMMITTED`

This is the lowest isolation level. It provides the fewest guarantees about data consistency because it allows all types of anomalies to occur.

There is an interesting fact about PostgreSQL here. Even though READ UNCOMMITTED is recognized in PostgreSQL, dirty reads are not actually allowed. PostgreSQL Docs — Table 13.1

`READ COMMITTED`

This is a common default used by systems such as PostgreSQL. It specifically disallows dirty reads. At this level, uncommitted data cannot reach another transaction. It offers far greater protection than READ UNCOMMITTED but still allows other types of anomalies to occur.

`REPEATABLE READ`

This is one level higher than READ COMMITTED. In addition to preventing dirty reads, this level disallows non-repeatable-read anomalies. At this level, a row read at the beginning of a transaction will have the same data when read again at the end of the transaction, unless the transaction changes the data itself. A transaction running outside it cannot affect the row data it sees.

Phantom reads, however, are still allowed by the SQL standard at this level, but PostgreSQL specifically disallows them. See the same PostgreSQL Docs — Table 13.1.

`SERIALIZABLE`

This is the strictest level. It prevents all the anomalies described earlier. It guarantees that transactions are committed as if they were executed sequentially. If a serialization anomaly could be introduced, one of the transactions will encounter a serialization failure instead of introducing an inconsistency into the data.

Isolation levels and anomalies

Isolation level	Dirty read	Non-repeatable read	Phantom read	Serialization anomaly
READ UNCOMMITTED	✅ Possible	✅ Possible	✅ Possible	✅ Possible
READ COMMITTED	❌ Prevented	✅ Possible	✅ Possible	✅ Possible
REPEATABLE READ	❌ Prevented	❌ Prevented	✅ Possible	✅ Possible
SERIALIZABLE	❌ Prevented	❌ Prevented	❌ Prevented	❌ Prevented

Let's sum up!

Why not implement SERIALIZABLE everywhere, then? Depending on the database and workload, it can increase blocking, coordination overhead, or the number of serialization failures. Applications must also be prepared to retry failed transactions.

Choosing the right database isolation level involves balancing implementation complexity, execution speed, and data integrity.

For simple CRUD use cases, READ COMMITTED will usually be sufficient. REPEATABLE READ is worth using when a transaction performs several reads and needs a consistent view of the database.

SERIALIZABLE comes into play when correctness across transactions is critical, such as when making a business decision based on a SELECT query. Consider this example:

Rule:
No more than 3 doctors may be on vacation simultaneously.

T1:
SELECT COUNT(*) → 2
approve Alice

T2:
SELECT COUNT(*) → 2
approve Bob

If these transactions are executed concurrently and the SERIALIZABLE isolation level is not in place, we might end up violating the business rule.

Below is a simple decision tree to help you choose an appropriate approach to preserving data consistency:

Do I need to read uncommitted data?
    │
    └─ Almost certainly no.

Do multiple SELECTs need to see the exact same snapshot?
    │
    ├─ No
    │   → READ COMMITTED
    │
    └─ Yes
        ↓
        REPEATABLE READ

Am I making writes based on reads?
        │
        └─ Yes
            ↓
Can a UNIQUE/CHECK/FK constraint enforce the invariant?
        │
        ├─ Yes → use the constraint
        │
        └─ No
            ↓
Is the decision about a known row/resource?
        │
        ├─ Yes → consider SELECT ... FOR UPDATE
        │
        └─ No / invariant spans many rows
            ↓
        SERIALIZABLE