likes
comments
collection
share

MySql InnoDB行锁——“三剑客”

作者站长头像
站长
· 阅读数 30

前言

行锁在 InnoDB 中是基于索引实现的,所以一旦某个加锁操作没有使用索引,那么该锁就会退化为表锁。

一、行锁三剑客是什么?

行锁三剑客指的是:InnoDB引擎下的记录锁(Record Locks),间隙锁(Gap Locks),临键锁(Next-Key Locks)。

二、记录锁(Record Locks)

记录锁存在于包括主键索引在内的唯一索引中,锁定单条索引记录

-- id 列为主键列或唯一索引列

SELECT * FROM table WHERE id = 1 FOR UPDATE; 

id 为 1 的记录行会被锁住。需要注意的是:

**id 列必须为唯一索引列或主键列,否则上述语句加的锁就会变成临键锁。同时查询语句必须为精准匹配(=),不能为 >、<、like等,否则也会退化成临键锁**

其他实现在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时,也会对该行数据加记录锁:

-- id 列为主键列或唯一索引列

UPDATE SET age = 50 WHERE id = 1;

三、间隙锁(Gap Locks)

间隙锁基于非唯一索引,它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法,请务必牢记:使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据。

间隙锁测试:test表中,主键id,ageMySql InnoDB行锁——“三剑客”在进行测试之前,我们先来看看test表中存在的隐藏间隙:

  1. (-infinity, 1)
  2. (1, 5)
  3. (5, 7)
  4. (7, 11)
  5. (11, +infinity)

场景一:只使用记录锁,不会产生间隙锁开启事务一,并执行主键为id=5的语句。MySql InnoDB行锁——“三剑客”事务2开启执行insert, 正常执行不受影响。MySql InnoDB行锁——“三剑客”场景二:产生间隙锁(主键索引)事务一仍然是开启事务,但是执行的是范围内的查询。MySql InnoDB行锁——“三剑客”事务二开启,然后执行插入语句,插入id=6.age =8.MySql InnoDB行锁——“三剑客”从上面我们可以看到,(5, 7)、(7, 11) 这两个区间,都不可插入数据,其它区间,都可以正常插入数据。所以我们可以得出结论:当我们给 (5, 7)这个区间加锁的时候,会锁住 (5, 7)、(7, 11] 这两个区间。

场景三:产生间隙锁(主键索引+锁不存在的数据)MySql InnoDB行锁——“三剑客”Id=3并不存在这样的数据,发现锁住了区间(1,5]MySql InnoDB行锁——“三剑客”结论

  1. 对于指定查询某一条记录的加锁语句,如果该记录不存在,会产生记录锁和间隙锁,如果记录存在,则只会产生记录锁,如:WHERE id = 5 FOR UPDATE;
  2. 对于查找某一范围内的查询语句,会产生间隙锁,如:WHERE id BETWEEN 5 AND 7 FOR UPDATE;

场景四:产生间隙锁(非主键索引)在这张表上,我们有 id age 这两个字段,id 是我们的主键,我们在 age 上,建也立了一个普通索引,为了方便我们后面的测试在进行测试之前,我们先来看看test1表中 number 索引存在的隐藏间隙:

  1. (-infinity, 1)
  2. (1, 3)
  3. (3, 8)
  4. (8, 12)
  5. (12, +infinity)案例说明我们执行以下的事务(事务1最后提交),分别执行下面的语句:
/* 开启事务1 */
BEGIN;
/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行,防止锁释放 */
SELECT SLEEP(30);

# 注意:以下的语句不是放在一个事务中执行,而是分开多次执行,每次事务中只有一条添加语句

/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `test1` (`number`) VALUES (0); # 正常执行

/* 事务3插入一条 number = 1 的数据 */
INSERT INTO `test1` (`number`) VALUES (1); # 被阻塞

/* 事务4插入一条 number = 2 的数据 */
INSERT INTO `test1` (`number`) VALUES (2); # 被阻塞

/* 事务5插入一条 number = 4 的数据 */
INSERT INTO `test1` (`number`) VALUES (4); # 被阻塞

/* 事务6插入一条 number = 8 的数据 */
INSERT INTO `test1` (`number`) VALUES (8); # 正常执行

/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `test1` (`number`) VALUES (9); # 正常执行

/* 事务8插入一条 number = 10 的数据 */
INSERT INTO `test1` (`number`) VALUES (10); # 正常执行

/* 提交事务1 */
COMMIT;

我们会发现有些语句可以正常执行,有些语句被阻塞了。我们再来看看我们表中的数据:MySql InnoDB行锁——“三剑客”这里可以看到,number (1 - 8) 的间隙中,插入语句都被阻塞了,而不在这个范围内的语句,正常执行,这就是因为有间隙锁的原因这是为什么呢?我们来看看下边的图,大家就明白了。MySql InnoDB行锁——“三剑客”结论

  1. 在普通索引列上,不管是何种查询,只要加锁,都会产生间隙锁,这跟唯一索引不一样;
  2. 在普通索引跟唯一索引中,数据间隙的分析,数据行是优先根据普通索引排序,再根据唯一索引排序。

四、临键锁(Next-Key Locks)

**临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。注:临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。**

Next-key Lock A next-key lock is a combination of a record lock on theindex record and a gap lock on the gap before the index record.

当我们使用索引进行范围查询,命中了记录的情况下,就是使用了临键锁,他相当于记录锁+间隙锁。

两种退化的情况:

唯一性索引,等值查询匹配到一条记录的时候,退化成记录锁。

没有匹配到任何记录的时候,退化成间隙锁。

左开右闭区间,目的是为了解决幻读的问题。

select * from xx where id > 5 and id < 9;

上面的sql命中了id = 7的记录,也包含了记录不存在的区间,所以他锁住(4,7]和(7,10]区间,在这区间,别的事务插入不了数据,所以解决了幻读问题。

总结

以上就是这次对mysql行锁三剑客的理解和总结,主要参照mysql官方文档资料并加入自己的实践和理解,贴一下mysql官方文档地址,希望大家多多支持和关注。

转载自:https://segmentfault.com/a/1190000041688380
评论
请登录