SELECT FOR UPDATE
debt(d7/e3/b5/t7)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints indicate 'automated: no' and the code_pattern requires spotting a check-then-update without FOR UPDATE. No linter or SAST tool is listed. The race condition is silent under low concurrency and only manifests as corrupt data (negative stock, double-bookings) under production load — invisible until users hit the symptom.
Closest to 'simple parameterised fix' (e3). The quick_fix says: wrap SELECT ... FOR UPDATE inside beginTransaction()/commit() and index the WHERE clause. This is more than a single-line swap because it requires adding a transaction boundary and verifying index coverage, but it is localised to the one query/code path rather than a cross-cutting refactor.
Closest to 'persistent productivity tax' (b5). The choice applies to both web and cli contexts. Every developer working on inventory, balance, or reservation logic must understand pessimistic locking semantics, transaction scoping, and index discipline to avoid lock escalation. This shapes how concurrent write paths are designed but does not redefine the entire system architecture.
Closest to 'serious trap — contradicts how a similar concept works elsewhere' (t7). The misconception field states exactly this: developers believe a regular SELECT inside a transaction already prevents concurrent modification. This is the intuitive assumption — transactions feel like isolation — but without FOR UPDATE other transactions can freely modify rows between the SELECT and UPDATE. The 'obvious' pattern (begin transaction, SELECT, UPDATE) silently races, directly contradicting the expectation that transaction scope equals read lock.
Also Known As
TL;DR
Explanation
SELECT ... FOR UPDATE locks selected rows exclusively within a transaction. Other transactions attempting to modify or lock those rows block until the first transaction commits or rolls back. Used for 'check then update' patterns — e.g. reading inventory before decrementing. SELECT ... FOR SHARE acquires shared locks — multiple readers allowed, writers blocked. Requires an active beginTransaction(). Always index the WHERE clause or the lock may escalate to a table lock.
Watch Out
Common Misconception
Why It Matters
Common Mistakes
- Using SELECT FOR UPDATE outside a transaction — has no locking effect.
- Using an unindexed WHERE clause — can escalate to a full table lock blocking all writes.
- Holding FOR UPDATE locks across slow operations — causes lock timeout errors under concurrency.
Avoid When
- Avoid for read-heavy workflows — held locks block concurrent readers on the same rows.
- Do not hold FOR UPDATE locks across user-facing waits or external API calls.
When To Use
- Use when the gap between reading and writing a value must be race-condition free — inventory, account balances, seat reservations.
- Use when the cost of conflict detection (optimistic locking retries) is too high.
Code Examples
// Race condition — two requests both read stock=1 and both proceed
$stock = $pdo->prepare('SELECT stock FROM products WHERE id = ?');
$stock->execute([$id]);
if ($stock->fetchColumn() >= $qty) {
// Another request may decrement between this check and the UPDATE below
$pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ?')->execute([$qty, $id]);
}$pdo->beginTransaction();
try {
$stmt = $pdo->prepare('SELECT stock FROM products WHERE id = ? FOR UPDATE');
$stmt->execute([$productId]);
$row = $stmt->fetch();
if ($row['stock'] < $qty) { $pdo->rollBack(); throw new \RuntimeException('No stock'); }
$pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ?')->execute([$qty, $productId]);
$pdo->commit();
} catch (\Throwable $e) { $pdo->rollBack(); throw $e; }