Transactions, Concurrency Control, and Isolation Levels Questions

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

Raising isolation level doesn't slow the database down by making individual statements do more work; it slows the database down by turning previously-independent transactions into ones that now wait on each other, or that now sometimes fail outright and have to be retried. Both effects reduce effective write throughput even though the hardware's doing the same amount of underlying work. Diagnosing and fixing it without reverting means narrowing the isolation bump to the specific transactions that actually need it, rather than treating it as a single database-wide setting.

Structured elaboration

Why the regression happens. At Read Committed, two transactions writing to unrelated or lightly-overlapping rows almost never interact; each one takes only the row locks it needs, momentarily, and releases them on commit. Moving to Repeatable Read or Serializable changes that in two distinct ways depending on the engine and level: (a) a transaction's snapshot is now fixed for its whole duration, so if two transactions running concurrently happen to touch the same row, one of them may now be forced into a wait or a conflict it wouldn't have hit under per-statement Read Committed snapshots; (b) at true Serializable specifically, the engine has to track read-write dependencies between transactions (Postgres's predicate locks) and can abort a transaction at commit time for a conflict that was undetectable earlier, meaning some fraction of transactions now do all their work and then get thrown away and have to be retried from scratch, pure wasted throughput. On engines whose Repeatable Read or Serializable is lock-based rather than pure-MVCC (unlike Postgres's snapshot-based Repeatable Read), raising the level can additionally mean writes now take broader locks: a gap lock blocks other transactions from inserting into a range of not-yet-existing rows, and a next-key lock combines that with a lock on the existing row right after the gap (or shared locks on reads under Serializable), and any of these can block other writers outright, not just abort them later.

How to diagnose it, rather than assume. Look for the specific fingerprint of each mechanism: a rising rate of aborted transactions with a serialization-failure SQLSTATE (the five-character error code the database returns with every error, 40001 for a serialization failure) in application logs or pg_stat_database.xact_rollback climbing relative to xact_commit points at (b), retry-driven waste; a rising count of sessions in pg_stat_activity with wait_event_type = 'Lock' points at (a), genuine blocking. Those are different problems with different fixes, so confirm which one is actually happening before changing anything.

Mitigating without simply reverting:

• Scope the isolation bump to the transactions that actually need it, using SET TRANSACTION ISOLATION LEVEL per-transaction rather than a database- or session-wide default. Most of a typical online transaction processing (OLTP) workload doesn't need the stronger guarantee at all; if the change was made broadly "to be safe," narrowing it to the specific write paths with a real cross-row invariant (write-skew risk: two transactions each read a shared condition and write different rows) removes the regression from everything else without giving up the protection where it matters.
• Shrink the transactions that do need the stronger level, so each one holds its snapshot and any locks for less time: split a large multi-statement transaction into the smallest unit that still needs to be atomic, and make sure nothing unrelated (an external API call, an unrelated read) is happening inside the transaction boundary and extending how long it's exposed to conflict.
• Add the indexes the affected queries are missing. A query without a good index scans (and, on lock-based engines, locks) a wider range of rows than the logical operation requires; a targeted index can shrink both the work done and the surface area for conflict.
• Add application-level retry with backoff for the (b) failure mode. If Serializable's abort rate under real contention isn't zero, that's expected; the fix is making sure every write path that can hit it retries automatically and transparently, not treating each abort as an incident.

Trade-offs & pitfalls

• Reverting the isolation change entirely trades away whatever correctness problem it was meant to fix; the mitigations above are worth doing specifically because they let the team keep that protection where it's actually needed while giving it back everywhere else.
• Don't assume the regression is caused by locking without checking; on Postgres specifically, Repeatable Read and Serializable are MVCC-based and non-blocking for reads, so a throughput drop after raising isolation there is more likely retry-driven (mechanism b) than genuinely blocking-driven (mechanism a), which points the investigation at pg_stat_database.xact_rollback before it points at pg_locks.

Direct answer

PostgreSQL's MVCC (multi-version concurrency control) never updates a row in place: every UPDATE (and every DELETE) leaves the old row version on disk, marked dead, while a new version is written elsewhere. That old version has to stick around until no transaction's snapshot could possibly still need it, which is exactly why update- and delete-heavy tables accumulate dead tuples, bloat, over time. Detecting it is a matter of asking the engine directly how much of a table is live versus dead; remediating it means running VACUUM regularly enough that dead space never has the chance to build up in the first place.

Structured elaboration

Why MVCC causes bloat. Every row carries an xmin (creating transaction) and xmax (superseding transaction, if any). An UPDATE on Postgres 16, reproduced for real, physically relocates the row on every write (ctid, its physical address, changes each time) rather than editing bytes in place: a row updated twice moved from (0,7) to (0,14) to (0,15), leaving two dead versions behind. Nothing removes those dead versions automatically at write time, because another transaction's still-open snapshot might legitimately need to see the pre-update version; only a later cleanup pass can safely reclaim them, once no snapshot could possibly need them anymore.

Detecting bloat, with real numbers. A 50,000-row table, freshly created, measures 3320 kB on disk (pg_total_relation_size), reproduced directly against Postgres 16. With autovacuum (Postgres's background process that runs VACUUM automatically, without anyone scheduling it) disabled on that table to observe the raw state, a single bulk UPDATE touching every row (the shape of a routine nightly batch job) grows it to 6568 kB; the pgstattuple extension confirms why directly, reporting dead_tuple_count = 50000 (dead_tuple_percent = 39.04), exactly one dead version per live row, since the old version of every updated row is still sitting on disk next to its replacement. A second bulk update, still with no VACUUM run in between, grows the table further to 9032 kB, but pgstattuple measured right after that second update still reports dead_tuple_count = 50000, not 100,000. That is not a missing count: it is Postgres reclaiming part of the mess as it goes. The first update's transaction had already committed, so its dead row versions became invisible to every later snapshot immediately; when the second update needed room on a page that was now half-full of those dead versions, it opportunistically pruned them in place before writing its own new versions, reusing freed space rather than only ever adding to it. That pruning happens during ordinary reads and writes, with no VACUUM involved at all; it just does not return space to the operating system, which is what VACUUM and VACUUM FULL are for, covered next. On a comparable 20,000-row table, pgstattuple('bloat_demo2') reported dead_tuple_percent = 0 before any update and dead_tuple_percent = 38.25 after a single bulk update touching every row, real, measured numbers, not an estimate.

Remediation, with real behavior demonstrated. Plain VACUUM scans the table and marks dead tuple space reusable by future inserts and updates, without holding an exclusive lock and without shrinking the file on disk: running VACUUM (VERBOSE) on the bloated table reported tuples: 50000 removed, 50000 remain, matching the 50,000 dead tuples pgstattuple had just measured (the second update's dead versions only, since the first update's were already reclaimed in place by the mechanism above), and pg_total_relation_size immediately afterward was unchanged at 9032 kB, confirming that plain VACUUM frees space for the table to reuse internally but does not return it to the operating system. VACUUM FULL, by contrast, rewrites the entire table into a brand-new file with no wasted space (per the Postgres documentation, this "allows unused space to be returned to the operating system"), and running it on the same table dropped the size to 3288 kB, smaller than the original 3320 kB. The cost: VACUUM FULL requires an ACCESS EXCLUSIVE lock on the table for the duration, per the documentation, versus plain VACUUM, which "does not obtain an exclusive lock" and "can operate in parallel with normal reading and writing of the table."

Trade-offs & pitfalls

• autovacuum should be doing the routine VACUUM work automatically; if n_dead_tup is climbing steadily in pg_stat_user_tables and last_autovacuum is stale or null, that's the first thing to check, not a manual VACUUM schedule. A full maintenance plan also weighs autovacuum tuning, VACUUM FULL risk and reindexing.
• VACUUM FULL's exclusive lock makes it an availability decision, not just a maintenance one: it is real, verified, downtime for that table for however long the rewrite takes. It should be an intentional, scheduled operation on a table that's already badly bloated, not a routine cron job.
• A table with a high dead-tuple percentage isn't necessarily "broken"; a moderate, steady-state percentage that autovacuum keeps in check is normal MVCC behavior, not a defect. The problem is specifically when dead tuples accumulate faster than vacuuming reclaims them.

Direct answer

All four of these anomalies can be demonstrated with two ordinary concurrent transactions, and all four have a practical mitigation that does not depend on the database offering a knob you may not control: version-checked (optimistic) writes for lost update, an explicit contended row to force real conflict for write skew, combining multiple reads into one statement for read skew, and either accepting eventual consistency for a range check or app-level idempotency for phantom read. The common thread across a managed cloud database (where SERIALIZABLE might be unavailable, discouraged for cost reasons, or simply not offered as a tunable) is: push the correctness requirement into something the database does guarantee atomically (a single statement, a conditional write, a unique constraint) rather than relying on an isolation-level promise you cannot ask the vendor for.

Lost update

Two transactions read the same row, each computes a new value, and the second commit silently overwrites the first instead of compounding it.

-- T1                                    -- T2
SELECT qty FROM inventory WHERE id=1;    SELECT qty FROM inventory WHERE id=1;   -- both see 10
UPDATE inventory SET qty=8 WHERE id=1;
COMMIT;
                                          UPDATE inventory SET qty=7 WHERE id=1;
                                          COMMIT;                                -- final qty=7, T1's write lost

Run for real against PostgreSQL at READ COMMITTED: final qty was 7, confirming the loss. Mitigation: an optimistic version check, which works identically whether the managed service exposes tunable isolation levels or not, because it relies only on an ordinary conditional UPDATE:

UPDATE inventory SET qty = 8, version = version + 1
WHERE id = 1 AND version = 0;   -- the version the app originally read

Run for real: when a second session ran the equivalent statement with the same stale version = 0 after the first had already committed and bumped it, the UPDATE matched zero rows instead of silently overwriting. Trade-off: this trades silent data loss for a visible, retryable no-op, but under high contention on one row it produces a retry storm; that is the point at which pessimistic locking (SELECT ... FOR UPDATE, if the managed engine supports session-held locks) becomes the better trade, accepting blocking latency instead of wasted retries.

Write skew

Two transactions each read an overlapping set of rows, each write is individually valid given what it read, but the combined effect violates an invariant spanning both rows (here: at least one doctor must be on call).

-- invariant: at least one of doctor 1, doctor 2 must be on call
-- T1                                          -- T2
SELECT oncall FROM on_call WHERE id IN (1,2);  SELECT oncall FROM on_call WHERE id IN (1,2);  -- both see (true,true)
UPDATE on_call SET oncall=false WHERE id=1;
                                                UPDATE on_call SET oncall=false WHERE id=2;
COMMIT;                                        COMMIT;

Run for real at REPEATABLE READ (PostgreSQL's snapshot-isolation level, the level most managed relational services do expose): both commits succeeded, leaving both doctors off call, because neither transaction's write touched a row the other one wrote, so ordinary row-level conflict detection never fires. Mitigation: since SERIALIZABLE may not be available or may be too expensive to run everywhere in a managed environment, materialize the invariant into something the database will enforce mechanically: either force both transactions through a shared contended row (for example, SELECT ... FOR UPDATE on a single on_call_lock row before touching either doctor row, so the second transaction blocks instead of racing), or use a real constraint if the engine has one, such as a check that runs at commit. Trade-off: the shared-lock-row approach serializes every on-call change through one hot row, which is fine for a rarely-changed roster but would be a bottleneck for a high-frequency invariant.

Read skew

A transaction reads two related values as two separate statements, and a concurrent transfer commits between them, so the transaction's own two reads are individually correct but jointly inconsistent.

-- true invariant: balance(A) + balance(B) is always 1000+1000=2000
-- T1 (reader)                              -- T2 (transfer)
SELECT balance FROM accounts_pair WHERE name='A';  -- 1000
                                              UPDATE accounts_pair SET balance=balance-100 WHERE name='A';
                                              UPDATE accounts_pair SET balance=balance+100 WHERE name='B';
                                              COMMIT;
SELECT balance FROM accounts_pair WHERE name='B';  -- 1100

Run for real at READ COMMITTED: T1's own two remembered values, 1000 for A and 1100 for B, sum to 2100, not the true 2000 invariant; each individual read was accurate as of when it ran, but combined they describe a moment that never existed. Mitigation for a managed engine where you cannot force transaction-wide snapshot isolation (every read in the transaction sharing one fixed, consistent view of the database taken at the transaction's start): fold the two reads into one statement, since even READ COMMITTED guarantees a single statement's own snapshot is internally consistent. Run for real: SELECT sum(balance) FROM accounts_pair WHERE name IN ('A','B') issued mid-transfer, before T2 committed, returned exactly 2000, the correct pre-transfer total, from one atomic read. Trade-off: this only works when the two values live in the same table reachable by one query; if they are genuinely in different services or different databases, the honest fallback is accepting a bounded staleness window and documenting it, not pretending a cross-service read is atomic.

Phantom read

A transaction re-runs the same filtered query twice within one transaction and gets a different row count because a match was inserted in between.

-- T1                                          -- T2
SELECT count(*) FROM orders
  WHERE customer_id=42 AND status='open';  -- 2
                                              INSERT INTO orders (customer_id, status) VALUES (42,'open');
                                              COMMIT;
SELECT count(*) FROM orders
  WHERE customer_id=42 AND status='open';  -- 3: a phantom row appeared

Run for real at READ COMMITTED: the count went from 2 to 3 within one open transaction. Mitigation for a managed service without predicate-range locking: either read the count once and cache it for the rest of the transaction instead of re-querying (avoids the anomaly by avoiding the repeated read entirely, which is often all the business logic actually needs), or, if uniqueness matters more than a count (for example, "at most one open order per idempotency key"), enforce it with a unique constraint so a conflicting insert fails loudly at write time instead of silently changing a later read. Trade-off: caching the count sidesteps the anomaly but can go stale if the transaction runs long; a unique constraint is airtight but only applies to a uniqueness invariant, not an arbitrary count or aggregate.

Trade-offs and pitfalls, across all four

The pattern across all four fixes is the same: replace a promise you have to ask the vendor for (an isolation level) with a promise the vendor already always keeps (a single statement's atomicity, or a constraint enforced at write time). The failure mode to watch for is over-applying optimistic concurrency control everywhere out of caution: on a genuinely hot row, OCC's retry cost under contention can exceed what a short-held pessimistic lock would have cost, so the choice should be driven by measured conflict rate, not a blanket policy.

Direct answer

A database transaction is a group of one or more SQL statements that the database treats as a single unit of work: either every statement in the group takes effect, or none of them do. COMMIT tells the engine "this unit succeeded, make it permanent." ROLLBACK tells it "abandon everything this unit has done so far." That boundary is what stops a multi-step operation that fails halfway through from leaving the table in whatever state the failure happened to catch it in.

Structured elaboration

• What opens and closes a transaction. BEGIN (or START TRANSACTION) opens one; every statement after that runs inside it until an explicit COMMIT or ROLLBACK. If you never issue BEGIN, most engines (Postgres, MySQL, SQL Server) run in autocommit mode: each individual statement is its own implicit transaction, committed the instant it succeeds.
• Why the default is dangerous for multi-step work. Autocommit is fine for one UPDATE. It is not fine for a loop that inserts several related rows per input record: without an explicit boundary, the engine will happily commit record 1's rows, then record 2's, and if record 3 violates a constraint it stops there, leaving records 1 and 2 permanently in the table with nothing recorded to say the run didn't finish.
• What COMMIT guarantees (durability). Before the engine returns success from COMMIT, it has written a durable log record for the change (Postgres calls this the write-ahead log, or WAL) and forced that log to disk. Even if the process crashes a millisecond later, crash recovery replays the log and the row is still there.
• What ROLLBACK guarantees (atomicity). Every write the transaction made, however many statements it took, is discarded as if none of them ran. The table looks exactly like it did before BEGIN, and a SELECT from another session never saw the intermediate state.

Worked example

Consider a nightly job that loads new user signups into user_batch_load(id, email, signup_date), where email is NOT NULL. Three input rows arrive, and the third one has a bad record with a missing email (a real upstream data defect, the kind that shows up in production).

Without a transaction boundary (autocommit, one statement at a time, which is what you get if the loader just runs each INSERT on its own):

INSERT INTO user_batch_load VALUES (1, 'a@example.com', '2026-09-01');
INSERT INTO user_batch_load VALUES (2, 'b@example.com', '2026-09-01');
INSERT INTO user_batch_load VALUES (3, NULL,            '2026-09-01');

Run against Postgres 16, the actual output is:

INSERT 0 1
INSERT 0 1
ERROR:  null value in column "email" of relation "user_batch_load" violates not-null constraint
DETAIL:  Failing row contains (3, null, 2026-09-01).

and SELECT * FROM user_batch_load afterward returns rows 1 and 2: the batch is half-loaded, and nothing in the table distinguishes "batch finished successfully with 2 rows" from "batch died partway through."

With an explicit transaction boundary, the same three statements wrapped in BEGIN ... on failure:

BEGIN;
INSERT INTO user_batch_load VALUES (1, 'a@example.com', '2026-09-01');
INSERT INTO user_batch_load VALUES (2, 'b@example.com', '2026-09-01');
INSERT INTO user_batch_load VALUES (3, NULL,            '2026-09-01');
-- engine returns the same NOT NULL error, and the session is now in an
-- aborted-transaction state until you issue ROLLBACK (or just disconnect,
-- which implicitly rolls back)

SELECT * FROM user_batch_load afterward returns 0 rows. Both scenarios were run against the same table on the same data; the only difference is the boundary. Because the failed attempt left the table exactly as it was before the run, the retry (after the upstream data is fixed) can be the identical batch with no risk of double-counting rows 1 and 2 a second time.

Trade-offs & pitfalls

• Scope the boundary to the unit of work, not to each statement or to the whole job. Wrapping every single INSERT in its own transaction defeats the point (autocommit already does that). Wrapping an entire multi-hour load in one transaction holds locks and keeps one long-running snapshot open the whole time, which blocks routine table maintenance and can make failure recovery slower, not safer. Commit in logically meaningful chunks (per input file, per batch of N records).
• ORMs and connection pools sometimes leave autocommit on by default; a developer can assume there's an implicit transaction wrapping a request when there isn't. Check the framework's actual default rather than assuming.
• A transaction only undoes database writes. It does not undo side effects outside the database, an email already sent, a message already published to a queue, so a batch job with external side effects needs its own idempotency strategy on top of the DB transaction, not a substitute for one.