SQL Isolation Levels Explained

SQL isolation levels define what one transaction is allowed to observe while other transactions are running at the same time.

That sounds like a database theory topic until two API requests overlap in production and both make a decision from a view of data that looked valid locally. The code used transactions. The SQL was syntactically correct. The bug came from assuming the transaction protected more than the configured isolation level actually promised.

For related database topics such as locking, replicas, migrations, and query behavior, see the SQL And Data Correctness hub.

The Short Mental Model

Isolation levels are concurrency contracts.

They do not mean "safe" or "unsafe" by themselves. They define which kinds of concurrent behavior your transaction may still observe.

The important question is not "which isolation level is best?"

The useful question is:

Which incorrect interleavings can still happen for this workflow?

If the workflow is "show a dashboard," a slightly stale or changing view may be fine. If the workflow is "reserve the last seat," "debit an account," or "claim the next job," the allowed interleavings matter much more.

What A Transaction Protects And What Isolation Adds

A transaction gives you a unit of work. If it commits, its changes commit together. If it rolls back, its changes roll back together.

That is atomicity.

Isolation answers a different question:

While this transaction is running, what can it see from other transactions?

Two pieces of code can both use transactions and still have different correctness behavior:

BEGIN;
SELECT stock FROM products WHERE id = 42;
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

Whether this is safe depends on more than the BEGIN and COMMIT. It depends on the isolation level, the update predicate, locks, constraints, retries, and what other transactions do at the same time.

This is why transaction bugs often surprise teams. They checked that each step works. They did not check which concurrent histories are still allowed.

The Main Isolation Anomalies

The SQL standard describes isolation levels through concurrency phenomena. The names are dry, but the production failures are very real.

Dirty Read

Transaction B reads data written by Transaction A before A commits. If A rolls back, B used data that never became durable truth.

T1: UPDATE accounts SET balance = 0 WHERE id = 1;
T2: SELECT balance FROM accounts WHERE id = 1; -- sees 0
T1: ROLLBACK;

Most application systems should avoid this. If business logic can act on data that later disappears, debugging becomes miserable.

Non-Repeatable Read

Transaction A reads a row twice. Transaction B commits an update between those reads. A sees different values inside the same transaction.

T1: SELECT status FROM orders WHERE id = 10; -- pending
T2: UPDATE orders SET status = 'paid' WHERE id = 10; COMMIT;
T1: SELECT status FROM orders WHERE id = 10; -- paid

This matters when code assumes repeated reads inside a transaction represent one stable view.

Phantom Read

Transaction A runs a query that returns a set of rows. Transaction B commits a row that now matches the same predicate. When A reruns the query, a new row appears.

-- T1
SELECT id FROM reservations WHERE event_id = 7;

-- T2
INSERT INTO reservations (event_id, seat_id) VALUES (7, 42);
COMMIT;

-- T1
SELECT id FROM reservations WHERE event_id = 7;

This matters when logic depends on the absence or count of matching rows.

Lost Update

Two transactions read the same value, compute new values independently, and the later write overwrites the earlier one.

stock = 1

T1: read stock = 1
T2: read stock = 1
T1: write stock = 0
T2: write stock = 0

Both transactions thought they reserved the last unit. Only one unit existed.

Lost updates are why isolation levels often need to be paired with explicit write predicates, row locks, version columns, or unique constraints. For that implementation choice, see Optimistic vs Pessimistic Locking in SQL.

Write Skew

Two transactions read overlapping data, each decides its own write is safe, and together they violate a cross-row invariant.

The classic shape:

Rule: at least one doctor must be on call.

T1 reads: Alice on call, Bob on call.
T2 reads: Alice on call, Bob on call.
T1 sets Alice off call.
T2 sets Bob off call.
Both commit.
No doctor remains on call.

No single row was overwritten incorrectly. The invariant lived across rows. That is why higher isolation or explicit locking may be needed for business rules that depend on sets, counts, or absence.

Database-Specific Behavior Matters

Do not memorize isolation levels as if every database implements them identically.

The names are portable. The behavior is not always identical.

The official docs are worth checking before changing production behavior:

• PostgreSQL transaction isolation explains that PostgreSQL implements three distinct internal isolation levels and that Repeatable Read is stronger than the SQL minimum around phantom reads.
• SQL Server locking and row versioning explains locking, row versioning, READ_COMMITTED_SNAPSHOT, SNAPSHOT, and how read locks behave at each level.
• MySQL InnoDB transaction isolation levels explains that InnoDB defaults to Repeatable Read, how consistent reads use snapshots, and how locking reads use record, gap, or next-key locks.

This is not trivia. It affects production behavior.

If an engineer says "we run Repeatable Read," the next question is "in which database, with which query shape, and are these locking or non-locking reads?"

Read Committed

Read Committed is a common production default because it avoids dirty reads while preserving useful concurrency.

At a high level:

• a statement sees committed data
• it does not see uncommitted writes from other transactions
• a later statement in the same transaction may see data committed after the earlier statement began

In PostgreSQL, Read Committed is the default, and each SELECT sees a snapshot as of the start of that query. That means two SELECT statements in the same transaction can see different committed data if another transaction commits between them.

Example:

BEGIN;

SELECT stock
FROM products
WHERE id = 42;
-- returns 1

-- another transaction commits stock = 0

SELECT stock
FROM products
WHERE id = 42;
-- may now return 0

COMMIT;

From the database's perspective, nothing is broken. Each statement saw committed data.

From the application's perspective, the transaction did not provide one stable view of the product row.

Read Committed is often fine for:

• short request transactions
• simple CRUD operations
• reads that can tolerate freshness changing between statements
• writes protected by database constraints
• workflows where each statement is independently correct

It is risky for:

• read-check-write logic
• "if no row exists, insert one" logic without a uniqueness constraint
• cross-row business rules
• inventory or balance checks that rely on a stale value
• workflows where several reads must describe one coherent moment

The practical rule:

Under Read Committed, do not assume a transaction has one stable snapshot unless your database explicitly says so for the operation you are using.

Repeatable Read And Snapshot-Like Behavior

Repeatable Read usually gives a transaction a more stable view.

The useful mental model is:

If the transaction reads the same committed row again, it should not unexpectedly change because another transaction committed an update.

That makes it helpful for reports, batch processing, and multi-query decisions that need one coherent view.

But this level has important implementation differences.

In PostgreSQL, Repeatable Read sees a snapshot from the start of the transaction's first non-transaction statement. PostgreSQL also documents that its implementation prevents phantom reads, which is stronger than the minimum required by the SQL standard for Repeatable Read. However, serialization anomalies can still occur, so it is not the same as Serializable.

In MySQL InnoDB, Repeatable Read is the default. Plain consistent reads in the same transaction read from the snapshot established by the first read. But locking reads such as SELECT ... FOR UPDATE, plus UPDATE and DELETE, use locking behavior that depends on whether the query uses a unique lookup or a range search. Mixing locking and non-locking reads in one transaction can be harder to reason about.

That difference matters in real code:

BEGIN;

SELECT COUNT(*)
FROM reservations
WHERE event_id = 7;

-- business logic decides whether another reservation is allowed

INSERT INTO reservations (event_id, seat_id, user_id)
VALUES (7, 42, 99);

COMMIT;

The transaction may have a stable view for its reads, but the invariant "do not exceed event capacity" still needs to be enforced somehow. Depending on the database and query shape, that may require:

• Serializable
• a row lock on an event capacity row
• a uniqueness constraint on (event_id, seat_id)
• an atomic conditional update
• a retry path for serialization or deadlock failures

Repeatable Read improves the view. It does not remove the need to model the invariant.

Serializable

Serializable is the strongest standard isolation level.

The goal is to make committed concurrent transactions produce the same effect as if they had run one at a time in some serial order.

That is the closest isolation level to the phrase many engineers assume transactions already mean:

Make this concurrent workflow behave as if it were not concurrent.

But the implementation trade-off matters.

Serializable behavior may involve blocking, range locks, predicate locks, row versioning checks, serialization failures, or retries, depending on the database.

PostgreSQL's Serializable isolation builds on snapshot isolation and can abort a transaction when it detects a serialization anomaly. Applications must be ready to retry failed transactions. SQL Server's Serializable level uses range locks for range predicates to prevent phantom rows. MySQL InnoDB's Serializable is stricter than Repeatable Read and can turn plain reads into locking reads when autocommit is disabled.

That means Serializable is not "turn it on and forget it."

It is useful when:

• the business invariant spans multiple rows
• silent anomalies are worse than retries
• the transaction is short
• the application has a reliable retry strategy
• the extra contention or abort rate is acceptable

It is dangerous when:

• transactions are long
• the application cannot retry safely
• the query scans large ranges
• the system already has lock contention
• developers expect it to fix duplicate external side effects

A retry loop for serialization failures should retry the whole transaction, not just the final statement:

async function runSerializable<T>(operation: () => Promise<T>) {
  for (let attempt = 1; attempt <= 3; attempt++) {
    try {
      return await db.transaction(async (tx) => operationWithTransaction(tx, operation), {
        isolationLevel: 'serializable',
      })
    } catch (error) {
      if (!isSerializationFailure(error) || attempt === 3) {
        throw error
      }
    }
  }

  throw new Error('transaction retry budget exhausted')
}

The exact API depends on your database client. The important rule is stable: a serialization failure means the transaction did not commit and the whole logical operation must be retried from the beginning.

Concrete Example: Reserving The Last Unit

Suppose an API endpoint reserves product inventory.

The naive version:

BEGIN;

SELECT stock
FROM products
WHERE id = 42;

-- application checks stock > 0

UPDATE products
SET stock = stock - 1
WHERE id = 42;

INSERT INTO reservations (product_id, user_id)
VALUES (42, 1001);

COMMIT;

Under concurrency, two requests can both observe stock = 1 before either writes.

The safer version is to make the write itself enforce the condition:

UPDATE products
SET stock = stock - 1
WHERE id = 42
  AND stock >= 1
RETURNING stock;

If no row is returned, the reservation failed because the product no longer had enough stock.

Then insert the reservation only after the conditional update succeeds:

INSERT INTO reservations (product_id, user_id)
VALUES (42, 1001);

This works because the condition is part of the write, not just a prior read. The database now participates in enforcing the invariant.

Another approach is to lock the row before making the decision:

BEGIN;

SELECT stock
FROM products
WHERE id = 42
FOR UPDATE;

-- now decide and write while holding the row lock

COMMIT;

That can be appropriate when the decision is more complex than a single conditional update. The trade-off is blocking. If many transactions contend for the same row, row-level locking can protect correctness while reducing throughput.

The right answer depends on the invariant, contention level, and database behavior.

Isolation Level Alone Is Not Enough

Isolation levels are one part of a correctness design. They do not replace the other parts.

If a successful transaction must publish an event, the next correctness boundary is the dual-write problem, not the isolation level. That pattern is covered in Transactional Outbox Pattern in Microservices.

If the same API request may be retried after a timeout, isolation level does not know it is the same business operation. That is where API Idempotency Keys: Prevent Duplicate Requests Safely becomes part of the design.

How To Choose An Isolation Level

Choose isolation level from the invariant backward.

1. Name the business rule.
2. Identify the rows or predicates the rule depends on.
3. Identify whether the workflow reads, then writes based on what it read.
4. Check the database's actual behavior for that isolation level.
5. Decide whether a constraint, lock, conditional update, or retry is needed.
6. Test the workflow with concurrent requests.
7. Measure blocking, deadlocks, serialization failures, and transaction duration.

A practical decision table:

Do not move a whole application to a stronger isolation level because one workflow needs it. Start by protecting the specific invariant.

What To Test

Isolation bugs usually do not show up in one-request tests.

Test the overlapping behavior:

it('does not reserve the last unit twice', async () => {
  const product = await fixtures.product({ stock: 1 })

  const [first, second] = await Promise.all([
    reserveProduct(product.id, 'user-a'),
    reserveProduct(product.id, 'user-b'),
  ])

  expect([first.status, second.status].sort()).toEqual(['reserved', 'sold_out'])
  expect(await db.reservation.count({ where: { productId: product.id } })).toBe(1)

  const updated = await db.product.findUnique({
    where: { id: product.id },
  })

  expect(updated?.stock).toBe(0)
})

This is not a full proof of correctness. It is a practical guardrail.

For important API endpoints, run this through the real request and persistence boundary, not only through a mocked service. That testing approach is covered in How to Write API Integration Tests.

Also test retry behavior. If the database can abort a transaction with a serialization failure or deadlock, the application must know whether to retry, surface an error, or stop.

Production Debugging Checklist

When a suspected isolation bug appears in production, ask:

1. Which database engine and version are involved?
2. What isolation level is actually active for this transaction?
3. Does the ORM open one transaction or several smaller ones?
4. Are reads routed to replicas while writes go to the primary?
5. Does the code perform read-check-write logic?
6. Is the invariant enforced by a constraint or only by application code?
7. Are locking reads and plain reads mixed in the same transaction?
8. Are serialization failures, deadlocks, and lock waits logged separately?
9. Are retries safe and bounded?
10. Is the transaction held open across network calls or slow queries?

That last point matters. Sometimes the correctness design is reasonable, but long transactions, poor query plans, or missing indexes create operational instability. For that side of the problem, see How to Find and Fix Slow SQL Queries in Production and Why Database Indexes Didn't Fix Your Slow Query.

The Short Version

SQL isolation levels are not labels to memorize. They are contracts about what concurrent transactions are allowed to observe.

Read Committed is often a good default for simple work, but it does not give one stable transaction-wide view. Repeatable Read gives a stronger snapshot in many databases, but it may still need locks, constraints, or retries depending on the invariant. Serializable gives the strongest isolation contract, but the application must handle its operational cost.

The engineering move is to start with the invariant:

1. What must never happen?
2. Which concurrent transactions can make it happen?
3. Which database behavior prevents it?
4. What retry, lock, constraint, or idempotency rule is still needed?

That is how isolation levels become a practical correctness tool instead of a chart in a database manual.