What is a non-repeatable read under read committed?

A non-repeatable read occurs when transaction T1 reads a row, transaction T2 updates and commits that row, and T1 reads the same row again within the same transaction and sees the updated value. This is permitted under read committed because each statement gets a fresh snapshot of committed data.

What is the difference between statement-level and transaction-level snapshots?

Under read committed, each SQL statement acquires a fresh snapshot of committed data as of that statement's start time, allowing non-repeatable reads between statements in the same transaction. Under repeatable read and serializable, one snapshot is taken at transaction start and used for all statements, ensuring a consistent view throughout the transaction.

When are shared locks released under read committed?

Under a lock-based implementation, shared locks are released immediately after each read completes — they are not held for the duration of the transaction. This is what enables non-repeatable reads. Under MVCC (PostgreSQL), readers do not acquire shared locks at all; they read from an immutable snapshot.

How does MVCC implement read committed without shared locks?

MVCC maintains multiple versions of each row. Each statement under read committed reads from the most recent committed version as of that statement's start XID. Writers create new row versions without blocking readers. This eliminates reader-writer contention entirely and is why MVCC databases have high throughput under read committed.

How does read committed prevent dirty reads?

Under read committed, a reader acquires a short-lived shared lock (or takes a per-statement snapshot in MVCC engines) that is released immediately after each row is fetched; because uncommitted rows are either locked exclusively by their writer or invisible in the snapshot, no reader can observe data that has not yet been committed. In PostgreSQL's MVCC implementation, each statement sees only rows whose xmin transaction has been marked committed in pg_clog.

What are non-repeatable reads and why does read committed allow them?

A non-repeatable read occurs when a transaction re-reads the same row and finds a different committed value because another transaction updated and committed it between the two reads. Read committed allows this because each statement takes a fresh snapshot, so a concurrent commit that lands between two statements in the same transaction is immediately visible.

How does PostgreSQL implement read committed using snapshots?

At the start of each SQL statement PostgreSQL records the current transaction ID horizon as the statement's snapshot; a row version is visible if its xmin is committed and below the horizon and its xmax is either absent or not yet committed. Because the snapshot is refreshed per statement rather than per transaction, rows committed by other transactions between statements become visible immediately.

What lock types are used in read committed isolation?

In lock-based systems, read committed uses shared (S) locks on rows being read, held only for the duration of the read and released immediately rather than held until transaction end. Writers acquire exclusive (X) locks held until commit, so readers never block on uncommitted data but two readers never block each other.

Read Committed Isolation Low-Level Design: Statement-Level Snapshots, Lock Mechanics, and Non-Repeatable Read Behavior

⏱ 8 min read

What Is Read Committed Isolation?

Read committed is the weakest practical isolation level. It guarantees that a transaction only reads data that has been committed by other transactions — dirty reads (reading uncommitted data) are prevented. However, it allows non-repeatable reads and phantom reads. Read committed is the default isolation level in PostgreSQL and Oracle.

Statement-Level Snapshot

Under read committed, each SQL statement acquires a fresh snapshot of the committed database state at the moment the statement begins — not when the transaction began. This means:

Two identical SELECT statements within the same transaction may return different results if another transaction commits a change between them.
This is by design: read committed trades consistency within a transaction for reduced lock contention.

In contrast, repeatable read and serializable isolation levels take the snapshot at transaction start and use it for all statements within the transaction.

Shared Locks Under Read Committed

Under a traditional lock-based implementation:

Shared locks (S-locks): Acquired on rows being read. Released immediately after the read completes — the lock is not held for the duration of the transaction. This is sometimes called a “short-duration shared lock.”
Releasing shared locks immediately allows other writers to modify and commit those rows before the current transaction completes, enabling non-repeatable reads.

Under MVCC (Multi-Version Concurrency Control) implementations like PostgreSQL, readers do not acquire shared locks at all. Instead, they read from an immutable snapshot, so readers never block writers and writers never block readers.

Exclusive Locks

Write operations (INSERT, UPDATE, DELETE) acquire exclusive locks (X-locks) on affected rows. These locks are held until the transaction commits or rolls back, not released immediately. This means:

Two concurrent writers on the same row will block — the second writer waits for the first to commit or rollback.
A reader (under lock-based implementation) waiting to read a row being written must wait for the exclusive lock to be released.

Non-Repeatable Reads

A non-repeatable read occurs when transaction T1 reads a row, transaction T2 updates and commits that row, and T1 then reads the same row again and sees the new value. This is explicitly allowed under read committed.

Example sequence:

T1: SELECT balance FROM accounts WHERE id = 42; — returns 1000
T2: UPDATE accounts SET balance = 900 WHERE id = 42; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 42; — returns 900 (different result)

This is not a bug under read committed — it is the defined behavior.

Phantom Reads

A phantom read occurs when a range query returns different sets of rows across two executions within the same transaction because another transaction inserted or deleted rows in that range and committed. Read committed allows phantom reads.

Example:

T1: SELECT COUNT(*) FROM orders WHERE amount > 100; — returns 5
T2: INSERT INTO orders (amount) VALUES (200); COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE amount > 100; — returns 6

Use Cases and When Read Committed Is Appropriate

Read committed is appropriate for most OLTP workloads where:

Each operation is logically independent and non-repeatable reads are not a business problem.
Maximum throughput and minimal lock contention are priorities.
The application does not rely on seeing a consistent snapshot of data across multiple statements in a transaction.

It is inappropriate when a transaction must make decisions based on a consistent view of multiple rows (e.g., computing a derived value from several related rows and writing a result that must be consistent with the inputs).

Lock Contention and MVCC

Under MVCC, read committed has very low lock contention: readers and writers do not block each other. Each reader sees a snapshot of the committed state as of its statement start. Writers acquire row-level locks and block only concurrent writers to the same rows. This combination makes read committed the highest-throughput isolation level in MVCC databases.

SQL Schema

-- Tracks transaction metadata
CREATE TABLE Transaction (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    isolation_level VARCHAR(32) NOT NULL DEFAULT 'READ_COMMITTED',
    status          VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',  -- ACTIVE, COMMITTED, ABORTED
    started_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at    TIMESTAMPTZ
);

CREATE INDEX idx_transaction_status ON Transaction(status, started_at);

-- Tracks lock grants for audit and debugging
CREATE TABLE LockGrant (
    id          BIGSERIAL PRIMARY KEY,
    txn_id      UUID NOT NULL REFERENCES Transaction(id),
    resource_id VARCHAR(256) NOT NULL,  -- e.g. "table:row_id"
    lock_type   VARCHAR(8) NOT NULL,    -- SHARED, EXCLUSIVE
    granted_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    released_at TIMESTAMPTZ            -- NULL if still held
);

CREATE INDEX idx_lockgrant_txn ON LockGrant(txn_id);
CREATE INDEX idx_lockgrant_resource ON LockGrant(resource_id, released_at);

Python Implementation

import uuid
from dataclasses import dataclass, field
from enum import Enum
from typing import Dict, Optional

class IsolationLevel(Enum):
    READ_COMMITTED = "READ_COMMITTED"
    REPEATABLE_READ = "REPEATABLE_READ"
    SERIALIZABLE = "SERIALIZABLE"

class LockType(Enum):
    SHARED = "SHARED"
    EXCLUSIVE = "EXCLUSIVE"

@dataclass
class TxnContext:
    txn_id: str
    isolation: IsolationLevel
    snapshot_xid: Optional[int] = None  # For RC: per-statement; for RR: per-txn

class ReadCommittedEngine:
    def __init__(self):
        self.transactions: Dict[str, TxnContext] = {}
        self.committed_data: Dict[str, dict] = {}   # row_id -> {data, version}
        self.row_locks: Dict[str, str] = {}          # row_id -> exclusive holder txn_id

    def begin_transaction(self, isolation: IsolationLevel = IsolationLevel.READ_COMMITTED) -> str:
        txn_id = str(uuid.uuid4())
        self.transactions[txn_id] = TxnContext(txn_id=txn_id, isolation=isolation)
        return txn_id

    def _get_current_snapshot_xid(self) -> int:
        """Under read committed, each statement gets the current committed snapshot."""
        return id(object())  # Placeholder for actual XID

    def read_row(self, txn_id: str, row_id: str) -> Optional[dict]:
        """
        Read a row. Under read committed, we get a fresh snapshot for this statement.
        Under MVCC, no shared lock is acquired.
        """
        ctx = self.transactions[txn_id]
        if ctx.isolation == IsolationLevel.READ_COMMITTED:
            # Fresh committed snapshot per statement
            snapshot_xid = self._get_current_snapshot_xid()
        else:
            # Use transaction-level snapshot (set at BEGIN)
            snapshot_xid = ctx.snapshot_xid

        row = self.committed_data.get(row_id)
        if row is None:
            return None
        # In real MVCC: filter to rows visible to snapshot_xid
        return row["data"]

    def write_row(self, txn_id: str, row_id: str, data: dict) -> bool:
        """Acquire exclusive lock and stage the write."""
        holder = self.row_locks.get(row_id)
        if holder and holder != txn_id:
            # Block: another transaction holds exclusive lock
            # In production: use wait queue; here we return False
            return False
        self.row_locks[row_id] = txn_id
        # Stage write in transaction buffer (not yet committed)
        return True

    def commit(self, txn_id: str) -> None:
        """Release exclusive locks and make writes visible."""
        locks_to_release = [rid for rid, holder in self.row_locks.items()
                            if holder == txn_id]
        for rid in locks_to_release:
            del self.row_locks[rid]
        del self.transactions[txn_id]

    def rollback(self, txn_id: str) -> None:
        """Release locks and discard staged writes."""
        self.commit(txn_id)  # Same lock release; staged writes are discarded

FAQ

Non-repeatable read example: T1 reads row, T2 updates and commits, T1 re-reads same row — different result; permitted under read committed.
Statement vs transaction snapshot: RC gets a fresh snapshot per statement; RR and serializable get one snapshot at transaction start.
Lock release timing: Shared locks released immediately after read; exclusive locks held until commit/rollback.
MVCC vs lock-based: MVCC readers never acquire locks; they read from versioned snapshots, eliminating reader-writer blocking.