为啥mysql5.7在执行批量update的过程中阻塞了select查询?
环境：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' ANDsign_status='WAIT';
查询语句类似
select * from(
select *
from tb_reg_xxxx
WHERE (mch_id = '123456789'
and business_type = 'RESELLER' )
order bycreate_time desc
)row_ limit 0,10
没有其他语句了吗？比如 DDL、备份 update的where条件看起来用不到任何索引，这时会锁定表中全部记录，等同于表级锁效果 鸟山明 发表于 2023-7-20 20:09
没有其他语句了吗？比如 DDL、备份
没有其他操作了，只有一个更新，并且更新完成后就恢复了 yejr 发表于 2023-7-21 09:34
update的where条件看起来用不到任何索引，这时会锁定表中全部记录，等同于表级锁效果 ...
叶老师，理论上无索引更新锁全表，但是应该不影响查询吧，我模拟了一下，没有再现生产情况，模拟时，无索引更新时可以查询 qtiger 发表于 2023-7-21 10:23
叶老师，理论上无索引更新锁全表，但是应该不影响查询吧，我模拟了一下，没有再现生产情况，模拟时，无索 ...
可能是update期间有其他ddl sql，会被阻塞，后面的select会跟着被ddl阻塞 update是手工数据修订，执行过程中没有人工执行ddl操作，备份是放在从库做的。后面看生产还能复现不，自己没有模拟出来，可能有未发现的操作，待进一步验证。 本帖最后由 张旭峰 于 2023-7-23 15:45 编辑
qtiger 发表于 2023-7-21 17:33
update是手工数据修订，执行过程中没有人工执行ddl操作，备份是放在从库做的。后面看生产还能复现不，自己 ...
T1执行 update t1 语句执行了大约18秒
T2执行 flush tables
T3执行 select xxfrom t1
T4执行 select xxfrom t1
show processlist ； 看T3 T4都会出现waitting for table flush ；有没有业务上 会执行 flush tables
今天又重现了这个情况，
有一个400万左右的表，执行sql
SELECT COUNT(DISTINCT parent_id) FROM cp_xxxx_bind_relation
parent-id上没有索引，走全表扫描
通过另一个会话show processlist，最后一行是查询的distinct吗，其他等待的都是这个表上的单纯select，啥情况下才会出现这个情况？
d:\微信图片_20230802111335.png 本帖最后由 qtiger 于 2023-8-2 18:15 编辑
分析下来，是有配置ogg同步数据，ogg会定期进行flush table，当存在长事物的时候就会出现这个等待事件。
根据执行时间看，是大事务先执行，然后ogg进行了flush table，再后面发起的所有对这个表的dml都被挂起，造成了业务暂停
页:
[1]
2