mysql - 在 INSERT INTO SELECT 上找到 mysql innodb 死锁原因

Question

我有一个 java 应用程序，它在下面的 sql 上发生死锁异常:

insert into voucher (
    id,
    order_id,
    voucher_code
) SELECT
    #{id},
    #{orderId},
    #{voucherCode}
    FROM DUAL WHERE NOT EXISTS (SELECT id FROM voucher where order_id =  #{orderId})

order_id 是唯一键。而且我确定在并发执行sql的时候会死锁。但是，我没有足够的权限执行show engine innodb status，所以我无法获取有关死锁异常的信息。

我尝试在实验室环境中重现该问题。表格测试如下:

Create Table: CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`info` varchar(128) NOT NULL DEFAULT '',
`order_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8

然后我在两个不同的 session 中执行两个 sql:

insert into test(info,order_id) select '12345',sleep(10) from dual where not exists (select info from test where info='12345');  
insert into test(info,order_id) select '12345',234 from dual where not exists (select info from test where info='12345'); 

死锁日志如下:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-06-20 17:26:54 700000a83000
*** (1) TRANSACTION:
TRANSACTION 2321, ACTIVE 9 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 1, OS thread handle 0x700000a3f000, query id 29 localhost root executing
insert into test(info,order_id) select '12345',234 from dual where not exists (select info from test where info='12345')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9 page no 3 n bits 72 index `PRIMARY` of table `test`.`test` trx id 2321 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 2320, ACTIVE 10 sec setting auto-inc lock
mysql tables in use 2, locked 2
3 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x700000a83000, query id 28 localhost root User sleep
insert into test(info,order_id) select '12345',sleep(10) from dual where not exists (select info from test where info='12345')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 9 page no 3 n bits 72 index `PRIMARY` of table `test`.`test` trx id 2320 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test`.`test` trx id 2320 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)

我假设子查询 select info from test where info=‘12345’ 可能持有 SLock，而 insert into … select 想要 XLock。但是我没有找到官方文档来支持我的观点。

所以我的问题如下:
1.我的复现设计对吗？
2. 我的假设(子查询 select info from test where info=‘12345’ may hold SLock)对吗？任何官方文件都可以支持我的假设？

Answer 1

当您运行 INSERT ... SELECT 时，默认情况下 MySQL 会锁定 SELECT 中的所有行。如果将隔离级别更改为 READ-COMMITTED，则 SELECT 中的行不会被锁定。在您的情况下，这应该可以解决死锁问题。

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

PS:我建议阅读隔离级别并了解它们之间的区别。

http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.