Waiting for table metadata lock引发java.sql.SQLException: No operations allowed after statement closed

项目场景：

应用报错java.sql.SQLException: No operations allowed after statement closed

问题描述

接口报错后，show processlist 检查数据库，发现等待锁释放 Waiting for table metadata lock

原因分析：

mysql中如果一个表产生了Waiting for table metadata lock，针对表的任何操作都会被锁住，包括select，可以通过配置参数lock_wait_timeout减少等待时长。No operations allowed after statement closed原因是存在未提交事物，阻塞DDL，继而阻塞所有同表的后续操作

问题复现：

未提交事务：

begin;
select * from resource_yarn_queue_metadata limit 1;

DDL操作（表是一个分区表，通过自动化脚本定时新增分区）

ALTER TABLE resource_yarn_queue_metadata ADD PARTITION (
    PARTITION p20240610 VALUES LESS THAN (739413)
);

解决方案：

查找未提交事务id，kill

select t.PROCESSLIST_ID,
       t.PROCESSLIST_USER,
			 t.PROCESSLIST_HOST,
			 t.PROCESSLIST_DB,
			 t.PROCESSLIST_STATE,
			 t.PROCESSLIST_COMMAND,
			 t.PROCESSLIST_TIME,
			 t.PROCESSLIST_INFO,
			 e.CURRENT_SCHEMA,
			 GROUP_CONCAT(e.SQL_TEXT SEPARATOR ' ') as sqL_text
from performance_schema.threads t,
		 information_schema.INNODB_TRX trx,
		 performance_schema.events_statements_history e
where t.THREAD_ID = e.THREAD_ID
      and t.PROCESSLIST_ID = trx.trx_mysql_thread_id
group by t.THREAD_ID DESC;

kill 1125440;