Optimistic Locking
debt(d9/e5/b5/t7)
Closest to 'silent in production until users hit it' (d9). The detection_hints field explicitly states 'automated: no' and the code pattern (UPDATE without version check on shared mutable rows) requires human review. There is no tool in the ecosystem that will flag missing optimistic locking; the failure mode — a lost update — is a silent data corruption that only manifests under concurrent load in production.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix describes adding a version column and updating every UPDATE statement to check rowCount(), but the common_mistakes reveal additional concerns: retry logic must also be implemented, the version field must be propagated through forms/APIs/DTOs, and any financial or high-contention path must be audited for suitability. This is more than a one-liner but stays within one component or feature area rather than requiring codebase-wide architectural rework.
Closest to 'persistent productivity tax' (b5). The pattern applies to both web and cli contexts (applies_to) and affects any shared mutable row. Every future developer working on those entities must understand the version column contract, implement retry logic correctly, and reason about contention levels. It's not load-bearing across the entire system architecture (b7), but it does impose an ongoing cognitive and implementation tax on any code touching the affected tables.
Closest to 'serious trap — contradicts how a similar concept works elsewhere' (t7). The misconception field states the canonical wrong belief: developers assume optimistic locking is universally superior to pessimistic locking, when in fact under high write contention it causes excessive retries and is worse. Common mistakes compound this — using updated_at as version (timestamp granularity flaw) and omitting retry logic (silently discarding failed updates) are both natural-seeming implementations that are subtly but seriously wrong.
Also Known As
TL;DR
Explanation
A version INT (or updated_at DATETIME) column is incremented on every UPDATE. The WHERE clause includes AND version = :expected — if rowCount() returns 0, another process changed the row first. Advantages: no lock contention, scales well for read-heavy workloads. Disadvantages: requires retry logic, degrades under high write contention on the same row. Preferred over pessimistic locking for most web application patterns.
Common Misconception
Why It Matters
Common Mistakes
- Not implementing retry logic — silently discarding the failed update.
- Using updated_at as the version — two updates within the same second both pass the check.
- Using optimistic locking for financial operations where a lost update is unacceptable.
Avoid When
- Avoid when conflict rate is high — retries become the dominant code path.
- Do not use for financial transfers where a missed update is a data integrity failure.
When To Use
- Use for read-heavy workflows where conflicts are infrequent.
- Use when holding locks between read and write is impractical — e.g. user edits a form for 30 seconds.
Code Examples
// Last write wins — concurrent change silently overwritten
$pdo->prepare('UPDATE products SET name = ? WHERE id = ?')->execute([$newName, $id]);$stmt = $pdo->prepare('SELECT id, name, version FROM products WHERE id = ?');
$stmt->execute([$id]);
$product = $stmt->fetch();
$stmt = $pdo->prepare(
'UPDATE products SET name = ?, version = version + 1 WHERE id = ? AND version = ?'
);
$stmt->execute([$newName, $id, $product['version']]);
if ($stmt->rowCount() === 0) {
throw new ConcurrentModificationException('Row was modified by another process — retry');
}