PATTERN Cited by 1 source

NOWAIT lock for cascade-select¶

Problem¶

When application-level cascade orchestration issues the initial SELECT ... FOR UPDATE to lock parent rows before cascading child-side changes, it must acquire enough of a lock to prevent a concurrent insert from slotting a new matching row in between the select and the cascade. On a unique index, that "enough" is a next-key lock — record lock + the gap around it.

Vitess's cascade-select in early foreign-key support was missing the gap-lock portion on unique indexes, allowing this race:

Session A: SELECT col FROM t WHERE col IN (5) OR id IN (3) FOR UPDATE — acquires only the record lock, no gap lock.
Session B: inserts a new row matching col = 5. Session B expects to wait (and would, if Session A held a gap lock). Without the gap lock, Session B may or may not block — the outcome depends on MySQL's record-lock interaction with Session B's insert semantics.
Session A proceeds with its cascade using the rows it read, unaware of Session B's new matching row.
After A commits, the new row in B's transaction exists but was never included in the cascade — incomplete cascade, binlog missing an event, downstream CDC consumers drift.

Discovered during PlanetScale's work adding REPLACE INTO support for foreign keys. The missing gap-lock manifested as "incorrect results, and further, plan execution would result in missing cascading rows, leading to incomplete data in the binary logs."

Solution¶

Use NOWAIT locks for the cascade-select. MySQL 8's NOWAIT clause on SELECT ... FOR UPDATE changes the lock acquisition semantics from "wait for the lock to be available" to "acquire the lock immediately, or fail". Verbatim from the post:

"As a solution, we went ahead using the NOWAIT lock to promptly acquire the lock for cascade selection. NOWAIT ensures immediate lock acquisition or failure, which may result in more foreign key-related DMLs failing, necessitating query or transaction rollback. This approach, however, effectively addresses the problem of lock waiting and prevents incorrect results." (Source: sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints)

Applied shape (approximate):

-- Vitess cascade-select step, with NOWAIT
SELECT col FROM parent WHERE id = 7 FOR UPDATE NOWAIT;
-- If the lock is unavailable (e.g. gap held by another session),
-- the statement fails immediately. The client / Vitess rolls
-- back the cascade transaction and retries.

Why fail-fast is the right choice here¶

Three reasons fail-fast outperforms fail-slow (blocking indefinitely) for FK cascade contexts:

Cascade transactions hold significant locks — the parent select plus child DMLs plus (recursive) grandchild DMLs. Waiting on a gap lock while holding all those downstream locks multiplies deadlock risk.
Retry on the client side is cheap. Cascade operations are typically small (one parent row, a handful of child rows). Retrying from scratch after a NOWAIT abort costs orders of magnitude less than the knock-on deadlock cost of waiting.
The alternative — adding explicit gap locks to the SELECT — is harder to get right. Gap-lock semantics depend on isolation level, index type (unique vs secondary), and the predicate shape. Relying on MySQL to acquire them correctly under all FK-cascade shapes proved fragile; NOWAIT sidesteps the whole question by saying "if anything might interfere, abort now and retry."

Trade-offs¶

More aborts + retries, as the post acknowledges: "may result in more foreign key-related DMLs failing, necessitating query or transaction rollback." The client must be prepared to retry; applications that expect silent queueing under contention will see surfaced errors instead.
Not a full replacement for careful lock design. Fail-fast is the right reaction when lock acquisition would have to wait, but it doesn't help if the first attempt itself acquires conflicting locks — e.g. two cascade operations racing on disjoint parent rows with a shared grandchild. That class of conflict still deadlocks or needs a different locking order.
Requires MySQL 8+ — NOWAIT clause on SELECT ... FOR UPDATE isn't in MySQL 5.7.

Seen in¶

sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints — canonical wiki home. Noach + Gupta disclose the bug class via a worked example: on a unique-indexed table, a REPLACE INTO-triggered cascade-select + FOR UPDATE didn't block a concurrent DELETE-plus-INSERT in another session, producing inconsistent results. The fix ships NOWAIT on cascade-selects. "This approach, however, effectively addresses the problem of lock waiting and prevents incorrect results."