Hey there! Let's break down why you're hitting that "INSERT blocked by an UPDATE of the foreign key referenced row" error, and walk through how to fix it. First, let's recap your setup to make sure we're aligned:

You created tables x (parent) and y (child with a foreign key x_id pointing to x.id), inserted a row into x (id=1), then ran transactions where an INSERT into y got blocked by an UPDATE on the referenced x row.

This is all about how InnoDB handles row-level locking and foreign key constraints:

• When you insert a row into the child table y, InnoDB has to verify that the referenced x.id actually exists to keep your data consistent. To do this safely, it automatically grabs a shared (S) lock on the matching row in x.
• If another transaction is already running an UPDATE on that same x row, that transaction holds an exclusive (X) lock on the row. Since S locks and X locks can't coexist (they're mutually exclusive), your INSERT into y gets stuck waiting until the UPDATE transaction finishes (commits or rolls back) and releases the X lock.

This isn't a bug—it's normal concurrency control in InnoDB, but it can catch you off guard if you don't expect the lock interaction between foreign key checks and parent table writes.

Here are practical ways to handle this scenario:

Option 1: Adjust Transaction Isolation Level (Carefully) #

If your application can tolerate slightly looser isolation, switch to the READ COMMITTED level:

• Run this before your transactions:

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• With READ COMMITTED, InnoDB uses consistent reads for foreign key checks instead of acquiring S locks. This means your INSERT into y won't block on an ongoing UPDATE to the parent row. Just make sure this isolation level fits your data consistency needs—since it lets transactions see committed changes from other mid-transaction, it's not ideal for all use cases.

Option 2: Shorten Parent Table Update Transactions #

Long-running transactions hold locks longer, increasing the chance of blocking.

• Keep UPDATEs on x as brief as possible. Avoid doing unrelated work in the same transaction as the UPDATE.
• If you're updating multiple rows in x, split the work into smaller transactions to reduce lock hold time.

Option 3: Explicitly Lock the Parent Row First #

If you need to guarantee your INSERT into y doesn't get blocked, explicitly grab an S lock on the x row before inserting:

START TRANSACTION;
-- Grab shared lock on the x row first to reserve access
SELECT * FROM x WHERE id = 1 LOCK IN SHARE MODE;
-- Now insert into y safely
INSERT INTO y (x_id, value) VALUES (1, 100);
COMMIT;

This flips the script: if another transaction tries to UPDATE the x row after you've got the S lock, it will be blocked instead of your INSERT. Just watch out for potential deadlocks if you mix this with other locking operations.

Option 4: Identify & Resolve Long-Running Transactions #

Use this query to find active transactions that might be holding locks on your x row:

SELECT trx_id, trx_started, trx_query FROM INFORMATION_SCHEMA.INNODB_TRX;

If you spot a stuck or long-running transaction holding the X lock, you can either wait for it to complete or (as a last resort in production) kill it with KILL [trx_id];—just be careful not to interrupt critical work.

To confirm locks are the root cause, run this query to see current locks:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

You'll see an X lock on the x row from the UPDATE transaction, and a pending S lock request from your blocked INSERT.

内容的提问来源于stack exchange，提问作者HelloSam