浅谈MySQL的并发控制_mysql 并发能力

1、背景

        一套系统的运行一定离不开底层数据库的支持，由于mysql体积小、安装成本低、易于维护、速度快、总体拥有成本低、开放源码且无版权制约，所以成为商业数据库的首选。目前的系统都以高并发，低延迟为目标，数据库的并发和性能成为了至关重要的影响点，所以我们平时在使用mysql、编写sql语句时都需要了解下mysql的并发控制，否则可能会导致一行sql让数据库直接挂掉或者影响到接口的QPS。以下的分享都是基于mysql的InnoDB引擎和可重复读的隔离级别下。

2、概念

        众所周知，数据库的事务包括 原子性（Atomicity）、一致性（Consistent）、隔离性（Isolation）、持久性（Durable）。而我们的并发控制就是为了保证数据的一致性和隔离性。

        在没有隔离性的情况下，事务之间会出现3种数据一致性问题：

        脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

        不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

        幻读：事务A第一次查询到有5条数据，但是这时候事务B插入了一条新数据，当事务A使用相同的查询条件进行第二次查询时却有6条数据了，就好像发生了幻觉一样，这就叫幻读。

        所以为了解决上述问题，数据库产生了4种隔离级别，隔离性也是递增的：

        Read Uncommitted（读未提交）：最低的隔离级别，允许一个事务读取另一个事务尚未提交的数据变更。可能导致脏读（Dirty Read）问题。

        Read Committed（读已提交）：确保一个事务只能读取另一个事务已经提交的数据变更。防止脏读问题，但可能导致不可重复读（Non-repeatable Read）问题。

        Repeatable Read（可重复读）：确保在同一个事务中多次读取同一数据时，能够得到一致的结果。防止脏读和不可重复读问题，但可能导致幻读（Phantom Read）问题。

        Serializable（串行化）：最高的隔离级别，强制事务串行执行，确保不会发生脏读、不可重复读和幻读问题。但是并发性能较差，通常不建议在高并发环境中使用。

        所谓的并发控制，就是规避多个会话并发访问数据库带来的诸如脏数据之类的数据一致性问题，MySQL中提供了锁机制来保证数据并发访问的一致性。不同的隔离级别底层就是用不同的锁来实现的。

3、锁机制

        MySQL的锁可以从两种维度来分，一个维度是按照锁的是一行，还是锁的是整张表，分为：行锁、表锁，另一个维度是按照锁的操作是读操作，还是写操作，分为：读锁（S锁，共享锁）、写锁（X锁，排他锁）。

  3.1 表锁

      表锁，顾名思义也就是给整个表加上锁。

    3.1.1 读锁

      读锁，一种共享锁，针对被锁表，所有会话都可以进行读操作，所有会话都无法进行写操作。加锁方和其他客户端的区别是，加锁方直接不允许进行写操作，而其他会话的写操作允许进行，只是会被阻塞挂起。锁解开后，所有挂起的操作线程会去重新争抢资源。

      加读锁指令：

        lock tables 表名 read;

      释放锁指令：

        unlock tables;

    3.1.2 写锁

      写锁，排它锁，针对被锁表，加锁方可以读写，其他会话的写操作会直接失败，读操作会被阻塞挂起，解锁以后，被挂起的线程会重新去争抢资源。

    加写锁指令：

        lock tables 表名 write;

    释放锁指令：

        unlock tables;

    3.1.3 保护机制

          读锁、写锁中，加锁方都只能读当前被自己锁定的表，这是MySQL的一个保护机制，为的就是强制要求加锁方给出一个说法，到底准备锁多久，不给说法不让走。

  3.2 行锁

          行锁，顾名思义也就是按行来进行加锁。MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。InnoDB行锁是通过给索 引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点：只有通过索引条件检索的数据， InnoDB 才使用行级锁，否则，InnoDB将使用表锁！InnoDB 行级锁加锁的基本单位是 next-key lock（也叫临键锁）。

    3.2.1 读锁

         允许一个事务去读一行，阻止其他事务更新目标行数据。同时阻止其他事务加写锁，但 不阻止其他事务加读锁。

    3.2.2 写锁

        允许获得写锁的事务更新数据，阻止其他事务获取或修改数据。同时阻止其他事务加读锁和写锁。

4、示例建表和初始化语句

CREATE TABLE `t_lock` ( `id` int(11) NOT NULL, `m` int(11) DEFAULT NULL, `n` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `m` (`m`) ) ENGINE=InnoDB; insert into t_lock values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

5、行锁下的间隙锁和next-key lock

        上面说了MVCC通过快照读解决幻读问题，那在当前读场景下该怎么解决幻读问题呢（insert、update、select ... for update语句都使用了当前读）？

        首先我们看行锁是否可以解决，在极端情况下当表中所有行都被锁住，我们依然可以insert一条数据，这条数据还是可以被未提交事务查询到，从而导致幻读。这是因为还未insert的数据无法在加行锁时找到。其次在表锁和全局锁下虽然没有幻读问题，但是也没有了任何并发性能可言了。

        间隙锁的诞生就是为了解决当前读的"幻读"问题，这个"间隙"的意思是在索引记录之间的间隙，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

        间隙锁的作用： 比如事务A update table ···· where id >100 那么加上这个锁，不仅id大于100的行记录上锁，还能保证id>100区间在 A未提交之前不能插入新的数据。

        根据示例数据表，下图就是间隙锁和行锁的示意图，0,5,10,15,20,25代表的是行锁会锁住的，而-∞,0到25,+∞代表的是间隙锁会锁住的。此时如果执行 select * from t where n=5 for update 的话，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

        间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间，也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

        备注：这篇文章中，如果没有特别说明，我们把间隙锁记为开区间，把 next-key lock 记为前开后闭区间。

        你可能会问说，这个 supremum 从哪儿来的呢？因为 +∞是开区间。实际上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的“前开后闭区间”。

6、行锁下的加锁规则（这个规则只限于5.x 系列 <=5.7.24，8.0 系列 <=8.0.13）

        规则1：查询过程中访问到的对象才会加锁，而加锁的基本单位是next-key lock（前开后闭）。

        规则2：对于非索引字段进行update或select .. for update等操作，代价极高。所有记录上锁，以及所有间隔的锁。

        规则3：等值查询：索引上的等值查询，如果是唯一索引且查询的这一行存在，则next-key lock会退化为行锁，如果不存在，则退化为间隙锁。如果不是唯一索引，向右遍历时且最后一个值不满足等值条件的时候，此时next-key lock会退化为间隙锁。

        规则4：范围查询：非唯一索引，范围查询需要访问到不满足条件的第一个值为止；唯一索引在mysql8.0.18之前是需要访问到不满足条件的第一个值为止，在这之后修复了这个bug，也就是只扫描到最后符合条件的数据。

7、案例

  案例一：等值查询间隙锁

        第一个例子是关于等值条件操作间隙：

        由于表 t_lock 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：

        根据规则1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；

        同时根据规则2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

        所以，session B 要往这个间隙里面插入 id=9 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

案例二：非唯一索引等值锁

        第二个例子是关于覆盖索引上的锁：

        看到这个例子，你是不是有一种“该锁的不锁，不该锁的乱锁”的感觉？我们来分析一下吧。

        这里 session A 要给索引 m 上 m=5 的这一行加上读锁。

        根据规则1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 m 是普通索引，因此仅访问 m=5 这一条记录是不能马上停下来的，需要向右遍历，查到 m=10 才放弃。

        根据规则1，访问到的都要加锁，因此要给 (5,10]加 next-key lock。但是同时这个符合规则3：向右便利且访问到最后一个值不满足 m=5 这个等值条件，因此退化成间隙锁 (5,10)。

        这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。但 session C 要插入一个 (8,8,8) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。

        需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。

        执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

        这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select n from t_lock where m=5 lock in share mode。

案例三：主键索引范围锁

        第三个例子是关于范围查询的。

        举例之前，可以先思考一下这个问题：对于我们这个表 t，下面这两条查询语句，加锁范围相同吗？

        mysql> select * from t where id=10 for update;

        mysql> select * from t where id>=10 and id<11 for update;

        你可能会想，id 定义为 int 类型，这两个语句就是等价的吧？其实，它们并不完全等价。

        在逻辑上，这两条查语句肯定是等价的，但是它们的加锁规则不太一样。现在，我们就让 session A 执行第二个查询语句，来看看加锁效果。

        现在我们就用前面提到的加锁规则，来分析一下 session A 会加什么锁呢？

        开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。

        根据规则3， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。

        范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。这样，session B 和 session C 的结果你就能理解了。（注：实验中mysql Ver 8.2.0 for macos13.5不锁这段(10,15]间隙了，可能已经被优化了）

        这里需要注意一点，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

案例四：非唯一索引范围锁

        接下来，我们再看两个范围查询加锁的例子，你可以对照着案例三来看。

        需要注意的是，与案例三不同的是，案例四中查询语句的 where 部分用的是字段 m。

        这次 session A 用字段 m 来判断，加锁规则跟案例三唯一的不同是：

        在第一次用 m=10 定位记录的时候，索引 m上加了 (5,10]这个 next-key lock 后，由于索引 m 是非唯一索引，不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 m 上的 (5,10] 和 (10,15] 这两个 next-key lock。

        所以从结果上来看，sesson B 要插入（9,9,9) 的这个 insert 语句时就被堵住了。这里需要扫描到 m=15 才停止扫描，是合理的，因为 InnoDB 要扫到 m=15，才知道不需要继续往后找了。

案例五：非唯一索引上存在"等值"的例子

        接下来的例子，是为了更好地说明“间隙”这个概念。

        这里，我给表 t_lock 插入一条新记录。

        mysql> insert into t_lock values(30,10,30);

        新插入的这一行 m=10，也就是说现在表里有两个 m=10 的行。那么，这时候索引 m 上的间隙是什么状态了呢？

        要知道，由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。

        可以看到，虽然有两个 m=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个 m=10 的记录之间，也是有间隙的。

        图中画出了索引 m上的主键 id。为了跟间隙锁的开区间形式进行区别，我用 (m=10,id=30) 这样的形式，来表示索引上的一行。

        现在，我们来看一下案例六。这次我们用 delete 语句来验证。注意，delete 语句加锁的逻辑，其实跟 select ... for update 是类似的。

        这时，session A 在遍历的时候，先访问第一个 m=10 的记录。同样地，根据规则1，这里加的是 (m=5,id=5) 到 (m=10,id=10) 这个 next-key lock。然后，session A 向右查找，直到碰到 (m=15,id=15) 这一行，循环才结束。

        根据规则3，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (m=10,id=10) 到 (m=15,id=15) 的间隙锁。也就是说，这个 delete 语句在索引 m上的加锁范围，就是下图中蓝色区域覆盖的部分。

        这个蓝色区域左右两边都是虚线，表示开区间，即 (m=5,id=5) 和 (m=15,id=15) 这两行上都没有锁。

案例六：limit 语句加锁

        案例六也有一个对照案例，场景如下所示：

        session A 的 delete 语句加了 limit 2。表 t_lock里 m=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。

        可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。 这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (m=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。 因此，索引 m上的加锁范围就变成了从（m=5,id=5) 到（m=10,id=30) 这个前开后闭区间，如下图所示：

        可以看到，(m=10,id=30）之后的这个间隙并没有在加锁范围里，因此 insert 语句插入 m=12 是可以执行成功的。 这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

8、MVCC

        MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种多并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。MVCC机制只工作在读已提交和可重复读这两个隔离级别下。

        在数据库中，读操作主要分为两种：当前读（Current Read）和快照读（Snapshot Read）。

        当前读：就是读取记录的最新版本，也就是最新的数据。如果数据在读取过程中被其他事务修改了，那么会读取到最新的数据内容。当前读会对读取的数据加锁，阻止其他事务对数据进行修改。当前读主要出现在UPDATE、DELETE、INSERT、SELECT…FOR UPDATE这些需要进行写操作的SQL语句中。

        快照读：读取的是记录在事务开始时的版本，也就是读取快照中的数据。即使在读取过程中数据被其他事务修改，读取到的数据内容也不会改变。快照读不会对读取的数据加锁，不会阻止其他事务对数据进行修改。快照读主要出现在我们日常的普通查询select ... from ... 中，MVCC使用的就是快照读。

        那么MVCC是怎么实现的快照读呢？

        其基于undo log和Read View(读视图)来实现的。其具体逻辑可以在《MySQL的MVCC实现机制详解》这篇文章中给与解答。本文不再详述。

9、版本号机制实现乐观锁

        版本号机制实现乐观锁一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数，当数据被修改时，version 值会加一。当线程 A 要更新数据值时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新，否则将会重试更新操作，直到更新成功。version字段在每次更新的时候单调递增可以解决ABA问题。

        优点：实现简单，方法比较通用，不仅mysql可以，在其他其他关系型数据库也可以使用。

        缺点：当应用并发量高的时候，version值在频繁变化，则会导致大量请求失败，影响系统的可用性。

10、总结

        MySQL的并发控制基于表锁和行锁，同时利用了MVCC机制来优化并发问题，行锁加锁的基本单位是next-key lock，在不同情况下会退化为行锁和间隙锁。平时我们自己在写sql时要注意索引的使用，尽量减少扫描的行数和加锁范围，遇到大数据查询需要更多的思考，提高数据库并发操作的性能。优秀的数据库服务也是一个实现高并发以及高性能系统的必要条件。

        如果这篇文章有什么说的不对的地方，还请各位大佬留言指正。

11、参考

        https://blog.csdn.net/weixin_48052161/article/details/122025234