点击参与 GreatSQL 8.0.32-26 文档捉虫~~~

GreatSQL社区

搜索

[已解决] 为啥mysql5.7在执行批量update的过程中阻塞了select查询?

1670 10 2023-7-20 16:58
环境：mysql5.7 ，隔离级别是读已提交，表记录数大约32万，更新语句更新了大约12万记录。


现象：update语句执行了大约18秒，执行过程中processlist里面发现这个表上的select查询等待waitting for table flush，执行过程中没有对表进行其他操作，更新结束后恢复正常

建表语句，更新语句，查询语句如下：
建表语句

CREATE TABLE `tb_reg_xxxx` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT  ,
  `mch_id` varchar(32) NOT NULL  ,
  `business_code` varchar(64) NOT NULL  ,
  `login_account` varchar(512) DEFAULT NULL ,
  `sub_mch_id` varchar(32) DEFAULT NULL  ,
  `status` varchar(32) DEFAULT NULL  ,
  `business_type` varchar(32) DEFAULT NULL  ,
  `sign_status` varchar(32) DEFAULT NULL  ,
  `creator` varchar(32) DEFAULT NULL    ,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP  ,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  ,
  `contract_type` varchar(16) DEFAULT NULL    ,
  `pos_mch_id` varchar(32) DEFAULT NULL   ,
  `open_account_time` datetime DEFAULT NULL    ,
  `reseller_id` varchar(16) DEFAULT NULL  ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_reg_request_businesscode` (`mch_id`,`business_code`),
  KEY `udx_reg_request_ctime` (`create_time`),
  KEY `idx_reg_request_login_account` (`login_account`),
  KEY `udx_reg_request_utime` (`update_time`),
  KEY `udx_reg_request_otime` (`open_account_time`) USING BTREE,
  KEY `idx_regrequest_submchid` (`sub_mch_id`),
  KEY `idx_reg_req_status` (`status`,`update_time`),
  KEY `idx_reg_req_mch_resell` (`mch_id`,`reseller_id`,`creator`)
) ENGINE=InnoDB AUTO_INCREMENT=327821 DEFAULT CHARSET=utf8mb4 ;
更新语句
UPDATE tb_reg_xxxx SET sign_status='SIGNING' WHERE contract_type='E' AND  sign_status='WAIT';

查询语句类似

select * from  (
    select *
    from tb_reg_xxxx
     WHERE (  mch_id = '123456789'
                  and business_type = 'RESELLER' )
      order by  create_time desc
    )row_ limit 0,10




全部回复(10)
只看楼主
鸟山明 2023-7-20 20:09:25
没有其他语句了吗？比如 DDL、备份
回复 采纳

举报

yejr 2023-7-21 09:34:37
update的where条件看起来用不到任何索引，这时会锁定表中全部记录，等同于表级锁效果
回复 采纳

举报

qtiger 2023-7-21 10:21:53
鸟山明 发表于 2023-7-20 20:09
没有其他语句了吗？比如 DDL、备份

没有其他操作了，只有一个更新，并且更新完成后就恢复了
回复 采纳

举报

qtiger 2023-7-21 10:23:49
yejr 发表于 2023-7-21 09:34
update的where条件看起来用不到任何索引，这时会锁定表中全部记录，等同于表级锁效果 ...

叶老师，理论上无索引更新锁全表，但是应该不影响查询吧，我模拟了一下，没有再现生产情况，模拟时，无索引更新时可以查询
回复 采纳

举报

yejr 2023-7-21 16:30:49
qtiger 发表于 2023-7-21 10:23
叶老师，理论上无索引更新锁全表，但是应该不影响查询吧，我模拟了一下，没有再现生产情况，模拟时，无索 ...

可能是update期间有其他ddl sql，会被阻塞，后面的select会跟着被ddl阻塞
回复 采纳

举报

qtiger 2023-7-21 17:33:39
update是手工数据修订，执行过程中没有人工执行ddl操作，备份是放在从库做的。后面看生产还能复现不，自己没有模拟出来，可能有未发现的操作，待进一步验证。
回复 采纳

举报

张旭峰 2023-7-23 15:42:45
本帖最后由 张旭峰 于 2023-7-23 15:45 编辑
qtiger 发表于 2023-7-21 17:33
update是手工数据修订，执行过程中没有人工执行ddl操作，备份是放在从库做的。后面看生产还能复现不，自己 ...

T1  执行 update t1 语句执行了大约18秒
T2  执行 flush tables
T3  执行 select xx  from t1
T4  执行 select xx  from t1  

show processlist ； 看T3 T4都会出现  waitting for table flush   ；有没有业务上 会执行 flush tables
回复 采纳

举报

qtiger 2023-8-2 11:15:53
今天又重现了这个情况，
有一个400万左右的表，执行sql  
SELECT COUNT(DISTINCT parent_id) FROM cp_xxxx_bind_relation
parent-id上没有索引，走全表扫描

通过另一个会话show processlist，最后一行是查询的distinct吗，其他等待的都是这个表上的单纯select，啥情况下才会出现这个情况？

processlist

processlist
回复 采纳

举报

qtiger 2023-8-2 13:01:39
本帖最后由 qtiger 于 2023-8-2 18:15 编辑

分析下来，是有配置ogg同步数据，ogg会定期进行flush table，当存在长事物的时候就会出现这个等待事件。
根据执行时间看，是大事务先执行，然后ogg进行了flush table，再后面发起的所有对这个表的dml都被挂起，造成了业务暂停

回复 已采纳

举报

下一页 »
12下一页
qtiger

1

主题

0

博客

11

贡献

新手上路

Rank: 1

积分
17
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-12-4 01:30 , Processed in 0.022115 second(s), 19 queries , Redis On.
快速回复 返回顶部 返回列表