MySQL: INSERT blocked by an UPDATE of the foreign key referenced row

Question

Let me start my question with an SQL example.

Here is the table setup:

1. Create table x and y. With y.x refers to x.id.
2. Insert a row into x (id=1).

---

START TRANSACTION;
CREATE TABLE `x` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `value` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)  ENGINE=INNODB;
CREATE TABLE `y` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `x_id` INT(11) NOT NULL,
    `value` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_x` FOREIGN KEY (`x_id`)
        REFERENCES `x` (`id`)
)  ENGINE=INNODB;

INSERT INTO x values (1,123456);
COMMIT;

Now start a transaction (Trx A) to update the row in x.

START TRANSACTION;
UPDATE x SET value=value+1 WHERE id = 1;

Before it is committed, I am starting another transaction (Trx B) to insert a row to y.

START TRANSACTION;
INSERT INTO y VALUES (null,1,123456);
---- HANGED ----
-- Until Trx A is committed or rolled-back, the Trx B is hanged here.

The question is - is it expected that Trx B to be hanged at that point? Why and any way to workaround that?

This has been tested on MySQL 5.7.21, Percona 5.7.21, MariaDB 10.2.14

Answer 1

Yes it is expected.

Trx A has an exclusive lock (X) on the record because it is updating it.

Trx B has to acquire a shared mode (S) lock on all foreign key references, to ensure that the constraints are met. It waits for Trx A to release its X lock.

There is no way to avoid this and keep the referential integrity. If you manage to disable the locking, MySQL won't be able to guarantee that the referenced row exists.

The usual workaround is to remove the foreign keys.