MySQL分区表重新分区,导致lost connection

use tick_dev;
-- show status
show engine innodb status ;
-- show lock tables
show processlist;
show open tables where In_use>0;
show create table execution_detail;
begin;
update execution_detail set schedule_time='2022-08-01 09:00:00' where schedule_time='2022-08-01 09:00:00';

select * from execution_detail limit 1;

commit;


show create table execution_detail;
alter table execution_detail drop partition p20220810;
ALTER TABLE `execution_detail`
    REORGANIZE PARTITION pmax INTO (
        PARTITION p20220814 VALUES LESS THAN  ('20220815'),
        PARTITION pmax VALUES LESS THAN MAXVALUE
        );


show open tables where In_use>0;

当使用SQL查询时,开启事务会进行表的metadata_lock

此时重新分区,分区表会 wait for metadata lock

若事务一直不退出,会导致长时间等待,连接长期占用,表无法使用