IT源码网

MYSQL锁机制详解

xmjava 2021年05月15日 数据库 390 0

最近在学习MySQL的锁机制和隔离级别,发现跟Oracle还是有比较大的差异。例如Oracle默认隔离级别是read committed,而MySQL默认是repeatable read。Oracle除了串行化,无法避免幻读。而MySQL在repeatable read级别下,通过next-key锁机制,避免了幻读的产生。记录一下理论理解以及实验验证。
InnoDB锁类型有如下几种:
共享锁(S)和排他锁(X);
意向锁(Intention locks);
行锁(Record lock);
间隔锁(Gap lock);
Next-key lock;
插入意向锁(Insert Intention lock)
1.共享锁和排他锁
InnoDB的行级锁分为共享锁和排他锁。共享锁(S)锁定一行用于读取,排他锁(X)锁定一行用于修改写入。
如果一个事务在一行数据上持有共享锁(S),其他事务也可以获取该行上的共享锁(S),这种情况是多个事务同时持有该行数据的共享锁(S);但其他无法获取该行上的排他锁(X)。
如果一个事务在一行数据上持有排他锁(X),其他事务无法获取该行以上两种类型的锁。
同时,InnoDB的行锁是通过索引实现的,如果列上没有索引,那么锁的粒度是表级的。
实验:t1表的id列上没有索引
在这里插入图片描述
在这里插入图片描述
2.意向锁
InnoDB支持多粒度锁,允许同时存在表锁和行锁(但各种类型锁之间有冲突限制,后面会说明),引入了意向锁。意向锁是表级别的,在获取行锁之前,会先申请一个意向锁,表示之后该事务会在某些行上申请行锁。意向锁有两种类型:
1)共享意向锁(IS),表示事务将在某些数据行上持有共享锁;
2)排他意向锁(IX),表示事务将在某些数据行上持有排他锁。
例如,select … for share持有共享意向锁(IS),select … for update持有排他意向锁(IX)。
意向锁机制约定如下:
1)一个事务获取共享行锁前,必须先获取共享意向锁(IS);
2)一个事务获取排他行锁前,必须先获取排他意向锁(IS)。
意向锁及行锁之间的兼容性如下:
在这里插入图片描述
当事务申请一个锁时,不管是意向锁还是行锁,都要看是否与已存在的锁是否兼容。可以通过show engine innodb status\G查看意向锁的事务信息。
3.行锁
行锁是通过锁定索引行来实现的。同样可以通过show engine innodb status\G查看行锁的事务信息。
4.间隔锁
间隔锁用来锁定一个区间内的索引行,或者锁定索引第一个值以前和最后一个值往后的区间。例如
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
将会在锁定10<=c1<=20的区间,阻塞c1=15的插入,不管表上是否已存在c1=15。
间隔锁对单值也同样有效。
但如果列上有唯一索引,就不会用到间隔锁,而是只锁定指定的行。
间隔锁是性能和并发性的折中,如果将隔离级别设为read committed,即禁用间隔锁,但在外键和重复键约束检查的时候,仍然会启用间隔锁。
间隔锁实验在后面next-key锁一起演示。
5.next-key锁
next-key锁是行锁和间隔锁的结合,即锁住该行数据及该行数据往前的区间。MySQL就是通过next-key锁避免了幻读的产生。
例如ti表id列上有10, 11, 13, and 20这些值,那么可能的next-key锁区间会是以下:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
实验:
1)id列上没有索引,则select id from id=11 for update是锁表的,前文已经有过说明;
2)id列上有索引,
当id是11,会锁住(10,11]
在这里插入图片描述
在这里插入图片描述
当id是索引最小值10,会锁住(negative infinity, 10];当id是索引最大值20,会锁住(20, positive infinity),即id=13也不能插入。
next-key锁小结:
next-key总会锁住数据行本身(行锁)以及该行的前一个索引行对应的数据行(间隔锁);
当选定条件包含索引最小值,则包含锁住小于索引最小值的无穷区间;
当选定条件包含索引最大值,则包含锁住大于索引最大值的无穷区间。

实验:next-key锁避免幻读,以下实验中隔离级别是read committed,无论是用for share 还是for update锁住id>13的数据行,还是可以往表里插入id=23的数据,那么再发起同样查询的时候,就肯定会出现不一样的结果,像是出现了幻觉一样。以下就省略for update的测试了。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
6.插入意向锁
插入意向锁是一种间隔锁,作用在插入行及行往前的区间,只要插入的数据行不冲突,不同事物之间的插入意向锁即使在区间上重叠,也不会产生等待。


评论关闭
IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

the master has purged binary logs containing GTIDs that the slave requires的处理