innodb之死锁分析：读和删造成的死锁

一个例子的死锁分析：

环境：innodb引擎，RC隔离级别;

死锁信息：

RECORD LOCKS space id 0 page no 1492482 n bits 904 index `unit_id` of table `51fshenzhen`.`t_refresh_queue` trx id EB9C4A64 lock_mode X locks rec but not gap

表如下：

CREATE TABLE `t_refresh_queue` (
  `refresh_queue_id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_type` tinyint(1) DEFAULT '0',
  `unit_id` int(11) DEFAULT '0',
  `refresh_time` int(11) DEFAULT '0',
  `broker_id` int(11) DEFAULT '0',
  `agent_id` int(11) DEFAULT '0',
  `company_id` int(11) DEFAULT '0',
  `sector_id` int(11) DEFAULT '0',
  PRIMARY KEY (`refresh_queue_id`),
  UNIQUE KEY `unit_id` (`unit_id`,`unit_type`,`refresh_time`),
  KEY `index_broker_id` (`broker_id`),
  KEY `index_refresh_time` (`refresh_time`)
) ENGINE=InnoDB AUTO_INCREMENT=175716299 DEFAULT CHARSET=gbk;

删除SQL：

DELETE FROM `t_refresh_queue` 
WHERE `unit_id` IN ('1451914','1486249','1486175','1451826','1447935','1447807','1447624','1440273','1469855')   AND `unit_type` =  '1';

插入SQL：

INSERT INTO `t_refresh_queue` (`unit_type`, `unit_id`, `refresh_time`, `broker_id`, `agent_id`, `sector_id`, `company_id`) 
VALUES  ('1', '1451914', '1399806000', '74146', '0', '0', '2758'), 
('1', '1451914', '1399811400', '74146', '0', '0', '2758'), 
('1', '1451914', '1399816200', '74146', '0', '0', '2758'), 
('1', '1451914', '1399818900', '74146', '0', '0', '2758'), 
('1', '1451914', '1399823400', '74146', '0', '0', '2758'), 
('1', '1486249', '1399806000', '74146', '0', '0', '2758'), 
('1', '1486249', '1399811400', '74146', '0', '0', '2758'), 
('1', '1486249', '1399816200', '74146', '0', '0', '2758'), 
('1', '1486249', '1399818900', '74146', '0', '0', '2758'), 
('1', '1486249', '1399823400', '74146', '0', '0', '2758'), 
('1', '1486175', '1399806000', '74146', '0', '0', '2758'), 
('1', '1486175', '1399811400', '74146', '0', '0', '2758'), 
('1', '1486175', '1399816200', '74146', '0', '0', '2758');

首先、需要明确下面几个内容：

（1）RC禁止了gap锁

（2）in子句是索引最左前缀，因此delete不走索引；

SQL执行过程分析

1、删除操作的sql;

DELETE FROM `t_refresh_queue` WHERE `unit_id` IN ('1451914','1486249','1486175','1451826','1447935','1447807','1447624','1440273','1469855') and `unit_type` = '1';

index：unit_id子句IN，不走索引，只能进行全表扫描；

删除执行过程如下：首先进行全表扫描，便利每一条记录，匹配unit_id，unit_type，并对所有便利过的行加X锁；

锁 refresh_queue_id unit_id 　unit_type
X  rqid1            1447935　　1
X  rqid2            1486175　　1
X  rqid4            1486249　　1
X  rqid7            1440273　　1
X  rqid8            1451914　　1
X  rqid9            1469855　　1
X  rqid23           1447807　　1
X  rqid34           1451826　  1
X  rqid45           1447624　　1

在具体以[unit_id,unit_type]为（1451914,1）的行为例来说明：

具体sql变为：DELETE FROM `t_refresh_queue` WHERE `unit_id` IN ('1451914') AND `unit_type` = '1';为了保留不走索引的特点，还采用in。

锁 refresh_queue_id unit_id unit_type
X ...
X rqid80 　　　　　　 1451913 1
X rqid81 　　　　　　 1451914 1
X ...
X rqid83 　　　　　　 1451914 1
X ...
X rqid88 　　　　　　 1451914 1
X rqid89 　　　　　　 1451914 1
X rqid90 　　　　　　 1451915 1
X ...

再对这四条数据进行删除操作，保持行级X锁，获取页级X锁，标记对应页为dirty，等待之后的purge的时候进行物理删除，释放行级X锁，释放页级X锁;

2、对于插入操作

执行插入操作的时候，走索引unit_id，并且是完全覆盖唯一索引。继续来以unit_id=1451914来分析，对于下面五条数据，插入过程：

('1', '1451914', '1399806000', '74146', '0', '0', '2758')　　　　　　　　  键值:[1451914,1,1399806000]
('1', '1451914', '1399811400', '74146', '0', '0', '2758')                键值:[1451914,1,1399811400]
('1', '1451914', '1399816200', '74146', '0', '0', '2758')                键值:[1451914,1,1399816200]
('1', '1451914', '1399818900', '74146', '0', '0', '2758')                键值:[1451914,1,1399818900]
('1', '1451914', '1399823400', '74146', '0', '0', '2758')                键值:[1451914,1,1399823400]

首先在索引unit_id中查找对应键值，顺序为[unit_id,unit_type,refresh_time]

其中Index key：unit_id，unit_type，refresh_time  决定了插入范围 [1451914,1,1399805999]~[1451914,1,1399806001]

　　Index filer：is null

　　TABLE FILE：broker_id，agent_id，sector_id，company_id

首先查看是否存在[1451914,1]，存在，则查找[1451914,1,1399805999]~[1451914,1,1399806001]中是否存在[1451914,1,1399806000]，并且在[1451914,1,1399805999]和[1451914,1,1399806001]索引键值上添加S锁。结果如下：

锁 unit_id unit_type refresh_time refresh_queue_id
S  1451914 1 　　　　 1399805999 　　 rqid8100001
...
S  1451914 1 　　　　 1399806001 　　 rqid8100010

索引中有[1451914,1,1399806000]：二级索引不用发生改变，在[1451914,1,1399806000]上加上S锁，并返回refresh_queue_id id值，并且根据TABLE FILE信息来匹配；所有便利过的行添加X锁，对该位置的数据页添加X锁，写入数据，释放数据页的X锁，对数据行添加X锁，进行下一行；

索引中没有[1451914,1,1399806000]：二级索引需要发生改变。获取数据页X锁，写入行数据，主键ID自增，释放数据页X锁，添加主键X锁，修改二级索引，加入[1451914,1,1399806000]值其他二级索引值该挪的挪位置。

知道这个过程后，我们来看如何发生的死锁：