mysql - 多个 SELECT ... FOR UPDATE 延迟 INSERT INTO

Question

我遇到了 SELECT ... FOR UPDATE 和 INSERT INTO 语句在单独连接死锁方面的问题。

给定一个带有主键id的空表tblFoo，考虑以下伪代码:

function locate(array values) {

    BEGIN TRANSACTION;

    rows = SELECT * FROM tblFoo WHERE id IN values FOR UPDATE;

    if (rows is empty) {
        sleep(10); // i.e., do some stuff
        rows = INSERT INTO tblFoo (id) VALUES values;
    }

    COMMIT;

    return rows;
}

在进程 A @t=0 上:return locate([1,2,3]);

在进程 B @t=1 上:return locate([1]);

---

我的期望是，进程 1 将使用 ids 1、2、3 锁定行，这会在 SELECT ... FOR UPDATE 处阻止进程 B，直到进程 A事务已提交。一旦提交，进程 B 就会解除阻塞并返回 id 为 1 的行，该行刚刚由进程 A 插入。

观察到的行为是遇到死锁，导致进程 A 回滚，进程 B 插入 id 为 1 的行。

谁能帮我理解为什么 MySQL 会这样？

我在 MySQL 5.5 版中使用 innoDB。

编辑:下面是表结构

CREATE TABLE `tblFoo` (
    `id` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

编辑 2:以下是详细说明死锁的 innoDB 状态

------------------------
LATEST DETECTED DEADLOCK
------------------------
161205 15:55:50
*** (1) TRANSACTION:
TRANSACTION 32A3E743A, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 12243323, OS thread handle 0x7fd7dd47f700, query id 4713227035 localhost root update
INSERT INTO test.tblFoo (id) VALUES (1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1556528 n bits 72 index `PRIMARY` of table `test`.`tblFoo` trx id 32A3E743A lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00032a3e5f6b; asc   *>_k;;
 2: len 7; hex b30017d06b0110; asc     k  ;;

*** (2) TRANSACTION:
TRANSACTION 32A3E5FD3, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 5 row lock(s)
MySQL thread id 12243319, OS thread handle 0x7fd7f0097700, query id 4713230393 localhost root update
INSERT INTO test.tblFoo (id) VALUES (1),(2),(3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1556528 n bits 72 index `PRIMARY` of table `test`.`tblFoo` trx id 32A3E5FD3 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00032a38e424; asc   *8 $;;
 2: len 7; hex cc001c166a0110; asc     j  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00032a3e5f6b; asc   *>_k;;
 2: len 7; hex b30017d06b0110; asc     k  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1556528 n bits 72 index `PRIMARY` of table `test`.`tblFoo` trx id 32A3E5FD3 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00032a3e5f6b; asc   *>_k;;
 2: len 7; hex b30017d06b0110; asc     k  ;;

*** WE ROLL BACK TRANSACTION (2)

Answer 1

我认为正在发生的事情是，您开始了 2 笔交易。两者都在同一张表上获得“select ... for update”。您会期望第二个事务在更新之前等待。但是来自文档 https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html听起来好像不会(这是您看到的行为)。所以两个“selects ...for update”互相阻塞，让你陷入僵局。