并发 INSERT 和 SELECT 导致的 MySQL 死锁

Question

• MySQL版本:5.6
• 存储引擎:InnoDB

当两个任务试图选择然后插入同一个表时发生死锁。过程如下:

          Task_1       Task_2
          ------      ------
Phase 1 | SELECT      SELECT
Phase 2 | INSERT      INSERT

SELECT count(id) from mytbl where name = 'someValue' and timestampdiff(hour, ts, now()) < 1;
INSERT mytbl (id, name, ts) values ('newId', 'anotherValue', now());

死锁日志如下(删减了一些细节):

------------------------
LATEST DETECTED DEADLOCK
------------------------
151225  8:22:17
*** (1) TRANSACTION:
TRANSACTION 0 746402, ACTIVE 0 sec, process no 4690, OS thread id 140411390486272 inserting
mysql tables in use 1, locked 1
LOCK WAIT 1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3909, query id 31751474 10.20.36.38 mydb update
INSERT INTO mytbl -- truncated
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`mytbl` trx id 0 746402 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 0 746449, ACTIVE 0 sec, process no 4690, OS thread id 140411389953792 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3906, query id 31751477 10.20.36.38 mydb update
INSERT INTO mytbl  -- truncated
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 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:
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 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;;

*** WE ROLL BACK TRANSACTION (2)

---

问题

1. 根据 MySQL 手册，简单的 SELECT 语句使用不需要S 锁 的快照读取。 INSERT 语句需要对要插入的单行进行 X 锁定。那为什么Task_2持有S锁导致死锁呢？

---

编辑

SHOW CREATE TABLE的结果如下:

| task_content | CREATE TABLE `mytbl` (
`id` bigint(20) NOT NULL,
`ts` timestamp NULL DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Answer 1

文章here对锁和隔离级别进行了详尽的解释。

感谢@newtover 提供有关隔离级别的线索。我对这篇文章的总结和我自己的问题的答案如下:

InnoDB 中默认的隔离级别是Repeatable Read，它会锁定索引(不锁定数据表)直到事务结束。

在我的情况下，唯一的索引是 PRIMARY，它在我的 SELECT 查询中没有用(可以通过 explain select... 验证) >).结果，PRIMARY 索引中的所有 条目都被锁定。当 TXN_2 等待某个条目上的 X 锁 时，该条目被 TXN_1 保留的 S 锁 锁定>。同样，TXN_1 等待另一个条目上的 X 锁，但该条目也被自身保留的 S 锁 锁定。 "one S two X"发生死锁。

相比之下，在 name 列上创建索引 name 后，索引 name 将在 SELECT 中使用 语句(可以通过 explain select ... 验证)，因此锁将在索引 name 而不是 PRIMARY。更重要的是，SELECT 语句只会对等于 someValue 的条目发出 S 锁，而不是索引 name 。此外，INSERT 所需的IX 锁 和X 锁 将在索引PRIMARY 上发出。 S锁与IX锁、X锁的冲突将得到解决。

列 name 上的索引不仅加快了查询速度，更重要的是防止锁定索引的所有条目。