记一次修改表产生waiting for table metadata lock锁的问题;
今天为表增加个自增主键过程中被锁住,首先我查看了一下线程
show full processlist;
确实看到状态为waiting for table metadata lock
但是从线程中并没有发现有同一表的修改或者其他操作来锁住表
想着可能有未提交的事务锁住表,如果有将其kill掉即可
但是结果显示为空,百思不得其解,然后查到了大佬的文章,这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效,没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。
进行如下查询
select * from performance_schema.events_statements_current\G
这是因为程序中运行着这个查询,报错后没有释放,关掉连接后恢复正常(顺便吐槽一下这个破编辑器)
处理方法:通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.
也可以做如下查询
select PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_DB,OBJECT_NAME,PROCESSLIST_COMMAND,PROCESSLIST_ID from metadata_locks mdl join threads thr on mdl.OWNER_THREAD_ID= thr.THREAD_ID ;
SELECT
COUNT(1) AS '数量',
p2.`HOST` AS '被阻塞方主机',
p2.`USER` AS '被阻塞方用户',
r.trx_mysql_thread_id AS '被阻塞方线程号',
r.trx_query AS '被阻塞的语句',
TIMESTAMPDIFF(SECOND,r.trx_wait_started,current_timestamp) AS '等待时间',
p.`HOST` AS '阻塞方主机',
p.`USER` AS '阻塞方用户',
b.trx_mysql_thread_id AS '阻塞方线程',
b.trx_query AS '阻塞方语句'
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.lock_trx_id=b.trx_id
INNER JOIN information_schema.INNODB_LOCKS m ON m.lock_id=w.requested_lock_id AND m.lock_trx_id=r.trx_id
INNER JOIN information_schema.`PROCESSLIST` p ON p.ID = b.trx_mysql_thread_id
INNER JOIN information_schema.`PROCESSLIST` p2 ON p2.ID = r.trx_mysql_thread_id
GROUP BY r.trx_query
大佬链接:MySQL出现Waiting for table metadata lock的原因以及解决方法 - digdeep - 博客园