Searching rows for update状态初探

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

一、背景说明

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

二、实验环境:


  1. root@mysqldb 10:15:  [xucl]> show create table test1\G

  2. *************************** 1. row ***************************

  3.       Table: test1

  4. Create Table: CREATE TABLE `test1` (

  5.  `a` int(11) NOT NULL,

  6.  `b` varchar(20) DEFAULT NULL,

  7.  PRIMARY KEY (`a`),

  8.  KEY `b` (`b`)

  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

  10. 1 row in set (0.00 sec)


  11. root@mysqldb 10:15:  [xucl]> select * from test1;

  12. +---+------+

  13. | a | b    |

  14. +---+------+

  15. | 2 | b    |

  16. | 3 | c    |

  17. | 1 | ccc  |

  18. +---+------+

  19. 3 rows in set (0.00 sec)

大概出现的状态如下所示

Searching rows for update状态初探

三、初探过程

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

Searching rows for update状态初探

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

  1. mysql_parse

  2. mysqlexecutecommand

  3. Sqlcmdupdate::execute_command

  4. mysql_update

  5. ...

  6. hainnobase::indexread

  7. ...

  8. lockwaitsuspend_thread

  9. ...

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


  1. bool mysql_update(THD *thd,

  2.                  List<Item> &fields,

  3.                  List<Item> &values,

  4.                  ha_rows limit,

  5.                  enum enum_duplicates handle_duplicates,

  6.                  ha_rows *found_return, ha_rows *updated_return)

  7. {

  8. ...

  9. if (used_key_is_modified || order)

  10. {

  11. /*

  12.          When we get here, we have one of the following options:

  13.          A. used_index == MAX_KEY

  14.          This means we should use full table scan, and start it with

  15.          init_read_record call

  16.          B. used_index != MAX_KEY

  17.          B.1 quick select is used, start the scan with init_read_record

  18.          B.2 quick select is not used, this is full index scan (with LIMIT)

  19.          Full index scan must be started with init_read_record_idx

  20.        */


  21.        if (used_index == MAX_KEY || (qep_tab.quick()))

  22.          error= init_read_record(&info, thd, NULL, &qep_tab, 0, 1, FALSE);

  23.        else

  24.          error= init_read_record_idx(&info, thd, table, 1, used_index, reverse);


  25.        if (error)

  26.          goto exit_without_my_ok;


  27.        THD_STAGE_INFO(thd, stage_searching_rows_for_update);

  28.        ha_rows tmp_limit= limit;

  29. }

  30. ...

debug结果如下

Searching rows for update状态初探

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

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

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

函数iskeyused定义如下:


  1. bool is_key_used(TABLE *table, uint idx, const MY_BITMAP *fields)

  2. {

  3.  bitmap_clear_all(&table->tmp_set); //清空tmp_set位图

  4.  table->mark_columns_used_by_index_no_reset(idx, &table->tmp_set); //这里设置位图

  5.  const bool overlapping= bitmap_is_overlapping(&table->tmp_set, fields); //比较索引位置和修改位置是否重合


  6.  // Clear tmp_set so it can be used elsewhere

  7.  bitmap_clear_all(&table->tmp_set);

  8.  if (overlapping)

  9.    return 1; //如果重合返回1

  10. ...

Searching rows for update状态初探

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

Searching rows for update状态初探

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

Searching rows for update状态初探

而如果我们修改的是其他字段,那么进入的状态便是 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);处打上断点,然后更新

Searching rows for update状态初探

实验结果符合预期

其他测试结果:

case 结果
update主键 直接进入 stage_updating
update唯一索引 直接进入 stage_updating
update普通二级索引+limit 进入 stage_searching_rows_for_update,完成后进入stage_updating

四、总结

最后总结一下:

  1. Searchingrowsforupdate状态出现的要求比较严格,当进行数据更新时,如果更新的字段为当前执行计划用到的索引,并且该索引属于普通二级索引(不能是主键也不能是唯一索引),那么就会出现 Searchingrowsforupdate状态,正因为出现了锁等待,所以能看到这种状态

  2. 如果不是,那么直接进入 Updating状态,这个状态也就是我们经常看到的状态

  3. 出现如上两种状态并且持续时间长,并不是造成数据库性能下降的根本原因,而应该考虑其他原因,如本案例中的锁等待问题

问题点:由于本人对优化器部分研究并不深入,并未列出全部情况,待后续慢慢补充,也欢迎各位读者补充

-The End-


本公众号长期关注于数据库技术以及性能优化,故障案例分析,数据库运维技术知识分享,个人成长和自我管理等主题,欢迎扫码关注。

Searching rows for update状态初探


上一篇:Codeforces Round #700 (Div. 2) C. Searching Local Minimum(二分)


下一篇:# 20192315 2020-2021-1 《数据结构与面向对象程序设计》实验七报告