Transactions, Concurrency Control, and Isolation Levels Questions

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.

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.

Direct answer

A distributed deadlock happens when a cycle forms in the global wait-for graph (the graph where an edge from transaction A to transaction B means "A is blocked waiting for a lock B holds") but the cycle spans more than one shard's local lock manager, so no single node ever sees the whole cycle to catch it the way a single-instance database would. In practice, combine strict cross-shard resource ordering as the default prevention, since it eliminates the cycle before it can form, with a lightweight timeout as a backstop for the failure modes ordering cannot cover, and reserve real graph-based detection for systems where the resource set a transaction will touch is not knowable upfront.

How it arises

Picture a two-shard system where transaction A needs to update a row on Shard 1 and then a row on Shard 2, while transaction B needs the same two rows in the opposite order:

sequenceDiagram
    participant T1 as Txn A
    participant SA as Shard 1 (row R1)
    participant SB as Shard 2 (row R2)
    participant T2 as Txn B

    T1->>SA: lock R1 (granted)
    T2->>SB: lock R2 (granted)
    T1->>SB: request R2 (blocks, waiting on Txn B)
    T2->>SA: request R1 (blocks, waiting on Txn A)
    Note over SA,SB: wait-for cycle: A waits on B, B waits on A

Shard 1's local lock manager only knows "Txn B is waiting for R1"; Shard 2's only knows "Txn A is waiting for R2." Each shard is individually correct and non-deadlocked from its own point of view; the deadlock only exists as the union of both shards' local wait-for edges, which is exactly why a single-instance database's local deadlock detector (which every shard still runs, and which handles purely local deadlocks fine) cannot see this one.

Detecting it

• Timeout-based: give every lock acquisition, or the transaction as a whole, a deadline; the first waiter to exceed it aborts and releases its locks, breaking the cycle by construction. Simple to build and requires no cross-shard coordination, but it cannot distinguish "genuinely deadlocked" from "just a slow but healthy transaction," so it produces false aborts, and the timeout value has to be tuned against your own p99 transaction latency (the latency below which 99% of your transactions finish; the slowest 1% sit above it), not guessed.
• Global wait-for-graph detection: periodically collect each shard's local wait-for edges into one place (a coordinator, or a gossip round between shards) and run ordinary cycle detection over the combined graph. This is precise (no false aborts from a slow-but-fine transaction) but costs coordination overhead and detection latency: the cycle exists for however long it takes the next collection round to notice it.
• Probe-based detection (Chandy-Misra-Haas): a decentralized alternative to building a full global graph. A blocked transaction sends a small probe message, a triplet recording who initiated the search and which edge it is currently traveling, along its own outgoing wait-for edge; whichever transaction receives it forwards the same probe along its own outgoing edges if it is also blocked. If a probe a transaction initiated ever comes back to that same transaction, a cycle exists. This needs no central coordinator and no shard has to build the whole graph, at the cost of message overhead proportional to the wait-for chain's length.

Resolving it once detected

Pick a victim and abort it: standard practice is to prefer the transaction that is cheapest to redo (fewest locks held, least work done, or simply the youngest transaction, so older transactions are not repeatedly punished for having started earlier) rather than an arbitrary one, and release all of that victim's locks across every shard it touched so the remaining transaction(s) in the cycle can proceed. The aborted transaction retries from the start; the victim-selection rule needs to be consistent enough that the same transaction is not chosen every time under a repeating access pattern, which would starve it indefinitely (livelock) rather than just costing it one retry.

Prevention: strict cross-shard resource ordering

If every multi-shard transaction is required to acquire its locks in one globally agreed order (for example, always by ascending shard ID, then by primary key within a shard), a cycle becomes provably impossible: a cycle in a wait-for graph requires at least one edge that goes "backward" relative to some transaction's acquisition order, and a consistent global order forbids that by construction. This is the cheapest possible fix when it applies, since it needs no runtime detection machinery at all, but it requires knowing the full resource set a transaction will touch before it starts acquiring locks, which breaks down for transactions whose later lock requests depend on data they read earlier (a conditional access pattern), since you cannot order-sort a resource you do not know you need yet.

Worked example: a minimal wait-for-graph cycle detector

The same depth-first-search cycle check that a global-detection approach would run centrally, applied to the exact two-transaction scenario above:

A quick key to the coloring scheme the code below leans on without spelling out: WHITE means a node has not been visited yet, GRAY means the depth-first search is currently inside that node, partway through exploring its neighbors, so the node is on the search's active path, and BLACK means the search has completely finished with that node and everything reachable from it. The cycle check is the elif color.get(v) == GRAY branch: while the search is inside node u (which is why u is GRAY), if one of u's neighbors v is also GRAY, v must be an ancestor of u on that same active path, so the edge u -> v loops back to a node the search has not finished with yet, which is exactly what a cycle looks like. A BLACK neighbor, by contrast, is safe: the search already finished exploring it on a different branch entirely, so revisiting it from here cannot create a cycle back to the current path.

def find_cycle(wait_for: dict[str, list[str]]) -> list[str] | None:
    WHITE, GRAY, BLACK = 0, 1, 2
    color = {n: WHITE for n in wait_for}
    parent = {}

    def dfs(u):
        color[u] = GRAY
        for v in wait_for.get(u, []):
            if color.get(v, WHITE) == WHITE:
                parent[v] = u
                cyc = dfs(v)
                if cyc:
                    return cyc
            elif color.get(v) == GRAY:
                cycle, cur = [v], u
                while cur != v:
                    cycle.append(cur)
                    cur = parent[cur]
                cycle.append(v)
                cycle.reverse()
                return cycle
        color[u] = BLACK
        return None

    for node in wait_for:
        if color[node] == WHITE:
            cyc = dfs(node)
            if cyc:
                return cyc
    return None

wait_for_graph = {"T1": ["T2"], "T2": ["T1"]}   # T1 waits on whoever holds R2 (T2); T2 waits on whoever holds R1 (T1)
print(find_cycle(wait_for_graph))

no_cycle_graph = {"T3": ["T4"], "T4": []}       # T4 is running, not blocked on anyone
print(find_cycle(no_cycle_graph))

Running this prints ['T1', 'T2', 'T1'] for the deadlocked graph (the cycle) and None for the non-deadlocked one, exactly as expected: the algorithm is the same graph-cycle check whether it is running locally inside one shard's lock manager or centrally over a collected global graph, only the wait-for edges themselves have to come from more than one shard's local lock manager to build the graph the cross-shard case needs.

Prevention versus detection: the trade-off, and when to lean which way

Prevention (strict ordering) pays a throughput (how many transactions the system can complete per unit time) and flexibility cost: it forces a transaction to lock resources in a fixed global order even when it might not end up needing all of them, and it fundamentally cannot express a transaction whose second lock request depends on the result of its first. Detection-and-recovery pays a different cost: coordination overhead and latency for graph-based detection, or a false-abort rate for timeout-based detection, plus wasted work on every aborted victim.

Lean toward prevention when the transaction's full resource set is known upfront and stable, such as a fixed multi-shard join pattern executed the same way every time (a scheduled cross-shard reconciliation job, for instance): the ordering cost is paid once in the design and never shows up as a runtime surprise. Lean toward detection when transactions are interactive or conditional, where the next lock needed depends on data just read, and where deadlocks are rare enough in practice that occasional abort-and-retry costs less than the throughput ceiling a blanket ordering rule would impose on every transaction, deadlocked or not.

Trade-offs and pitfalls

Timeout-based detection in an autoscaled cloud environment is a specific trap: a transient network blip or a garbage-collection pause can make a perfectly healthy transaction look "stuck" past its timeout, triggering a false abort exactly when the system is already under stress, which is the worst possible moment for extra retries. A second pitfall is conflating this with the purely local deadlock detection every shard's own database engine already runs (a two-transaction deadlock entirely within one shard, involving no cross-shard wait at all, is caught by that shard's own built-in detector, not by any of the cross-shard machinery described here); building a whole cross-shard detection system to catch deadlocks that were already local is solving a problem that did not exist. The third is non-deterministic victim selection: if the tie-break for which transaction to abort is effectively random, a transaction that happens to always be on the losing side of the same recurring access pattern can be aborted repeatedly and never make progress, which needs an explicit fairness rule (age-based preference is the common one) to rule out.

Direct answer

Diagnose from the engine's own deadlock report first (it names the exact transactions, locks, and queries involved, so there's no guessing), stop the immediate incident with a fast, low-risk mitigation that doesn't require a code deploy, reproduce the exact interleaving in staging to confirm the real cause before changing anything permanent, and then fix the root cause: almost always inconsistent lock acquisition order across the two jobs, sometimes compounded by transactions that hold locks longer than they need to.

Structured elaboration

1. Diagnose from real evidence, not guesswork. Postgres logs every deadlock to the server log with the exact detail needed to reconstruct it. Reproduced for real: two sessions each update accounts rows 1 and 2 in opposite order (T1: row 1 then row 2; T2: row 2 then row 1). The second conflicting statement from whichever session loses returns:

ERROR:  deadlock detected
DETAIL:  Process 69 waits for ShareLock on transaction 772; blocked by process 70.
Process 70 waits for ShareLock on transaction 771; blocked by process 69.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,8) in relation "accounts"

That DETAIL line is the diagnosis: two processes, each waiting on the other's transaction, over the same relation. In production, the server log (with log_lock_waits enabled) captures this automatically for every real deadlock, including the actual queries each side was running, which table and tuple, and how long each side had already been waiting, exactly the information needed to identify which two jobs collide and in what order they touch the shared tables.

2. Reproduce in staging before changing anything. Take the two real query patterns from the log and run them as two literal interleaved sessions against a staging copy with the same schema and comparable data volume, in the same order the log shows. If the deadlock reproduces on the first or second attempt (as it does with a genuinely opposite lock order, deadlocks like this aren't rare timing flukes, they're near-certain once two jobs regularly touch the same two tables in reverse order under load), that confirms the root cause before you touch production code.

3. Short-term mitigation to stop the incident. Nothing here requires a schema change or a deploy: (a) if the two colliding nightly jobs can be sequenced instead of run in parallel, even temporarily, do that first, it removes the concurrent access entirely; (b) make sure the application already retries on Postgres's deadlock SQLSTATE (40P01), since one side of a deadlock is always safely rolled back by the engine and is a normal candidate for a retry, not a data-loss event; if it doesn't retry yet, that's the fastest real fix to ship.

4. Long-term fix. The two most durable options: (a) transaction redesign for consistent lock order, have every job that touches both tables acquire them in the same order (for example, always sort the rows being updated by primary key before issuing the updates), which makes the specific cycle structurally impossible regardless of timing; (b) shrink the transaction's lock-holding window, batch the nightly job into smaller transactions so each one holds its locks for less time, reducing the odds two jobs overlap on the same rows at all. Indexing matters here too, but indirectly: a missing index can force a wider scan (and therefore more rows locked, for longer) than the logical update actually needs, so confirming the query plans for both jobs aren't taking broader locks than necessary is worth checking alongside the lock-order fix, not instead of it.

Trade-offs & pitfalls

• Postgres's automatic deadlock resolution (abort one transaction, let the other proceed) is not a bug to eliminate; it's the correct behavior. The actual production problem is an increasing frequency, which means treat the deadlock detector's rollback as safe and expected, and focus effort on lock order and transaction scope, not on trying to prevent every deadlock from ever occurring.
• Sequencing the two jobs (short-term) trades throughput (how many jobs the system can get through per unit time) for safety; it buys time, it is not the long-term fix, because it doesn't scale and reintroduces risk the moment a third job starts touching the same tables.

Direct answer

Each of these five anomalies is a concrete, demonstrable way two concurrent transactions can produce a result that could not happen if they ran one after another, and each has a specific weakest standard SQL isolation level that rules it out: READ COMMITTED stops dirty reads, REPEATABLE READ stops non-repeatable reads, and SERIALIZABLE is the only standard level that stops phantom reads and write skew. Lost update sits slightly outside the official standard table (more on that below) but every mainstream engine treats REPEATABLE READ as the weakest level that blocks it. All five demonstrations below were run against a live database, not written from memory; the dirty-read one specifically needed MySQL's InnoDB engine rather than PostgreSQL, because PostgreSQL does not actually implement a literal dirty-read mode (explained in its section).

Dirty read: weakest level that prevents it is READ COMMITTED

A transaction reads a row that another transaction has modified but not yet committed. If the writer then rolls back, the reader saw a value that never really existed in the database.

PostgreSQL cannot demonstrate this: even when you explicitly request READ UNCOMMITTED, PostgreSQL silently runs it as READ COMMITTED, so this had to be run on MySQL 8.4 with InnoDB, which implements READ UNCOMMITTED literally:

-- T1 (MySQL, InnoDB)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- uncommitted, balance now 900

-- T2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- returns 900: T1's uncommitted write

-- T1
ROLLBACK;   -- T1's decrement never actually happened
-- T2 already read and acted on a value that never existed
COMMIT;

Run for real: T2's SELECT printed 900, T1's balance was 1000 again the instant T1 rolled back. Note that if this exact sequence were tried against PostgreSQL, T2 would print 1000, not 900, because PostgreSQL's READ UNCOMMITTED is not literal: it is documented to behave exactly like READ COMMITTED, so it never actually exposes an uncommitted write. That is itself a useful fact to know: "the SQL standard permits an anomaly at this level" and "this specific engine's implementation of that level permits it" are two different claims.

Non-repeatable read: weakest level that prevents it is REPEATABLE READ

A transaction reads the same row twice and gets two different, both-committed values because another transaction's write landed and committed in between.

-- T1 (PostgreSQL, READ COMMITTED)
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- 1000

-- T2
BEGIN; UPDATE accounts SET balance = 900 WHERE id = 1; COMMIT;

-- T1
SELECT balance FROM accounts WHERE id = 1;   -- 900: changed mid-transaction
COMMIT;

Run for real on PostgreSQL 16.14: the two SELECTs in T1 printed 1000 then 900. Re-running the identical interleaving with BEGIN ISOLATION LEVEL REPEATABLE READ instead, T1's second SELECT printed 1000 again: the transaction's whole snapshot stayed fixed as of its first statement, regardless of what T2 committed afterward.

Phantom read: weakest standard level that prevents it is SERIALIZABLE

A transaction re-runs the same filtered query twice and gets a different set of matching rows because another transaction inserted or deleted a matching row in between.

-- T1 (PostgreSQL, READ COMMITTED)
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM orders WHERE customer_id = 42 AND status = 'open';   -- 2

-- T2
BEGIN; INSERT INTO orders (customer_id, status) VALUES (42, 'open'); COMMIT;

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

Run for real: the count went 2 then 3 inside one open transaction. Note a PostgreSQL-specific wrinkle: unlike the standard's table (which still allows phantoms at REPEATABLE READ), PostgreSQL's REPEATABLE READ happens to block this specific phantom too, because it is implemented as a full snapshot rather than per-statement locking; but per the standard itself, SERIALIZABLE is the level actually required to guarantee it.

Lost update: REPEATABLE READ is the weakest level every mainstream engine treats as sufficient

Two transactions read the same row, each computes a new value from what it read, and the second commit silently overwrites the first transaction's change instead of compounding it. Standard SQL's official P1/P2/P3 anomaly table (dirty read, non-repeatable read, phantom read) does not name lost update directly; it was added as anomaly "P4" by Berenson et al.'s 1995 critique of the ANSI levels, and in practice every mainstream engine (including PostgreSQL) blocks it starting at REPEATABLE READ.

-- T1 (PostgreSQL, READ COMMITTED)                 -- T2
BEGIN;                                              BEGIN;
SELECT qty FROM inventory WHERE id = 1;  -- 10      SELECT qty FROM inventory WHERE id = 1;  -- 10
UPDATE inventory SET qty = 8 WHERE id = 1;
COMMIT;
                                                     UPDATE inventory SET qty = 7 WHERE id = 1;
                                                     COMMIT;

Run for real at READ COMMITTED: final qty was 7. T1's decrement of 2 (10 to 8) was silently lost; only T2's decrement of 3 survived, because T2's blind UPDATE re-read the current row at execution time and overwrote it without ever checking whether it still matched what T2 originally saw. Re-running the identical sequence at BEGIN ISOLATION LEVEL REPEATABLE READ instead, T2's UPDATE raised ERROR: could not serialize access due to concurrent update and aborted, exactly the "loud failure instead of silent data loss" you want.

Write skew: SERIALIZABLE is the only standard level that prevents it

Two transactions each read an overlapping set of rows, each makes a change that is individually valid given what it read, but the combined effect of both commits violates an invariant that spans both rows. This is the anomaly REPEATABLE READ (in every engine, not just PostgreSQL) does not catch, because each transaction only conflicts on rows it writes, and here the two transactions write different rows.

-- invariant: at least one doctor must be on call
-- T1 (REPEATABLE READ)                            -- T2 (REPEATABLE READ)
BEGIN;                                              BEGIN;
SELECT oncall FROM on_call WHERE doctor_id IN (1,2);  -- both true (same read, both txns)
UPDATE on_call SET oncall=false WHERE doctor_id=1;
                                                     UPDATE on_call SET oncall=false WHERE doctor_id=2;
COMMIT;                                              COMMIT;

Run for real at REPEATABLE READ: both commits succeeded, and the final state was both doctors off call, an invariant violation neither transaction could see coming from its own read. Re-running the identical sequence at SERIALIZABLE instead, T2's COMMIT raised ERROR: could not serialize access due to read/write dependencies among transactions (PostgreSQL's Serializable Snapshot Isolation, or SSI, detects the dangerous read/write pattern even though the two transactions never touched the same row) and rolled back, leaving exactly one doctor off call.

Trade-offs and pitfalls

The weakest-level answer is a floor, not a recommendation: moving a transaction up to the level that prevents an anomaly also raises its abort rate under contention, so "use SERIALIZABLE everywhere to be safe" trades a silent correctness bug for a retry-storm risk if the application is not built to retry aborted transactions. The dirty-read case is also a good interview trap: an engine's marketing name for a level ("READ UNCOMMITTED") does not guarantee it behaves like the standard's definition; always verify against the specific engine before writing code that depends on an anomaly being present or absent.