Update高并发下变慢分析及semi-consistent read
The following article is from MySQL学习 Author 高鹏(八怪)
背景提醒
本文主要讨论的是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)
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 |
+----+------+------+---+
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一说。
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出现了热点锁，但是还有一个奇怪的问题如下：
注意到这里的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个伪记录Infimum 和 Supremum）。利用工具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似乎做了拆分，也许情况会好一些，随后也可以学习下这部分内容，看看官方如何拆锁的。
全文完。
《深入浅出MGR》视频课程
戳此小程序即可直达B站
https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0
文章推荐：
想看更多技术好文，点个“在看”吧！