skip to content

Several application instances poll the same jobs table for pending work. Using only JPA/Hibernate locking, how would you design the claim step so no two instances process the same row, and what are the trade-offs of your design?

level: principalimportance: should knowfreq 28%

answer

  1. FOR UPDATE SKIP LOCKED = disjoint claims, no queueing
  2. hint value -2, PESSIMISTIC_WRITE, limited batch
  3. hold-the-lock: self-healing but pins a connection
  4. claim-and-commit: short tx, needs lease + reaper + idempotency
  5. no fairness; keep the claim query join-free

basics

~20 s

Claim with a limited query using PESSIMISTIC_WRITE plus the SKIP LOCKED timeout value (-2), mark the rows claimed, and commit quickly — then process outside the lock. Trade-offs: no ordering fairness, dialect support required, and crash recovery needs a lease or heartbeat.

solid answer

~60 s

The claim step is a short transaction: `select ... from jobs where status = 'PENDING' order by created_at limit N for update skip locked`, expressed in JPA as a query with `setLockMode(PESSIMISTIC_WRITE)` and the lock-timeout hint set to Hibernate's `SKIP LOCKED` value (`-2`). Skipped rows are the ones other workers already hold, so pollers never queue behind each other and never see the same row twice. Then decide where the lock ends. **Lock-for-the-duration** — process inside the claiming transaction — is simplest and self-healing (a crash rolls back and the row is instantly claimable again), but it pins a connection and a row lock for the whole job and blocks nothing else usefully. **Claim-and-commit** — flip status to `RUNNING` with an owner and a lease timestamp, commit, then process — keeps transactions short but you now own crash recovery: a reaper must reclaim leases that expired. Trade-offs: no fairness or strict ordering, engine support is required, and long holders bloat MVCC housekeeping. Beyond modest scale, a real broker beats a table.

code

java · 13 lines
java
List<Job> batch = em.createQuery(
        "select j from Job j where j.status = :s order by j.createdAt", Job.class)
    .setParameter("s", Status.PENDING)
    .setMaxResults(20)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE)
    .setHint("jakarta.persistence.lock.timeout", -2) // SKIP LOCKED
    .getResultList();

for (Job j : batch) {
    j.setStatus(Status.RUNNING);
    j.setOwner(instanceId);
    j.setLeaseUntil(Instant.now().plus(LEASE));
}

go deeper

for a junior

Recognise that two workers must not take the same row and that the database can do this with a locking select that skips already-locked rows.

for a middle

Write the claim query with PESSIMISTIC_WRITE plus the SKIP LOCKED hint value and a batch limit, and know it needs engine support.

for a senior

Choose between holding the lock and claim-and-commit explicitly, and account for lease expiry, reaping, retry counters and idempotency.

for a principal

Set the boundary: state where a table-as-queue is the right call because the job commits with the business data, and where throughput and MVCC pressure make a broker plus outbox the better architecture.

## Why the naive designs fail *Read then update* — select pending rows, then update their status — races: two pollers read the same row and both think they own it. *Plain `for update`* fixes correctness but destroys throughput: every poller queues behind the first, so N workers do the work of one and each additional worker only adds lock waits. `SKIP LOCKED` is the primitive that makes a database table behave like a queue. Rows currently locked by another transaction are omitted from the result rather than waited on, so each poller naturally receives a disjoint set of rows. ## The claim step In JPA: a query restricted to claimable rows, ordered, limited to a batch size, with `setLockMode(LockModeType.PESSIMISTIC_WRITE)` and the hint `jakarta.persistence.lock.timeout` set to `-2`, which Hibernate renders as `skip locked`. Batch size is a tuning knob — larger batches amortise round trips but coarsen load balancing and lengthen the window a crash can strand. Engine support is a hard prerequisite: PostgreSQL, Oracle and MySQL 8 have it; older MySQL does not. Also keep the claim query simple — `for update` is invalid with outer joins on most engines, so no `join fetch`, and avoid `distinct`/`group by`. ## Where does the lock end? The central decision **Option A — hold the lock for the whole job.** One transaction: claim, process, mark done, commit. Correctness is free — if the worker dies, the transaction rolls back and the row is immediately available to someone else; no lease, no reaper, no stuck rows. The cost is that a connection, a transaction and a row lock are held for the job's entire duration. Long transactions keep old row versions alive and hurt the engine's cleanup, and if the job makes network calls you have coupled an external system's latency to your database. Viable only for short, purely-database work. **Option B — claim and commit.** Claim the rows, set `status = 'RUNNING'`, `owner = <instance>`, `lease_until = now() + t`, commit immediately, then process outside any lock, and commit the result in a second short transaction. Transactions stay milliseconds long, which is what an operational system wants. In exchange you own failure recovery: a crashed worker leaves rows `RUNNING` forever, so you need a reaper that returns rows whose lease expired, plus heartbeat extension for long jobs. And once processing is outside the transaction that claimed the row, delivery is at-least-once — an expired lease can be reclaimed while the original worker is merely slow, so the job body must be idempotent. Most production systems choose B, because unbounded lock and connection hold times are the failure mode that takes a service down, and idempotency is something you want anyway. ## Trade-offs to state out loud - **No fairness or ordering.** `order by` expresses a preference, not a guarantee — skipping means a later row can be processed before an earlier locked one. If strict per-key ordering matters, partition by key and let exactly one worker own a partition. - **Poll cost.** Idle pollers still execute queries; use a backoff and an index on the claimable predicate, or a notification channel to wake workers. - **Retries and poison messages.** Add an attempt counter and a dead-letter status, or one bad row is retried forever. - **Visibility.** A claimed-and-committed row is observable (`RUNNING`, owner, lease), which is a genuine operational advantage over a lock, which is invisible to monitoring. - **Know the ceiling.** A table-as-queue is excellent at modest rates and buys transactional consistency with the business data — the job and its data commit together, with no dual-write problem. At high throughput, contention on the claim query and MVCC pressure make a dedicated broker the better answer; the transactional-outbox pattern is the usual bridge. The strongest answer names the SKIP LOCKED mechanism, then spends most of its time on lock duration, crash recovery and idempotency — those are the parts that decide whether the design survives production.

  • With claim-and-commit, how do you recover jobs from a worker that crashed mid-processing?
    Store an owner and a lease expiry when you claim, and run a reaper that returns rows whose lease has passed to the claimable state. Long-running jobs extend the lease with a heartbeat so a slow job is not reclaimed. Because a slow worker can be reclaimed while still alive, processing must be idempotent — the design is at-least-once, not exactly-once.
  • Does adding ORDER BY to the claim query guarantee jobs are processed in that order?
    No. The ordering picks which candidate rows a poller prefers, but SKIP LOCKED removes rows another worker already holds, so a later row can be claimed and finished before an earlier one. If you need ordering within a key, partition the work so a single worker owns each key, or serialise on a per-key row lock.
  • Why not just hold the lock for the entire job?
    It is the simplest correct design and self-healing on crash, so it is fine for short, database-only work. But it pins a connection and a transaction for the job's whole duration, keeps old row versions alive and lengthens engine cleanup, and couples any external call's latency to your database. At any real volume the connection pool becomes the limit.

saying these in an interview costs you the question

  • Selecting pending rows and then updating them in a separate statement, assuming that is atomic.
  • Using plain FOR UPDATE and expecting multiple workers to scale — they serialise.
  • Claiming with SKIP LOCKED but holding the lock across slow external calls.
  • Committing the claim without a lease or owner, leaving rows permanently stuck when a worker dies.
  • Promising exactly-once processing from a lease-based design.

context

resolving links…