Transactions, Concurrency Control, and Isolation Levels Questions

Direct answer

The naive pattern loses updates because "read the value, compute a new value, write it back" is three separate steps with a gap between the read and the write, and the database has no idea those three steps are supposed to be atomic together. Two workers can both read the same starting value, both compute the same "correct" next value from it, and both write that value back, so instead of two increments you get one. The fix is to never let application code hold a value in memory across the gap: push the increment itself into a single statement the database executes atomically.

Structured elaboration

Why it breaks. SELECT count FROM daily_user_counts WHERE day = ... then, in application code, count + 1, then UPDATE ... SET count = <the number you computed> gives the database three independent operations. Under Read Committed (the common default), nothing stops two sessions from both executing the SELECT before either executes the UPDATE.

Three fixes, in order of preference:

1. Atomic in-place update, the simplest and usually correct default: UPDATE daily_user_counts SET user_count = user_count + 1 WHERE day = $1. The arithmetic happens inside the database's own read-modify-write of the row, under a row lock the engine takes automatically, so a concurrent identical statement simply waits for the first to commit and then reads the already-incremented value.
2. Upsert via ON CONFLICT DO UPDATE, needed when the row for day may not exist yet: INSERT INTO daily_user_counts (day, user_count) VALUES ($1, 1) ON CONFLICT (day) DO UPDATE SET user_count = daily_user_counts.user_count + 1. This requires a unique index on day as the conflict target (here, the primary key already provides one); without it Postgres has nothing to detect the conflict against.
3. Explicit row lock (SELECT ... FOR UPDATE), useful when the read-then-write logic is more than a single arithmetic expression (for example, the new value depends on a lookup elsewhere first): SELECT user_count FROM daily_user_counts WHERE day = $1 FOR UPDATE; then UPDATE ... SET user_count = $2 WHERE day = $1; in the same transaction. FOR UPDATE takes the row lock at read time, so a second session's SELECT ... FOR UPDATE on the same row blocks until the first transaction commits, closing the same gap the naive version left open.

Worked example

Reproducing the bug, run for real against Postgres 16: starting user_count = 0, two sessions both SELECT the row (both see 0), both compute 0 + 1 = 1 in application code, both UPDATE ... SET user_count = 1:

T1: SELECT user_count -> 0        T2: SELECT user_count -> 0
T1: UPDATE ... SET user_count = 1  COMMIT
                                   T2: UPDATE ... SET user_count = 1  COMMIT

Final value: 1. Two increments happened; one was lost.

Fix 1 confirmed, same starting value, both sessions instead run UPDATE daily_user_counts SET user_count = user_count + 1 WHERE day = $1:

T1: UPDATE ... SET user_count = user_count + 1   (row lock acquired)
T2: UPDATE ... SET user_count = user_count + 1   (blocks on T1's row lock)
T1: COMMIT                                        (T2 unblocks, reads the just-committed value, adds 1)
T2: COMMIT

Final value: 2, run and verified against the live database, both increments preserved.

Fix 2 confirmed, run twice in sequence on a fresh row:

INSERT INTO daily_user_counts (day, user_count) VALUES ('2026-09-25', 1)
  ON CONFLICT (day) DO UPDATE SET user_count = daily_user_counts.user_count + 1;
-- run again --

Result after two calls: user_count = 2, matching the atomic-update result exactly.

Trade-offs & pitfalls

• Performance: the atomic UPDATE and the upsert both serialize writers on the same row through a brief row lock, which is fine at the throughput of a per-day counter but becomes a real bottleneck if the same row is hit by thousands of concurrent workers per second (a single hot row can only accept one committing writer at a time). At that scale, the standard mitigation is sharding the counter (N sub-rows summed on read, or an in-memory/Redis-backed counter flushed periodically to the row) rather than fighting row-lock contention directly.
• Consistency: the atomic update and SELECT FOR UPDATE are both immediately consistent, every committed read sees the true count. If a shard-and-sum approach is adopted for throughput, reads become eventually consistent within the flush interval, a real trade-off to state explicitly rather than gloss over.
• The naive read-then-write pattern is not "slightly wrong under heavy load," it silently loses data under any concurrent load, including two workers running a few milliseconds apart; do not treat it as a rare-edge-case risk.

Direct answer

Each of the four anomalies (dirty read, non-repeatable read, phantom read, write skew) has a distinct two-transaction shape and a distinct minimum fix. In a banking system aggregating account balances, the practical rule is: use Read Committed as the floor for anything that reads a single row once, Repeatable Read the moment a transaction reads the same data more than once or aggregates across rows, and Serializable (or an explicit lock) the moment two transactions can each independently satisfy an invariant that only holds when you consider both of them together.

Structured elaboration and worked examples

All four demonstrations below ran against Postgres 16 with two real interleaved psql sessions.

1. Dirty read. T1 debits an account inside an open transaction, then rolls back; T2 tries to read the balance mid-flight.

-- T1                                          -- T2
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
UPDATE accounts SET balance = 9999.00
  WHERE id = 1;
                                                BEGIN ISOLATION LEVEL READ UNCOMMITTED;
                                                SELECT balance FROM accounts WHERE id = 1;
                                                -- returns 500.00, not 9999.00
ROLLBACK;                                      COMMIT;

Even requesting Read Uncommitted, T2 never saw the uncommitted 9999.00, because Postgres maps Read Uncommitted to Read Committed internally, so a dirty read on a reconciliation job (which would have misstated the balance had it happened) is structurally impossible here at any level. On engines that do allow it, Read Committed is the fix.

2. Non-repeatable read. A transaction totals assets by reading the same account's balance twice, with a concurrent transfer committing in between.

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;  -- 500.00
                                                UPDATE accounts SET balance = 750.00 WHERE id = 1; COMMIT;
SELECT balance FROM accounts WHERE id = 1;  -- 750.00, same row, same transaction
COMMIT;

For a balance-aggregation job, this means the same account can be counted at two different values within one report. Fix: Repeatable Read. Re-run under Repeatable Read, both reads return 500.00; the transaction's snapshot doesn't move.

3. Phantom read. A transaction counts "accounts currently flagged" twice, with a concurrent transaction inserting a new flagged row in between.

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM on_call WHERE on_call = true;  -- 2
                                                INSERT INTO on_call VALUES (3, 'carol', true); COMMIT;
SELECT count(*) FROM on_call WHERE on_call = true;  -- 3
COMMIT;

Applied to accounts, this is the shape of "count all overdraft accounts opened this quarter" changing mid-report as new accounts land. Fix: Repeatable Read works on Postgres specifically (its Repeatable Read blocks phantoms too, stronger than the SQL standard requires); on an engine that only guarantees the standard's minimum, use Serializable.

4. Write skew. Two joint sub-accounts share a combined overdraft floor of -50.00 (business rule: balance(A) + balance(B) >= -50.00). Two withdrawals happen concurrently under Repeatable Read:

-- T1 (id=1)                                    -- T2 (id=2)
BEGIN ISOLATION LEVEL REPEATABLE READ;           BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts
  WHERE id IN (1,2);           -- 100.00                SELECT sum(balance) ...  -- 100.00
-- looks safe: 100 - 100 = 0 >= -50
UPDATE accounts SET balance = balance - 100
  WHERE id = 1;
                                                  UPDATE accounts SET balance = balance - 100 WHERE id = 2;
COMMIT;                                          COMMIT;

Both commit. Actual combined balance afterward: -100.00, well past the -50.00 floor, even though each transaction individually checked the rule and it held at the moment each one checked. Neither transaction wrote a row the other read, so Repeatable Read's row-level conflict detection never sees a problem. Fix: re-running the identical sequence under SERIALIZABLE, the second transaction's COMMIT fails outright with the real error ERROR: could not serialize access due to read/write dependencies among transactions, and the invariant holds (only one withdrawal lands). The application must be prepared to catch that error and retry.

Trade-offs & pitfalls

• The first three anomalies are caught by picking a high-enough level. Write skew is different: it requires either Serializable (the engine detects the conflict for you, at the cost of occasional aborted transactions your app must retry) or an explicit lock that makes the shared invariant visible to the engine (SELECT ... FOR UPDATE on a row that represents the constraint, so the second transaction blocks instead of racing). Recommending "just use Serializable everywhere" is not free: it can add code paths that didn't exist before (retry-on-serialization-failure) to parts of the system that never needed them.
• Don't reach for Serializable as a reflex for the first three anomalies; it is correct but more expensive than necessary when Repeatable Read already solves the actual problem.

Direct answer

The SQL standard defines four isolation levels, each one permitting fewer of three named anomalies than the last: Read Uncommitted (may allow dirty reads, non-repeatable reads, and phantom reads), Read Committed (blocks dirty reads only), Repeatable Read (also blocks non-repeatable reads), and Serializable (blocks all three, and write skew besides). Higher isolation buys correctness at the cost of more blocking or more aborted-and-retried transactions, so the right level is the weakest one that still protects the invariant your transaction actually depends on.

Structured elaboration

A dirty read is seeing another transaction's uncommitted write. A non-repeatable read is reading the same row twice in one transaction and getting two different values because another transaction committed a change in between. A phantom read is re-running the same filtered query twice and getting a different set of rows because another transaction inserted or deleted a matching row in between.

One caveat worth stating out loud in an interview: Postgres never actually implements Read Uncommitted as written. Running BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; and updating a row from one session, then reading it uncommitted from a second session at that level, still returns the last committed value, not the dirty one, because Postgres silently upgrades Read Uncommitted to Read Committed internally. The standard permits an engine to offer stronger guarantees than a level requires, and Postgres does that here; engines built on multiversion concurrency control (MVCC, keeping multiple versions of a row instead of overwriting it in place) in general tend to make dirty reads hard to produce even when asked for.

Worked example

A concrete non-repeatable read, run against Postgres 16 with two real interleaved sessions:

-- T1 (READ COMMITTED)                       -- T2
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- returns 500.00
                                              BEGIN;
                                              UPDATE accounts SET balance = 750.00 WHERE id = 1;
                                              COMMIT;
SELECT balance FROM accounts WHERE id = 1;   -- returns 750.00 (!)
COMMIT;

Two SELECTs in the same transaction, same row, no write from T1 in between, and the value changed underneath it, that is the anomaly by definition. Re-running the identical sequence with REPEATABLE READ instead of READ COMMITTED on T1, both reads returned 500.00; T1's snapshot was fixed at the start of the transaction and did not move even though T2's write committed in between.

A second, less obvious point the anomaly table doesn't capture: isolation strength is not an all-or-nothing choice for a whole system. A single application often mixes levels deliberately: an inventory-and-purchase path (decrementing stock on checkout) needs strong protection because a lost decrement means overselling a physical item, while a page-view or "active users today" counter on the same schema can tolerate a much weaker level, because losing an occasional increment under load costs nothing but a slightly-off vanity metric. Treating every table in the schema as if it needed Serializable is a common overcorrection.

Trade-offs & pitfalls

• Write skew is real and none of the three named anomalies cover it. Two transactions can each read a consistent snapshot, each individually satisfy a business invariant based on what they saw, and still jointly violate the invariant when both commit, because neither transaction wrote a row the other read. Repeatable Read (even Postgres's stronger version) does not catch this; only true Serializable does. The fix is Serializable isolation or an explicit lock on the rows the invariant depends on.
• Detecting anomalies in a live production system is harder than in a two-session demo. A dirty read or lost update rarely throws an error; it shows up later as a number that's simply wrong (a report that doesn't reconcile, an inventory count that goes negative). Practically, that means instrumenting for it before it happens: log pg_stat_activity.wait_event and blocking chains during incidents, add a periodic reconciliation query for any invariant a transaction can't enforce directly, and treat "we changed nothing and the numbers moved" reports as a concurrency anomaly first, not a bug in the report.

Direct answer

For a high-traffic inventory system, the right default is optimistic concurrency control (OCC) for the general case: most SKUs in a catalog have low per-item contention, so checking a version on write and retrying the rare conflict is cheap and keeps the system fully non-blocking. The one place to switch to pessimistic concurrency control (PCC), an explicit row lock, is the specific hot item: a single flash-sale SKU or a single ticket-booking event where thousands of requests target the exact same row in the same second, because at that level of contention, optimistic retries stop being rare and start being a self-inflicted retry storm.

Structured elaboration

Optimistic concurrency control. Each row carries a version number (or a updated_at timestamp). A worker reads the row including its version, computes the new state, then writes it conditioned on the version being unchanged: UPDATE inventory SET qty_available = qty_available - 1, version = version + 1 WHERE sku = $1 AND version = $2. If another worker committed a change in between, that WHERE clause matches zero rows, the write silently fails to apply, and the application must detect "0 rows affected" and retry (re-read, recompute, re-attempt). No lock is held between the read and the write; the cost of a conflict is entirely on the loser, who has to redo the round trip.

Pessimistic concurrency control. A worker takes an explicit row lock before reading, SELECT qty_available FROM inventory WHERE sku = $1 FOR UPDATE, and every other transaction that tries to touch the same row simply waits until the first one commits or rolls back. There is no conflict to detect and no retry logic to write; contention shows up as latency (queued waiters) rather than as failed attempts.

Locking's deadlock risk. The moment a transaction holds a lock on row A and then tries to acquire a lock on row B, while a second transaction holds B and tries to acquire A, the engine detects the cycle and aborts one of them with a real error (Postgres: ERROR: deadlock detected, naming the two blocked processes and the two locks each is waiting on). OCC has no equivalent failure mode: nothing is held between transactions, so there is nothing to deadlock on. This is a structural argument in OCC's favor whenever the write pattern touches more than one row, which most checkout flows do (decrement inventory, then write an order row).

Worked example

The version-conflict mechanic, run for real against Postgres 16: inventory('SKU-100', qty_available=3, version=1). A worker (call it B) already sold one unit and committed, moving the row to version=2. Worker A, still holding the stale version=1 it read earlier, attempts its own decrement:

UPDATE inventory SET qty_available = qty_available - 1, version = version + 1
  WHERE sku = 'SKU-100' AND version = 1;

Actual result: UPDATE 0. Zero rows matched, because the row's real version is now 2. That's the entire conflict-detection mechanism: no exception, no lock wait, just a row count the application must check. Worker A's retry logic re-reads (qty_available=2, version=2), recomputes, and re-issues the same conditional update, which now succeeds.

For the pessimistic side, taking a row lock and having a second transaction queue behind it is the same underlying mechanism you can observe directly in the lock views: pg_locks shows the second session's request in granted = f with pg_blocking_pids() naming the first session's process id, and the second transaction's wall-clock latency is exactly however long the first one holds the lock.

Trade-offs & pitfalls

• Retry storms under extreme contention. OCC's cost model assumes conflicts are rare. On one specific hot row (a flash-sale item selling out in the first second, or the last few seats on a popular flight), that assumption breaks: hundreds of workers can all read the same version simultaneously, all attempt to write, and all but one lose, immediately re-read, and race again. That amplifies load on the exact row that's already the bottleneck. PCC avoids this by making excess demand wait in an orderly queue instead of retry in a storm, at the cost of every waiter's latency going up and the deadlock exposure noted above the moment more than one resource is locked per transaction.
• User experience under contention differs in kind, not just degree. OCC surfaces as a retry that's invisible to the user if it resolves in milliseconds, or as a visible "someone else grabbed this, try again" message if the application chooses not to auto-retry (common in collaborative-editing UIs, where silently overwriting a conflicting edit is worse than telling the user). PCC surfaces as the request simply taking longer, with no distinct "conflict" state at all, until a lock-wait timeout is hit.
• Recommendation, stated plainly: OCC as the default across the catalog; a targeted pessimistic lock (or a queue in front of the hot row entirely, decoupling the write from the request) for the handful of SKUs an ops team can identify in advance as flash-sale hot items. Don't pick one strategy database-wide; the right choice is a property of the contention level on a specific row, not of the schema.

Direct answer

Both Postgres and MySQL's InnoDB advertise Repeatable Read, and both actually prevent phantom reads for it, but they get there by fundamentally different mechanisms: Postgres does it with pure multiversion concurrency control (MVCC, keeping old row versions around instead of locking anything) snapshot visibility and no locking of any kind, while InnoDB does it with gap and next-key locks that physically block conflicting inserts. That difference is invisible in a correctness test and very visible the moment you look at blocking, deadlocks, or what Serializable costs, which is exactly what an app migrating between the two engines needs to know before it ships.

Structured elaboration

Postgres's Repeatable Read: snapshot only, no locks. Confirmed for real against Postgres 16: a transaction counts rows matching a predicate, a concurrent transaction inserts a new matching row and commits, and the first transaction's identical re-count still returns the original number:

T1: SELECT count(*) WHERE on_call -> 2
    (concurrent INSERT + COMMIT happens here)
T1: SELECT count(*) WHERE on_call -> 2   (still 2, not 3)

No lock was taken anywhere in this sequence; the second transaction's INSERT never blocked. Postgres's own documentation is explicit that this is stronger than the SQL standard requires: "PostgreSQL's Repeatable Read implementation does not allow phantom reads. This is acceptable under the SQL standard because the standard specifies which anomalies must not occur at certain isolation levels; higher guarantees are acceptable." The mechanism is purely visibility: every row's xmin (the id of the transaction that created that row version) and xmax (the id of the transaction that superseded it, if any) are compared against the transaction's fixed snapshot, so a row inserted after the snapshot was taken simply isn't visible, nothing needs to be locked to make that true.

InnoDB's Repeatable Read: MVCC for plain reads, next-key locks for anything that locks. Per the MySQL 8.4 reference manual: "By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows." A next-key lock is "a combination of a record lock on the index record and a gap lock on the gap before the index record," and gap locks are "purely inhibitive": their only job is to stop another transaction from inserting into that gap. Critically, this locking behavior applies to locking reads and writes (SELECT ... FOR UPDATE, UPDATE, DELETE, index scans during those statements), not to a plain SELECT, which InnoDB serves from its own MVCC snapshot much like Postgres does. The practical divergence shows up specifically on write paths: an UPDATE ... WHERE clause matching a range on InnoDB under Repeatable Read will take gap locks across that range and can block a concurrent INSERT into the gap outright, something Postgres's Repeatable Read never does, because Postgres has no concept of a gap lock at all.

Serializable diverges even more sharply. Postgres implements true Serializable as Serializable Snapshot Isolation: non-blocking predicate locks that detect a conflict only at commit time and abort the losing transaction with a real, reproduced error (ERROR: could not serialize access due to read/write dependencies among transactions), while every read remains a cheap, non-blocking snapshot read throughout. MySQL's InnoDB Serializable, per its own documentation, "is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled." That is a pessimistic, locking strategy: ordinary reads start taking shared locks, which means reads that used to never block writers under Repeatable Read can now block them, and can now participate in genuine deadlocks, under Serializable.

Why this matters when migrating

An application built and tested against Postgres can rely on the property that reads, at any isolation level up to and including Serializable, never block a writer and are never involved in a deadlock (Postgres's predicate locks are documented as non-blocking and cannot participate in one). Porting the same isolation level names to MySQL does not port that property: at Repeatable Read, a write that scans a range now risks blocking concurrent inserts into that range via gap locks in a way it never did on Postgres; at Serializable, plain reads themselves start taking locks and can now deadlock. A team that migrated an isolation-level name 1:1 without re-testing concurrent load could see new blocking and new deadlocks appear in production that never showed up on the original engine, from code that changed nothing about its own logic.

Trade-offs & pitfalls

• Don't treat "prevents phantom reads" as a single fact you can compare across engines; ask which reads (plain vs. locking) and by what mechanism (visibility vs. locks), because the answer to "does this block a concurrent writer" depends entirely on that mechanism, not on the isolation level's name.
• InnoDB's gap locking can be turned off for a session by switching to Read Committed (gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking), which is a real, documented lever for reducing this specific blocking risk on InnoDB if Repeatable Read's phantom protection isn't actually needed for a given write path.