PATTERN Cited by 1 source

Application-level cascade orchestration¶

Problem¶

A proxy or clustering layer sitting above MySQL/InnoDB must apply FK cascading actions (ON DELETE CASCADE, ON UPDATE CASCADE, ON DELETE SET NULL, ON UPDATE SET NULL) in a way that every child-side write reaches the binlog. InnoDB does the right thing internally — but its child-row changes are invisible to binlog consumers (see concepts/innodb-silent-cascade-in-binlog). Any CDC tool, any replica with FK configuration differing from the primary, any snapshot-plus-catchup migration tool will miss the cascaded deletes/updates and drift.

The proxy can't fix this by patching InnoDB (high risk, doesn't generalise cross-shard). It has to own cascade logic itself — replacing InnoDB's single-statement cascade with explicit, ordered, logged operations.

Solution¶

Orchestrate the cascade above MySQL, splitting what used to be one parent-side statement into a locked select + explicit child DML + parent DML, all in one transaction. The canonical shape for ON DELETE CASCADE, verbatim from PlanetScale's Shlomi Noach + Manan Gupta (2023):

Lock the affected parent rows (prevent concurrent modification that would invalidate the cascade):

SELECT col FROM parent WHERE id=7 FOR UPDATE

This reads the FK column values and acquires write locks on the matching parent rows.

Delete from the child first (so the child deletes are explicitly logged):

DELETE FROM child WHERE col IN (<parent_column_values>)

If the child is itself a parent to a grandchild with ON DELETE CASCADE, recurse — lock the relevant child rows, delete grandchildren first, then the child.

Delete the parent row (InnoDB sees no matching children; nothing to cascade):

DELETE FROM parent WHERE id=7

All three steps in a single transaction so the operation is atomic. If any step fails, the whole thing rolls back.

For ON UPDATE CASCADE the sequencing differs subtly — the child must be updated with the new parent value before the parent is updated, which means turning off FOREIGN_KEY_CHECKS=0 for the statement (so MySQL doesn't reject the child update for violating the current FK relationship). The proxy then becomes responsible for validating grandchild RESTRICT constraints that the FOREIGN_KEY_CHECKS=0 silenced.

Why it works¶

Every child-side write happens explicitly in the proxy's transaction, so the MySQL binlog records all of them. CDC consumers, replicas with different FK config, and snapshot-plus-catchup migration tools see complete event streams.
Parent-side locks (SELECT ... FOR UPDATE) prevent the classic race where the parent's FK column value changes between the select-for-cascade and the subsequent child DML — without the lock, the cascade could touch rows that no longer "belong" to the parent it's cascading from.
Atomicity in one transaction means either the full cascade applies or none of it does. If the child DML fails (e.g. a grandchild RESTRICT rejects), the parent DML doesn't run either.

Trade-offs¶

Performance cost — extra SELECT ... FOR UPDATE + explicit child DML per FK depth level. The source post frames this as "non-zero performance impact to using foreign key constraints with Vitess/PlanetScale." No specific numbers disclosed.
Lock surface grows — the parent's FOR UPDATE holds write locks on the matched rows for the transaction duration. Under high contention, this can produce deadlocks or lock-wait timeouts where native InnoDB cascades (running entirely inside the storage engine) would not.
Implementation complexity — the cascade walk must handle recursion, self-reference, multi-children, concurrent DMLs, isolation-level interactions, gap locks on unique indexes (see patterns/nowait-lock-for-cascade-select for a bug class), and MySQL's subtle cases like no-op updates and expression-type coercions. The PlanetScale post walks through four concrete bug categories surfaced by their fuzzer.
Application-level FK validation for RESTRICT when using FOREIGN_KEY_CHECKS=0 — because MySQL is silenced, the proxy must re-check RESTRICT constraints itself (e.g. grandchild RESTRICT must hold when parent UPDATE cascades through child).

Not a substitute for native FKs in general¶

This pattern is the right answer when you need FK semantics visible above the storage engine: CDC, cross-shard, replicas with FK differences, snapshot-plus-catchup tooling. For a single-server workload that doesn't feed any of those consumers, native InnoDB enforcement is cheaper and simpler — the cascade-in-binlog gap doesn't matter if nothing downstream cares about the binlog.

Composes with¶

patterns/nowait-lock-for-cascade-select — the SELECT ... FOR UPDATE step must acquire adequate locks (including gap locks on unique indexes) to prevent concurrent inserts racing between select and cascade. Using NOWAIT fails fast instead of blocking indefinitely.
patterns/snapshot-plus-catchup-replication — the broader data-motion pattern that relies on every change being in the binlog; this pattern is what enables that guarantee for FK workloads.
patterns/shadow-table-online-schema-change — becomes compatible with FK tables once cascades are logged explicitly (combined with internal operations tables and rename_table_preserve_foreign_key).

Seen in¶

sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints — canonical wiki home. Noach + Gupta name the pattern implicitly through a step-by-step walkthrough:

"It must first explicitly DELETE FROM child WHERE parent_id=7 — thereby ensuring that any affected rows are recorded in the binary log — and only then issue a DELETE FROM parent WHERE id=7. When faced with this latter DELETE on parent, InnoDB still checks for matching rows in child, but finds none, because Vitess had already purged them." Also specifies the lock discipline, the recursion, the single-transaction atomicity, and the FOREIGN_KEY_CHECKS=0 requirement for cascading updates.