mysql锁机制之示例讲解

作者：杨恒

背景

2、隔离级别

理论

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

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
read uncommited	yes	yes	yes	no
read commited	no	yes	yes	no
repeatable read	no	no	yes	no
serializable	no	no	no	yes

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’);

t1	t2
begin; select * from t where id=1; commit;	begin; update t set name=’yy’ where id=1; commit;

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锁
意向锁产生的主要目的是为了处理行锁和表锁之间的冲突，用于表明“某个事务正在某一行上持有了锁，或者准备去持有锁”

共享锁、排它锁与意向锁的兼容矩阵

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	冲突	兼容	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

行锁

记录锁

仅仅锁住索引记录的一行。单行索引记录上加锁，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)后行的锁定顺序)

	gap	insert	record	next-key
gap	兼容	兼容	兼容	兼容
insert	冲突	兼容	兼容	冲突
record	兼容	兼容	冲突	冲突
next-key	兼容	兼容	冲突	冲突

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

4、测试

实例一、

t1	t2
begin; select * from t where id=8 for update; commit;	begin; insert into t values(4); insert into t values(5); insert into t values(6); insert into t values(11); insert into t values(12); rollback;

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。

实例二、

t1	t2
begin; delete from t where id=8; commit;	begin; insert into t(id,name) values(6,’f’); insert into t(id,name) values(5,’e1’); insert into t(id,name) values(8,’gg’); insert into t(id,name) values(10,’p’); insert into t(id,name) values(11,’iz’); insert into t(id,name) values(5,’cz’);

分析：因为会话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]的右边)不属于上面的范围从而可以写入。

实例三、

t1	t2
begin; select * from t where id=8 for update; commit;	begin; insert into t values(7); insert into t values(9); rollback;

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，即仅锁住索引本身，不是范围。

实例四、

t1	t2
begin; select * from t where id=15 for update; commit;	begin; insert into t(id,name) values(10,’k’); insert into t(id,name) values(12,’k’); rollback;

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 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;
select * from t;
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	t2	t3
begin; insert into t values(6,’test’,12,3); rollback;	begin; insert into t values(6,’test’,12,3); OK	begin; insert into t values(6,’test’,12,3); ERROR

死锁成因
事务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	t2
begin; select * from t where b=6 for update; insert into t values(4,5); commit;	begin; select * from t where b=8 for update; insert into t values(4,5); commit;

死锁成因
事务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锁。于是，死锁便产生了。

背景