- delete WAITING FOR lock_mode X locks rec but not gap
- delete WAITING FOR lock_mode X, HOLDS lock_mode X
- 隔离级别：RR、RC
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 1E7D49CDD, ACTIVE 69 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 1
MySQL thread id 1385867, OS thread handle 0x7fcebd956700, query id 837909262 10.246.145.78 im_mobile updating
delete from offmsg_0007 WHERE target_id = 'Y25oaHVwYW7mmZbmmZblpKnkvb8=' and gmt_modified <= '2012-12-14 15:07:14'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 203 page no 475912 n bits 88 index `PRIMARY` of table `im_mobile`.`offmsg_0007` trx id 1E7D49CDD lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1E7CE0399, ACTIVE 1222 sec fetching rows, thread declared inside InnoDB 272
mysql tables in use 1, locked 1
1346429 lock struct(s), heap size 119896504, 11973543 row lock(s), undo log entries 1
MySQL thread id 1090268, OS thread handle 0x7fcebf48c700, query id 837483530 10.246.145.78 im_mobile updating
delete from offmsg_0007 WHERE target_id = 'Y25oaHVwYW7niLHkuZ3kuYU5OQ==' and gmt_modified <= '2012-12-14 14:13:28'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 203 page no 475912 n bits 88 index `PRIMARY` of table `im_mobile`.`offmsg_0007` trx id 1E7CE0399 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 203 page no 1611099 n bits 88 index `PRIMARY` of table `im_mobile`.`offmsg_0007` trx id 1E7CE0399 lock_mode X waiting
CREATE TABLE `msg` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`target_id` varchar(100) COLLATE utf8_bin NOT NULL ,
`flag` tinyint(4) NOT NULL ,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`datablob` blob,
`nickname` varchar(64) COLLATE utf8_bin DEFAULT NULL ,
`source` tinyint(4) DEFAULT NULL ,
PRIMARY KEY (`id`),
KEY `idx_o_tid` (`target_id`,`gmt_modified`,`source`,`flag`)
) ENGINE=InnoDB| Session 1 | Session 2 |
|---|---|
| delete from offmsg_0007 WHERE target_id = 'Y25oaHVwYW7niLHkuZ3kuYU5OQ==' and gmt_modified <= '2012-12-14 14:13:28' | delete from offmsg_0007 WHERE target_id = 'Y25oaHVwYW7mmZbmmZblpKnkvb8=' and gmt_modified <= '2012-12-14 15:07:14' |
从死锁日志里可以看出事务二已经持有了 page no 475912 主键上的 next-key 锁，并想要继续获取 page no 1611099 主键上的 next-key 锁，并且这个事务状态为 fetching rows，可以推测出事务二正在走主键扫描。而事务一要获取的锁为主键上的记录锁（lock_mode X locks rec but not gap），这是 delete 语句走二级索引正常情况下的加锁模式。
那么为什么事务二没有走二级索引？原因很可能是事务二的 WHERE 条件匹配的数据行太多，这种情况 MySQL 的优化器会认为走二级索引回表效率低，还不如直接走主键全表扫描。
虽然两个 delete 语句很类似，但是由于走不同索引，对主键索引的加锁顺序的不一致将导致死锁。