Pessimistic vs Optimistic Locking
debt(d7/e5/b5/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints indicate automated detection is 'no' — semgrep and laravel-debugbar can help identify patterns but won't reliably catch the semantic problem of choosing the wrong locking strategy for a given conflict level. Lost updates from missing FOR UPDATE or retry storms from misapplied optimistic locking typically only surface under production load or careful code review.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix describes choosing the right strategy, but retrofitting locking into existing code requires modifying repository methods, adding version columns to tables (with migrations), and implementing retry logic for optimistic locking. This spans database schema, model code, and application logic — a significant refactor within the data access layer.
Closest to 'persistent productivity tax' (b5). Locking strategy applies across web, cli, and queue-worker contexts per applies_to. Once a locking approach is embedded in your data access patterns, every future concurrent operation must consider it. Teams must understand when to use which strategy, and inconsistent application leads to subtle bugs. Not quite b7 because it's isolated to data mutation paths rather than shaping every change.
Closest to 'serious trap' (t7). The misconception explicitly states developers believe 'optimistic locking is always better because it avoids locks' — this contradicts how locking works in databases where optimistic locking under high contention causes expensive retry storms. Common_mistakes reinforce this: not retrying on failure, not incrementing atomically, using optimistic in high-contention scenarios. Developers familiar with general concurrency concepts often guess wrong about which strategy fits which scenario.
Also Known As
TL;DR
Explanation
Pessimistic locking (SELECT ... FOR UPDATE) locks the row immediately — no other transaction can modify it until the lock is released. Best for high-conflict scenarios (inventory, account balances). Optimistic locking uses a version counter or timestamp — read the row, remember the version, update only if version unchanged (UPDATE ... WHERE version = :original). If another transaction modified it, the update affects 0 rows — the application retries. Best for low-conflict scenarios (profile updates, settings).
Diagram
sequenceDiagram
participant T1 as Transaction 1
participant DB as Database
participant T2 as Transaction 2
Note over T1,T2: Pessimistic Locking
T1->>DB: SELECT ... FOR UPDATE - locks row
T2->>DB: SELECT ... FOR UPDATE
DB-->>T2: WAIT - row locked
T1->>DB: UPDATE + COMMIT
DB-->>T2: Lock released
Note over T1,T2: Optimistic Locking
T1->>DB: SELECT balance version=5
T2->>DB: SELECT balance version=5
T1->>DB: UPDATE WHERE version=5 - sets version=6
T2->>DB: UPDATE WHERE version=5 - 0 rows conflict
Common Misconception
Why It Matters
Common Mistakes
- Not retrying on optimistic locking failure — when the update affects 0 rows, the application must reload and retry.
- Pessimistic locking without a transaction — locks are released at transaction end; a long-running operation holds them for too long.
- Not incrementing the version column atomically — UPDATE ... SET version = version + 1 must be in the same statement.
- Optimistic locking in high-contention scenarios — retry storms under load can degrade performance worse than pessimistic locks.
Code Examples
// No locking — race condition:
$balance = $db->query('SELECT balance FROM accounts WHERE id = ?', [$id])->fetchColumn();
if ($balance >= $amount) {
// Another process can read same balance here!
$db->query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [$amount, $id]);
// Both processes deduct — balance goes negative
}// Pessimistic — lock the row before reading:
$db->beginTransaction();
$balance = $db->query('SELECT balance FROM accounts WHERE id = ? FOR UPDATE', [$id])->fetchColumn();
if ($balance >= $amount) {
$db->query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [$amount, $id]);
}
$db->commit();
// Optimistic — detect conflict at write time:
$row = $db->query('SELECT balance, version FROM accounts WHERE id = ?', [$id])->fetch();
$affected = $db->query('UPDATE accounts SET balance = balance - ?, version = version + 1 WHERE id = ? AND version = ?',
[$amount, $id, $row['version']])->rowCount();
if ($affected === 0) throw new ConflictException('Retry required');