Transactions, Concurrency Control, and Isolation Levels Questions

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

Multi-version concurrency control (MVCC) works by never overwriting a row in place: every UPDATE writes a brand-new physical version of the row, tags it with the transaction ID that created it, and leaves the old version alone until nothing can possibly still need it. A transaction's "snapshot" is simply the rule it uses to decide, for any given row version, whether that version existed yet and wasn't since superseded, as of the moment the snapshot was taken. Snapshot isolation gives every transaction a single such consistent view for its whole duration; true serializability additionally guarantees the outcome is equivalent to running all transactions one at a time, which snapshot isolation alone does not guarantee, because of write skew.

Structured elaboration

Transaction IDs and snapshots, shown directly. Every row carries a hidden xmin (the ID of the transaction that created this version) and xmax (the ID of the transaction that superseded it, 0 if still current). Run for real against Postgres 16, updating the same row twice:

SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
-- 770 |    774 | (0,7)  | 500.00
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
-- 775 |      0 | (0,14) | 501.00
UPDATE accounts SET balance = balance - 1 WHERE id = 1;
SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
-- 776 |      0 | (0,15) | 500.00

Each UPDATE produced a physically new row (ctid, the row's physical location, changes every time), stamped with a new xmin. Nothing was edited in place; the previous version simply became a dead tuple whose xmax now points at the transaction that replaced it. That dead tuple is not removed on the spot, MVCC keeps it around precisely because some other transaction's still-open snapshot might legitimately need to see it; VACUUM is the process that later scans the table and reclaims a dead tuple's space once no snapshot could possibly need it anymore. A snapshot is then just: "a row version is visible to me if its xmin committed before my snapshot was taken, and its xmax either doesn't exist or belongs to a transaction that hadn't committed yet when my snapshot was taken." That single rule is what makes a REPEATABLE READ transaction see a fixed picture of the database throughout its lifetime without ever taking a read lock: it's just filtering row versions by transaction ID, not blocking anyone.

Snapshot isolation vs. true serializability. Snapshot isolation (what Postgres implements at REPEATABLE READ) guarantees each transaction sees one consistent point-in-time view and detects genuine write-write conflicts (two transactions both trying to write the same row version) as a serialization failure. What it does not guarantee is that the combined outcome of several concurrently committed transactions is equivalent to some order of running them one at a time, because two transactions can each read a row, never write it, and separately write two different rows, and snapshot isolation's conflict detection never looks at reads at all, only at writes to the same row.

Write skew, concretely. Two on-call staff, an invariant that at least one must remain on call. Both transactions read the on-call count under REPEATABLE READ, both see 2 (safe), both then set themselves off-call and commit:

T1: SELECT count(*) WHERE on_call -> 2   T2: SELECT count(*) WHERE on_call -> 2
T1: UPDATE ... SET on_call=false WHERE id=1   COMMIT
                                          T2: UPDATE ... SET on_call=false WHERE id=2   COMMIT

Both commits succeed under REPEATABLE READ (verified: final state has both rows on_call = false), because T1 wrote row 1 and T2 wrote row 2, never the same row, so there is no write-write conflict for snapshot isolation to catch, even though the invariant it was protecting is now violated.

Preventing it in an engine that provides snapshot isolation: use true SERIALIZABLE, which Postgres implements as Serializable Snapshot Isolation (SSI): it adds predicate locks that track which values a transaction's query depended on, not just which rows it wrote, and detects the read/write dependency cycle between T1 and T2 even though they never touched the same row. Re-running the identical scenario under SERIALIZABLE, the second transaction's COMMIT fails with the real error ERROR: could not serialize access due to read/write dependencies among transactions, DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt., and the invariant holds.

Trade-offs & pitfalls

• Postgres's predicate locks (visible in pg_locks as SIReadLock) are non-blocking by design, per the Postgres documentation: they never cause another transaction to wait, they only get checked at commit time, which is why SSI can catch write skew without turning reads into locking reads the way some other engines' Serializable implementations do.
• A transaction that fails with a serialization error hasn't corrupted anything; it simply never committed. The application is expected to catch that specific error and retry the whole transaction, which is the price of getting true serializability instead of merely snapshot isolation.

Direct answer

ACID is the contract a database makes about what a transaction guarantees: Atomicity (all of a transaction's writes happen or none do), Consistency (a transaction can only move the database from one state that satisfies its declared rules to another such state), Isolation (concurrent transactions don't see each other's half-finished work), and Durability (once committed, a write survives a crash). Each one protects against a distinct, concrete way an application can corrupt its own data.

Structured elaboration

Worked example

The atomicity and consistency rows above are not hypothetical: both ERROR messages were produced by running the exact statements shown against a live Postgres 16 instance, not paraphrased from documentation. That distinction matters for an interview answer: a candidate who can show the actual constraint-violation error text is demonstrating they've watched the engine enforce the rule, not just recited the definition.

Trade-offs & pitfalls

• Full ACID is not free, and some systems deliberately relax it for specific data paths. The common shorthand for the alternative is BASE (Basically Available, Soft state, Eventually consistent): an online feature store (a database holding the input values, called features, that a machine-learning model reads to make a live prediction) serving a live ML prediction path, for instance, will often keep strict consistency for feature values that directly decide a yes/no business outcome (for example, a fraud-risk value that blocks a payment) but relax it (accept a few seconds of staleness) for aggregate features, values computed by summarizing many past events, like a rolling seven-day average, that only nudge a ranking score (the number a model uses to order results, rather than to gate them outright), because blocking the prediction path on a strict read would cost more in latency than the staleness costs in accuracy. Similarly, an SRE running a mixed online transaction processing (OLTP, the day-to-day inserts and updates that keep an application running) plus analytics pipeline might intentionally let the analytics side read slightly stale or eventually-consistent data (via a replica) while keeping the OLTP writes themselves fully ACID, and catch the gap operationally: an alerting rule on replication lag, a periodic reconciliation job, or a checksum comparison between primary and replica, rather than relying on the database to prevent the staleness at all.
• Don't confuse "the database enforces consistency" with "the database enforces business consistency." The C in ACID only enforces the rules you actually declared (constraints); an invariant that only lives in application code, never expressed as a CHECK or UNIQUE constraint, gets no protection from the database at all, however carefully atomicity and isolation are otherwise honored.

Direct answer

For a high-throughput analytical workload that's read-only or nearly so, snapshot isolation (Postgres's Repeatable Read) is almost always the right default: it gives every query a consistent view at essentially the cost of an ordinary read, with no locking and no risk of the transaction itself being aborted. Reserve Serializable for the specific write paths that must actually prevent write skew (an invariant spanning more than one row); imposing it on read-only analytical queries buys a guarantee they don't need at a real, measurable cost.

Structured elaboration

Multiversion concurrency control (MVCC) snapshot management under Repeatable Read. A snapshot is not a data copy; it's a fixed rule for filtering row versions by transaction ID (visible in the xmin/xmax columns of any row: xmin is the id of the transaction that created that row version, xmax is the id of the transaction that superseded it, if any). Taking a snapshot at the start of a long analytical transaction costs essentially nothing extra per query: every read simply filters against the same fixed snapshot it already has. The price is paid elsewhere, on storage: any row version created after the snapshot but needed to satisfy it (because it's still visible to that old snapshot) cannot be vacuumed away until the snapshot's transaction ends, so a long-running analytical transaction under Repeatable Read can hold back cleanup of dead tuples that would otherwise be reclaimed, contributing to table bloat under concurrent writes elsewhere in the system.

Why Serializable costs more, concretely. True Serializable (Postgres's Serializable Snapshot Isolation) adds predicate locks on top of the same MVCC snapshot: as a transaction reads, the engine records what values it depended on (visible as SIReadLock entries in pg_locks), so it can detect at commit time whether another transaction's write invalidated that dependency. This bookkeeping has real memory cost, controlled by max_pred_locks_per_transaction (Postgres's own default is 64 objects per transaction, and the documentation says explicitly that this "might need to raise this value if you have clients that touch many different tables in a single serializable transaction"), meaning a wide analytical query touching many tables can exhaust the default predicate-lock budget before it even runs into a real conflict. Worse for a high-throughput workload: unlike Repeatable Read, a Serializable transaction can be aborted at commit time for a conflict it had no way to see coming. That's not hypothetical, it was reproduced directly: two transactions each read a shared invariant, each write a different row, and the second one to commit fails with ERROR: could not serialize access due to read/write dependencies among transactions, even though its own query was perfectly correct in isolation. At high throughput, that failure rate scales with contention, and every failure means a wasted execution plus a mandatory application-level retry, work that Repeatable Read never has to redo.

Worked example: what Repeatable Read never pays and Serializable sometimes does

Running the identical write-skew scenario (two transactions, shared invariant, disjoint writes) twice against Postgres 16, once under each level:

• Repeatable Read: both transactions commit successfully, zero retries, zero aborts, at the cost of the invariant silently breaking (this is the correctness gap; it's a real cost of choosing snapshot isolation, not a free lunch).
• Serializable: the second transaction's COMMIT is rejected outright with the reproduced error above; the invariant holds, but the application now has one failed execution to retry.

For a purely read-only analytical workload with no write-skew-sensitive invariant at all (the common case for reporting and analytics), Serializable's entire cost, predicate-lock bookkeeping and possible aborts, buys nothing, because there's no write for it to protect against. That's the core of the recommendation: match the level to what's actually being protected, not to "the strongest available option."

Trade-offs & pitfalls

• Recommendation, stated plainly: default read-only analytical transactions to Repeatable Read. Escalate specific, identified write paths (not the whole analytical workload) to Serializable only where a cross-row invariant genuinely needs protecting, and build the retry-on-serialization-failure path into those specific transactions, not everywhere.
• When it's reasonable to relax further, to plain Read Committed: only if the analytical query is a single statement that doesn't need to agree with any other statement in the same transaction, since Read Committed already gives per-statement consistency; the moment a report runs more than one query against related data, that guarantee stops being enough, as a two-session phantom-read test shows directly.
• Don't conflate "isolation level" with "performance knob" in the abstract; the actual cost differences above are structural (locking vs. non-locking, abort-and-retry vs. never-aborts), not a vague sense that "stronger is slower."

Direct answer

Pessimistic concurrency control (PCC, acquiring a lock before touching data, so a conflicting transaction blocks until the lock is released) fits the hourly batch ETL (extract-transform-load, the scheduled job that pulls raw data and writes it into reporting tables) job; optimistic concurrency control (OCC, proceeding without a lock and checking for conflicts only at commit time, retrying if one is found) fits the interactive UI edits. The deciding factor is not "which is generally better" but two concrete properties of each workload: how expensive a wasted attempt is, and how long the "think time" is between reading data and writing it back. ETL writes are large and cheap to serialize with a short, coarse lock; a lock held across a human's think time in a UI would stall every other user touching that record for however long that person takes to click save.

Path A: hourly batch ETL into shared reporting tables

Recommendation: pessimistic, and prefer avoiding row-level locking altogether via a staging-table swap. The best version of this is not "add SELECT ... FOR UPDATE to millions of rows," it is to load into a separate staging table with no concurrent readers, then atomically swap it into place (partition attach/detach or a table rename) so the production table is never locked row-by-row at all; where the load genuinely must touch a live table in place, take a single coarse table- or partition-level lock for the short duration of the load, which is safe because the job's own writes do not conflict with each other (there is only one writer) and any reader conflict is best resolved by making readers wait briefly rather than by racing thousands of individually-versioned optimistic writes.

• Locking: a single short-lived exclusive lock on the target table or partition, held for the load's duration and released immediately after; not per-row locks.
• Throughput: this maximizes throughput for the writer, because bulk-loading primitives (COPY in PostgreSQL, or equivalent bulk-insert paths in other engines) bypass the per-row overhead that either locking or optimistic version-checking would add across millions of rows.
• Conflict resolution: conflicts are avoided by scheduling (one ETL run at a time) rather than resolved after the fact; if a conflict does happen (an overlapping run), fail fast and alert rather than attempting a partial merge, since re-running a bulk load from scratch is usually cheaper and safer than reconciling a half-applied one.

Optimistic concurrency control is a poor fit here because the cost of a wasted attempt is enormous: re-validating and retrying a version check on millions of rows after a conflict is far more expensive than the short exclusive lock it was trying to avoid, and ETL writes are not interleaved with unpredictable human timing, so there is little for optimism to buy.

Path B: interactive UI edits to shared metadata

Recommendation: optimistic, via a version column. Edits here are small, infrequent per record, and separated by unpredictable human think time between when a user loads a record and when they click save; holding a database lock across that gap would block every other editor of that record for as long as the first user takes, which can be minutes, not milliseconds.

• Locking: no held locks. Real, executed evidence of the two options for comparison: taking a pessimistic row lock (SELECT ... FOR UPDATE) in one session and having a second session request the same lock made the second session block and wait, with no error, until the first session committed; that is the correct pessimistic behavior, but it is exactly the behavior you do not want stretched across a user's think time.
• Throughput: high, because reads and the vast majority of writes (the non-conflicting ones) never wait on anything.
• Conflict resolution: an optimistic version check. Run for real against PostgreSQL: UPDATE metadata SET value = 'new', version = version + 1 WHERE id = 1 AND version = 0 matched the row and succeeded for the first writer; the identical statement from a second session, still holding the stale version = 0 it read earlier, matched zero rows once the first commit had already bumped the version to 1. That zero-row result is the conflict signal: the application detects it, reloads the current value, and shows the user a friendly "this was changed by someone else, here's the new value, please reapply your edit" prompt rather than silently overwriting or silently blocking.

Trade-offs summary

Pitfalls

The most common wrong turn is applying one concurrency model uniformly across a whole system instead of per write-path: teams that default to row-level pessimistic locking everywhere end up holding locks across network round trips and user interaction time on the UI path, and teams that default to optimistic everywhere end up burning enormous retry cost re-processing bulk writes on the ETL path. The second pitfall specific to OCC on the UI path is designing the conflict UI as a silent overwrite of whichever write lands last (turning the version check into decoration instead of protection) rather than surfacing the conflict to the user, which quietly reintroduces the lost-update anomaly the version column existed to prevent.