MySQL：Update高并发下变慢的案例及其涉及的特性

最新推荐文章于 2025-06-04 15:33:46 发布

原创

最新推荐文章于 2025-06-04 15:33:46 发布 · 4.1k 阅读

标签

#mysql #java #数据库

本文主要讨论的是RC隔离级别，代码主要集中在5.7.22，为了描述方便本文中涉及的semi update就是官方说的semi-consistent read特性。水平有限，仅供参考。

一、问题说明

最近遇到一个问题，以下是模拟出来的现象（RC隔离级别，5.7.31版本），正常情况下，这个update语句的执行时间很快，但是到了高并发情况下就很慢了。

当然这个问题解决很简单，但是其背后还是有很多值得挖掘的地方，这里就从问题分析出发，顺带挖一下其涉及的部分。

二、分析方式

既然是update语句并发处理的情况变慢，我们先从常规触发看看是不是被堵塞了。首先我们能看到state为updating状态，那么就说明如下：

MDL LOCK堵塞不可能，因为state状态不对，MDL LOCK堵塞的现象是waitting for开头的。
可能是row lock堵塞，因为在update语句的情况下row lock堵塞也是updating状态。

进一步通过show engine 和确认没有出现row lock堵塞，show engine截图如下：

我们可以看到这里事务都处于活跃状态，大部分是unlock_row阶段，也有fetching rows阶段的事务，那么说明事务是在运行的，那么接下来通过CPU耗用确认是否会话出现了内部堵塞，如果长时间的堵塞CPU肯定会下降，如果是在耗用CPU干活就可能CPU就比较高，如下：

我们看到CPU还是比较高的，那么CPU高也有两种可能就是遇到spin 和正常的代码逻辑，对于spin来讲一般是内部mutex在正式放弃CPU前做的多次尝试，这个和我们的参数innodb_sync_spin_loops/innodb_spin_wait_delay设置有关（一般没有设置保持默认值），并且show engine 可能会有输出，通过show engine进行确认如下：

这里我们确实可以看到一个mutex叫做LOCK_SYS，接着看看perf信息如下：

确实有大量的ut_delay耗用CPU，且函数指向了加行锁等待上，同时LOCK_SYS也正是row_lock的全局hash结构所在位置的mutex，这就说明了这个语句出现了大量的row_lock需要加锁和解锁，导致LOCK_SYS mutex出现了热点锁。

接着查看表结构，建表语句如下：

create table testsemi(a int auto_increment primary key,b int,c int,d int,key(b,c));
修改语句大概如下：
update testsemi set d=20 where c=20;
数据量大约百万左右。

当然这样由于c=20不是索引的前缀，在RR模式下会出现全纪录加锁，而在RC模式下会触发2个优化：

Innodb层 semi update
MySQL层unlock row

解决当然也很简单，起码c列上要有个索引能够用到。接下来我们就讨论这两个优化大概实现方式和一个存在的问题。

三、RC隔离级别下的semi update和unlock row优化

3.1 相关列子

为了更好的解释这两种特性我们先来看两个例子，建表语句和数据如下：

mysql> show variables like '%transaction_isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
mysql> show create table testsemi30 \G;
*************************** 1. row ***************************
       Table: testsemi30
Create Table: CREATE TABLE `testsemi30` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from testsemi30;
+----+------+------+---+
| a  | b    | c    | d |
+----+------+------+---+
|  2 |    2 |    2 | 0 |
|  4 |    4 |    4 | 0 |
|  6 |    6 |    6 | 0 |
|  8 |    8 |    8 | 0 |
| 12 |   12 |   12 | 0 |
+----+------+------+---+
5 rows in set (0.00 sec)

3.1.2 例子1：

session1:
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> update testsemi30 set d=6 where c=6;
Query OK, 1 row affected (0.00 sec)
Rows match

最低0.47元/天解锁文章