MySQL中的锁-从for update说起

### 缘起：

有一张表如下图：

image-20200602224524398

然后有两种情况进行加锁

情况一：

Transaction1 Transaction2
begin; begin;
select * from user where mobile = “10” for update;(mobile 10 存在)
select * from user where mobile = “10” for update;（mobile 10存在） 被锁住了
commit;
正常执行

情况二：

Transaction1 Transaction2
begin; begin;
select * from user where mobile = “5” for update;(mobile 5 不存在)
select * from user where mobile = “5” for update;（mobile 5不存在） 未被锁住
select * from user where mobile = “4” for update;（mobile 4不存在） 未被锁住
select * from user where mobile = “6” for update;（mobile 6不存在） 未被锁住
commit;
正常执行

从上面的这两个例子可以很容易的给出一个好多人一直以为的错误的结论：当数据存在时，此条数据会被锁住，当数据不存在时，数据不会被锁住。

但事实真的是这样的嘛，显然不是，上面的例子从一开始就缺少了几个关键条件：哪个存储引擎，什么隔离级别，加锁字段是否有索引，这三个条件是讨论加锁的前提，下面我们就来详细了解一下MySQL中锁的知识

锁相关基础知识

本篇文章选用的最常用的InnoDB存储引擎，隔离级别会从常用的**Read Uncommited(RU)、Read Committed (RC)、Repeatable Read (RR)**三种隔离级别来分析，加锁字段会从唯一索引，普通索引，和没有索引来分析。

在确定一上问题后，我们需要首先了解的一个事情是InnoDB中锁有哪几种：

锁的种类

从官方文档中https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks，我们可以总结出锁的类型分为以下几种：

  • **共享锁(S Lock)**：

    允许持有锁的事务去读取行，如果事务T1对行r拥有S锁，然后，来自不同事务T2的对r行进行锁定的请求将按以下方式处理：

    • 如果T2对行r加S锁，此时会立刻加上，然后T1和T2都拥有了S锁
    • 如果T2对行r加X锁，此时将会被阻止

  • **排他锁(X Lock)**：

    允许持有锁的事务去更新或者修改行，如果事务T1对行r拥有X锁，那么来自不同事务T2的不管何种加锁请求都会被拒绝

  • **意向共享锁(IS锁)**：

    在事务给表中某行加S锁之前，必须先给表加IS锁或更强的锁。

  • **意向排他锁(IX锁)**：

    在事务给表中某行加X锁之前，必须先给表加IX锁

意向锁补充：意向锁简单来讲就是在给行上加锁的时候，先给表加一个意向锁，这个意向锁对于正常的行锁是没有影响的，但是当需要对表进行一些编辑的时候比如加一个字段一类的，这时候如果表中有正在加锁的行，是可以立刻感知到的，需要等待表中没有行加锁才可以进行表的操作，这也是意向锁的最主要作用

在了解锁的种类后，就需要知道这些锁是怎么被使用的，姑且叫做加锁方式吧，这里介绍常用的三种

加锁方式：

  • **行锁(Record Locks)**：行锁是锁在索引上的一种锁，是把锁加在索引上了，这里需要注意下，好多地方翻译的不清楚，行锁始终锁的都是索引，即使没有定义索引的也是，对于这种情况InnoDB会创建一个隐藏的索引来让它锁

  • **间隙锁(Gap Locks)**：间隙锁是对行锁之前的锁定，为了是防止其他数据来插入进来，引起幻读，当隔离级别为Repeatable Read等级及以上才会有间隙锁，并且对于使用唯一索引来锁定唯一行来锁定行的语句，不需要间隙锁定。

  • Next-Key Locks：是行锁以及行锁之前的间隙锁组成的（例如：1，2，3；这里所说的”前“的概念是“2”的前是“1”）

下面我们来通过for update的例子来看一下加锁情况：

举例详解

所用到的表

picture 19

有两种不同的隔离等级（RC和RR）的数据库，有两张字段和数据一样的表，其中id为自增主键，uni_index被设置了唯一索引，norm_index设置了普通索引，no_index没有索引，然后我们通过对这两张表加锁进行举例分析，加锁情况在MySQL8.0中可以在performance_schema库中的data_locks表中查看

走主键或唯一索引加锁情况

命中一条：

  • RC级别

    1
    2
    begin;
    select * from lock_demo where uni_index = 10 for update

picture 20

其中，X,REC_NOT_GAP代表的就是我们上面说的行锁(Record Locks)，此时锁住了id为1这一行，然后给表加了一个意向锁

  • RR级别

    1
    2
    begin;
    select * from lock_demo where uni_index = 10 for update

picture 21

情况一样

未命中：

  • RC级别

    1
    2
    begin;
    select * from lock_demo where uni_index = 100 for update

picture 22

仅仅给表加了一个意向锁

  • RR级别

    1
    2
    begin;
    select * from lock_demo where uni_index = 100 for update

    picture 23

    这里出现了两个陌生东西，一个是LOCK_MODE中的X：代表是加了一个Next-Key Locks，一个是LOCK_DATA中的supremum pseudo-record代表不存在的无穷大的值。

    在这里我们去查询了一个uni_index不存在的且大于表中最大数据的数，所以加了一个Next-Key Locks，而这个Next-Key Locks锁的是一个不存在的无穷大的值，即锁住了表中现存的最大值到不存在无穷大的值中间的间隙和这个不存在无穷大的这一行，其实就是在最大记录后面加了一个间隙锁，如果uni_index=11的话，按照推断应该是在10-20之间加一个间隙锁，我们来试一下

    1
    2
    begin;
    select * from lock_demo where uni_index = 11 for update

picture 24

这里的LOCK_MODE为X,GAP意味着在当前LOCK_DATA前到上一条记录中间加了个间隙锁

走唯一索引总结：当走唯一索引时，在命中的情况下RC和RR的表现一致，都是只锁当前唯一行，并且给表加一个意向锁，但是当不命中时RR级别会加一个锁，锁住未命中的那个间隙。

走普通索引加锁情况

命中一条：

  • RC级别

    1
    2
    begin;
    select * from lock_demo where norm_index = 100 for update

picture 25

仅锁住了id为1的这一行，依旧为行锁

  • RR级别

    1
    2
    begin;
    select * from lock_demo where norm_index = 100 for update

picture 26

给命中的那条记录加一个行锁和next-key lock，以及前条记录加一个间隙锁，最终表现为id为1的这条记录的前面和后面都加了间隙锁，本身加了一个行锁

命中两条：

  • RC级别

    1
    2
    begin;
    select * from lock_demo where norm_index = 200 for update

picture 27

给命中记录加行锁

  • RR级别

    1
    2
    begin;
    select * from lock_demo where norm_index = 200 for update

picture 28

最终表现为(1,4]这个区间被加了锁

未命中

  • RC级别

    1
    2
    begin;
    select * from lock_demo where norm_index = 201 for update

    picture 29

    只加了一个意向锁

  • RR级别

    1
    2
    begin;
    select * from lock_demo where norm_index = 201 for update

    picture 30

    和唯一索引加锁情况一致，把没有查询到的这个区间给锁住了

不走索引加锁情况：

命中一条：

  • RC级别

    1
    2
    begin;
    select * from lock_demo where no_index = 1000 for update

picture 31

仅加了一个行锁

  • RR级别

    1
    2
    begin;
    select * from lock_demo where no_index = 1000 for update

picture 32

全部行都加了next-key lock，这意味着无穷小到无穷大都被锁了：1有next-key lock意味着1前面的空隙（无穷小）及1本身被锁了，supremum pseudo-record意味着这个不存在的最大值和5之间的空隙也被锁住了，这种给人的感觉是这整张表都被锁了，其实并不是

命中两条

  • RC级别

    1
    2
    begin;
    select * from lock_demo where no_index = 2000 for update

    picture 33

    好像没啥特殊的，和前面有索引一样，但是实际情况并不是这样的，当没有索引的情况下，MySQL会给所有的行加上行锁，但是在MySQL Server层会把一些不满足条件的给过滤掉了，虽然这违反了二段锁协议的约束，但是却提高了效率。

  • RR级别

    1
    2
    begin;
    select * from lock_demo where no_index = 2000 for update

    picture 34

    和命中一条一样，把所有行及空隙全锁了

未命中

  • RC级别

    1
    2
    begin;
    select * from lock_demo where no_index = 2001 for update

    picture 35

    只加了一个意向锁

  • RR级别

    1
    2
    begin;
    select * from lock_demo where no_index = 2001 for update

    picture 36

    由此可以看出RR下只要是不走索引，就会把所有的行和间隙都锁掉

总结：

从我们的例子上来看判断锁的情况，首先应该明确隔离等级是什么，然后再看查询字段是否有索引，才能判断出是怎么加的锁；对于加锁的分析一定要学会去看data_locks这张表，理解这几种LOCK_MODE的含义

扩展：

这里有个小小的坑各位小伙伴有兴趣的可以自己试一下：问题是order by limit是怎么加锁的，这应该是order by的坑吧，其实加锁原理还是一样的，分析的时候可以使用explain把语句进行分析一下，然后应该就比较容易了解原因了