Pessimistic Locking
debt(d7/e5/b5/t5)
Closest to 'only careful code review or runtime testing' (d7). The detection_hints explicitly state 'automated: no' and only provide a code pattern (FOR UPDATE|lockForUpdate). There is no automated tool that catches misuse — such as holding locks across slow I/O or using pessimistic locking in low-contention scenarios. These mistakes are silent in normal operation and only surface under load or concurrency testing, making careful code review the primary detection mechanism.
Closest to 'touches multiple files / significant refactor in one component' (e5). The quick_fix suggests switching strategies (e.g., from pessimistic to optimistic, or adding SKIP LOCKED for queue workers), but fixing misuse requires identifying all transaction boundaries, reviewing lock scopes, and potentially restructuring code flow to keep transactions short. This touches transaction management across multiple query calls and often requires refactoring the surrounding business logic, going beyond a simple one-liner swap.
Closest to 'persistent productivity tax' (b5). The term applies_to web, cli, and queue-worker contexts — broad reach across the application. Holding pessimistic locks imposes a throughput constraint felt system-wide under load, and every developer working with affected code paths must reason about lock ordering and transaction duration. However, it doesn't fully define the system's shape, so it stops short of b7.
Closest to 'notable trap (a documented gotcha most devs eventually learn)' (t5). The misconception field directly states the canonical trap: developers assume pessimistic locking is universally safer than optimistic, but it creates deadlock risk with multiple locks and reduces throughput. The common_mistakes reinforce this — using it in low-contention scenarios and holding locks across slow I/O are well-documented but frequently encountered pitfalls that experienced developers have learned the hard way.
TL;DR
Explanation
SELECT ... FOR UPDATE locks the selected rows until the transaction commits or rolls back. No other transaction can modify those rows. High-contention scenario: inventory reservation, seat booking, financial transfers. MySQL: SELECT ... FOR UPDATE, SELECT ... FOR SHARE (read lock). PostgreSQL: similar plus SKIP LOCKED (skip already-locked rows — useful for queue workers). Trade-offs: prevents races completely but serialises updates, reducing throughput. Long transactions increase deadlock risk. FOR SKIP LOCKED is excellent for queue workers — each worker takes uncontested rows.
Common Misconception
Why It Matters
Common Mistakes
- Using pessimistic locking for low-contention scenarios — unnecessary performance cost.
- Holding pessimistic locks across slow I/O (HTTP calls, file writes) — deadlock risk.
- Not using SKIP LOCKED for queue workers — causes lock contention between workers.
Code Examples
-- Long-running pessimistic lock across slow operation:
START TRANSACTION;
SELECT * FROM payments FOR UPDATE;
-- Calls external payment API (2 seconds) while holding lock
UPDATE payments SET status = 'processed';-- Queue worker with SKIP LOCKED:
START TRANSACTION;
SELECT * FROM jobs
WHERE status = 'pending'
LIMIT 1
FOR UPDATE SKIP LOCKED; -- Each worker gets a different row
-- Seat booking:
START TRANSACTION;
SELECT * FROM seats WHERE id = ? FOR UPDATE;
-- Check if available, then book — all under lock