Database Locks, Isolation, And Optimistic Versions

Database coordination can use conditional writes, row locks, constraints, isolation levels, or optimistic version checks.

Why This Matters

The right mechanism depends on the invariant, contention, transaction length, and database engine.

Working Model

Database coordination can use conditional writes, row locks, constraints, isolation levels, or optimistic version checks. Correctness comes from preserving an explicit invariant across every permitted ordering, not from expecting one observed timing.

Practical Rules

  • Prefer the narrowest atomic statement.
  • Use SELECT ... FOR UPDATE only inside a transaction.
  • Add version columns for editable records.
  • Define deadlock retry boundaries.
  • Keep external calls outside locked transactions.

Failure Modes

  • Assuming repeatable read prevents every anomaly.
  • Locking rows in inconsistent order.
  • Returning success without checking affected rows.
  • Retrying non-idempotent transaction bodies.

Verification

  • Test two real connections.
  • Force version mismatch and deadlock paths.
  • Inspect lock duration.
  • Verify retry count and final invariant.

What You Should Be Able To Do

After this lesson, you should be able to explain conditional updates, pessimistic locks, optimistic versions, isolation, and deadlock handling, choose a suitable approach for a real PHP project, and verify the result instead of relying on assumptions.

Practice

Practice: Implement Optimistic Locking

Update an article only when its version matches.

Your answer must:

  • state the intended outcome;
  • show the commands, data flow, or implementation shape;
  • identify at least one unsafe alternative;
  • explain how the result will be verified.
Show solution

Include WHERE id = ? AND version = ?, increment the version, require one affected row, and return a conflict when no row matches.

The important part is not memorising one command or vendor screen. The solution makes the invariant, failure behavior, and verification evidence explicit.

Practice: Order Row Locks

Transfer value between two accounts without inconsistent lock order.

Your answer must:

  • state the intended outcome;
  • show the commands, data flow, or implementation shape;
  • identify at least one unsafe alternative;
  • explain how the result will be verified.
Show solution

Lock both account rows in a deterministic ID order, validate the debit, update both, and commit. Retry bounded deadlocks at the use-case boundary.

The important part is not memorising one command or vendor screen. The solution makes the invariant, failure behavior, and verification evidence explicit.

Practice: Choose An Isolation Strategy

Protect a cross-row booking rule.

Your answer must:

  • state the intended outcome;
  • show the commands, data flow, or implementation shape;
  • identify at least one unsafe alternative;
  • explain how the result will be verified.
Show solution

Use engine-supported constraints where possible; otherwise choose locks or serializable behavior around the queried range and test concurrent overlapping bookings.

The important part is not memorising one command or vendor screen. The solution makes the invariant, failure behavior, and verification evidence explicit.