2026-09-17 · 6 min read · Rahul Chitturi
- sql
- queues
- concurrency
SELECT FOR UPDATE SKIP LOCKED can let several workers claim different rows without waiting behind one busy transaction. It improves throughput, but it does not by itself define durable ownership, fairness, retries, or exactly-once effects.
Rows that remain locked or repeatedly lose the ordering race can be skipped for a long time. A worker crash after an external effect but before commit can also cause later duplicate processing.
Trace claim and completion as separate states
Record the selection predicate, stable ordering, lock clause, transaction boundaries, claimed row ID, attempt ID, lease or visibility deadline, commit result, and completion update. Use database time consistently for lease comparisons.
- Add a deterministic tie-breaker to queue ordering.
- Keep the locking transaction short.
- Do not hold row locks during slow network calls.
- Use idempotency at every external side-effect boundary.
Design recovery and fairness explicitly
A durable claim column with an expiry can make abandoned work visible again, but reclaim logic must fence a stale worker that resumes later. Attempts and logical jobs need different identifiers.
Monitor the age of the oldest eligible row, not only queue depth. Priority ordering, poison jobs, continuous high-priority arrivals, and retry delays can starve work while workers remain busy.
Run controlled multi-worker tests
Use the SQL formatter for claim statements, timestamp converter for lease timelines, UUID generator for attempt identities, and diff for sanitized worker histories.
Test simultaneous claims, equal priorities, rollbacks, worker crashes, lease expiry, stale completion, poison rows, long transactions, deadlocks, empty queues, replica lag, graceful shutdown, and duplicate downstream calls.