REPEATABLE READ Phantom Inventory — DBMS Transactions

REPEATABLE READ is often mistaken as a silver bullet for consistency, but it leaves a gaping hole for phantom inserts that can corrupt inventory counts in production. If you're running range scans on MySQL InnoDB without gap locks, you'll see phantom rows appear mid-transaction — a $50k stock discrepancy waiting to happen. PostgreSQL's snapshot isolation handles this transparently, but relying on the SQL standard alone will burn you. Know your DBMS's implementation, or escalate to SERIALIZABLE when phantom prevention is non-negotiable.

Why REPEATABLE READ Still Lets Phantom Inventory Slip Through

REPEATABLE READ is a transaction isolation level that guarantees that any row read during a transaction will appear unchanged on subsequent reads within the same transaction. The core mechanic is that it locks every row the transaction touches (via shared or exclusive locks) and holds those locks until commit. This prevents dirty reads and non-repeatable reads — but it does not prevent phantom reads. A phantom read occurs when a new row inserted by another transaction matches the WHERE clause of a query you already executed. Because REPEATABLE READ locks only existing rows, not the gap where a new row could appear, phantoms are possible. In practice, this means that if you run SELECT ... WHERE quantity > 0 twice in the same transaction, the second query may return rows that weren't there the first time. This is a direct consequence of the isolation level's lock granularity: row locks, not predicate locks. Most databases implement REPEATABLE READ with snapshot isolation (e.g., PostgreSQL, Oracle) or with lock-based semantics (e.g., MySQL InnoDB). Under snapshot isolation, you get a consistent view of the database as of the transaction start — which actually prevents phantoms for reads, but not for write skew or write conflicts. Under lock-based REPEATABLE READ, phantoms are real and can corrupt inventory counts, reservation systems, or any logic that assumes a stable result set. Use REPEATABLE READ when you need to read the same rows multiple times and ensure they haven't changed, but you can tolerate new rows appearing. For inventory systems where phantom inserts would break business logic (e.g., overselling stock), you must escalate to SERIALIZABLE or use explicit gap locks (e.g., SELECT ... FOR UPDATE with a range condition).

ACID in Practice: Beyond the Acronym

While every textbook defines ACID, seeing it handle a failure in real-time clarifies why we pay the performance tax for a relational engine. Atomicity is the 'all-or-nothing' guarantee, but Durability is the 'promise'—ensuring that once a COMMIT is acknowledged, even a total power failure won't revert the change.

-- io.thecodeforge: Atomic Bank Transfer Example
BEGIN;

-- Step 1: Deduct from source
UPDATE io_thecodeforge.accounts 
SET balance = balance - 500, updated_at = NOW() 
WHERE account_id = 'ACC-001' AND balance >= 500;

-- Step 2: Add to destination
UPDATE io_thecodeforge.accounts 
SET balance = balance + 500, updated_at = NOW() 
WHERE account_id = 'ACC-002';

-- If a network timeout or constraint violation occurs here,
-- the entire block is invalidated.
COMMIT;

-- PRO TIP: In Spring Boot, use @Transactional(propagation = Propagation.REQUIRED)
-- to wrap these calls in a single proxy-managed transaction.

Isolation Levels and Read Phenomena

Isolation levels are a slider between 'Perfect Safety' and 'Maximum Speed.' As you lower the level, you introduce specific anomalies: 1. Dirty Reads: Reading data that hasn't been committed yet. 2. Non-Repeatable Reads: A row changes its value while you're still looking at it. 3. Phantom Reads: New rows appear in a range query because another transaction inserted them.

-- io.thecodeforge: Demonstrating Read Phenomena

-- 1. READ COMMITTED (Default for Postgres)
-- Prevents Dirty Reads but allows Non-Repeatable Reads.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 2. REPEATABLE READ (Default for MySQL InnoDB)
-- Uses MVCC (Multi-Version Concurrency Control) to ensure you see a snapshot.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 3. SERIALIZABLE (Highest Safety)
-- Prevents Phantoms by placing range locks or using predicate locking.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Production Pattern: Use 'SELECT ... FOR UPDATE' to skip isolation logic
-- and force a row-level write lock immediately.
BEGIN;
SELECT balance FROM io_thecodeforge.accounts WHERE account_id = 'ACC-001' FOR UPDATE;
-- This row is now locked until we COMMIT.

Deadlocks: Detection and Prevention

A deadlock occurs when Transaction A holds Lock 1 and wants Lock 2, while Transaction B holds Lock 2 and wants Lock 1. They will wait forever. Most modern RDBMS (Postgres, MySQL) run a background 'Deadlock Detector' thread that identifies these cycles and kills the 'cheapest' transaction to break the loop.

-- io.thecodeforge: Deadlock Prevention via Deterministic Ordering

-- BAD: Locking in random order based on app logic
-- TX1: Update A, then B
-- TX2: Update B, then A  <-- High Deadlock Risk!

-- GOOD: Always sort IDs before locking
BEGIN;
-- By ordering our IDs, all transactions wait in the same 'queue'
SELECT * FROM io_thecodeforge.accounts 
WHERE account_id IN ('ACC-001', 'ACC-002') 
ORDER BY account_id ASC 
FOR UPDATE;

UPDATE io_thecodeforge.accounts SET balance = balance - 100 WHERE account_id = 'ACC-001';
UPDATE io_thecodeforge.accounts SET balance = balance + 100 WHERE account_id = 'ACC-002';
COMMIT;

MVCC: How Isolation Levels Actually Work

Multi-Version Concurrency Control (MVCC) allows each transaction to see a snapshot of the data at the start of the transaction (or at the statement level, depending on isolation). Instead of locking every row a reader might touch, the database keeps multiple versions of each row. When a transaction writes, it creates a new version; concurrent readers see the old version until the writer commits. This is why PostgreSQL can run thousands of concurrent SELECTs without blocking — readers never block writers, and writers never block readers (unless they conflict).

-- io.thecodeforge: MVCC Snapshot Demonstration (PostgreSQL)

-- Session 1: Start a repeatable read transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM io_thecodeforge.accounts WHERE account_id = 'ACC-001';
-- Returns 1000

-- Session 2 (concurrent): Update and commit
BEGIN;
UPDATE io_thecodeforge.accounts SET balance = 800 WHERE account_id = 'ACC-001';
COMMIT;

-- Session 1: Still sees 1000 (its snapshot is from the start)
SELECT balance FROM io_thecodeforge.accounts WHERE account_id = 'ACC-001';
-- Returns 1000

COMMIT;

-- After Session 1 commits, new queries see 800.

Transaction Management in Practice: Application-Level Patterns

Real-world apps rarely use raw SQL transactions. Frameworks like Spring Boot, Hibernate, and Entity Framework manage transaction boundaries declaratively. You need to understand: - Transaction propagation: REQUIRED, REQUIRES_NEW, NESTED — when does a new transaction start? - Isolation level override: @Transactional(isolation = Isolation.REPEATABLE_READ) can clash with database defaults. - Transactional outbox pattern for reliable messaging. - Handling rollback-only exceptions: sometimes a caught exception still marks the transaction for rollback. - Setting timeout and read-only hints to optimize connection usage.

// io.thecodeforge.service.TransferService
@Service
public class TransferService {

    @Transactional(isolation = Isolation.READ_COMMITTED, timeout = 5)
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        Account from = accountRepo.findByIdForUpdate(fromId);
        if (from.getBalance().compareTo(amount) < 0) {
            throw new InsufficientBalanceException();
        }
        Account to = accountRepo.findByIdForUpdate(toId);
        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));
        accountRepo.save(from);
        accountRepo.save(to);
        // If an exception is thrown here (e.g., optimistic lock failure),
        // the entire transaction rolls back automatically.
    }
}

The Forgotten Enemy: Lost Updates and Write Skew

Atomicity, isolation, and the rest of ACID still leave a bastard child — write skew. Two transactions read overlapping data, then write conflicting results based on stale snapshots. The bank example: you check your balance ($1000), I check mine ($1000). We both transfer $200 from your account to mine. REPEATABLE READ says each sees its own snapshot. Neither sees the other's write until commit. Result: your balance goes to $800, mine goes to $1200. We just printed $200. That's a phantom surplus, and no isolation level under SI fixes it. Why this happens: MVCC snapshots freeze read sets but not write sets. Both transactions see the original $1000 balance — neither notices the other's deduction. Prevent it with SELECT FOR UPDATE or explicit predicate locks. In PostgreSQL: BEGIN; SELECT balance FROM accounts WHERE account_id = 123 FOR UPDATE; — that locks the row, forcing the second transfer to wait. Without it, you're printing money in production.

-- io.thecodeforge Write Skew in Postgres
-- Two concurrent transactions that both deduct from account 123

-- Session 1 (T1)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 123;
-- sees: 1000

-- Session 2 (T2) — starts while T1 is open
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 123;
-- also sees: 1000 (snapshot from T2 start)

-- T1 deducts $200
UPDATE accounts SET balance = balance - 200 WHERE account_id = 123;
COMMIT;  -- balance becomes 800

-- T2 continues with stale snapshot
UPDATE accounts SET balance = balance - 200 WHERE account_id = 123;
COMMIT;  -- balance becomes 800 (expects 600, but T1 already set to 800)

-- Query after both commits
SELECT * FROM accounts WHERE account_id = 123;
-- result: balance = 800 (wrong; should be 600)

When Your Cache Lies: Transaction Boundaries for Read-Heavy Workloads

Every junior dev learns to cache. Few learn that distributed caches break transaction isolation. Picture this: your RDBMS holds account balances. The app caches 'balance = 1000' in Redis. T1 deducts $200 from the DB — no cache update. A second service reads Redis, sees $1000, and initiates another deduction. The database becomes consistent after T1 commits, but your cache serves stale data for minutes. The fix: cache-aside with a TTL no longer than your business tolerance for inconsistency. Better: use database-level caching (PgBouncer with statement caching) or implement a cache invalidation protocol that aligns with transaction commit timing. Do not cache aggregate values that change per transaction unless you are willing to accept eventual consistency. If your business logic depends on strict serializability, disable caching completely. Real production incident: a fintech startup lost $12k because a Redis cache returned a stale pre-tax balance after a bulk transaction update. The DB was consistent the whole time — but nobody asked the DB.

# io.thecodeforge Cache Invalidation After Transaction
import redis
import psycopg2

def transfer_funds(account_id: int, amount: int):
    conn = psycopg2.connect("dbname=bank user=app")
    cursor = conn.cursor()
    cache = redis.Redis(host="redis-cluster", port=6379)

    try:
        # Read balance directly from DB — never trust cache
        cursor.execute(
            "SELECT balance FROM accounts WHERE account_id = %s FOR UPDATE",
            (account_id,)
        )
        balance = cursor.fetchone()[0]

        if balance + amount < 0:
            raise ValueError("Insufficient funds")

        cursor.execute(
            "UPDATE accounts SET balance = balance + %s WHERE account_id = %s",
            (amount, account_id)
        )

        # Invalidate cache AFTER commit — not before
        conn.commit()
        cache.delete(f"balance:{account_id}")

    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

# If you cache before commit, a crash leaves stale data forever
# This pattern ensures cache only updates after DB persistence