Read Committed Isn’t What You Think: Cross-Engine Concurrency Anomalies in Production

We were migrating a payment reconciliation service from Microsoft SQL Server to PostgreSQL. The codebase relied on transactions running under the standard, default READ COMMITTED isolation level.

In our testing environment, every unit test passed. But on day two of the production cutover, our accounting team alerted us that fourteen customer accounts had received duplicate promotional credits during batch processing. The discrepancy totaled over $18,000.

The software engineers insisted their code was safe: they had wrapped the account check and balance update inside a single transaction. They believed that Read Committed guaranteed that if they read a record inside a transaction, no other transaction could modify that record before they committed. That assumption was completely wrong.

What Read Committed actually guarantees

The ANSI SQL-92 standard defines transaction isolation levels primarily in terms of three phenomena: Dirty Reads, Non-Repeatable Reads, and Phantom Reads.

Read Committed only guarantees one thing: you will never read uncommitted, dirty data written by another transaction. It provides absolutely no guarantee that data you read five milliseconds ago has not been modified or deleted by someone else.

Even more dangerously, each major database engine implements Read Committed using fundamentally different concurrency control mechanisms.

PostgreSQL: Statement-level snapshots and concurrent updates

In PostgreSQL, Read Committed uses Multi-Version Concurrency Control (MVCC) with a statement-level snapshot. Each query inside your transaction sees a fresh snapshot taken at the exact instant that specific query began executing.

If Transaction 1 runs SELECT balance FROM accounts WHERE id = 42;, sees $100, and pauses to do application work, Transaction 2 can update the balance to $50 and commit. When Transaction 1 issues an UPDATE accounts SET balance = balance + 10 WHERE id = 42;, PostgreSQL finds the new version of the row and applies the update on top of $50, not $100.

However, if Transaction 1 checks a condition using a SELECT and then branches in application code before inserting, it is entirely vulnerable to race conditions unless it uses explicit row locking (SELECT … FOR UPDATE).

-- The classic check-then-act bug in Read Committed
-- Session 1:
BEGIN;
SELECT count(*) FROM promotions WHERE user_id = 100; -- returns 0
-- (Application logic: count is 0, so proceed with credit)

-- Session 2 commits an insert for user_id = 100 right now...

-- Session 1 continues:
INSERT INTO promotions (user_id, promo_code) VALUES (100, 'WELCOME50');
COMMIT; -- Duplicate promotion granted!

SQL Server: Shared locks vs. RCSI

In Microsoft SQL Server, default Read Committed does not use MVCC at all. It uses shared (S) read locks. When a query reads a row, it acquires a shared lock, and releases that lock as soon as the row is read—not at the end of the transaction.

This means readers block writers and writers block readers. If Transaction A updates row 10, Transaction B attempting to read row 10 blocks until Transaction A commits.

Many SQL Server databases enable Read Committed Snapshot Isolation (RCSI), which brings SQL Server’s behavior closer to PostgreSQL by using row versioning in TempDB. But if RCSI is not enabled, migrating code between engines produces completely unexpected deadlocks.

The Repeatable Read serialization error trap

When developers discover Read Committed anomalies, their instinctive reaction is switching the transaction isolation level to REPEATABLE READ or SERIALIZABLE.

In PostgreSQL, Repeatable Read ensures that a transaction sees the exact same snapshot throughout its entire lifespan. But when two concurrent transactions attempt to update the same row under Repeatable Read, PostgreSQL does not queue them up: it aborts the second transaction with error ‘could not serialize access due to concurrent update’.

If your application does not implement automated retry loops with exponential backoff, switching to Repeatable Read will trade subtle data anomalies for overt HTTP 500 errors in production.

The practical standard

High-level database architecture is not about drawn boxes on an infrastructure diagram. It is about how the engine manages shared resources under concurrency — memory, latches, write-ahead logs, and lock tables. When things break at 2 AM, the fix is rarely adding another replica or throwing more CPU at the host. The fix is understanding the underlying resource bottleneck, measuring the exact wait event, and applying the architectural constraint that makes the system predictable.