mysql gap_mysql事务死锁案例分析(GAP锁篇)

gap lock(间隙锁)，主要用在mysql中解决RR级别事务隔离的幻读问题

下面是mysql对gap lock给出的官方解释：

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。

在官方详细解析中有提到一个要点，也是导致本次事务死锁的根本原因

Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.

大致意思是：X-lock的gap和S-lock的效果是一样的，都是可以被多个事务获取到。

下面开始先分析gap lock 在唯一索引中的表现

表结构：

建表sql

DROP TABLE IF EXISTS `staff_role_biz_role_id`;

CREATE TABLE `staff_role_biz_role_id` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

`kdt_id` int(11) unsigned NOT NULL COMMENT 'kdtId',

`admin_id` int(11) unsigned NOT NULL COMMENT '有赞id',

`biz` varchar(20) NOT NULL DEFAULT '1' COMMENT '角色类型',

`role_id` int(11) unsigned NOT NULL COMMENT '角色id',

`shop_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'shopId,多门店使用',

`operator` varchar(20) NOT NULL DEFAULT '0' COMMENT '操作人',

`operator_id` int(11) NOT NULL DEFAULT '0' COMMENT '操作人id',

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_kdtId_adminId_biz_roleId` (`kdt_id`,`admin_id`,`role_id`,`biz`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

LOCK TABLES `staff_role_biz_role_id` WRITE;

INSERT INTO `staff_role_biz_role_id` (`id`, `kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`)

VALUES

(1,10,1,'retail',1,0,'0',0,'2017-05-09 15:55:26','2017-05-09 15:55:26'),

(2,20,1,'retail',1,0,'0',0,'2017-05-09 15:55:40','2017-05-09 15:55:40'),

(3,30,1,'retail',1,0,'0',0,'2017-05-09 15:55:55','2017-05-09 15:55:55'),

(4,40,1,'retail',1,0,'0',0,'2017-05-09 15:56:06','2017-05-09 15:56:06'),

(5,50,1,'retail',1,0,'0',0,'2017-05-09 15:56:16','2017-05-09 15:56:16');

UNLOCK TABLES;

执行sql(多个session模拟并发,请按照sql前的序号来执行相应的sql)

场景一：唯一索引，delete条件无任何命中，然后执行insert

session A:

begin;

1.

delete from staff_role_biz_role_id where kdt_id = 15 and admin_id = 1 and biz = 'retail' and role_id = '1';

4.

INSERT INTO `staff_role_biz_role_id` (`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES ('15', '1', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

session B:

begin;

2.

delete from staff_role_biz_role_id where kdt_id = 18 and admin_id = 2 and biz = 'retail' and role_id = '1';

3.

INSERT INTO `staff_role_biz_role_id` (`kdt_id`, `admin_id`, `biz`, `role_id`, `shop_id`, `operator`, `operator_id`, `create_time`, `update_time`) VALUES ('18', '2', 'retail', '2', '0', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

当执行完第4步时，会出现如下的死锁提示

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

相关事务锁信息

INNODB_LOCKS

INNODB_TRX

INNODB_LOCK_WAITS

gap锁简单模型如图：

场景二：唯一索引，delete条件部分命中，然后执行insert

session A

delete from staff_role_biz_role_id where kdt_id = 20 and admin_id = 1 and biz = 'retail' and role_id = '1';

session B

delete from staff_role_biz_role_id where kdt_id = 20 and admin_id = 1 and biz = 'retail' and role_id = '1';

由于where条件命中，所以直接在索引上给记录(kdt_id = 20 and admin_id = 1 and biz = 'retail' and role_id = '1')上面加上了X锁，session B的事务就需要等待session A事务释放了X锁后才能执行事务，此情况下不会出现死锁

相关事务锁信息

以上为mysql中，innodb下，建立唯一索引，在执行sql中存在索引不命中时使用了gap锁导致的死锁场景分析，主要原因还是在于gap锁可以被多个事务获取到，谢谢拍砖^_^