193: Isolation Levels, MVCC, Locks, Lost Updates, Write Skew, and Deadlocks

Learning outcomes

By the end of this lesson, you can:

explain and apply mvcc in a realistic implementation;
explain and apply read committed in a realistic implementation;
explain and apply repeatable read in a realistic implementation;
explain and apply serializable in a realistic implementation;
explain and apply explicit row locks in a realistic implementation.

These outcomes are deliberately practical. You should be able to look at a concurrent workflow, state the invariant it must protect, choose an isolation or locking strategy, and explain what the application should do when PostgreSQL rejects or delays a transaction.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before reading, retrieve one concrete example from a previous project where concurrent work could have changed the result: two users editing the same record, two requests consuming the same inventory, or two workers claiming the same job are all useful examples. The aim is not to memorize terminology. It is to make a defensible decision inside a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter.

Terminology

MVCC: PostgreSQL uses multi-version concurrency control so readers can often see a consistent snapshot without blocking writers. A row can have multiple visible versions over time; the snapshot determines which version a statement or transaction can read.
Read Committed: Each statement sees a snapshot appropriate to PostgreSQL Read Committed semantics. A later statement in the same transaction may see commits that happened after the earlier statement began.
Repeatable Read: A transaction uses a stable snapshot, preventing many read anomalies, but concurrent conflicting changes can still produce serialization-related failures depending on the pattern. A stable snapshot does not mean every write can succeed.
Serializable: Serializable aims to make committed transactions equivalent to some serial order and may abort transactions that cannot be safely serialized. The application must treat a serialization failure as an expected retryable outcome, not as proof that the database is broken.
Explicit row locks: SELECT ... FOR UPDATE and related lock modes request a lock on specific existing rows. They coordinate transactions that must reserve or modify those rows, but they do not automatically protect rows that do not exist or arbitrary predicates.
Optimistic concurrency: A version/timestamp predicate can reject stale updates without holding locks across user think time. The caller must surface or reconcile the conflict rather than silently overwriting a newer change.

Three distinctions prevent many concurrency bugs. MVCC describes how PostgreSQL presents row versions to readers; an isolation level describes the visibility and conflict rules for a transaction; and an explicit lock is a deliberate coordination mechanism for particular rows. None of these removes the need to state the business invariant first.

Mental model

Treat Isolation Levels, MVCC, Locks, Lost Updates, Write Skew, and Deadlocks as a design problem with observable inputs, outputs, invariants, and failure modes. Concurrent correctness is not established by adding BEGIN around a few statements. You need to understand which anomalies an isolation level permits, which rows or predicates participate in the invariant, and whether the application can retry or report a conflict.

For now, keep the MVCC model simple: a reader uses a snapshot, and an update creates a newer row version rather than changing what an already-started reader has observed in place. PostgreSQL's implementation has additional details around transaction IDs, vacuum, and locking, so this model is useful for reasoning but is not a complete storage-engine specification. Old row versions remain until vacuum can reclaim them; long-running transactions can therefore keep old versions relevant and increase cleanup pressure.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement directly to a library call or an ORM option. First state what must remain true. Then identify the rows and predicates involved, choose the mechanism that enforces the rule, and define what happens when two transactions compete.

Deep dive

1. MVCC

The problem MVCC addresses is that ordinary reads should not have to wait every time another transaction writes. PostgreSQL uses multi-version concurrency control so readers can often see a consistent snapshot without blocking writers. When a row changes, the database can retain the prior version long enough for transactions whose snapshots still need it. Old row versions remain until vacuum can reclaim them.

This is why a reader can continue working with a coherent view while a writer is changing the current version. It does not mean that every reader sees the latest committed value, and it does not mean writes never wait. Statements can still contend for row locks, and long-running transactions can delay cleanup or keep old versions visible.

Decision rule: Use mvcc deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. When debugging an unexpected value, inspect the transaction boundaries and statement timing instead of assuming that a query always reads the most recent value.

2. Read Committed

Read Committed is PostgreSQL's default isolation level. Each statement sees a snapshot appropriate to PostgreSQL Read Committed semantics. A later statement in the same transaction may observe newly committed data, so “the transaction read it” is not necessarily the same as “every statement in the transaction sees the same value.”

That behavior is often a good default for short request transactions. It becomes dangerous when code reads a value, makes a decision, and later writes on the assumption that the earlier observation remains true. Two transactions can both read an available balance or an unclaimed item before either one has committed. The resulting lost update or double claim is a correctness problem, not merely a timing oddity.

Decision rule: Use read committed deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Protect a read-then-write invariant with an atomic conditional statement, an appropriate row lock, a uniqueness or check constraint, optimistic versioning, or a stronger isolation level as appropriate.

3. Repeatable Read

A Repeatable Read transaction uses a stable snapshot, preventing many read anomalies: repeated reads of the same data do not drift because another transaction commits between the reads. This is useful when a multi-step calculation needs a consistent view of the rows it reads.

The stable snapshot is not a promise that the transaction will always commit. Concurrent conflicting changes can still produce serialization-related failures depending on the pattern. Also, a stable snapshot by itself does not turn a rule involving absent rows or multiple independently updated rows into a safe reservation protocol. A transaction can consistently observe a condition and still conflict with another transaction that observed the same condition.

Decision rule: Use repeatable read deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Test the concurrent write path and make the caller ready to retry or return a conflict when PostgreSQL cannot reconcile the writes.

4. Serializable

Serializable aims to make committed transactions equivalent to some serial order. In practical terms, PostgreSQL tracks dangerous interactions and may abort transactions that cannot be safely serialized. The database is preserving the correctness guarantee by refusing an unsafe result; the application must be prepared to retry the complete transaction with bounded backoff, or return an appropriate conflict when retrying is not safe.

Do not treat “serializable” as “the transaction runs alone.” Other transactions can run concurrently, and the cost is expressed through aborts, waits, and additional database work. A retry must start a fresh transaction and repeat the reads and writes that form the unit of work. External side effects should not be repeated blindly inside that retry loop.

Decision rule: Use serializable deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Monitor serialization failures and verify that the workload can tolerate retries; otherwise, a targeted atomic statement or explicit lock may be easier to operate.

5. Explicit row locks

SELECT ... FOR UPDATE and related lock modes coordinate writers when a transaction must reserve or modify specific existing rows. A transaction that obtains a FOR UPDATE lock holds it until the transaction ends, so another transaction attempting a conflicting operation on that row may wait or fail according to the command and timeout configuration.

The lock must cover the row whose state controls the decision, and the transaction must remain open until the protected update is complete. Locking rows in a consistent order reduces deadlock risk when a workflow touches more than one row. A row lock is not a general lock on a table or a predicate: it does not by itself protect a missing row, a future insert, or every row matching a business rule.

Decision rule: Use explicit row locks deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Keep the critical section short, lock only what is needed, and observe wait time because correct locking can still become a throughput problem.

6. Optimistic concurrency

Optimistic concurrency assumes conflicts are uncommon and checks for a stale write at the point of update. A version or timestamp predicate can reject an update when the row has changed since it was read, without holding locks across user think time. The update should report whether it changed exactly one row; zero affected rows means the caller's representation is stale or the row is absent and must be distinguished according to the API contract.

The caller must surface or reconcile the conflict rather than silently overwriting the newer change. This approach is different from pessimistic locking: it allows other work to proceed while the user is editing, but makes conflict handling part of the user-visible workflow.

Decision rule: Use optimistic concurrency deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. It is usually a poor substitute for a transaction when the invariant spans several rows unless the entire conflict detection strategy covers those rows.

Worked example

Consider a transfer between two accounts in a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. The invariant is that the debit and credit belong to one completed transfer, and the source account must not be allowed to go below its permitted balance. Start by writing that requirement in one sentence, list the input and output contracts, and identify which concept owns each failure mode.

The important move is separation: parsing or validation belongs at the boundary; domain rules belong in the domain/service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Mixing these concerns makes a happy-path demo look shorter but makes edge cases much harder to reason about. Client-side validation is useful for feedback, but it cannot establish an authorization or concurrency guarantee because the client can be modified and requests can race.

The following example locks the source account before changing either balance:

BEGIN;

SELECT balance
FROM accounts
WHERE id = $1
FOR UPDATE;

UPDATE accounts SET balance = balance - $2 WHERE id = $1;
UPDATE accounts SET balance = balance + $2 WHERE id = $3;

COMMIT;

The snippet demonstrates the lock, transaction boundary, and paired updates. A production implementation still needs to validate the amount, verify that the source row exists, enforce the balance rule, authorize the transfer, and decide what happens when the destination is missing or the source and destination IDs are equal. It should also consider locking both accounts in a deterministic order when both rows need protection. Otherwise, one transfer can lock account A and wait for B while another locks B and waits for A.

Walk the example with at least four cases: the normal path, an empty or missing value, a duplicate/retry/concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. For a concurrent transfer, ask whether the second transaction waits, receives a serialization or lock error, or performs an atomic conditional update. For a duplicate request, define an idempotency or uniqueness strategy rather than assuming that replaying the transaction is harmless. This is the level of explanation expected in a senior code review or technical interview.

Two common anomalies make the distinction concrete. A lost update occurs when two transactions read the same old value and later write results based on that value, causing one completed write to erase the other's contribution. An atomic expression such as SET balance = balance - $2 can avoid that particular read-modify-write race, provided the predicate and business rule are part of the same statement. A lock or optimistic version check can also make the conflict explicit.

Write skew is different: two transactions read multiple rows, each sees a valid overall condition, and each updates a different row. Neither update necessarily overwrites the other, yet together they can violate an invariant such as “at least one on-call clinician remains.” A row lock on only the row each transaction changes may not protect the predicate. Use a schema constraint where possible, lock a stable coordination row or all relevant rows, or use serializable isolation with a retry policy.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence.

For concurrency, measure lock wait time, transaction duration, deadlock events, serialization failures, retry counts, and the rate of optimistic conflicts. A transaction that is logically correct but held open while a remote service responds can turn a small amount of contention into a queue of waiting requests. Keep the transaction as short as correctness allows, and do not hold it open while waiting for user input or a slow remote provider.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and the network input is untrusted. A database transaction does not make an email, HTTP call, or queue publish atomic with the database; use an explicit integration strategy when those effects must be coordinated.

Guided lab

Reproduce a lost-update scenario with two SQL sessions. Have both sessions read the same value, delay one of the writes, and observe how a read-modify-write sequence can discard an update. Fix it with an atomic update, then with row locking, then with optimistic versioning. Compare the behavior of the sessions: which one waits, which one affects zero rows, and which one must retry?

Create a deadlock intentionally by having two transactions lock the same two rows in opposite order. Document the retry rule: keep the transaction body retryable, detect the database's deadlock or serialization error, roll back the failed transaction, and retry the whole unit of work only when repeating its external effects is safe. Then change both transactions to acquire locks in the same order and confirm that the deadlock is removed, while still checking for ordinary lock waits and timeouts.

Use the lab to compare Read Committed, Repeatable Read, and Serializable. Record what each session can see, when a statement takes its snapshot, whether a write waits or aborts, and what the application receives. Do not infer the result from the isolation-level name; verify it with two sessions, transaction logs, or database inspection.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Edge cases and failure modes

MVCC: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include a long-running transaction and inspect whether old row versions or vacuum behavior become relevant.
Read Committed: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify whether separate statements can observe different committed states.
Repeatable Read: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include a conflicting write and verify that the caller handles a possible serialization-related failure.
Serializable: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Exercise retry exhaustion, backoff, and the rule that the complete transaction is retried rather than only its final statement.
Explicit row locks: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include missing rows, lock waits, lock timeouts, and two transactions that touch multiple rows in different orders.

Also test lost updates, write skew, duplicate delivery, stale optimistic versions, and retries after a client timeout. A client timeout does not prove that the database transaction did not commit, so retry behavior needs an idempotency or reconciliation rule.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Treating BEGIN as a complete concurrency strategy without identifying the invariant and the rows or predicates it covers.
Assuming Read Committed gives one stable snapshot for the whole transaction, or assuming Repeatable Read guarantees that every concurrent write will commit.
Locking rows in inconsistent orders, which creates avoidable deadlocks.
Using a row lock to protect a predicate or a missing row without a coordination row, constraint, or stronger strategy.
Retrying only one statement after a serialization or deadlock failure, or repeating an external side effect without an idempotency design.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case with two database sessions. Record the statement order, transaction start and commit times, isolation level, affected row counts, and the exact database error. Inspect the actual value, lock wait, query plan, or transaction log rather than guessing. Trace the boundary where the invariant first becomes false, then fix the owning layer rather than adding a downstream patch. If a query appears stuck, distinguish CPU or I/O work from lock waiting before changing the SQL.

Interview questions

What problem does MVCC solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Read Committed solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Repeatable Read solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Serializable solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Explicit row locks solve, and what trade-off or failure mode would make you choose a different approach?

When answering, distinguish a visibility guarantee from a coordination guarantee. Explain how you would handle a lost update, why write skew can survive separate row updates, and how consistent lock ordering changes deadlock risk. A strong answer includes the failure the caller sees and the verification you would perform in PostgreSQL.

Checkpoint

Without notes, explain Isolation Levels, MVCC, Locks, Lost Updates, Write Skew, and Deadlocks to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Include the transaction's retry or conflict behavior, not just its successful SQL path.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.