Locks, Deadlocks and Optimistic Concurrency

1. First screen — waiting, deadlocking, and rejecting a stale write are different failures

Two workers transfer inventory between two bins. T1 locks row A with SELECT ... FOR UPDATE and later needs B. T2 locks B and later needs A. If each transaction waits for the row owned by the other, this is no longer ordinary blocking; the wait-for graph contains a cycle that PostgreSQL must resolve by aborting one transaction. In the same system, a reservation carries version=7. A client that read version 7 before a competing update must not silently overwrite a newer decision when it later writes with the stale expected version.

This lesson is not a lock-mode vocabulary exercise. The required artifact is Concurrency Conflict Decision Record v1. Each conflict row records the predicted schedule, lock or version state, real observation, SQLSTATE or affected-row count, prevention/detection/retry decision, and one residual limitation.

Three boundaries apply from the start. A row lock does not prove the surrounding business invariant. Consistent lock ordering removes only the targeted opposite-order pattern, not every possible deadlock. Application-level optimistic concurrency is not PostgreSQL Serializable isolation.

2. Routes — scaffolding changes, the evidence bar does not

Foundation uses an annotated two-session timeline. Repair reduces the case to one diagnosed misconception. Experienced may skip syntax walkthroughs, but prediction, deliberate failure evidence, mitigation, conflict policy, and independent transfer remain mandatory.

Before each schedule, write four statements: which object is contested, what each session currently owns or expects, which request creates a wait/conflict, and what observation would falsify your prediction. Keeping only successful runs is incomplete evidence.

3. Mental model — ownership, wait edges, cycles, and compare-and-swap

Lock ownership means a transaction holds a lock that conflicts with another requested lock. Blocking occurs when T2 waits for T1, producing an edge such as T2 → T1; this is not automatically a deadlock.

A deadlock requires a cycle of incompatible waits. If T1 waits on a resource held by T2 while T2 waits on a resource held by T1, PostgreSQL can detect the cycle and abort one transaction. In this lab the bounded failure evidence is SQLSTATE 40P01.

Consistent lock ordering is prevention: if both transactions acquire the contested objects in the same order, the demonstrated two-row opposite-order cycle cannot form. This is distinct from retry after deadlock detection.

Optimistic compare-and-swap uses a conditional write such as UPDATE ... WHERE id=? AND version=?. A zero affected-row count means the expected version is no longer current for this contract, so the application records a stale-write conflict and chooses a domain policy.

asset://DAT-004/DAT-004-A1 places both paths side by side: lock ownership → wait edge → cycle/no-cycle → victim/progress and expected version → conditional UPDATE → affected rows → conflict decision.

4. Foundation layer — five contracts that must remain exact

A lock wait is not a deadlock

A transaction may wait normally and continue when the owner commits or rolls back. Deadlock requires a cycle. The frozen PG18-CONCURRENCY source bounds this claim to PostgreSQL 18.

SELECT FOR UPDATE does not prove the business invariant

It controls a selected database resource, not every state transition participating in the invariant. Multi-table conditions and external side effects require their own reasoning.

NOWAIT and SKIP LOCKED encode different policies

NOWAIT fails immediately instead of waiting for a conflicting row lock; in this bounded lab the expected PostgreSQL SQLSTATE is 55P03 (lock_not_available). SKIP LOCKED omits locked rows from the result. PG18-SELECT explicitly bounds SKIP LOCKED to specialized queue-like contention rather than a general complete/consistent read.

Detection and prevention are different

PostgreSQL can detect deadlocks and abort a transaction. PG18-SERFAIL identifies 40P01 as deadlock_detected and supports bounded retry reasoning, but the application retry boundary still needs to respect idempotency and business semantics.

Version checking is an application-level contract

This lesson does not claim a PostgreSQL feature named “optimistic locking.” It models a conditional update. If the expected version is stale, the affected-row count is zero; the application then decides whether to refresh, reject, merge, or retry.

5. Deep explanation — solve the wait-for graph before running SQL

Consider bin 1 and bin 2:

-- T1
BEGIN;
SELECT * FROM bins WHERE id = 1 FOR UPDATE;
-- later
SELECT * FROM bins WHERE id = 2 FOR UPDATE;

-- T2
BEGIN;
SELECT * FROM bins WHERE id = 2 FOR UPDATE;
-- later
SELECT * FROM bins WHERE id = 1 FOR UPDATE;

After the first locks, T1 owns 1 and T2 owns 2. T1's second request points to T2. T2's second request points back to T1. The cycle is complete. Predict that one transaction will be aborted with 40P01, but do not predict the victim identity as a guaranteed fact without runtime evidence.

Repair the exact fixture by making both sessions acquire rows in the same 1→2 order. One session may still wait, but the targeted opposite-order cycle no longer exists. The correct conclusion is “the demonstrated cycle was prevented,” not “deadlocks are impossible.”

For the optimistic branch, start with reservation version=7:

UPDATE reservations
SET note = 'worker decision', version = version + 1
WHERE reservation_id = 1 AND version = 7;

If a competing change already advanced the row to version 8, the update affects zero rows. That observation is the stale-write oracle for this bounded contract. It does not prove why the competing change happened or whether merge is the right domain decision.

6. Worked example — one fixture, five probes

lab://DAT-004/DAT004-LAB1 uses an isolated disposable schema.

1. Blocking probe: T1 holds a row FOR UPDATE; T2 requests the same row. Record a one-way wait versus a cycle.
2. Deadlock probe: T1 and T2 acquire two bins in opposite order. Preserve stdout/stderr and the 40P01 failure.
3. Ordering repair: both sessions acquire rows in the same order. Show that the targeted cycle disappears even if waiting remains.
4. NOWAIT/SKIP LOCKED: NOWAIT demonstrates immediate lock conflict; SKIP LOCKED demonstrates omission of a locked queue row. Do not treat that result as a complete view.
5. Optimistic CAS: a current version changes one row; a stale version changes zero rows and becomes an explicit conflict.

Prediction and observation must stay separate. Authoring does not fabricate PostgreSQL runtime evidence.

7. Failure modes — plausible shortcuts that remain wrong

“Every wait is a deadlock”

No. A wait is an edge; deadlock requires a cycle.

“FOR UPDATE means the invariant is safe”

It only locks the selected resource. The invariant may depend on other rows, tables, or side effects.

“Lock ordering eliminates all deadlocks”

It prevents the targeted pattern only when every relevant code path follows the same discipline. Other resources or hidden acquisition paths can form other cycles.

“SKIP LOCKED is a general consistency technique”

It deliberately omits locked rows and therefore yields an incomplete view. Queue-like work distribution is the intended bounded use in this lesson.

“Zero rows means a technical database failure”

For compare-and-swap it may be an expected conflict signal. The application must map it to domain policy rather than blindly retry.

“Optimistic concurrency is Serializable”

They are different mechanisms. A conditional version predicate is an application-level contract; Serializable is a database isolation mechanism.

8. Experienced bridge — compress syntax, never evidence

Experienced learners may go directly to the matrix: Conflict | T1 Locks/Version | T2 Locks/Version | Wait Edge | Observed SQLSTATE/Row Count | Prevention or Detection | Retry/Conflict Policy | Residual Risk.

Minimum evidence still includes a blocking observation, a real deadlock, ordering repair, a locking-clause boundary, a stale version conflict, and independent transfer. Hidden expected outcomes never substitute for actual session output.

9. Alternatives and trade-offs — choose policy from the invariant

Pessimistic row locking fits contested state that must be stabilized before a write decision and where waiting is acceptable. It increases blocking and exposes multi-resource paths to deadlock risk.

Consistent lock ordering is inexpensive targeted prevention but requires discipline across all acquisition paths and is not a global proof.

NOWAIT is useful when the application prefers immediate conflict over waiting and has an explicit fallback.

SKIP LOCKED supports multiple workers claiming independent queue jobs, but fairness/starvation and incomplete views belong in residual risk.

Optimistic version checking fits lower-contention workflows where conflicts can be surfaced to the application. It avoids holding locks during user think time but requires refresh/merge/reject logic and can starve under repeated contention.

This lesson establishes bounded correctness evidence, not production throughput benchmarks.

10. Guided and independent practice

Guided

Run the blocking schedule first and draw one wait edge. Then convert the same fixture into an opposite-order deadlock. Before either second lock request, write the complete wait-for graph. Preserve the 40P01 failure instead of deleting it.

Run the ordering repair and distinguish ordinary wait from cycle removal. Lock a queue row in T1 and run NOWAIT and SKIP LOCKED in T2 to observe their different policies.

Independent

Reset the reservation. First use the current expected version and record affected rows. Then let a competing change increment the version and retry with the stale expected version. Map the zero-row conflict to a learner-owned refresh/reject/merge/retry decision.

11. Verification and evidence — keep four statement classes distinct

Every row in Concurrency Conflict Decision Record v1 identifies:

• Prediction: expected lock/wait/version outcome before execution.
• Observation: actual stdout/stderr, SQLSTATE, affected-row count, or selected row identity.
• Inference: what the observation supports inside the bounded fixture.
• Decision: prevention, retry, or conflict policy for the stated invariant.

All five critical dimensions—lock_wait, deadlock_cycle, lock_ordering, locking_clause_boundary, and optimistic_compare_swap—must remain traceable. A deliberate failure, mitigation, changed-context transfer, and residual limitation are mandatory.

12. Assessment and transfer — solve the job dispatcher independently

Multiple consumers claim rows from jobs while an operator can cancel a job concurrently. Choose row locking, NOWAIT/SKIP LOCKED, or an optimistic version check and state the invariant explicitly.

If you choose SKIP LOCKED, state what evidence would expose starvation/fairness problems and never call the result a complete view. If you choose optimistic concurrency, design the stale cancel/claim race and its zero-row conflict. If you choose pessimistic locking, define wait/deadlock surface and acquisition order.

“Faster” or “stronger” without evidence is not a defensible decision. The final transfer policy must be learner-owned.

13. References and freshness boundary

This candidate uses only the frozen sources:

• PG18-CONCURRENCY — PostgreSQL 18 concurrency control, explicit locking, and deadlocks; retrieved 2026-09-02, review due 2026-12-31.
• PG18-SELECT — row-locking clauses, NOWAIT, and SKIP LOCKED; retrieved 2026-09-02, review due 2026-10-17.
• PG18-SERFAIL — 40P01 and bounded retry guidance; retrieved 2026-09-02, review due 2026-12-31.

As of 2026-09-15, no review date has arrived, so authoring does not expand the source scope. Claims are PostgreSQL 18-specific, and the disposable fixture does not prove universal production correctness or performance.