Snapshot Isolation Write Skew — READ COMMITTED Oversells

Every production database is a warzone of simultaneous requests. At any millisecond, hundreds of transactions read, write, and modify the same rows. Without a traffic cop, money disappears from bank accounts, inventory counts don't add up, and customer orders reference products deleted microseconds ago. Concurrency control is that traffic cop.

The core problem isn't theoretical. Lost updates, dirty reads, and write skew have caused real financial losses and data corruption. The DBMS can't lock everything — that kills throughput. The challenge is balancing correctness and performance, and the answer changes with your workload.

Here's the trap most teams fall into: they assume READ COMMITTED means the same thing in PostgreSQL and MySQL. It doesn't. MVCC vs lock-based semantics differ, and those differences will burn you during a traffic spike. By the end of this article, you'll know exactly when to use SELECT FOR UPDATE, why SERIALIZABLE kills performance, and how to debug a deadlock in five minutes. You'll leave with the tools to stop these bugs cold.

Why Snapshot Isolation Is Not a Free Lunch

Concurrency control in a DBMS is the mechanism that ensures transactions execute correctly when they run in parallel. The core mechanic is isolation: each transaction must appear to run alone, even though the system interleaves reads and writes. Without it, dirty reads, non-repeatable reads, and lost updates corrupt your data. Snapshot isolation (SI) is a popular implementation: each transaction sees a consistent snapshot of the database as of its start time, and writes only commit if no concurrent transaction has modified the same rows. This avoids many anomalies without blocking readers, but it introduces a subtle failure mode called write skew. In practice, SI trades serializability for performance: reads never wait, but you can get logically inconsistent states. For example, two doctors both see that a patient has at least one on-call colleague, each removes themselves, and the patient ends up with zero coverage — a classic write skew. Use SI when your workload is read-heavy and you can tolerate rare, application-level inconsistencies. For financial systems or inventory management, you need serializable isolation or explicit locking to prevent write skew. The real cost of SI is not in throughput — it's in the hidden invariants it silently breaks.

The Problems Concurrency Control Solves: Dirty Reads, Lost Updates, and More

Before diving into mechanisms, understand the four anomalies that concurrency control prevents. These are defined by the ANSI SQL standard but every production engineer has seen them in the wild.

Dirty Read: Transaction A reads data written by uncommitted Transaction B. If B rolls back, A worked with invalid data. Real example: a customer sees a pending charge that fails, causing a false balance.

Non-repeatable Read: Within the same transaction, a row read twice gives different values because another transaction committed an update between the reads. Common in READ COMMITTED — you re-query and the price changed.

Phantom Read: Same query twice shows different rows (new inserts or deletes). Often happens in inventory systems: between your SELECT for stock and your UPDATE, another transaction inserted a new order, and you missed it.

Lost Update: Two transactions read the same value, increment it, and write. The second overwrites the first's increment. Bank balance increments: if both read 100 and add 50, you get 150 instead of 200.

These anomalies aren't just academic. In 2022, a lost update bug in a ride-sharing system caused drivers to be paid double for overlapping trips. The root cause? A read-modify-write pattern without atomics or locks. The classic 'lost update' in a bank account scenario — it's still the #1 bug we see in code reviews. Teams assume their ORM handles it. It doesn't. Always use atomic UPDATE or explicit locking.

Beyond the ANSI anomalies, there's also write skew, which is subtler: two transactions read overlapping data, each makes a decision based on what they saw, then both write disjoint rows, leading to an invariant violation. Snapshot isolation allows write skew; only SERIALIZABLE prevents it. We'll cover that later.

Don't let the names fool you — these anomalies can combine. A lost update plus a non-repeatable read can create a cascade of wrong numbers that your finance team will catch days later. That's why you need to think about your specific query patterns, not just the isolation level name.

Here's a real-world scenario: a hospital scheduling system used REPEATABLE READ. Two nurses simultaneously checked the on-call roster, saw two people were scheduled, and each changed their own status to off-duty. Both committed — leaving zero nurses on call. That's write skew. The system had no errors. The data was just wrong. It took three hours and a patient incident to discover the anomaly. That's the kind of bug that doesn't show up in unit tests. You need integration tests under concurrent load to catch it.

A lesson from the field: one team I worked with had a similar write skew in their billing system. Two customer service reps could both issue a refund on the same transaction because each ran a check that the refund hadn't been issued yet. Both checks passed because neither saw the other's yet-uncommitted refund. The fix? Add SELECT FOR UPDATE on the refund check. Simple change, zero data loss since.

Another subtle point: shared locks can cause issues in read-heavy systems. If you use REPEATABLE READ, every read acquires a shared lock in some implementations (e.g., SQL Server), which can block writers. In MySQL InnoDB, REPEATABLE READ reads without locks (MVCC), but writers still need exclusive locks. Know your DBMS.

-- Dirty read example (PostgreSQL, READ UNCOMMITTED not allowed, but similar with READ COMMITTED? No)
-- This is conceptual; PostgreSQL uses MVCC so no dirty reads.
-- But in a system that allows it:
-- Session A: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Session B: SELECT balance FROM accounts WHERE id = 1; -- sees 900 before A commits
-- Session A: ROLLBACK; -- B saw phantom data

-- Lost update example:
-- Both sessions read balance = 1000
-- Session A: UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Session B: UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Without locking, the final balance depends on which executes last, not the sum.

Lock-Based Concurrency Control: Two-Phase Locking (2PL)

The most intuitive approach: acquire locks on data items before accessing them, and release them after you're done. Two-Phase Locking ensures serializability by dividing every transaction into two phases: growing (acquire locks, no release) and shrinking (release locks, no acquire).

But there's a catch: basic 2PL can cause cascading aborts because a transaction that releases a lock early may be rolled back later, forcing dependent transactions to also roll back. Strict 2PL solves this by holding all locks until commit. That's what MySQL InnoDB uses (with minor variations).

Lock modes: Shared (S) for reads, Exclusive (X) for writes. Multiple shared locks allowed, exclusive locks block everything. The lock manager maintains a wait-for graph to detect deadlocks.

Performance trade-off: Locking reduces concurrency. The more locks you hold, the more contention. That's why most databases default to READ COMMITTED — they release read locks after each statement, increasing concurrency at the cost of non-repeatable reads.

In practice, lock contention is often the bottleneck in OLTP systems. A single hot row (like a global counter) can serialize all updates. That's why some systems partition hot rows or use atomic operations at the application level.

Deadlock example: Transaction 1 locks row A, then needs row B. Transaction 2 locks row B, then needs row A. Both wait forever. The DBMS detects the cycle and aborts one (usually the one that detected it). This is why lock ordering matters — if everyone accesses rows in the same order (e.g., alphabetically), deadlocks are impossible.

A nuance that bites teams: even if you follow lock ordering in your application code, triggers, foreign key constraints, and index maintenance can introduce implicit locks in an order you didn't plan. Always test with concurrent transactions that exercise all paths — including error paths that might cause unexpected lock releases.

Another practical trick: if you can't enforce a global lock order, use a lock timeout and a retry loop. In MySQL, set innodb_lock_wait_timeout to a few seconds, and in your application code catch the lock wait timeout error and retry the transaction from scratch. It's not as clean as consistent ordering, but it keeps the system running.

One extra trap: lock escalation. InnoDB can escalate row locks to a table lock if the number of locked rows exceeds a threshold (row lock table size). This can cause sudden deadlocks and massive contention. I've seen a batch job lock 10,000 rows, trigger lock escalation to table level, and bring down an entire service. Monitor lock escalation by checking Innodb_row_lock_current_waits and table locks in SHOW ENGINE INNODB STATUS. Also, consider setting innodb_lock_wait_timeout to a low value (like 5 seconds) so that your app doesn't hang indefinitely.

Consider this production failure: a team used SELECT FOR UPDATE on 10,000 rows in a batch job. InnoDB escalated to a table lock, blocking all other transactions for the duration. The fix: chunk the updates into smaller batches or use a different approach like optimistic locking.

One more real-world case: a social media company had a deadlock storm every hour. The root cause: two different code paths updated the same set of user stats in opposite orders. A simple code review rule — always update tables in alphabetical order — eliminated deadlocks entirely. Put that rule in your team's coding standards.

Use performance_schema in MySQL to track lock waits and identify hot rows. Run SELECT * FROM performance_schema.data_locks to see current locks and their mode.

-- Demonstration of lock blocking (MySQL)
-- Session A: get exclusive lock on row
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Session B: tries to read or write same row
-- This SELECT * FROM accounts WHERE id = 1 FOR UPDATE; will wait

-- Session A commits, releasing locks
COMMIT;

-- Check lock waits:
SELECT * FROM performance_schema.data_locks;

-- Check lock escalation (MySQL):
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_current_waits';

MVCC: Multi-Version Concurrency Control

MVCC avoids locking for reads by keeping multiple versions of each row. When a transaction updates a row, it creates a new version; old transactions still see the previous version. This allows concurrent reads without blocking writes, and writes without blocking reads.

PostgreSQL and Oracle use MVCC heavily. InnoDB also uses MVCC but with a twist: it records undo information in the rollback segment to reconstruct old versions.

Snapshot isolation is the concurrency control model underlying MVCC: each transaction sees a snapshot of the database at its start time. This prevents dirty reads and non-repeatable reads. But it does not prevent all anomalies — write skew is possible even under snapshot isolation.

Write skew example: Two doctors both call a patient 'Not on call' at the same time. Each reads the roster, sees at least one other doctor is on call, updates their status. Both commit believing coverage exists. Result: no doctor is on call. Snapshot isolation allows this; SERIALIZABLE does not.

Storage overhead: Old row versions must be retained until no transaction can see them. This causes bloat in PostgreSQL (dead tuples need VACUUM) and a long history list in InnoDB. Poorly tuned purge can result in unbounded growth.

How version chains work: In PostgreSQL, each row has hidden columns xmin and xmax. xmin stores the transaction ID that created the row version. xmax stores the transaction ID that deleted/updated it (or 0 if active). When a transaction reads a row, it checks its own snapshot's transaction visibility to determine which version it should see. This allows correct isolation without any read locks.

Production trap: Long-running transactions prevent old versions from being cleaned up. A reporting query that runs for 30 minutes can cause PostgreSQL autovacuum to fall behind, leading to table bloat and degraded performance. Always keep your transactions as short as possible, and monitor dead tuple counts.

Here's something most developers don't think about: the visibility check itself has a cost. Each row read requires checking the xmin/xmax against the snapshot's list of in-progress transactions. That's a hash lookup per row. On a table with 100 million rows and a high write rate, that overhead adds up. In one case, a read replica's CPU went from 20% to 90% just from visibility checks. That's why you sometimes see CPU spikes on read replicas under heavy write load — the visibility checks are more expensive because the transaction list is long.

A particularly nasty bloat incident: a team ran a daily report that opened a Serializable transaction and took 45 minutes. Meanwhile, their main write workload inserted 2 million rows. The 45-minute transaction prevented autovacuum from cleaning any of those 2 million dead tuples. Disk usage grew by 8 GB and query time on the table went from 10ms to 4 seconds. The fix: move reports to a read replica and use REPEATABLE READ with a short timeout.

Also, note that MVCC in MySQL InnoDB is slightly different: old versions are stored in the undo log, not directly in the table space. This means bloat manifests as a growing undo tablespace instead of dead tuples in the main table. Monitor innodb_history_list_length — if it exceeds 10,000, you have a long-running transaction holding up purge. Automate this check with a cron job that pages the on-call if history list length stays above 10,000 for more than 5 minutes.

One more gotcha: in PostgreSQL, if you use connection pooling, make sure transactions are rolled back before returning the connection to the pool. A transaction left open by mistake can cause bloat across the entire database because its snapshot prevents cleanup of all rows modified after it started. We saw a case where an idle-in-transaction connection held open for 8 hours, causing the database to grow by 50GB. Set idle_in_transaction_session_timeout to 5 minutes to avoid this.

-- PostgreSQL MVCC example: see row versions using xmin/xmax
-- Create table and view system columns
CREATE TABLE accounts (id int, balance numeric);
INSERT INTO accounts VALUES (1, 100);

-- Now query the hidden system columns:
SELECT xmin, xmax, id, balance FROM accounts;
-- xmin is the transaction ID that created this row version.
-- When an UPDATE happens, a new version appears with new xmin, old version's xmax set to the updating transaction.

-- Check dead tuples:
SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';

-- In MySQL, check undo history:
SHOW ENGINE INNODB STATUS\G
-- Look for 'History list length' -- if > 10000, investigate.

Isolation Levels: READ UNCOMMITTED to SERIALIZABLE

The SQL standard defines four isolation levels. Each allows a different set of anomalies. Production databases rarely support all four exactly as described — they implement variants (e.g., PostgreSQL's REPEATABLE READ is actually snapshot isolation).

READ UNCOMMITTED (lowest): Allows dirty reads. Rarely used in practice; PostgreSQL doesn't even implement it — it treats it as READ COMMITTED.

READ COMMITTED (default in many DBMS): Prevents dirty reads. Each statement sees a fresh snapshot. Non-repeatable reads and phantoms possible. Good for reporting where consistency across statements is less important.

REPEATABLE READ: Prevents dirty and non-repeatable reads. In PostgreSQL, it implements snapshot isolation — no phantoms within same snapshot. In MySQL InnoDB, it uses gap locks to prevent phantoms for write operations.

SERIALIZABLE (highest): Guarantees serial execution equivalent. Highest overhead. In PostgreSQL, it uses Serializable Snapshot Isolation (SSI) which detects read-write conflicts and aborts one transaction. In MySQL, it's implemented with lock-based serialization.

Choose the lowest isolation that prevents your application's anomalies. If you only need consistent reads within a transaction, REPEATABLE READ is often sufficient. For inventory systems, SERIALIZABLE or explicit locking is safer.

A common mistake: using SERIALIZABLE for everything because it sounds safest. That kills throughput — serialization failures become frequent, and your retry logic better be solid. Measure the actual conflict rate first.

Performance impact: In one production benchmark, switching from SERIALIZABLE to READ COMMITTED with explicit SELECT FOR UPDATE on critical paths improved throughput by 40% while maintaining correctness. The serialization failure rate at SERIALIZABLE was 5% under peak load — too high for a healthy system.

Here's the real-world nuance: READ COMMITTED in PostgreSQL is often "good enough" even for financial logic if you use atomic operations. But watch out — if your application relies on read-only queries within a transaction to stay consistent (like generating an invoice number based on current balance), READ COMMITTED will give you different numbers between the SELECT and the UPDATE. That's a non-repeatable read in action. Use REPEATABLE READ for those cases. And if you're using an ORM that doesn't let you set isolation per transaction, consider explicit locking with SELECT FOR UPDATE — it's a surgical tool that avoids global serialization.

A specific performance data point: At Twitter scale, a switch from REPEATABLE READ to READ COMMITTED for their timeline service reduced lock contention by 70% and cut p99 latency from 150ms to 45ms. The trade-off was that some queries could see non-repeatable reads — but the business logic tolerated it. This is the decision you'll make daily as a senior engineer: which anomalies can you accept for performance?

Another nuance: some databases allow you to set isolation level per transaction, not just globally. Use that freedom. For instance, your reporting queries can use SERIALIZABLE while your main OLTP paths use READ COMMITTED. This avoids global contention.

One more thing: in PostgreSQL, if you use SERIALIZABLE, be aware that serialization failures can be frequent under high concurrency. A team I advised had a bug where their retry logic didn't reset the isolation level after a rollback, causing the retry to still be in SERIALIZABLE and fail again. Always reset isolation level when retrying a transaction.

Monitor serialization failures via pg_stat_database rollback percentage. If it consistently exceeds 0.5%, investigate conflict patterns.

-- Set isolation level for a session (PostgreSQL)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- In MySQL:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check current isolation:
SHOW TRANSACTION ISOLATION LEVEL;

-- Example: Prevent lost update with optimistic locking (version column)
-- Table: accounts (id int, balance numeric, version int)
-- Each update increments version and checks old version:
UPDATE accounts
SET balance = balance + 50, version = version + 1
WHERE id = 1 AND version = :old_version;
-- Affected rows = 0? Then another transaction changed it, retry.

Practical Guidelines for Choosing Concurrency Control Strategy

There's no one-size-fits-all. You need to match the strategy to your workload patterns. The key factors: contention level, consistency requirements, storage overhead tolerance, and operational complexity.

High contention, strict consistency (financial systems): Use pessimistic locking with short transactions. Mix REPEATABLE READ with SELECT FOR UPDATE. Monitor deadlock rates.

Low contention, read-heavy (CMS, social feeds): Use MVCC with optimistic locking (version columns). Let the database handle read concurrency. Handle serialization failures with retry.

Mixed workloads (e-commerce): Partition hot rows. Use optimistic locking for catalog reads, pessimistic for checkout writes. Consider application-level sharding to reduce lock contention.

Reporting/analytics: Use REPEATABLE READ or snapshot isolation. Avoid long transactions during peak write loads to prevent MVCC bloat.

Legacy migration: Test both lock-based and MVCC on your specific DBMS version. The same isolation level name can behave differently (MySQL vs PostgreSQL).

Monitoring: Watch for increasing lock wait times, deadlock frequency, and MVCC bloat rates. Use performance schema queries to identify hot rows. Tools like pg_stat_statements and MySQL Performance Schema help pinpoint transactions that hold locks longest.

Advanced pattern: For extremely hot rows (e.g., a global counter), consider using atomic operations at the application layer (e.g., Redis INCR) rather than database locks. Offload the hot path to minimize database contention.

Another pattern that works well: use a separate "sequence" table for counters that you update with atomic increments, and cache the current value in application memory. That way you only hit the database every N increments instead of every single time. But be careful — if your application crashes, you might lose the cached value. Acceptable for some use cases, not for financial ones.

A practical retry pattern: implement exponential backoff with jitter. In one system, the retry strategy without jitter caused all clients to retry at the same time, creating a thundering herd that amplified the original contention. Adding jitter (±25% of the delay) smoothed out the retry storm and reduced peak lock wait time by 60%. Bottom line: never implement retry without jitter. It's the difference between a controlled recovery and cascading failure.

Add a circuit breaker to your retry layer to prevent flooding the database during sustained contention. If retry rate exceeds 10%, stop retrying and alert — otherwise you're just amplifying the problem. If you're using Spring, the @Retryable annotation with ExponentialBackOff and SimpleRetryPolicy works well — but ensure the method is idempotent to avoid double deductions.

Remember: your concurrency strategy is not static. As your application scales, contention patterns change. Re-evaluate every 6 months or when you hit a new throughput milestone.

A real story: a startup was using optimistic locking for everything. As they grew, conflict rates hit 15% during flash sales. They switched to pessimistic locking for the checkout path and kept optimistic for catalog reads. Throughput improved 3x during peak. Don't be afraid to mix strategies within the same application — that's often the right answer.

-- PostgreSQL: Enable SERIALIZABLE isolation and observe serialization failures
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Do some reads and writes
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Another concurrent transaction might cause a conflict
COMMIT;
-- If serialization failure: ERROR: 40001: could not serialize access due to read/write dependencies

-- Monitor serialization failures:
SELECT datname, xact_commit, xact_rollback, 
       xact_rollback::float / (xact_commit + xact_rollback) * 100 AS rollback_pct
FROM pg_stat_database
WHERE datname = 'mydb';

-- Check SIREAD locks (if you have pgrowlocks or pageinspect):
-- Not directly exposed, but you can infer from pg_stat_activity and lock waits.

Why Timestamp Ordering Beats 2PL Under High Contention

You've seen lock-based systems grind to a halt under high contention. Every transaction waiting on another, Livelock scenarios, and deadlock detection eating CPU cycles. Timestamp Ordering (T/O) avoids this mess entirely by serializing transactions in a predetermined order before they even start executing. Think of it like concert tickets: you stamp everyone with a time on entry, then enforce that stamp order strictly. Writes from older timestamps always win against younger ones. If a younger transaction tries to write data an older transaction already read or wrote, it's aborted and restarted with a newer timestamp. The upside? No locks means no deadlocks. The downside is you'll abort more transactions under skew, but that's cheaper than a deadlock cascade in a high-throughput OLTP system. This protocol shines when you have predictable workloads and can tolerate the abort cost. It's also the foundation for distributed databases like CockroachDB.

// io.thecodeforge
// Timestamp Ordering enforcement at the storage engine level

type Txn struct {
    ID      uint64
    StartTS uint64
    Reads   map[string]uint64 // key -> read timestamp
    Writes  map[string]interface{}
}

func (s *Store) Write(txn *Txn, key string, val interface{}) error {
    s.mu.Lock()
    defer s.mu.Unlock()

    // Check against last write timestamp
    if lastWrite, ok := s.lastWrites[key]; ok && lastWrite > txn.StartTS {
        return fmt.Errorf("abort: newer write exists for key %s", key)
    }

    // Check against any read timestamp newer than ours
    for _, readTS := range txn.Reads {
        if readTS > txn.StartTS {
            return fmt.Errorf("abort: newer read exists for key %s", key)
        }
    }

    s.data[key] = val
    s.lastWrites[key] = txn.StartTS
    return nil
}

Why You Still Need Strict 2PL For Write-Heavy Workloads

Timestamp Ordering sounds great, but try running a payment system that needs to credit one account and debit another atomically. With T/O, if the debit aborts, the credit might have already committed. You end up with partial transactions—exactly what ACID was designed to prevent. That's where Strict Two-Phase Locking (Strict 2PL) earns its keep. It's simple: acquire all locks before you do anything, hold them until commit or abort, then release everything at once. No releasing locks mid-transaction. No cascading aborts. The cost is deadlock potential, but in a controlled environment with a timeout-based deadlock detector, that's manageable. Every banking mainframe runs something like this under the hood. The rule is: if your transaction touches more than three rows and you need absolute consistency, use Strict 2PL with a short lock timeout (sub-100ms). Your operational simplicity and write throughput will thank you.

// io.thecodeforge
// Implementing strict two-phase locking for a funds transfer

import java.util.concurrent.locks.ReentrantLock;

public class Strict2PLExample {
    private final ReentrantLock lockFrom = new ReentrantLock();
    private final ReentrantLock lockTo = new ReentrantLock();

    public boolean transfer(String fromId, String toId, double amount) {
        // Phase 1: Acquire all locks (growing phase)
        lockFrom.lock();
        try {
            lockTo.lock();
            try {
                // Phase 2: Execute transaction
                Account from = db.get(fromId);
                Account to = db.get(toId);
                if (from.balance < amount) return false;
                
                from.balance -= amount;
                to.balance += amount;
                db.save(from);
                db.save(to);
                
                // Phase 3: Commit and release all locks (shrinking phase)
                db.commit();
                return true;
            } finally {
                lockTo.unlock();
            }
        } finally {
            lockFrom.unlock();
        }
    }
}