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

75 阅读 0 评论 50 点赞

我是靠谱客的博主痴情樱桃，最近开发中收集的这篇文章主要介绍MySQL：Update高并发下变慢的案例及其涉及的特性，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

本文主要讨论的是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 matched: 1  Changed: 1  Warnings: 0
mysql> desc update testsemi30 set d=6 where c=6;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | testsemi30 | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.01 sec)

显然这个语句是全表扫描的update，但是最终看到的加锁row lock 只有一条 如下：

---TRANSACTION 808623, ACTIVE 19 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140735862056704, query id 349 localhost root
TABLE LOCK table `test`.`testsemi30` trx id 808623 lock mode IX
RECORD LOCKS space id 9694 page no 3 n bits 72 index PRIMARY of table `test`.`testsemi30` trx id 808623 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 0000000c56af; asc     V ;;
 2: len 7; hex 7b000001ea0fdc; asc {      ;;
 3: len 4; hex 80000006; asc     ;;
 4: len 4; hex 80000006; asc     ;;
 5: len 4; hex 80000006; asc     ;;

这就是unlock row的核心作用，但是实际上 每行都加过锁 ，只是不符合where条件的记录的被unlock 掉了，下文描述。继续做一个操作如下：

session2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testsemi30 where c=4 for update;
此处堵塞，row lock如下：
TABLE LOCK table `test`.`testsemi30` trx id 808624 lock mode IX
RECORD LOCKS space id 9694 page no 3 n bits 72 index PRIMARY of table `test`.`testsemi30` trx id 808624 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 0000000c5687; asc     V ;;
 2: len 7; hex e200000089011d; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000004; asc     ;;
 5: len 4; hex 80000004; asc     ;;

RECORD LOCKS space id 9694 page no 3 n bits 72 index PRIMARY of table `test`.`testsemi30` trx id 808624 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 6; hex 0000000c56af; asc     V ;;
 2: len 7; hex 7b000001ea0fdc; asc {      ;;
 3: len 4; hex 80000006; asc     ;;
 4: len 4; hex 80000006; asc     ;;
 5: len 4; hex 80000006; asc     ;;

这是因为这个语句虽然会触发unlock row，但是当加锁在primary id a=6 这一行的时候被session 1堵塞掉了，因为session 1经过unlock row特性优化后还是持有primary id a=6的这行记录的锁，当然select语句不存在semi update一说。

例子2：如果将上面session 2的select for update语句换为update语句就不同了如下：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update testsemi30 set d=4 where c=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
这个语句是可以完成。事务上锁如下：
---TRANSACTION 808627, ACTIVE 4 sec
2 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 18, OS thread handle 140735862867712, query id 363 localhost root
TABLE LOCK table `test`.`testsemi30` trx id 808627 lock mode IX
RECORD LOCKS space id 9694 page no 3 n bits 72 index PRIMARY of table `test`.`testsemi30` trx id 808627 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 0000000c56b3; asc     V ;;
 2: len 7; hex 7e000001da1d79; asc ~     y;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000004; asc     ;;
 5: len 4; hex 80000004; asc     ;;

这实际上就是semi update的核心理念，它能够让本应该堵塞的update语句继续执行，即便session 1持有primary id a=6的这行记录的锁，也可以继续。

3.2 unlock row特性

就是例子1中的测试

1、Update访问一条数据，innodb层获取row lock。

2、MySQL层根据where条件，如果是不需要的行，则直接unlock掉，这个操作的核心函数就是ha_innobase::unlock_row

而在Update上，我们也很容看到这种比较和过滤，下面是MySQL 过滤where条件的行

mysql_update:
 if ((!qep_tab.skip_record(thd, &skip_record) && !skip_record)) //跳过操作 是否符合查询条件
table->file->unlock_row(); //如果是where条件过滤的直接跳到解锁这步

对比比较我们可以直接debug整数的比较函数如下：
#0  Item_func_eq::val_int (this=0x7fff2800ad28) at /opt/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.cc:2506
#1  0x0000000000f4a17b in QEP_TAB::skip_record (this=0x7fff9f1cdf78, thd=0x7fff28012cc0, skip_record_arg=0x7fff9f1ce0fe) at /opt/percona-server-locks-detail-5.7.22/sql/sql_executor.h:457
#2  0x0000000001626efa in mysql_update (thd=0x7fff28012cc0, fields=..., values=..., limit=18446744073709551615, handle_duplicates=DUP_ERROR, found_return=0x7fff9f1ce268, 
    updated_return=0x7fff9f1ce260) at /opt/percona-server-locks-detail-5.7.22/sql/sql_update.cc:816
这个地方可以看到两个比较的值
(gdb) p val1
$12 = 2
(gdb) p val2
$13 = 2

另外在ha_innobase::unlock_row函数中为了适配semi update，也做了相应的逻辑如下，

 switch (m_prebuilt->row_read_type) {
 case ROW_READ_WITH_LOCKS: //如果是加锁了
  if (!srv_locks_unsafe_for_binlog //判定隔离级别为RC才做解锁
      && m_prebuilt->trx->isolation_level
      > TRX_ISO_READ_COMMITTED) {
   break;
  }
  /* fall through */
 case ROW_READ_TRY_SEMI_CONSISTENT://如果semi update，TRY_SEMI才进行解锁
  row_unlock_for_mysql(m_prebuilt, FALSE);  mysql_update
  break;
 case ROW_READ_DID_SEMI_CONSISTENT://如果semi update，为DID_SEMI那么就不做了，因为没有锁可以解了，semi update 已经在引擎层解掉了
  m_prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT;
  break;
 }

这是因为对于semi update遇到row lock堵塞的时候直接就在堵塞后直接解锁了，不需要回到MySQL层解锁（如下文所述）。那么这个特性两个重要影响就是如下：

每行row lock加锁是不可避免的，但是会在MySQL层判定后解锁，那么最终这个事务加锁的记录就会很少，这会提高业务的并发，这一点是非常重要的，这种情况下show engine 最终看到的row lock 锁信息就很少了。
但是频繁的lock/unlock rec导致LOCK_SYS这个mutex很容易成为热点mutex。

我们可以简单看一下unlock rec的函数lock_rec_unlock，这个函数一上来就可能看到加锁LOCK_SYS，然后通过hash算法，在lock_sys_t中找到对用cell的头节点，然后遍历找到相应的block对应的lock_t结构，然后调用lock_rec_reset_nth_bit函数，解锁相应的位图结构（row lock所在的位置）。

3.3 semi update特性

就是例子2中的测试，这个特性一定要在出现了row lock堵塞后才会进行判定，是innodb层直接就解除了堵塞，如下，

1、Update 修改一行数据之前设置标记ROW_READ_TRY_SEMI_CONSISTENT

2、访问一行数据，innodb层尝试获取row lock，如果被堵塞则触发semi update判定，判定的规则包含

不能为唯一性扫描（unique_search）
必须为主键（index != clust_index）
不能产生死锁（Check whether it was a deadlock or not）
RC隔离级别或者innodb_locks_unsafe_for_binlog参数设置了(8.0移除了本参数)
update语句才可以

主键的非唯一性扫描，最常见的就是全表扫描了。

3、访问本行修改前的old rec 记录（row_sel_build_committed_vers_for_mysql），并且解除堵塞（lock_cancel_waiting_and_release），解除的时候，会将事务wait_lock设置为NULL，同时从 trx_lock中移除，lock_sys_t中的hash结构也会清除掉。实际上lock_cancel_waiting_and_release就是本特性的核心函数。及如下：

lock_cancel_waiting_and_release
   ->lock_rec_dequeue_from_page //lock_sys_t中的hash结构会清除，trx_lock中移除
   ->lock_reset_lock_and_trx_wait //wait_lock设置为NULL

4、返回old rec给mysql层，并且设置变量did_semi_consistent_read=true（导致设置标记ROW_READ_DID_SEMI_CONSISTENT）

5、判定是否满足where条件，如果不满足就扫描下一行了，如果满足再次进入innodb层进入堵塞状态，这个时候ROW_READ_DID_SEMI_CONSISTENT标记已经设置不会再做semi update的判定了，同时如上文如果ROW_READ_DID_SEMI_CONSISTENT标记设置了就不会真正触发unlock row操作。

和unlock row特性不同，unlock row 围绕的核心是让 整个语句执行完成后加锁的行更少 ，而semi update 围绕的核心是出现了 堵塞后update语句(触发了全表扫描)是否能够继续 ，这是非常重要的不同点。

四、额外的问题

分析到这里，我们知道了本案例中是由于没有使用到索引进行update语句出现了大量的lock rec和unlock rec 导致lock_sys_t 结构的mutex LOCK_SYS出现了热点锁，但是还有一个奇怪的问题如下：

image.png

注意到这里的row lock和lock struct 都是比较多的，为什么会这样呢，经过unlock row和semi update过后锁定的行数应该是只有1行。为了更方便的讨论这部分，我们将涉及到的数据结构的元素画个简单的图，同时讲上面提到的lock_sys_t涉及的hash结构也画一下，需要注意的是这些数据结构元素很多很多，这里只话了和问题相关的部分，涉及得很少。

这里需要注意几点：

对于这个rec_hash这个hash查找表的 hash值来自于space_id和page_no 。
lock_t是所谓的lock struct，相关的属性比如LOCK_X/LOCK_S，还有LOCK_REC_NOT_GAP/LOCK_GAP/LOCK_WAIT/LOCK_ORDINARY/LOCK_INSERT_INTENTION 等都是它的属性，而不是某行记录的属性。言外之意如果获取一个row lock，如果正常获取就可以合并到现有page的lock_t中，如果堵塞了必须要新建lock_t，因为这个lock_t带有属性LOCK_WAIT。
一个lock_t的bit map最多能够容纳下一个page的所有行的加锁情况。
bit map才是实际的加锁的体现，它附着在每一个lock_t结构上，innodb通过lock_t[1]快速的找到了他的位置，然后进行设置，在函数lock_rec_reset_nth_bit可以看到这种操作如下：

reinterpret_cast<byte*>(&lock[1])

好了回到上面的问题， row locks和lock struct这两个输出，实际上来自如下：

row locks:trx->lock->n_rec_locks 这个值是trx_lock_t上的一个统计值而已，在每个调用函数lock_rec_reset_nth_bit和lock_rec_set_nth_bit的末尾减少和增加，对应是解锁和加锁某一行操作。
lock struct: UT_LIST_GET_LEN(trx->lock.trx_locks) 这个值实际上就是上面我们看到的链表的长度，应该来说是比较准确的。

那么，虽然unlock row 释放了rec lock也就是设置了其标记的bit位，但是lock_t结构本身没有释放，所以lock struct多也可以理解，但是因为上锁和解锁通常要遍历整个page所在lock_sys_t的cell链表上的所有lock struct，如果lock struct多那上LOCK_SYS mutex持有的时间就更长，也符合我们本次问题由于没有用到索引，且并发执行大量的update导致的LOCK_SY mutex的spin。

但是row locks看起来就不那么准确了，随后我做了一个测试，只做了少量的行，触发了一次semi update，看到了结果也是2 row lock，如下：

表结构和数据：
mysql> show create table testsemi40 G
*************************** 1. row ***************************
       Table: testsemi40
Create Table: CREATE TABLE `testsemi40` (
  `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=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
mysql> select *from testsemi40;
+---+------+------+----+
| a | b    | c    | d  |
+---+------+------+----+
| 2 |    2 |    2 | 0 |
| 4 |    4 |    4 | 0 |
| 6 |    6 |    6 | 0 |
+---+------+------+----+
3 rows in set (0.00 sec)
session 1：
mysql> begin;
Query OK, 0 rows affected (0.10 sec)
mysql> update testsemi40 set d=6 where c=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session2：
mysql> begin;
Query OK, 0 rows affected (0.10 sec)
mysql> update testsemi40 set d=2 where c=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

show engine信息，session2上锁的信息如下：
---TRANSACTION 808633, ACTIVE 4 sec
2 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1 (这里有2 row locks)
MySQL thread id 18, OS thread handle 140735862867712, query id 381 localhost root
TABLE LOCK table `test`.`testsemi40` trx id 808633 lock mode IX
RECORD LOCKS space id 9695 page no 3 n bits 72 index PRIMARY of table `test`.`testsemi40` trx id 808633 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000c56b9; asc     V ;;
 2: len 7; hex 21000001ec2701; asc !    ' ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000002; asc     ;;
 5: len 4; hex 80000002; asc     ;;

但是我顺着show engine打印本事务的每个lock_t中的bit map加锁结构如下：

断点：lock_rec_print 
大体输出流程如下：
lock_print_info_all_transactions
循环输出所有的事务的信息 
 ->lock_trx_print_locks 
    循环输出当前事务的所有lock_t 行锁信息
    ->lock_rec_print 
      循环lock_t的位图信息，打印出详细的加锁行

我们只需要在lock_rec_print 函数中通过如下输出
(gdb) p (&lock[1])
$21 = (const ib_lock_t *) 0x2fd79c0
(gdb) x/8bx 0x2fd79c0
0x2fd79c0:      0x04    0x00    0x00    0x00    0x00    0x00    0x00    0x00
打印所有的lock_t结构就可以了

实际上这里只有一个实际上就只有1个lock_t(当然是rec_lock，不讨论table_lock)结构，看到的加锁信息就是0x04，转二进制就是100，显然就是1行加锁了嘛，对应的heap no 2这一行， heap no 0和heap no 1是innodb的page里面的2个伪列。工具blockinfo输出可以确认如下：

(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(3) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3

这样我们就确认了在semi update的方式下，row locks的这个计数器统计应该是出现问题的，有什么情况下不会调用lock_rec_reset_nth_bit函数来减少这个计数器呢？

实际这个问题就出现在semi update的核心函数lock_cancel_waiting_and_release上，解除等待时候是将整体lock_t结构给抹掉了，而MySQL层又不会调用unlock row，因为lock_t结构都没有了，也就是核心减少计数器的函数lock_rec_reset_nth_bit并没有调用。因此这个trx->lock->n_rec_locks 计数器在semi update触发的情况下 只增加了没减少 。言外之意就是semi update在高并发下发生的次数越多，row locks的计数就越不准确。那么稍微修改一下代码验证一下（仅为验证这种场景，这种修改可能并不可取），我使用在8.0.23上做了同样测试结果一致，同时在8.0.23代码上做的修改，增加2行如下：

void lock_reset_lock_and_trx_wait(lock_t *lock) /*!< in/out: record lock */
{
...
  @see trx_lock_t::wait_lock_type for more detailed explanation. */
  lock->type_mode &= ~LOCK_WAIT; 
  ut_ad(lock->trx->lock.n_rec_locks.load() > 0); //增加
  lock->trx->lock.n_rec_locks.fetch_sub(1, std::memory_order_relaxed);  //增加

然后我们使用前面的方式继续测试发现得到row lock值已经准确了如下：

---TRANSACTION 2740515, ACTIVE 6 sec
2 lock struct(s), heap size 1200, 1 row lock(s), undo log entries 1 （这里显示正确了）
MySQL thread id 9, OS thread handle 140736352634624, query id 36 localhost root starting
show engine innodb status
---TRANSACTION 2740513, ACTIVE 54 sec
2 lock struct(s), heap size 1200, 1 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140736353167104, query id 21 localhost root

当然这么改可能是不合适的，因为这个函数调用者还很多，这里只是修改后验证一下这个猜想。确实这种情况容易导致DBA误判，实际上row lock 并没有row locks统计出来的那么多，随后给官方提交下BUG看看。

最后

这个问题处理起来还是比较简单，但是背后还是有很多可以深挖的地方，本文主要使用的代码是5.7.22，对于semi update下row locks不准的情况在8.0.28 也测试了，依旧存在这个问题。另外在8.0中热点锁LOCK_SYS视乎做了拆分，也许情况会好一些，随后也可以学习下这部分内容，看看官方如何拆锁的。