skip to content

Why does a flush fail on a unique constraint when the same unit of work deleted that row and then inserted a replacement?

level: seniorimportance: should knowfreq 54%

answer

  1. call order is not statement order
  2. derived from the set, not the history
  3. grouped by kind, ordered by dependency
  4. inserts commonly precede deletes
  5. flush between to force the sequence

basics

~20 s

The unit of work does not replay statements in call order; it groups them by kind and dependency, sending inserts before deletes. So the replacement meets the original still holding the key. Flushing between them forces the order.

solid answer

~40 s

Statement order at flush is derived from the final state of the tracked set, not from the sequence of calls that produced it. A unit of work typically emits statements grouped by kind and ordered so referential dependencies hold — parents inserted before children, children deleted before parents — and inserts commonly precede deletes. So the insert of the replacement is sent while the deleted row is still present, and the unique index rejects it. The fixes, best first: update the existing row instead of deleting and re-creating it; flush between the delete and the insert so the delete really goes first; or, where the engine supports deferring constraint checks to the end of the transaction, let both statements land before the check runs.

code

pseudocode · 7 lines
pseudocode
remove(load(Member, 11))                      // currently holds (team 7, slot "lead")
add(Member(team = 7, slot = "lead", person = 42))

flush(trackedSet)
// sent:  INSERT INTO member (team_id, slot, person_id) VALUES (7, 'lead', 42)
//        -> rejected by the unique index on (team_id, slot)
// never: DELETE FROM member WHERE id = 11

go deeper

for a junior

Take away one fact: the order your code calls delete and insert is not the order the statements reach the database. The layer decides that at flush time.

for a middle

Explain the derivation — statements grouped by kind, ordered so referential dependencies hold — and why that makes an insert land before a delete that freed its key.

for a senior

Diagnose from emitted statements rather than source order, and pick a fix with its cost stated: update in place, an explicit flush between, or a deferred constraint check where supported.

for a principal

Question the model. A unique constraint over a value that legitimately moves between rows will keep generating these; decide whether the key design or the write pattern is the thing to change.

## The order you wrote is not the order sent When code runs against a tracked set, each call changes the *set*, not the database. A delete marks an object for removal; an insert adds a new object to be written. Nothing has been sent. At the flush, the layer looks at the set it now holds and derives a statement sequence from it — from the **state**, not from the history of calls that produced that state. That derivation follows rules of its own: - **Dependency first.** Rows that others point at are inserted before the rows that point at them, and deleted after them, so referential integrity holds at every step. - **Grouped by kind.** Statements of the same kind are emitted together, and the kinds run in a fixed sequence. Inserts commonly go first and deletes last, because that order is the safe one for the parent/child case the rule exists to protect. - **Grouped by shape.** Like statements against the same table travel together, which is what makes sending them in groups possible at all. - **Coalesced.** Several changes to one object become one statement; an object created and removed before the flush may produce nothing. None of those rules knows the intent "free this key, then take it". ## The collision A table has a unique index over, say, `(team_id, slot)`. The code removes the row holding that slot and adds a replacement with the same slot. At flush: 1. the insert is sent — the old row is still there, so the unique index rejects it 2. the delete never runs, because the flush already failed The error names a constraint the code was careful to respect, at a line that is nowhere near either operation, which is why this one costs people an afternoon. | what the code expressed | what the flush sent | |---|---| | delete the row holding the key | `INSERT` with the key | | insert a row taking the key | `DELETE` of the old row — never reached | The same shape appears whenever a unique value moves rather than being created: swapping two positions in an ordered list, moving a child to another parent under a unique `(parent, position)`, or reusing a natural key after a logical replacement. ## Fixes, best first 1. **Update in place.** If the row is conceptually the same slot with new contents, one `UPDATE` expresses that and never contends with itself. This is usually the right answer, and it also removes a needless delete/insert pair from the write set. 2. **Flush between the two operations.** The delete is sent immediately, then the insert is accumulated and sent by a later flush. Correct, explicit, and cheap to read — but it costs the reordering and grouping the layer would otherwise have applied, and it takes the delete's locks earlier. Say why in a comment; this line looks removable to the next reader. 3. **Defer the constraint check.** Where the engine supports checking a constraint at the end of the transaction rather than per statement, both statements land and the check runs once, on the final state. It is the cleanest expression of "the intermediate state was never meant to be valid", but it is engine-dependent and it moves the error to the boundary. 4. **Reconsider the key.** A unique constraint over a value that legitimately moves between rows will keep producing this. A surrogate identity with the moving value handled as an update is a structural fix rather than a workaround. What is *not* a fix: dropping the constraint, adding a second index, retrying the flush, or sleeping. The constraint is doing its job, and a retry sends the same statements in the same order. ## The general lesson Statement order is **derived state**. If correctness depends on statement A reaching the database before statement B, one of two things must be true: the dependency is one the layer already understands (a parent/child link it can see), or you force it yourself with a flush between them. Depending on call order because it happened to work is depending on an implementation detail that can change with a mapping change, a version bump, or the addition of an unrelated row to the same flush. The diagnostic habit worth keeping: when a constraint error names a rule your code appears to honour, stop reading the code's order and get the actual statement order the layer emitted. The gap between the two is the bug, every time.

  • Why do layers order statements by dependency instead of just replaying call order?
    Because call order is frequently invalid at the database. Application code creates a child and its parent in whatever sequence reads well, while the engine requires the referenced row to exist first and to be removed last. Deriving the order from dependencies makes the common case work without the author thinking about it — at the cost of the rarer case where the author's order was the meaningful one.
  • What does the extra flush cost, since it fixes the problem?
    It splits one write into two round trips, gives up the grouping the layer would have applied across them, and takes the delete's row locks earlier, so they are held for longer. On a hot table that widens the contention window. It is a fair trade for correctness, but it is a trade, not a free fix.
  • How do you confirm this diagnosis rather than guess it?
    Capture the statements the layer actually emitted for that transaction and read their order. If the insert appears before the delete, the diagnosis is settled. Reasoning from the source is exactly what fails here, because the source expresses intent and the flush expresses derived order.

saying these in an interview costs you the question

  • Assumes statements are sent in the order the code called them.
  • Blames the database for enforcing a unique index mid-transaction.
  • Retries the flush and expects a different statement order.
  • Drops or duplicates the constraint instead of ordering the writes.
  • Thinks reordering happens only for parent-child links, never same-table.
  • Believes committing rather than flushing would avoid the conflict.