DB-Backed Jobs With Leases (Db Backed Jobs With Leases)

The previous part built the retry algorithm — but it quietly assumed something: that we know which single worker is running a job. If multiple workers pull from the same job table, can the same payment job end up processed by two workers at once?

Message brokers solve this with visibility timeouts or ack/nack. In a database-backed job queue — a common choice in payment systems, since job state already lives in the database anyway — the same guarantee comes from the lease pattern.

Worker A                          Worker B
   │ SELECT ... FOR UPDATE?           │
   │ or a conditional UPDATE           │
   ▼                                  ▼
  Tries to lock the job          Tries to lock the job
   → only one wins

Where the concepts first show up

📦 Lease
A timestamped record marking that a worker 'owns' a job for a bounded period.

📦 Conditional UPDATE
An atomic SQL statement that only updates a row if an expected condition (e.g. status = Pending) holds.

📦 Lease TTL
The upper bound on how long a worker may hold a job; once it passes, the job becomes claimable again.

📦 Stuck watcher
A background process that periodically scans for jobs whose lease has expired but were never completed, and returns them to the pool.

A lease is not a lock; if the process holding a lock crashes, the lock can persist forever. Because a lease has a TTL, even a crashed worker eventually releases the job automatically.

The atomic way to acquire a lease

Reading a row and then writing to it (read-then-write) is exposed to a race: two workers can read the same row, both see it as free, and both try to claim it. The correct approach folds the read and the condition into a single atomic statement:

UPDATE payment_jobs
SET status = 'Processing',
    lease_owner = :workerId,
    lease_until = now() + interval '60 seconds'
WHERE id = :jobId
  AND (status = 'Pending' OR (status = 'Processing' AND lease_until < now()))
RETURNING id;

If this UPDATE returns no rows, the job is already owned by another worker under a still-valid lease — this worker quietly moves on to the next job. If a row comes back, that worker is now the sole owner until the lease expires.

Why the lease TTL needs a heartbeat extension

A fixed lease duration (say, 60 seconds) may not fit every operation. A step that can legitimately run long (a PSP call that stalls unexpectedly) should extend the lease via a heartbeat before it expires:

Worker starts → lease_until = now + 60s
  ... work continues ...
Worker sends heartbeat → lease_until = now + 60s (renewed)
  ... work completes ...
Worker marks status = Completed

A worker that can't send a heartbeat (crashed, disconnected) can't renew the lease; it expires, and the job becomes claimable again. This is what makes crash recovery automatic rather than manual.

The stuck watcher: who notices an expired lease

An expired lease doesn't 'rescue itself' — some worker has to SELECT the row again. A periodic watcher process scans for jobs stuck in Processing past their lease expiry and either flags them or returns them to Pending directly.

Watcher (every 30 seconds)
  SELECT id FROM payment_jobs
  WHERE status = 'Processing' AND lease_until < now()
  → these jobs get marked stuck, or reset to Pending directly

Without a watcher, a job abandoned by a crashed worker can sit forever in Processing — and no one notices that a payment never actually finished.

Why a broker's Nack alone isn't enough

In a message broker, if a worker Nacks a message (or its visibility timeout expires), the message goes back on the queue. This resembles a DB-backed lease closely — but two differences matter: first, the broker's own visibility window is usually not synchronized with your persistent job-state record (a message can be lost or delivered twice); second, Nack only says 'let this message go' — it doesn't durably track which step the job reached or how many times it's been attempted. A database-backed lease keeps job state and attempt history in the same, queryable transaction boundary.

Distinctions that get blurred

❌ Lease = lock
✓ A lease has a TTL; a lock can persist forever if the process holding it crashes

❌ Read-then-write is good enough
✓ Read-then-write is exposed to a race; a conditional UPDATE must be atomic

❌ Nack fully replaces a lease
✓ Nack manages message visibility; a lease durably tracks job state and attempt history

DB-backed lease vs. broker visibility timeout

Criterion	Broker visibility timeout	DB-backed lease
Queryability of state	Limited	Full via SQL
Durability of attempt history	Depends on broker	Naturally in the same row
Detecting stuck jobs	Indirect	Direct query

Checklist when designing leases

1. Is acquiring a lease a single atomic UPDATE ... WHERE statement, or read-then-write?
2. Is the lease TTL genuinely longer than the longest expected operation?
3. Is there a heartbeat mechanism to renew the lease for operations that can legitimately run long?
4. Does a stuck watcher run periodically, or can an expired lease leave a job stuck in Processing forever?
5. Does the lease_owner field record which worker instance holds the job, for diagnostics?
6. Is the attempt count incremented and persisted on every lease acquisition?

What to take away

1. A lease is not a lock; it's a time-bounded claim of ownership that expires automatically.
2. Acquiring a lease requires an atomic conditional UPDATE; read-then-write is exposed to a race.
3. Long-running operations must renew the lease via a heartbeat, or it can expire prematurely.
4. A stuck watcher is a mandatory background process that rescues jobs left behind by crashed workers.

The reliability of a job queue isn't proven on the happy path — it's proven the moment a worker crashes mid-job.

The next part looks at a worker built on top of this exact lease mechanism: the reconciliation worker that heals payments the PSP marked successful while the system still shows them pending.