MySQL的行锁

背景

自从过年值班就一直在公司读《 MySQL实战45讲》这本书,当时看完了MySQL的全局锁、表级锁还有行锁和间隙锁。其中的行锁和间隙锁迟迟无法理解,最近趁着有空再次阅读了一遍,并且对照了《深入浅出MySQL》,发现对MySQL锁的理解又上了一个台阶,今天就来分享一下MySQL的行锁。

行锁

MySQL的行锁又分为共享锁(S锁)和排他锁(X锁)。

一般普通的select语句,InnoDB不加任何锁,我们称之为快照读

  • select * from test;

通过加S锁和X锁的select语句或者插入/更新/删除操作,我们称之为当前读

  • select * from test lock in share mode;
  • select * from test for update;
  • insert into test values(…);
  • update test set …;
  • delete from test …;

特殊说明:以上的当前读,读取的都是记录的最新版本。对读取记录都会加锁,除了第一条语句lock in share mode是对记录加S锁(共享锁)外,其他的操作都是加X锁(排他锁)

两阶段锁协议

传统的关系型数据库加锁都要遵循一个原则:两阶段锁原则。

两阶段锁是将锁的操作分为两个阶段,加锁阶段和解锁阶段,并且保证加锁阶段和解锁阶段不相交。

分享一个例子说明两阶段锁协议:

事务A 事务B
begin;
update test set name=’god-jiang’ where id=1;
begin;
delete from test where name=’god-jiang’;
commit;

事务A执行完update语句后,手上持有着name=’god-jiang’的X锁,而事务B执行delete语句删除name=’god-jiang’记录的时候会阻塞,直到事务A执行commit之后,事务B才会执行delete。

也就是说,在InnoDB存储引擎中,行锁是在需要的时候加上的,但并不是不需要了就立刻释放行锁,而是要等到事务结束的时候才释放,这个就是两阶段锁协议。

事务隔离级别

不同事务隔离级别对应的行锁也是不同的,所以我们需要先了解事务的隔离级别后,再来演示不同隔离级别的行锁如何加上去。

MySQL的4种隔离级别:

  1. READ UNCOMMITTED(读未提交):任何一个事务当中,都可以看见其他事务的执行情况,会出现脏读现象。
  2. READ COMMITTED(读已提交,简称:RC):在当前事务中只能看见已经提交事务的执行结果,当同一事务在读取期间出现新的commit操作,会出现不可重复读现象。
  3. REPEATABLE READ(可重复读,简称:RR):这是MySQL默认的隔离级别,得益于MVCC,它能在同一事务在多实例并发读取数据时看到相同的数据行,消除了脏读、不可重复读,默认不会出现幻读(MySQL的行锁+间隙锁解决了快照读的幻读,未解决当前读的幻读)。
  4. SERIALIZABLE(串行):MySQL的最高隔离级别,通过加锁,强制事务执行顺序,保证不会出现幻读问题。

加锁分析(以下默认是RC隔离级别并且都是当前读)

这里我挑选出RC隔离级别下三种常见情况分析SQL如何加锁:

  • RC隔离级别,where字段没有索引
  • RC隔离级别,where字段有唯一索引
  • RC隔离级别,where字段有普通索引

以下加锁分析默认表名为test,主键为id,唯一索引为a,普通索引为b,无索引为c。

RC隔离级别+where无索引

img

由于c字段没有索引,SQL将会进行全表扫描。这个时候的所有记录,都会加上X锁。

为什么不是只在c=10的记录上加锁呢?

这是因为在MySQL中,如果where条件不能通过索引快速过滤,那么在MySQL的server层就会将所有记录都加锁然后调用InnoDB存储引擎查询,因此也就把所有记录都锁上了。

总结:没有索引的情况下,InnoDB的当前读会对所有记录都加锁。所以在实际开发中,如果是当前读或者是插入/更新/删除等操作一定要使用索引,否则会产生大量的锁等待

RC隔离级别+where唯一索引

img

由于a字段有唯一索引,因此通过MySQL的server层会选择走a列的索引进行过滤,找到a=2记录后,将唯一索引上a=1的索引记录加上X锁,同时读取主键id并找到聚集索引树给id=2的记录加上X锁。

总结:如果查询的条件是唯一索引,那么SQL在满足的唯一索引的记录上加X锁,并且在对应的聚集索引上加X锁

RC隔离级别+where普通索引

img

由于b字段有普通索引,所以在满足b=2的所有记录上都加上了X锁,同时对应的聚集索引记录也加上了X锁。与唯一索引对比,唯一索引查询最多有一行记录上锁,而普通索引会把满足条件的所有记录上锁。

总结:如果查询的条件是普通索引,那么SQL会在满足条件的非唯一索引记录加上X锁,并且会在它们对应的聚集索引上加X锁

总结

  • InnoDB支持行锁,是替代MyISAM存储引擎的重要原因之一
  • 分享了两阶段锁协议和MySQL的事务隔离级别
  • 分析了RC隔离级别下常见的当前读加锁情况

最最重要的就是,在RC隔离级别下,我们更新数据,插入数据,删除数据都要尽可能走索引,不然会使所有的记录都被加上X锁,假如在线上操作的话,会严重影响业务

参考资料

  • 《MySQL实战45讲》林晓斌
  • 《深入浅出MySQL》20.3.4 InnoDB行锁实现方式
-------------本文结束感谢您的阅读-------------

本文标题:MySQL的行锁

文章作者:god-jiang

发布时间:2021年03月12日 - 22:31:21

最后更新:2021年03月12日 - 22:42:26

原始链接:https://god-jiang.github.io/2021/03/12/MySQL的行锁/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

创作不易,您的支持就是我坚持的动力,谢谢大家!
0%