The incident that made me respect foreign key locking involved a statuses table with eleven rows. Every order in the system referenced a status, every status transition updated the order row, and at peak, throughput on order updates fell off a cliff with lock wait timeouts pointing at the statuses table. Eleven rows. The foreign key checks everyone assumed were free were taking shared locks on those parent rows, and under enough concurrency those eleven rows became the hottest real estate in the database.
Foreign keys are good. I keep them on by default and I will argue for their integrity value below. But InnoDB's implementation has a locking footprint almost nobody budgets for, and it deserves a clear-eyed look on MySQL 8.0 and 8.4.
What an FK check actually does
When you INSERT a child row, or UPDATE one in a way that changes the foreign key columns, InnoDB must verify the parent row exists. It does this with a locking read on the parent — effectively a SELECT ... FOR SHARE on the matching parent record, taking a shared record lock. That lock is held to the end of the transaction, not the end of the statement. When you DELETE a parent or UPDATE its key, InnoDB must check (or cascade to) the children, which means locking reads down the child side; if the child's foreign key columns are not indexed, that check becomes a full scan of the child table. InnoDB auto-creates a child index when you define the constraint, which saves you from the worst case — and then it guards that index: dropping the last index a foreign key depends on is refused with error 1553, "needed in a foreign key constraint." I have watched that refusal surprise more than one developer who judged the auto-created index redundant next to some wider composite. It is the server telling you the index is load-bearing for every parent delete; the scan it prevents is the ugly kind.
Two details matter under concurrency. The FK check is a locking read regardless of your isolation level — running READ COMMITTED does not make it lock-free, it only relaxes the gap behavior. And under the default REPEATABLE READ, the child-side check that a parent delete or a parent-key update performs can take next-key locks on the child index, and those block other transactions inserting child rows into the checked range. Ordinary child inserts take shared locks on the parent row and coexist with each other happily — the collision that bites is child writes meeting a parent delete, not child inserts meeting one another. The gap-lock side of that story is in the gap lock reduction post.
Hot reference tables: the serialization point
Put the mechanics together and the failure shape writes itself. Any table that a huge fraction of your writes references — statuses, currencies, the tenant or account row everything hangs off — accumulates shared locks from every child insert and update in flight. Shared locks are compatible with each other, so reads mostly scale. The collision arrives when something needs to write the parent: a status rename, a balance update on the account row, a cleanup job. That write wants an exclusive lock, which must wait behind every open transaction holding a shared lock from an FK check, and every new child insert queues behind the waiting writer. Throughput on unrelated child rows drops toward zero because of a lock on a row they merely reference.
This is the contention that never names itself. The lock wait timeout says "lock wait on statuses," the application team says "nobody writes to statuses," and everyone stares at the child table's indexes. Finding the real waiter is one query away:
SELECT
r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread,
dl.object_schema,
dl.object_name,
dl.index_name,
dl.lock_type,
dl.lock_mode
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
JOIN performance_schema.data_locks dl
ON dl.engine_lock_id = w.requesting_engine_lock_id;More on reading those tables in the InnoDB lock waits post, and the full after-incident workflow in the deadlock postmortem post.
Cascades: the multiplier nobody models
ON DELETE CASCADE and ON UPDATE CASCADE move the work from your application into the engine, invisibly. Deleting one parent row can delete two million child rows inside your transaction: two million row locks, two million undo records, two million changes to replicate, all attributed to a one-line DELETE. I have watched a single harmless-looking cleanup statement balloon into a transaction that lagged every replica and pinned the history list for an hour — the purge lag post covers what that does next. Cascades also compose: one constraint cascades into a table that has its own cascade into a third, and the total lock footprint is the product. And because the work happens inside the statement, monitoring keyed on your application's query patterns sees nothing unusual until the locks start colliding.
My rule for cascades: fine for genuinely owned, bounded child sets — an order and its dozen line items. Never for unbounded sets like events, logs, or anything a marketing campaign can multiply by a thousand overnight.
Enforcing in the app instead: the honest trade
So when do I take FK enforcement out of the database? Rarely, and for specific reasons. High-churn queue and event tables where the integrity cost of an orphan is near zero and the locking cost is real. Hot reference contention that indexing and transaction hygiene cannot fix, where the parent is effectively immutable and the check is pure overhead. Cross-shard designs where the parent will not live in the same database much longer. And bulk-load pipelines that validate once up front rather than per row.
If you drop the constraint, you own the invariant. Enforce it in the application's write path, and back it with a periodic orphan sweep that pages someone when it finds anything:
SELECT c.id
FROM orders c
LEFT JOIN statuses s ON s.id = c.status_id
WHERE c.status_id IS NOT NULL
AND s.id IS NULL
LIMIT 100;Run something like that on a schedule and alert on any row returned. The day it returns a row, your application has a bug the database used to reject. That is the trade, stated plainly: foreign keys make whole classes of bug impossible at the cost of locking overhead; app-side enforcement removes the overhead and makes those bugs your job to catch. For most tables in most systems, I keep the constraint, index the child columns, keep transactions short — the long transaction post is your friend there — and reserve drops for the tables that have earned them.
Lock visibility without the 3am mystery
Lock waits deserve better than a lucky sample of the process list at the wrong moment. MonPG ships that kind of lock visibility for PostgreSQL today — waits graphed by object, blocking chains reconstructed instead of guessed at — and the same design, informed by incidents like the statuses table, is going into the MySQL support that is on the way and being built now. MonPG does not monitor MySQL yet; when it does, "eleven-row lookup table melts the order pipeline" should be a screen you pull up, not a mystery you solve at 3am. The PostgreSQL version is at MonPG for PostgreSQL, and the rest of this series is on the blog.