concurrency and conflict control

why

any fact with more than one writer eventually gets two concurrent updates; the database default — last writer wins — silently discards someone's valid change. an explicit conflict proof (expected version, unique key, lease) turns the race into a typed, retryable outcome instead of silent data loss. transactions give atomicity; only a declared business policy says which write wins.

rule

for every contested write, state whether duplicate delivery is accepted, rejected, or merged; encode the choice with a unique key, expected version, transaction, or named lock. a transaction gives atomicity, not an automatic business conflict policy.

choose the conflict proof

write shape	proof
create one fact once despite retries	scoped unique constraint plus idempotency key.
update only if the reader saw the latest state	compare-and-swap monotonic numeric version.
reserve a scarce value	unique constraint or one short transaction around the reservation.
serialize a high-contention owner	owner-scoped lease/lock with expiry and observable holder.
commutative additive fact	append immutable entries; project the current total separately.

never silently overwrite a concurrent update.
return a typed conflict naming the stale operation, with the correct reload/retry boundary.
default to an atomic monotonic numeric version.
a timestamp is valid only when the database generates it atomically at sufficient precision on every write.

verification

two writes from the same prior version: exactly one succeeds.
same idempotency key repeated: one durable fact exists.
lease expiry plus worker restart: a later owner proceeds without two active claims.

good and bad

bad

update content_asset set title = ? where id = ?;

no conflict proof: the last writer silently erases another editor's valid change, and nothing records it. the winning write may itself be based on an outdated view, so the surviving row can be internally wrong. no log, no conflict type, no retry path — the race was never declared a business decision.

good

update content_asset
set title = ?, version = version + 1
where id = ? and version = ?;

zero affected rows is a typed stale-version conflict; one affected row proves the write was based on the current fact. every concurrent edit becomes a detectable, retryable outcome: the caller reloads the latest state and reapplies with full information. races stop being production mysteries and become ordinary typed diagnostics.