Claiming work with a conditional UPDATE

Summary: when two processes might pick up the same row, don't check then act — make the claim itself a conditional write. UPDATE … SET status='processing' WHERE id=%s AND status='pending' succeeds for exactly one caller; the rest update zero rows and back off. The row is the lock.

How it works

• The gap between a check and an act is the race. SELECT status → decide → UPDATE leaves a window where another process runs the same three steps and both see 'pending'. Adding the expected state to the WHERE clause closes it: check and act are one statement.
• A single UPDATE is atomic. The database serialises writes to a row, so of N concurrent identical statements only the first finds status='pending'. The others match nothing.
• rowcount is the answer. 1 = you own it. 0 = someone else got there first, or the row doesn't exist. Branch on it; don't assume the update landed.
• This is optimistic concurrency control — no lock is taken and held. The condition travels inside the write, and the conflict shows up as a write that matched nothing (OCC).
• The status column is doing two jobs. A value like 'drafting' isn't a stage anyone cares about reporting on — it means a worker owns this row. Worth a comment, or the next person models it as business state.

cur.execute(
    "UPDATE patch_sessions SET status='drafting' WHERE sc_id=%s AND status='clarified'",
    (sc_id,),
)
if cur.rowcount != 1:
    return          # another delivery already claimed it

What to do

• Put the expected current state in the WHERE clause, every time.
• Release the claim on failure. A crash mid-work leaves the row stuck in 'drafting' and no other worker will ever touch it. Reset the status in except. Better still, store a claim timestamp so a dead worker's claim expires and can be reclaimed.
• ON CONFLICT DO NOTHING is the same idea for inserts — let the unique constraint arbitrate instead of SELECT-then-INSERT.
• Pick the right tool for how the row is chosen:

	Conditional UPDATE	SELECT … FOR UPDATE SKIP LOCKED
You know which row	yes — an ID arrived with the request	no — you're pulling the next available
Lock style	optimistic; nothing held	pessimistic; row locked for the transaction
Losers	update 0 rows, return	skip that row, take the next
Claim survives a crash	yes — must be rolled back	no — lock dies with the transaction

Postgres documents SKIP LOCKED for exactly this: it "can be used to avoid lock contention with multiple consumers accessing a queue-like table" (docs).

Where this came up

Webhook delivery is at-least-once, so the same Shortcut event can arrive twice and start the draft phase twice — see consuming webhooks. Gating the phase on UPDATE … WHERE status='clarified' plus rowcount == 1 makes the second delivery a no-op, with the status rolled back in except so a failure doesn't strand the session.