注:不同的参数设置,不同的mysql版本,不同的事务隔离级别都对加锁过程有影响
1.通过show variables like 'innodb_locks_unsafe_for_binlog'可以查看gap锁是否开启
![](https://images-cdn.shimo.im/nqcEnofWrU41jOLf/image.png!thumbnail)
该参数默认值为0,即启用gap锁,但是这个参数的设置不会影响外键和唯一索引(包含主键)对gap进行加锁的需要。
2.通过show variables like 'innodb_autoinc_lock_mode'查看自增锁的模式
![](https://images-cdn.shimo.im/urPr6E118h4ueFvO/image.png!thumbnail)
该参数默认有3种配置模式:
0(传统模式):对auto_increment列插入语句时加表级auto-inc锁,只有插入执行结束之后才会释放锁
1(连续模式):可以事先确定插入行数的语句(包括多行和单行),会分配连续确定的auto-increment值;对于插入行数不确定的语句,仍然加表级auto-inc锁。(注:在这种模式下,如果回滚,auto-increment并不会回滚,则导致自增列不连续)
2(交错模式):同一时刻多条SQL语句产生交错的auto-increment值
行锁:
record lock:,仅仅锁住索引记录的一行,单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身
gap lock:仅仅锁住一个索引区间(开区间),在索引记录之间的间隙中加锁,或在某一条索引记录之前或之后加锁,并不包括索引记录本身
next-key锁:record lock+gap lock 左开右闭区间
插入意向锁(insert intention lock,属于gap lock):在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候,不需要等待其他事务完成。
加锁过程:insert会在插入成功的行加一个X锁,这个锁是record lock而不是一个next-key lock(更不会是gap lock),不会阻止其他并发事务往这条记录之前的间隙插入记录。在插入之前,会先在插入记录所在的间隙加上一个insert intention gap lock,并发事务可以对同一个加insert意向锁。如果insert的事务出现了主键重复错误,事务会对这个记录加共享锁。这个共享锁在并发的时候是会产生死锁。
行锁之间的兼容情况(横向为已加的锁)
![](https://images-cdn.shimo.im/ERtwx5Kgx1YPQ2W1/image.png!thumbnail)
说明:
insert操作之间不会有冲突
gap、next-key会阻止insert
gap和record、next-key不会冲突
record和record、next-key之间相互冲突
已有的insert锁不阻止任何准备加的锁
insert死锁场景分析
1.主键错误引发的死锁
表结构:
CREATE TABLE `aa` (
`id` int(10) unsigned NOT NULL COMMENT '主键',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`stage` int(11) NOT NULL DEFAULT '0' COMMENT '关卡数',
PRIMARY KEY (`id`),
UNIQUE KEY `udx_name` (`name`),
KEY `idx_stage` (`stage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表数据:
mysql> select * from aa;
+----+------+-----+-------+
| id | name | age | stage |
+----+------+-----+-------+
| 1 | yst | 11 | 8 |
| 2 | dxj | 7 | 4 |
| 3 | lb | 13 | 7 |
| 4 | zsq | 5 | 7 |
| 5 | lxr | 13 | 4 |
+----+------+-----+-------+
注:如果T1未rollback,而是commit的话,T2和T3会报唯一键冲突:ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’
死锁原因:首先session1插入一条记录,获得该记录的排它锁,这时session2和session3都检测到了主键冲突错误,但是由于session1并没有提交,所以session1并不算插入成功,于是它并不能直接报错吧,于是session2和session3都申请了该记录的共享锁,这时还没获取到共享锁,处于等待队列中。这时session1 rollback了,也就释放了该行记录的排它锁,那么session2和session3都获取了该行上的共享锁。而session2和session3想要插入记录,必须获取排它锁,但由于他们自己都拥有了共享锁,于是永远无法获取到排它锁,于是死锁就发生了。如果这时session1是commit而不是rollback的话,那么session2和session3都直接报错主键冲突错误。
2.gap和insert intention冲突引发的死锁
表结构:
CREATE TABLE `t` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表数据:
mysql> select * from t;
+----+------+
| a | b |
+----+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 11 | 22 |
+----+------+
死锁原因:
事务T1执行查询语句,在索引b=6上加排他Next-key锁(LOCK_X | LOCK_ORDINARY),会锁住idx_b索引范围(4, 22)。
事务T2执行查询语句,在索引b=8上加排他Next-key锁(LOCK_X | LOCK_ORDINARY),会锁住idx_b索引范围(4, 22)。由于请求的GAP与已持有的GAP是兼容的,因此,事务T2在idx_b索引范围(4, 22)也能加锁成功。
事务T1执行插入语句,会先加排他Insert Intention锁。由于请求的Insert Intention锁与已有的GAP锁不兼容,则事务T1等待T2释放GAP锁。
事务T2执行插入语句,也会等待T1释放GAP锁。
于是,死锁便产生了。
译者介绍:家华,从事mysqlDBA的工作,记录自己对mysql的一些总结