专栏首页程序员历小冰MySQL探秘(七):InnoDB行锁算法

MySQL探秘(七):InnoDB行锁算法

 在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。然而锁这个事情是无法避免的,数据的写入,修改和删除都需要加锁。今天我们就继续学习InnoDB锁相关的知识。

 由于文章涉及的概念比较多,害怕大家看完后会骂人,有一种字我都认识,就不太懂的感觉,文章会给出一些实例和试验,依据具体案例来讲解这些概念。毕竟,实践才能出真知。

 InnoDB存储引擎支持表锁和行锁。顾名思义,表锁是锁住整张表,行锁只是锁住某些行。InnoDB通过给索引项加锁来实现行锁,如果没有索引,则通过隐藏的聚簇索引来对记录加锁。如果操作不通过索引条件检索数据,InnoDB 则对表中的所有记录加锁,实际效果就和表锁一样。InnoDB存储引擎有3种行锁的算法,分别是:

  • Record Lock: 单个记录上的锁
  • Gap Lock: 间隙锁,锁定一个范围,但不包括记录本上
  • Next-Key Lock: Gap Lock+Record Lock,锁定一个范围,并且锁定记录本身

 如下图所示,

三种锁算法

 例如一个索引有10,11,13,20这四个值。InnoDB可以根据需要使用Record Lock将10,11,13,20四个索引锁住,也可以使用Gap Lock将(-∞,10),(10,11),(11,13),(13,20),(20, +∞)五个范围区间锁住。Next-Key Locking类似于上述两种锁的结合,它可以锁住的区间有为(-∞,10],(10,11],(11,13],(13,20],(20, +∞),可以看出它即锁定了一个范围,也会锁定记录本身。

 InnoDB存储引擎的锁算法的一些规则如下所示,后续章节会给出对应的实验案例和详细讲解。

  • 在不通过索引条件查询时,InnoDB 会锁定表中的所有记录。所以,如果考虑性能,WHERE语句中的条件查询的字段都应该加上索引。
  • InnoDB通过索引来实现行锁,而不是通过锁住记录。因此,当操作的两条不同记录拥有相同的索引时,也会因为行锁被锁而发生等待。
  • 由于InnoDB的索引机制,数据库操作使用了主键索引,InnoDB会锁住主键索引;使用非主键索引时,InnoDB会先锁住非主键索引,再锁定主键索引。
  • 当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时,InnoDB存储引擎会将Next-Key Lock降级为Record Lock,即只锁住索引本身,而不是范围。
  • InnoDB对于辅助索引有特殊的处理,不仅会锁住辅助索引值所在的范围,还会将其下一键值加上Gap LOCK。
  • InnoDB使用Next-Key Lock机制来避免Phantom Problem(幻读问题)。

真的了解本质吗?

 在不通过索引条件查询时,InnoDB 会锁定表中的所有记录。大家可以登录上自己的MySQL服务器,亲自试验一下。

示例一

 试验发现,会话二的查询操作真的是会发生等待。那么,这句话真的是对的吗?我们可以使用《InnoDB锁的类型和状态查询》中查询数据锁的方法查询一下,注意必须在会话二操作还在等待时进行查询,否则查询不到

查询锁信息

 其中lock_trx_id为1851的事务是会话二的事务,另一个是会话一的事务。我们可以看到两个锁都要对值为1的主键索引加锁。需要注意的是,这里是对主键进行加锁。二者之间的关系是怎么确定的呢?我们可以通过information_schema.INNODB_LOCK_WAITS中的数据确定。

 奇怪,不是说好的锁定表中的所有记录嘛?查找了很多资料,发现INNODB_LOCKS的定义如下:

The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction.

 也就是说,这张表并不会显示所有锁的信息,而是只显示要申请却没有申请到,和已经持有锁并且阻塞其他线程的锁信息。怪不得必须在会话二进行等待时进行查询才能查得到数据。

 因为两个会话的操作都要锁住所有的行,所以发现每次在第一行记录上就发生了锁等待。那我们使用插入语句试试。表e1的主键a的值为1-4,我们分别插入主键为1-4(当然会有主键重复问题,但是由于有锁,一直等待)的新记录,分别查询锁信息,就能看到会话一的事务对所有的主键都加了锁,也就是对所有的记录都加了锁。

是索引,而不是记录

 InnoDB存储引擎的行锁是通过锁住索引实现的,而不是记录。这是理解很多数据库锁问题的关键。

 由于InnoDB特殊的索引机制,数据库操作使用主键索引时,InnoDB会锁住主键索引;使用非主键索引时,InnoDB会先锁住非主键索引,再锁定主键索引。不了解InnoDB索引机制的可以参考这篇文章

 如下图所示,当InnoDB锁定非主键索引b时,它也会锁住其对应的主键索引,所以锁住b值为2和3的非主键索引,那么与其相关的a值为6,5的主键索引也需要被锁住。

非主键索引的加锁

 比如说,一种常见的死锁情况一般出现在如下图所示的操作场景中。

示例2

 会话一的语句使用了b上的索引,因为它是非主键索引,所以会先在b索引上添加锁,再去a索引上加锁。而会话二的语句恰恰相反,会先在索引a上加锁,再去索引b加锁。这种情况下,就可能出现死锁。

Next-Key Lock锁到底有什么用?

 默认隔离级别REPEATABLE-READ下,InnoDB中行锁默认使用算法Next-Key Lock,只有当查询的索引是唯一索引或主键时,InnoDB会对Next-Key Lock进行优化,将其降级为Record Lock,即仅锁住索引本身,而不是范围。当查询的索引为辅助索引时,InnoDB则会使用Next-Key Lock进行加锁。InnoDB对于辅助索引有特殊的处理,不仅会锁住辅助索引值所在的范围,还会将其下一键值加上Gap LOCK。

 废话不多说,我们来看一下相关的实验,先做一下准备。

CREATE TABLE e4 (a INT, b INT, PRIMARY KEY(a), KEY(b));
INSERT INTO e4 SELECT 1,1;
INSERT INTO e4 SELECT 3,1;
INSERT INTO e4 SELECT 5,3;
INSERT INTO e4 SELECT 7,6;
INSERT INTO e4 SELECT 10,8;

 然后开启一个会话执行下面的语句。

SELECT * FROM e4 WHERE b=3 FOR UPDATE; 

 因为通过索引b来进行查询,所以InnoDB会使用Next-Key Lock进行加锁,并且索引b是非主键索引,所以还会对主键索引a进行加锁。对于主键索引a,仅仅对值为5的索引加上Record Lock(因为之前的规则)。而对于索引b,需要加上Next-Key Lock索引,锁定的范围是(1,3]。除此之外,还会对其下一个键值加上Gap Lock,即还有一个范围为(3,6)的锁。  大家可以再新开一个会话,执行下面的SQL语句,会发现都会被阻塞。

SELECT * FROM e4 WHERE a = 5 FOR UPDATE;  # 主键a被锁
INSERT INTO e4 SELECT 4,2;   # 插入行b的值为2,在锁定的(1,3]范围内
INSERT INTO e4 SELECT 6,5; # 插入行b的值为5,在锁定的(3,6)范围内

 InnoDB引擎采用Next-Key Lock来解决幻读问题。因为Next-Key Lock是锁住一个范围,所以就不会产生幻读问题。但是需要注意的是,InnoDB只在Repeatable Read隔离级别下使用该机制。

后记

 我们后续还会探讨InnoDB的事务的知识,请大家持续关注。

参考

  • 淘宝数据库博客 http://mysql.taobao.org/monthly/2018/05/04/
  • Mysql Innodb 中的锁 https://zhuanlan.zhihu.com/p/31875702
  • MySQL关于Lock类型的官方文档 https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html

本文分享自微信公众号 - 程序员历小冰(gh_bcc90a2a52c5)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-12-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL探秘(七):InnoDB行锁算法

     在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

    程序员历小冰
  • Mysql探索(一):B-Tree索引

    MySQL是目前业界最为流行的关系型数据库之一,而索引的优化也是数据库性能优化的关键之一。所以,充分地了解MySQL索引有助于提升开发人员对MySQL数...

    程序员历小冰
  • Java 数据持久化系列之JDBC

    前段时间小冰在工作中遇到了一系列关于数据持久化的问题,在排查问题时发现自己对 Java 后端的数据持久化框架的原理都不太了解,只有不断试错,因此走了很多弯路。于...

    程序员历小冰
  • MySQL探秘(七):InnoDB行锁算法

     在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

    aoho求索
  • [zz学习]MySQL索引背后的数据结构及算法原理MySQL索引背后的数据结构及算法原理MyISAM索引实现InnoDB索引实现

    虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,...

    一个会写诗的程序员
  • MySQL探秘(七):InnoDB行锁算法

     在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

    程序员历小冰
  • 图解:深入理解MySQL索引底层数据结构与算法

    以Col1为主键,则上图是一个MyISAM表的主索引(Primary key)示意

    你好戴先生
  • MySql中InnoDB表为什么要建议用自增列做主键

    如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索...

    一觉睡到小时候
  • 优化页面访问速度(二) ——数据库优化

    数据库优化,主要包括数据表设计、索引、sql语句、表拆分、数据库服务器架构等方向的优化。

    用户1327360
  • 从MyISAM转到InnoDB需要注意什么

    转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

    joshua317

扫码关注云+社区

领取腾讯云代金券