Searching rows for update状态初探

比眉伴天荒 2024-04-17 06:28 53阅读 0赞

导读

> 作者徐晨亮，MySQL DBA，知数堂学员。热衷于数据库优化，自动化运维及数据库周边工具开发，对MySQL源码有一定的兴趣。

--------------------

### 一、背景说明 ###

最近有位朋友咨询说为何如此多线程处于Searching rows for update，当时看到这个状态的第一反应就是锁，这里暂且抛开锁不谈，谈一谈为何出现`Searching rows for update`

### 二、实验环境： ###

root@mysqldb 10:15:  [xucl]> show create table test1\G
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `a` int(11) NOT NULL,
      `b` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`a`),
      KEY `b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    root@mysqldb 10:15:  [xucl]> select * from test1;
    +---+------+
    | a | b    |
    +---+------+
    | 2 | b    |
    | 3 | c    |
    | 1 | ccc  |
    +---+------+
    3 rows in set (0.00 sec)

大概出现的状态如下所示：

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

三、初探过程

简单做了pstack，其中id=2的线程堆栈如下：

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 1]

从堆栈很明显可以看出，该线程处于锁等待的状态，但为何处于`Searching rows for update`并没有给出明确暗示，可以看到调用的顺序是

1.  mysql\_parse
2.  mysql\_execute\_command
3.  Sql\_cmd\_update::execute\_command
4.  mysql\_update
5.  ...
6.  ha\_innobase::index\_read
7.  ...
8.  lock\_wait\_suspend\_thread
9.  ...

废话不多说，咱们直接从`mysql_update`切入，该入口函数位置在`sql_update.cc`

bool mysql_update(THD *thd,
                      List<Item> &fields,
                      List<Item> &values,
                      ha_rows limit,
                      enum enum_duplicates handle_duplicates,
                      ha_rows *found_return, ha_rows *updated_return)
    {
    ...
    if (used_key_is_modified || order)
    {
     /*
              When we get here, we have one of the following options:
              A. used_index == MAX_KEY
              This means we should use full table scan, and start it with
              init_read_record call
              B. used_index != MAX_KEY
              B.1 quick select is used, start the scan with init_read_record
              B.2 quick select is not used, this is full index scan (with LIMIT)
              Full index scan must be started with init_read_record_idx
            */
    
            if (used_index == MAX_KEY || (qep_tab.quick()))
              error= init_read_record(&info, thd, NULL, &qep_tab, 0, 1, FALSE);
            else
              error= init_read_record_idx(&info, thd, table, 1, used_index, reverse);
    
            if (error)
              goto exit_without_my_ok;
    
            THD_STAGE_INFO(thd, stage_searching_rows_for_update);
            ha_rows tmp_limit= limit;
    }
    ...

debug结果如下：

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 2]

这里判断条件为`used_index`是否等于`MAX_KEY`，其中`MAX_KEY`为常量，`used_index`的定义如下：

used_index= get_index_for_order(order, &qep_tab, limit, &need_sort, &reverse);

这里的判断就比较复杂了，本人水平有限，暂时未深入理解优化器的部分，也不展开说明，源码位置在`sql_select.cc`，有兴趣的同学可以深入研究一下

函数is\_key\_used定义如下：

bool is_key_used(TABLE *table, uint idx, const MY_BITMAP *fields)
    {
      bitmap_clear_all(&table->tmp_set); //清空tmp_set位图
      table->mark_columns_used_by_index_no_reset(idx, &table->tmp_set); //这里设置位图
      const bool overlapping= bitmap_is_overlapping(&table->tmp_set, fields); //比较索引位置和修改位置是否重合
      
      // Clear tmp_set so it can be used elsewhere
      bitmap_clear_all(&table->tmp_set);
      if (overlapping)
        return 1; //如果重合返回1
    ...

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 3]

看到debug的结果变量，`used_key_is_modified`为true，那么进入如下判断

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 4]

然后就进入`stage_searching_rows_for_update`状态，也就是我们一开始在show processlist中看到的状态

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 5]

而如果我们修改的是其他字段，那么进入的状态便是`Updating`，对应的源码为

if (used_key_is_modified || order)
    {
    ...
    }
    ...
    thd->count_cuted_fields= CHECK_FIELD_WARN;
    thd->cuted_fields=0L;
    THD_STAGE_INFO(thd, stage_updating);
    ...

废话不多说，我们来测试验证一下`THD_STAGE_INFO(thd, stage_updating);`处打上断点，然后更新数据

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 6]

实验结果符合预期

其他测试结果：

<table> 
 <thead> 
  <tr> 
   <th>case</th> 
   <th>结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>update主键</td> 
   <td>直接进入stage_updating</td> 
  </tr> 
  <tr> 
   <td>update唯一索引</td> 
   <td>直接进入stage_updating</td> 
  </tr> 
  <tr> 
   <td>update普通二级索引+limit</td> 
   <td>进入stage_searching_rows_for_update，完成后进入stage_updating</td> 
  </tr> 
 </tbody> 
</table>

### 四、总结 ###

最后总结一下：

1.  `Searching rows for update`状态出现的要求比较严格，当进行数据更新时，如果更新的字段为当前执行计划用到的索引，并且该索引属于普通二级索引（不能是主键也不能是唯一索引），那么就会出现`Searching rows for update`状态，正因为出现了锁等待，所以能看到这种状态
2.  如果不是，那么直接进入`Updating`状态，这个状态也就是我们经常看到的状态
3.  出现如上两种状态并且持续时间长，并不是造成数据库性能下降的根本原因，而应该考虑其他原因，如本案例中的锁等待问题

**作者寄语：****由于本人对优化器部分研究并不深入，并未列出全部情况，待后续慢慢补充，也欢迎各位在留言区补充**

END

--------------------

![640?wx\_fmt=png][640_wx_fmt_png]

***![640?wx\_fmt=png][640_wx_fmt_png 1]***

![640?wx\_fmt=gif][640_wx_fmt_gif]

扫码加入MySQL技术Q群

**（群号：****529671799）**

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 7]

[640_wx_fmt_jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/92828305a9da41548543d91d76cd8fb7.png
[640_wx_fmt_jpeg 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/efe9e4e7df8042e093d27c6f841f0836.png
[640_wx_fmt_jpeg 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8cd86090c63d4a6db9ef196e696f1dad.png
[640_wx_fmt_jpeg 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8901bbdf9b0f4293af53246d64ce3849.png
[640_wx_fmt_jpeg 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/60d5ea0b99064cffb03e367cc7f8d497.png
[640_wx_fmt_jpeg 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/03dd837e6010495085895d1d4256b494.png
[640_wx_fmt_jpeg 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/bdb0a55261e541008734869cd8bc6cd5.png
[640_wx_fmt_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/ea451b3fc18d4c25bd7ad80e0a79236d.png
[640_wx_fmt_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/db9b2c9611ab458db8ae08d08c9048d6.png
[640_wx_fmt_gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/5bee333969404a008553424d4b742ac0.gif
[640_wx_fmt_jpeg 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/165a006648ef482dba4acf4a118e1d88.png