mysql锁机制要览+示例讲解

作者：杨恒

背景

2、隔离级别

理论

1、read uncommited
可以读取未提交记录。此隔离级别，不会使用，忽略
2、read commited
针对当前读，rc隔离级别保证对读取到的记录加锁（记录锁），存在幻读现象
3、repeatable read
针对当前读，rr隔离级别保证对读取到的记录加锁（记录锁），同时保证对读取的范围加锁，新的满足查询条件的记录不能插入(间隙锁)，不存在幻读现象
4、seriablizable
从mvcc并发控制退化为基于锁的并发控制。不区别快照读和当前读，所有的读操作均为当前读，读加读锁(S锁)，写加写锁(X锁)

5、rc 与rr对比：
set global transaction isolation level read commited;
set global transaction isolation level repeatable read;

测试

drop table if exists t;
create table t(id int,name varchar(10),key idx_id(id),primary key(name))engine=innodb;
insert into t values(1,‘a’),(3,‘c’),(5,‘e’),(8,‘g’),(11,‘j’);

3、锁

理论

基本锁：共享锁与排它锁

mysql允许拿到S锁的事务读一行，允许拿到X锁的事务更新或删除一行
加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁；
加了X锁的记录，不允许其他事务再加S锁或X锁

mysql对外提供加这两种锁的语法如下：
加S锁： select … lock in share mode
加X锁： select … for update

意向锁（表级锁）：意向共享锁(IS锁)和意向排它锁（IX锁）

事务在请求S锁和X锁前，需要先获得对应的IS、IX锁
意向锁产生的主要目的是为了处理行锁和表锁之间的冲突，用于表明“某个事务正在某一行上持有了锁，或者准备去持有锁”

共享锁、排它锁与意向锁的兼容矩阵(先行后列)

行锁

记录锁

仅仅锁住索引记录的一行。单行索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚簇主键索引，那么锁住的就是这个隐藏的聚簇主键索引。
所以说当一条sql没有走任何索引时，那么将会在每一条聚簇索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。(Is it true??)

间隙锁

区间锁，仅仅锁住一个索引区间（开区间）
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或之后加锁，并不包括该索引记录本身。

next-key锁

record lock + gap lock，左开右闭区间。默认情况下，innodb使用next-key locks来锁定记录。但当查询的索引含有唯一属性的时候，next-key lock会进行优化，将其降级为record lock，即仅锁住索引本身，不是范围

插入意向锁：特殊的间隙锁

gap lock中存在一种插入意向锁，在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

行锁的兼容矩阵 (先列(如gap)后行的锁定顺序)

1、已有的insert锁不阻止任何准备加的锁
2、gap、next-key会阻止insert
3、gap和record、next-key不会冲突
4、record和record、next-key之间相互冲突

4、测试

实例一、

drop table if exists t;
create table t(id int,key idx_a(id))engine=innodb;
insert into t values(1),(3),(5),(8),(11);
select * from t;

分析：
因为innoDB对于行的查询都是采用了next-key lock的算法，锁定的不是单个值，而是一个范围。上面索引值有(1,3,5,8,11),其记录的gap区间如下：是一个左开右闭的空间（原因是默认主键的有序自增的特性，结合后面的例子说明）(-∞,1],(1,3],(3,5],(5,8],(8,11],(11,+∞)
innoDB存储引擎还会对辅助索引下一个键值加上gap lock。

实例二、

分析：因为会话1已经对id=8的记录加了一个X锁，由于是RR隔离级别，innodb要防止幻读需要加gap锁：即id=5(8的左边),id=11(8的右边)之间需要加间隙锁（gap）。这样[5,e]和[8,g]，[8,g]和[11,j]之间的数据都要被锁。上面测试已经验证了这一点，根据索引的有序性，数据按照主键name排序，后面写入的[5,cz] ([5,e]的左边)和[11,ja] ([11,j]的右边)不属于上面的范围从而可以写入。

实例三、

drop table if exists t;
create table t(id int primary key)engine=innodb;
insert into t values(1),(3),(5),(8),(11);
select * from t;

分析：
因为innoDB对于行的查询都是采用了next-key lock的算法，锁定的不是单个值，而是一个范围，按照这个方法和第一个测试结果一样。但是，当查询的索引含有唯一属性的时候，next-key lock会进行优化，将其降级为record lock，即仅锁住索引本身，不是范围。

实例四、

drop table if exists t;
create table t(id int,name varchar(10),primary key(id))engine=innodb;
insert into t values(1,‘a’),(3,‘c’),(5,‘e’),(8,‘g’),(11,‘j’);
select * from t;

分析：
通过主键或者唯一索引来锁定不存在的值，也会产生gap锁定。

5、死锁

show engine innodb status;

duplicate key error 引发的死锁

drop table if exists t;
create table t(id int(10) unsigned not null ,
name varchar(20) not null default ‘’,
age int(11) not null default ‘0’ ,
stage int(11) not null default ‘0’ ,
primary key(id),
unique key udx_name(name),
key idx_stage(stage))engine=innodb;

insert into t(id,name,age,stage) values(1,‘yst’,11,8),(2,‘dxj’,7,4),(3,‘lb’,13,7),(4,‘zsq’,5,7),(5,‘lxr’,13,4);
select * from t;
select * from information_schema.innodb_locks;

死锁成因
事务t1成功插入记录，并获得索引id=6上的排他记录锁（lock_x）
紧接着事务t2、t3也开始插入记录，请求排他插入意向锁(lock_insert_intention);但由于发生重复唯一键冲突，各自请求的排他记录锁（lock_x）转成共享记录锁(lock_s)

t1回滚释放索引id=6上的排他记录锁（lock_x），t2和t3都要请求索引id=6上的排他记录锁(lock_x)。
由于x锁和s锁互斥，t2和t3都等待对方释放s锁。
于是，死锁便产生了。

如果此场景下，只有两个事务t1与t2或者t1与t3，则不会引发如上死锁情况发生。

gap与insert intention冲突引发的死锁

drop table if exists t;
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;
insert into t(a,b) values(1,2),(2,3),(3,4),(11,55);
select * from t;

死锁成因
事务t1执行查询语句，在索引b=6上加排他next-key锁（lock_x），会锁住idx_b索引范围(4,22)。
事务t2执行查询语句，在索引b=8上加排他next-key锁（lock_x），会锁住idx_b索引范围(4,22)。
由于请求的gap与已持有的gap是兼容的，因此，事务t2在idx_b索引范围(4,22)也能加锁成功。

事务t1执行插入语句，会先加他insert intention锁。由于请求的insert intention锁与已有的gap锁不兼容，则事务t1等待t2释放gap锁。
事务t2执行插入语句，也会等待t1释放gap锁。于是，死锁便产生了。

6、程序应用