Isolation Levels

Backend Fundamentals · Databases · 10 min read

1. What problem does this solve?

When multiple users or services interact with the same database concurrently, their operations can interfere with each other.
Typical problems include:

ProblemExampleWhy it matters
Dirty readTransaction A reads a row that Transaction B has modified but not yet committed.A user sees data that may never become permanent.
Non‑repeatable readTransaction A reads a row, Transaction B updates it, then Transaction A reads again and sees a different value.Business logic that assumes stable data can break.
Phantom readTransaction A counts rows, Transaction B inserts a new row that satisfies the same query, then Transaction A counts again and gets a different result.Inconsistent aggregates or pagination.
Lost updateTwo transactions read the same row, compute a new value, and write it back. The second write overwrites the first.Data loss, incorrect balances, inventory counts, etc.

If isolation levels are not used or are set incorrectly:

  • Data integrity breaks – balances, inventory, or user state can become wrong.
  • User experience suffers – users may see stale or inconsistent data.
  • Business rules fail – e.g., double‑spending, over‑booking, or duplicate orders.

Isolation levels appear in every relational DB (PostgreSQL, MySQL, SQL Server, Oracle) and in any system that relies on ACID transactions, such as microservices that share a PostgreSQL instance or a monolith that processes orders.

2. Mental model

Analogy: The Library

RoleActionIsolation level
ReaderLooks at a bookRead committed – sees only books that are fully checked out.
LibrarianUpdates a book’s metadataRepeatable read – ensures the same book stays the same while the librarian works.
AuthorPublishes a new editionSerializable – the library reorganizes shelves so no other librarian can interfere.

In this analogy, the isolation level is the rule that determines how much a librarian can “see” or “touch” while working on a book. A lower level allows more concurrency but risks seeing half‑finished edits; a higher level guarantees a clean view but may block others.

Engineering model

Isolation levels are part of the ACID properties of a transaction:

  • Atomicity – all or nothing.
  • Consistency – database constraints hold.
  • Isolation – concurrent transactions don’t interfere.
  • Durability – committed changes survive crashes.

Isolation levels control the visibility of uncommitted changes and the ordering of operations between concurrent transactions.

3. Deep explanation

Internals

Isolation LevelWhat it guaranteesTypical implementation
Read UncommittedNo guarantees; dirty reads allowed.No locks, just read whatever is in memory.
Read CommittedNo dirty reads; each statement sees a snapshot of committed data at its start.Locks are released after each statement (PostgreSQL uses MVCC).
Repeatable ReadNo dirty or non‑repeatable reads; all reads see the same snapshot.Locks are held until commit; MVCC snapshot at transaction start.
SerializableFull serializability; transactions appear to run one after another.Snapshot isolation + conflict detection or strict two‑phase locking.

PostgreSQL uses MVCC (Multi‑Version Concurrency Control). Each row has a xmin (creating transaction) and xmax (deleting transaction). A transaction sees the row if xmin <= txid < xmax. Snapshot isolation takes a snapshot at transaction start; higher levels add conflict checks.

Important terminology

  • Dirty read – reading uncommitted data.
  • Non‑repeatable read – reading the same row twice and getting different values.
  • Phantom read – new rows appear in a range query between reads.
  • Lost update – two concurrent updates overwrite each other.
  • Deadlock – two transactions wait for each other’s locks.
  • Serialization failure – a transaction aborts because it would violate serializability.

Trade‑offs

LevelConcurrencyOverheadUse‑case
Read UncommittedHighNoneAnalytics, logs where stale data is acceptable.
Read CommittedMedium‑HighMinimalWeb apps, read‑heavy workloads.
Repeatable ReadMediumSome lock contentionFinancial apps, inventory.
SerializableLowHighestBanking, ticketing, any business rule that must be strictly consistent.

Failure modes

  • Deadlocks – higher isolation levels increase lock duration, raising deadlock risk.
  • Serialization failures – optimistic concurrency may abort transactions; must be retried.
  • Performance degradation – long‑running transactions hold locks, blocking others.

Limitations

  • Not all DBs support all levels (e.g., SQLite only supports Read Uncommitted and Read Committed).
  • Distributed systems may need distributed transactions (2PC) or optimistic concurrency; isolation levels apply only to a single DB instance.
  • Some ORMs abstract isolation levels; developers must know how to set them explicitly.

Where it fits in backend architecture

  • Service layer: Wrap business logic in a transaction with the appropriate isolation level.
  • Database layer: Use connection pools that respect isolation settings.
  • Monitoring: Track lock waits, transaction aborts, and serialization failures.
  • CI/CD: Run integration tests that simulate concurrent access.
  • Locking: Explicit row/table locks (SELECT … FOR UPDATE) can be combined with isolation levels.
  • Optimistic concurrency: Use WHERE version = X checks; often paired with Repeatable Read.
  • Event sourcing / CQRS: Isolation levels matter when persisting commands that modify state.
  • Cache invalidation: Consistency between DB and Redis/Redis Streams can be affected by isolation.

4. How it works

  1. Client starts a transaction (BEGIN).
  2. Database takes a snapshot of the current committed state (MVCC).
  3. Client executes statements; reads see the snapshot; writes create new row versions.
  4. Isolation level rules decide whether to allow reads of uncommitted data or to lock rows.
  5. Commit: if no conflicts, new versions become visible to others; otherwise, the transaction aborts (e.g., serialization failure).
  6. Rollback: discard all changes.
sequenceDiagram
    participant Client
    participant DB
    Client->>DB: BEGIN ISOLATION LEVEL SERIALIZABLE
    DB->>DB: Take snapshot S
    Client->>DB: SELECT * FROM accounts WHERE id=1
    DB->>Client: Return row from S
    Client->>DB: UPDATE accounts SET balance=balance-100 WHERE id=1
    DB->>DB: Create new version v2
    Client->>DB: COMMIT
    DB->>DB: Validate no conflicts
    DB->>Client: Commit OK

5. Practical implementation

Below is a minimal Node.js example using the pg library to demonstrate how isolation levels affect concurrent updates.

// db.ts
import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// updateBalance.ts
import { pool } from './db';

/**
 * Transfer money from one account to another.
 * Uses SERIALIZABLE isolation to avoid lost updates.
 */
export async function transfer(
  fromId: number,
  toId: number,
  amount: number
) {
  const client = await pool.connect();
  try {
    // Start a transaction with SERIALIZABLE isolation
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

    // Read balances
    const { rows: fromRows } = await client.query(
      'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
      [fromId]
    );
    const { rows: toRows } = await client.query(
      'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
      [toId]
    );

    const fromBalance = fromRows[0].balance;
    const toBalance = toRows[0].balance;

    if (fromBalance < amount) {
      throw new Error('Insufficient funds');
    }

    // Update balances
    await client.query(
      'UPDATE accounts SET balance = $1 WHERE id = $2',
      [fromBalance - amount, fromId]
    );
    await client.query(
      'UPDATE accounts SET balance = $1 WHERE id = $2',
      [toBalance + amount, toId]
    );

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

What happens without SERIALIZABLE?

If two concurrent transfer calls use the default READ COMMITTED isolation, they may both read the same initial balance, compute new balances, and write back, causing the second write to overwrite the first – a lost update. With SERIALIZABLE, PostgreSQL will detect the conflict and abort one transaction, forcing a retry.

6. Production considerations

ConcernRecommendation
ScalabilityUse the lowest isolation level that satisfies business rules. For high‑throughput services, consider optimistic concurrency or event sourcing instead of SERIALIZABLE.
ReliabilityRetry transactions that abort due to serialization failures. Keep retry limits reasonable to avoid infinite loops.
SecurityEnsure that only authorized services can start transactions with high isolation levels; expose minimal privileges.
ObservabilityMonitor pg_stat_activity, pg_locks, and pg_stat_statements for lock waits and aborted transactions.
PerformanceBenchmark lock contention. Use SELECT … FOR UPDATE sparingly; prefer row‑level locks only when necessary.
Error handlingCatch serialization_failure errors (SQLSTATE 40001) and retry.
Resource usageLong transactions hold locks; keep them short. Use connection pooling.
Operational concernsDocument isolation level choices in architecture docs. Use CI tests that simulate concurrent access.
Failure recoveryIn case of deadlocks, PostgreSQL aborts one transaction; ensure your application can recover gracefully.
DeploymentKeep database schema and isolation level settings in migration scripts.

7. Common mistakes

MistakeWhy it’s problematic
Assuming default isolation is safeMany frameworks default to READ COMMITTED, which can still produce lost updates.
Mixing isolation levels in the same transactionSetting isolation per statement is ignored; the transaction’s level applies to all.
Using SELECT … FOR UPDATE without understanding lock scopeCan lead to deadlocks if not paired with consistent ordering.
Ignoring serialization failuresFailing to retry leads to lost work or inconsistent state.
Over‑using SERIALIZABLEIncreases contention, reduces throughput, and can cause cascading aborts.
Not releasing connectionsLeaks can exhaust the pool, causing timeouts.
Assuming MVCC guarantees no phantom readsOnly REPEATABLE READ and SERIALIZABLE prevent phantoms.
Relying on application‑level locks instead of DB isolationHarder to reason about and can be bypassed by other services.

8. Senior engineer perspective

What changes at scale?

  • Sharding / Partitioning: Isolation levels apply per shard; cross‑shard consistency requires distributed transaction protocols or eventual consistency patterns.
  • Read replicas: READ COMMITTED can be satisfied on replicas, but higher levels require the primary. Design read‑heavy workloads to use replicas while writes stay on the primary.
  • Distributed services: When multiple services modify the same data, you may need optimistic concurrency or Saga patterns instead of relying on DB isolation alone.
  • Monitoring: Track lock wait times (pg_locks), transaction abort rates, and retry counts. Alert on spikes indicating contention.
  • Cost/performance: Higher isolation levels increase CPU and I/O due to conflict detection. Benchmark under realistic load before choosing.
  • Bottlenecks: Long‑running transactions holding locks can become the main bottleneck. Refactor to smaller, idempotent operations.
  • When NOT to use: If the business can tolerate eventual consistency (e.g., analytics, recommendation engines), avoid SERIALIZABLE. Use READ COMMITTED or even READ UNCOMMITTED with careful design.

9. Interview questions

1BeginnerWhat is a dirty read, and how does the READ COMMITTED isolation level prevent it?
Answer
A dirty read occurs when a transaction reads data written by another transaction that has not yet committed. READ COMMITTED ensures each statement sees only data that has been committed, so dirty reads cannot happen.
2BeginnerExplain the difference between REPEATABLE READ and SERIALIZABLE.
Answer
REPEATABLE READ guarantees that all reads within a transaction see the same snapshot, preventing non‑repeatable reads. SERIALIZABLE adds phantom read protection and ensures the transaction behaves as if it were the only one running, often via conflict detection or strict locking.
3SeniorIn PostgreSQL, how does MVCC enable READ COMMITTED without locking readers?
Answer
MVCC stores multiple versions of a row. Readers read the latest committed version without acquiring locks, while writers create new versions. READ COMMITTED simply reads the most recent committed snapshot at each statement.
4SeniorDescribe a scenario where using SERIALIZABLE could lead to a performance bottleneck, and how you would mitigate it.
Answer
In a high‑concurrency order‑processing service, SERIALIZABLE may cause many serialization failures, forcing retries and increasing latency. Mitigation: use optimistic concurrency (version columns) or switch to REPEATABLE READ with explicit row locks (FOR UPDATE) only on critical sections.
5Scenario/DebuggingYou have a microservice that occasionally throws “serialization_failure” errors during order placement. What steps would you take to diagnose and fix the issue?
Answer
  • Enable log_min_error_statement to capture failing statements.
  • Inspect pg_stat_activity and pg_locks to identify contention.
  • Check transaction size; split into smaller units.
  • Add retry logic with exponential backoff.
  • Consider lowering isolation level or using optimistic concurrency.
  • Review schema for missing indexes that could cause phantom reads.

10. Hands‑on task

Goal: Demonstrate a lost update and how SERIALIZABLE prevents it.

  1. Setup

    npm init -y
npm install pg
touch db.ts updateBalance.ts
export DATABASE_URL="postgres://user:pass@localhost:5432/testdb"

  2. Create a simple table

    CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  balance NUMERIC NOT NULL
);
INSERT INTO accounts (balance) VALUES (1000), (1000);

  3. Implement two concurrent transfers

    • transferReadCommitted.ts uses default isolation.
    • transferSerializable.ts sets BEGIN ISOLATION LEVEL SERIALIZABLE.

  4. Run both scripts concurrently (e.g., via node transferReadCommitted.js & node transferReadCommitted.js).
    Observe that the final balances may be wrong (lost update).

  5. Run the serializable version and verify that one transaction aborts with serialization_failure, and the final balances remain correct after retrying.

  6. Optional: Add a simple retry loop and log the number of attempts.

11. Quick revision

  • Isolation levels control visibility of uncommitted data.
  • Read Uncommitted – dirty reads allowed, no locks.
  • Read Committed – no dirty reads; each statement sees a snapshot.
  • Repeatable Read – same snapshot for all reads; prevents non‑repeatable reads.
  • Serializable – full serializability; prevents phantoms and ensures strict ordering.
  • PostgreSQL uses MVCC; higher levels add conflict detection.
  • Lost updates happen under READ COMMITTED if two transactions update the same row without locking.
  • SELECT … FOR UPDATE acquires row locks; useful with REPEATABLE READ.
  • Serialization failures (SQLSTATE 40001) must be retried.
  • Higher isolation → lower concurrency, higher overhead.
  • Monitor lock waits, abort rates, and retry counts in production.
  • Use the lowest level that satisfies business rules; avoid unnecessary SERIALIZABLE.

12. References