mysql select insert 哪个占内存_mysql - 多个SELECT…FOR UPDATE，延迟INSERT INTO - 堆栈内存溢出...

我在单独的连接死锁中遇到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将间隙锁定id s，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)