Let the Database Reject Impossible State

Two workers asked the same reasonable question: does this owner already have an active record? Both received the same honest answer: no.

Each worker created one.

Nothing in either request was obviously careless. The application checked first. The insert happened inside a transaction. Tests which ran one request at a time stayed green. The defect lived in the interval between the check and the write, where both transactions were allowed to be correct about a state which had already become stale.

The repair was not a faster check. We gave every active record a deterministic owner key and made that key unique in the database. A second writer could still race. It could no longer commit an impossible result.

Validation describes the rule; a constraint survives the race

Application validation remains useful. It can say “an active record already exists” instead of exposing a storage error. It can reject incomplete input before opening a transaction. It can keep an ordinary conflict off the expensive path.

It cannot make a check-then-insert sequence atomic:

worker A: SELECT active WHERE owner = 42  -> none
worker B: SELECT active WHERE owner = 42  -> none
worker A: INSERT active(owner = 42)       -> succeeds
worker B: INSERT active(owner = 42)       -> succeeds

Both reads can be valid when they happen. The combined state is invalid when both writes commit.

This is a time-of-check to time-of-use problem, but the useful lesson is about ownership. If every writer shares one database, the database is the last place where the invariant can still see all competing writes. A constraint there does not depend on a particular controller, command, queue worker, import, or future code path remembering the rule.

The application should explain the invariant. The database should make it inescapable.

Encode the state which must be unique

The retained design had several active states and several terminal states. Historical rows had to remain, while only one active row per owner was allowed. A unique owner column by itself would have prohibited history as well as duplicates.

The useful representation was conditional:

active record:
    owner_key = deterministic key for its owner

terminal record:
    owner_key = null

A unique index on owner_key then rejects a second active record. Databases which permit several null values in a unique index still retain any number of terminal rows.

The key must represent the actual identity boundary. A raw concatenation such as tenant + "|" + user + "|" + session is easy to get subtly wrong when a missing value and an empty value mean different things. The retained implementation length-prefixes present values, marks absent values explicitly, and hashes the resulting tuple. The hash is an index representation, not the source of identity. The original owner fields remain authoritative.

This design has a consequence worth stating plainly: changing the meaning of “owner” changes the invariant. If an owner may have one active record per currency, region, or workflow, that scope belongs in the key. A compact key cannot rescue a badly named rule.

A unique key needs a state agreement

Uniqueness answers only one question: can two rows carry this key? It does not prove that the correct rows carry it.

Without another guard, these states remain possible:

active row with no owner key
terminal row which still occupies the owner key
active row with neither a user nor a session identity

The first quietly bypasses uniqueness. The second prevents a legitimate new record. The third hashes an absence which has no domain meaning.

The retained migration therefore adds checks around the unique index. Active rows must have an owner identity and a derived key. Terminal rows release the key. Database-side derivation protects older or lower-level writers which do not pass through the current application model.

That last detail can look redundant. It is not. A durable constraint is only as strong as its least disciplined write path. If a direct import can insert an active row without the key, the application has moved the race rather than closed it.

The rules have different jobs:

Treating these as one vague “database validation” feature makes it harder to see the hole each rule closes.

Existing data gets a vote

Adding a constraint to an empty table is easy. Adding one to a table which already violates it is a data decision disguised as schema work.

The migration found duplicate active sets in retained data. It could have chosen the oldest row, the newest row, or the row with the most fields and deleted the rest. None of those rules was known to preserve the right business state.

Instead, the migration refused to continue until each duplicate set had an explicit canonical resolution. The chosen row received the owner key. Other rows remained as historical evidence with a narrow legacy exemption.

unresolved duplicate set
    -> migration stops

explicit canonical decision
    -> canonical row receives the key
    -> other rows remain visible as remediated history
    -> future duplicates are rejected

Failing the migration may feel less automated than choosing a winner. It is more honest. Schema code cannot infer which record corresponds to a remote effect, a payment, a user-visible result, or later work merely because one timestamp is newer.

The exemption is debt, not a general escape hatch. It exists for identified legacy rows which cannot be made historically false. New writes do not receive it. If ordinary application code can opt out, the invariant once again depends on every caller behaving.

A conflict is a normal result of correct enforcement

Once the database owns the invariant, concurrent creation has two possible outcomes:

one writer creates the active record
another writer discovers that record already won

The second outcome is not necessarily an application failure. The retained acquisition path catches only the named unique-key conflict, reads the winner, and returns it. Unrelated database errors still escape.

That narrow catch matters. Converting every integrity error into “already exists” would hide missing foreign keys, invalid states, truncated values, and other defects. The application should translate the one rejection it knows how to reconcile.

There is also a small interval where the losing transaction has received a conflict but the winning row is not yet visible under its current isolation and connection timing. A bounded retry can cover that visibility interval. The bound must remain finite, and it must not turn arbitrary database failures into retry traffic.

Application code still improves the common path:

find an existing active record
if found, return it
attempt creation
if the named uniqueness constraint rejects it, fetch the winner
otherwise propagate the error

The early lookup is an optimization. The constraint is the correctness boundary.

Locks are useful when they lock something real

A transaction does not automatically serialize a predicate which currently matches no rows. Asking to lock “the active record” cannot lock a row which does not exist.

Other designs can be correct:

• lock a stable parent row which always exists;
• use an advisory lock derived from the owner;
• run the operation at a serialization level which detects the conflict;
• allocate active ownership in a dedicated table; or
• send all commands for an owner through one ordered processor.

Each has a legitimate place. A parent-row lock works well when all writers share the transaction and the parent is not a contention hotspot. Advisory locks can express a missing-row predicate, but every writer must use the same key and release behavior. Serializable transactions protect broader predicates, usually with retry and throughput costs. Ordered processing helps when ownership already lives in a stream, but the ordering boundary must cover every writer.

For one equality invariant in one relational database, a unique key is often the smallest reliable mechanism. It uses the write boundary the data already shares and leaves the losing writer with an unambiguous signal.

The reproduction makes both interleavings visible

The DATA-01 fixture is a deterministic model, not a database benchmark. It runs the naive sequence with two successful observations and two accepted inserts. It then runs the same competing writes through the modeled constraint and accepts exactly one.

It also exercises the less dramatic parts of the repair:

• terminal transition releases the key;
• a later active record can then acquire it;
• an ownerless active write is rejected;
• an unresolved duplicate set blocks migration;
• explicit resolution keeps both legacy rows while naming one canonical row; and
• new writes cannot request the legacy exemption.

The fixture cannot prove the behavior of a particular database engine, transaction isolation level, trigger, migration, or private implementation. The retained tests provide evidence for that implementation. The public model exists so the argument can be inspected without publishing it.

Constraints should make the impossible expensive to reintroduce

A database constraint is not automatically the right answer. It cannot enforce an invariant spread across independent databases. It may be too rigid during a deliberate compatibility window. Some rules depend on remote facts which the database cannot know. Some are expensive predicates which need a different model rather than a clever check.

The decision turns on three questions:

Is this state invalid regardless of which writer produced it?
Can the shared write boundary observe every competing transition?
Can a rejected writer recover or report the conflict deliberately?

If the first answer changes, revise the invariant. If writers move across independent stores, introduce coordination at the new shared boundary. If a migration requires temporary invalid state, make that window explicit and remove the escape path afterward.

The useful property is not that invalid state becomes mathematically impossible. Operators can disable constraints, defects can live outside their scope, and future migrations can change the rules. The useful property is that ordinary application paths can no longer create the known contradiction by accident.

Two workers may still observe the same empty state. With the invariant at the shared write boundary, only one of them gets to make that observation obsolete.