2026-09-17 · 8 min read
- sql
- queues
- concurrency
A relational database can coordinate modest worker queues by selecting eligible rows with a locking read. SKIP LOCKED lets one worker pass rows currently owned by another instead of waiting, increasing concurrency when jobs have uneven durations.
It is a lock-wait policy, not a complete queue protocol. Durable ownership, visibility timeouts, retry scheduling, fairness, poison-job handling, and external idempotency still need explicit design.
Claim work in a short transaction
Select a bounded batch using an eligibility predicate and deterministic order, lock those rows, update them with an attempt identity and lease, then commit. Perform slow processing after the claim transaction has released its locks.
- Order by priority and availability time with a unique tie-breaker.
- Use database time consistently for eligibility and leases.
- Return claimed rows from the same atomic operation where supported.
- Index the predicate and ordering columns used by workers.
Recover abandoned claims with fencing
A lease makes a crashed worker's job eligible again after a deadline. Include an attempt or generation token in completion updates so a stale worker cannot finish after a replacement has reclaimed the same logical job.
Keep logical job IDs distinct from attempt IDs. The logical ID supports deduplication and business idempotency, while attempt IDs explain retries and reject stale writes.
Measure fairness and poison work
Queue depth can look healthy while old rows starve behind continuous high-priority arrivals or repeated lock conflicts. Monitor oldest eligible age, claim latency by priority, retry count, lease expiry, and terminal failures.
Use bounded retry delays and a terminal or dead-letter state. One permanently failing row should not consume every worker or remain invisible behind SKIP LOCKED forever.
Verify the deployed database behavior
Use Flashman's SQL formatter for claim statements, timestamp converter for lease timelines, UUID generator for attempt IDs, diff for sanitized worker traces, and hash for fixture identity.
Test simultaneous workers, equal priorities, small and large batches, rollback, crash after claim, expiry, stale completion, poison rows, lock contention, deadlocks, shutdown, replicas, schema migrations, transaction isolation, and duplicate downstream effects.