Concurrency Control Across Multiple Pods: The Single‑SQL CAS Pattern

The problem: last writer wins

A pod handling a cancel request (not the leader) and the executor running the job (the leader pod) tried to write the same job row as Done at the same time.

With no lock, both pods read state=running, and both writes succeed. The result follows whoever wrote last, so cancelled flips to success or the other way around. To the user, it’s a bug where a job they clearly cancelled shows up as successful.

The fix: put the expected state in the UPDATE

The fix was simple. Add the state you expect as a condition in the UPDATE’s WHERE, so only the first writer succeeds.

UPDATE job
SET state = ?, result = ?, finished_at = ?
WHERE id = 3
  AND run_seq = 3          -- the generation I knew
  AND state = 'running'    -- still in progress
  AND action = 'start'     -- same action

Success is judged by RowsAffected. If it’s 1, I was the first writer, so finish as usual. If it’s 0, someone else finished first, so skip.

It compares against the expected state (Compare) and changes the row only on a match (Swap), so it’s CAS. The database applies the UPDATE atomically with a row-level lock, so the application doesn’t need a lock of its own.

Why compare run_seq too

If you only check state = 'running', you can’t tell apart a restart or rerun that brings the same state back. run_seq (the generation number) confirms the row is still the run I knew. It guards against the ABA problem, where a value looks the same but actually belongs to a different generation.

The pattern was already in the codebase

Another workflow in the same codebase was already using this pattern to save intermediate checkpoints (SaveStatusIfCurrent). The new completion path (FinishIfCurrent) reuses the same WHERE and, since it only handles completion, keeps the SET simpler.

finish(Done, Cancelled)
  ├─ condition: {JobID=3, ExpectRunSeq=3, ExpectAction=start}
  ├─ FinishIfCurrent(cond, Done, Cancelled, now, logs)
  │    └─ UPDATE ... WHERE run_seq=3 AND state=running AND action=start
  ├─ matched=true  → finished normally
  └─ matched=false → warn "superseded by another actor", DB unchanged

Compared with a distributed lock

Before, this race was handled with a distributed lock.

When to use CAS, when to use a lock

Concurrency between pods often comes down to one line of WHERE, without any heavy infrastructure.