Transaction Isolation Levels: ACID, MVCC & Race Condition Prevention at Scale

A transaction is a sequence of operations that either all succeed (commit) or all fail (rollback). ACID properties—Atomicity, Consistency, Isolation, Durability—guarantee correctness. But implementing ACID completely comes at a cost: locking and reduced concurrency. Most systems relax the isolation guarantee: instead of serializing all transactions, they allow some transactions to see uncommitted changes from others (dirty reads), or see phantom rows that appeared between reads. Understanding isolation levels—and the race conditions each allows—is critical for building systems that are both correct and fast. This guide dissects the four SQL isolation levels, race conditions, and optimization strategies.

Md Sanwar Hossain
Md Sanwar Hossain

Software Engineer · Database · Systems Design

Database March 19, 2026 20 min read Database Management Series
Database transaction isolation and ACID properties

ACID Properties: The Foundation

Atomicity

A transaction is atomic: it either completes entirely or rolls back completely. There is no middle ground. If a transfer debits account A and credits account B, both operations succeed or both fail.

Consistency

A transaction moves the database from one consistent state to another. All constraints (primary keys, foreign keys, CHECK constraints) are satisfied. The database never violates its invariants, even if a transaction fails halfway.

Isolation

Concurrent transactions do not interfere with each other. Transaction A's reads and writes are isolated from Transaction B's. Isolation is the property relaxed by most systems for concurrency.

Durability

Once a transaction commits, the data persists, even if the server crashes. Durability is provided by write-ahead logging: transactions are written to disk before responding to the client.

The Four Isolation Levels

READ UNCOMMITTED (Dirty Reads)

Transaction A can read uncommitted changes from Transaction B. If B rolls back, A has read a value that never existed. This is rarely used in production.

-- Transaction B
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- balance = 900

-- Transaction A (same time)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Reads 900 (uncommitted!)
COMMIT;

-- Transaction B
ROLLBACK;  -- Oops, the -100 never happened. A read a phantom value.

READ COMMITTED (Default in Most Systems)

Transaction A can only read committed changes from Transaction B. But between two reads in A, B can insert new rows (phantom reads) or update rows (non-repeatable reads).

-- Transaction A
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 123;  -- Result: 5
COMMIT;

-- Transaction B (same time)
BEGIN;
INSERT INTO orders (user_id, ...) VALUES (123, ...);
COMMIT;

-- Transaction A (continued)
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 123;  -- Result: 6! (phantom read)

REPEATABLE READ

Within a transaction, the same query returns the same result. New rows cannot appear (phantom reads are prevented), and existing rows cannot change. But between transactions, updates are visible (lost update race condition possible).

SERIALIZABLE

Complete isolation. Transactions execute as if they were serial (one after another), even though they run concurrently. This is the highest isolation level but the slowest due to locking overhead.

MVCC: Multi-Version Concurrency Control

Modern databases use MVCC to achieve high concurrency without locking. Each transaction sees a snapshot of the database at a specific point in time. When a row is updated, the database creates a new version; the old version remains visible to transactions that started before the update.

-- Simplified MVCC model
rows = {
  (id=1, balance=1000, version=1),
  (id=1, balance=900, version=2),   // updated by T2
  (id=1, balance=800, version=3)    // updated by T3
}

// T1 (started at version 2) sees: balance=900
// T2 (started at version 3) sees: balance=800
// New transactions see: balance=800

Race Conditions and How to Prevent Them

Dirty Read

Reading uncommitted data. Prevented by: READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

Non-Repeatable Read

The same query returns different results within a transaction. Transaction A reads a row, Transaction B updates it, A reads again and gets a different value. Prevented by: REPEATABLE READ, SERIALIZABLE.

-- T1 (REPEATABLE READ)
BEGIN;
SELECT salary FROM employees WHERE id = 123;  -- 50,000

-- T2
UPDATE employees SET salary = 60,000 WHERE id = 123;

-- T1
SELECT salary FROM employees WHERE id = 123;  -- Still 50,000 (repeatable)

Phantom Read

A query returns different rows in two executions within the same transaction. Between the two SELECT statements, Transaction B inserted rows matching the WHERE clause. Prevented by: SERIALIZABLE (and sometimes REPEATABLE READ, depending on the database).

Lost Update

Two transactions read the same value, both increment it, and the increments are lost. Prevented by: optimistic locking (version numbers) or pessimistic locking (SELECT FOR UPDATE).

-- Lost update scenario
-- T1: SELECT balance = 100; balance++; UPDATE balance = 101;
-- T2: SELECT balance = 100; balance++; UPDATE balance = 101;
-- Result: balance is 101, not 102. One increment was lost.

-- Solution: SELECT FOR UPDATE (pessimistic locking)
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- Lock the row
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

Optimistic vs Pessimistic Locking

Pessimistic Locking

Lock the resource before reading: SELECT FOR UPDATE. If another transaction holds the lock, you wait. Safe but can cause deadlocks and reduce concurrency.

-- PostgreSQL
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

Optimistic Locking

Add a version field to each row. On update, check that the version hasn't changed. If it has, the transaction fails (conflict). Retry the transaction. Reduces locking overhead but requires retry logic.

@Entity
public class Account {
    @Version
    Long version;
    BigDecimal balance;
}

// JPA automatically handles optimistic locking
account.setBalance(account.getBalance() + 100);
accountRepository.save(account);  // Throws OptimisticLockingFailureException if version changed

Deadlocks and Prevention

A deadlock occurs when two transactions wait for each other. T1 holds lock A and waits for lock B; T2 holds lock B and waits for lock A. Both are stuck.

// Prevention strategies
// 1. Always acquire locks in the same order
// 2. Keep transactions short
// 3. Use consistent READ COMMITTED isolation level
// 4. Monitor deadlock frequency and investigate

SELECT * FROM pg_stat_database WHERE deadlocks > 0;

Choosing Isolation Levels in Practice

Performance Implications

Higher isolation levels reduce concurrency:

Understanding isolation is essential for building correct systems at scale. Master these patterns, and you avoid data corruption, race conditions, and cascading failures.

Key Takeaways

Tags:

transaction isolation ACID properties MVCC deadlock prevention database concurrency optimistic locking

Read More

Explore related articles on databases, consistency, and distributed systems:

Discussion / Comments

Related Posts

System Design

Database Replication

Leader-follower patterns and consistency models.
System Design

Database Sharding

Horizontal scaling with consistent hashing.
System Design

Distributed Caching

Cache patterns and consistency models.
Back to Portfolio

Last updated: March 2026 — Written by Md Sanwar Hossain