MySQL:INSERT被外键引用行的UPDATE阻止 [英] MySQL: INSERT blocked by an UPDATE of the foreign key referenced row

问题描述

让我以一个SQL示例开始我的问题.

Let me start my question with an SQL example.

这是表格设置:

1. 创建表x和y.用y.x指的是x.id.
2. 将一行插入x(id = 1).

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;

现在开始事务(Trx A)以更新x中的行.

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

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

在提交之前，我正在启动另一个事务(Trx B)，以在y中插入一行.

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.

问题是-预计Trx B会在此时被挂起吗?为什么以及任何解决方法?

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

这已经在MySQL 5.7.21，Percona 5.7.21，MariaDB 10.2.14上进行了测试

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

推荐答案

是的.

Trx A在记录上具有排他锁(X)，因为它正在对其进行更新.

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

Trx B必须在所有外键引用上获取共享模式(S)锁，以确保满足约束条件.它等待Trx A释放其X锁定.

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.

没有办法避免这种情况并保持引用完整性.如果您设法禁用锁定，MySQL将无法保证所引用的行存在.

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.

这篇关于MySQL:INSERT被外键引用行的UPDATE阻止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！