Recipe

Optimistic concurrency control

Optimistic locking lets multiple agents read and propose mutations to a shared resource without holding a database lock. Each write carries the version it was based on; the commit succeeds only if the version has not changed. This recipe shows the canonical pattern Meridian uses for concurrent agent dispatches against shared task state.

1.Add a version column

Every row that participates in optimistic locking needs a monotonic version integer. The version is incremented atomically by the database on every successful write. Reads always select the current version alongside the payload, so the caller knows the basis for any subsequent mutation.

2.Guard the UPDATE

The write predicate includes the version the caller observed. If a competing writer already incremented the row, the WHERE clause matches zero rows and the update is a no-op. The application layer treats a zero row count as a conflict and retries with a fresh read.

3.Retry with backoff

On conflict, re-read the row, recompute the mutation against the fresh state, and attempt the update again. Cap retries at three attempts with exponential backoff to keep contention bounded. For high-conflict workloads, fall back to a pessimistic transaction.

SQL pattern

-- Read
SELECT id, payload, version
FROM tasks
WHERE id = $1;

-- Write (guarded)
UPDATE tasks
SET payload = $2,
    version = version + 1
WHERE id = $1
  AND version = $3
RETURNING version;

-- Application:
-- if RETURNING produced 0 rows -> conflict, retry
-- else -> commit and use the new version