MySQL中出现Waiting for table metadata lock(等待表元数据锁)
简单理解为:临时锁表,无法执行其他语句,需要等当前的任务完成后,自动释放锁;
比如:对一张已有数据的大表添加/更换主键时,会出现临时锁表;
解决方案:
1、等待当前任务完成;
2、通过show processlist 或者performance_schema.events_statements_current查询出对应的id,并kill id(可能需要kill两次);
官方文档解释为:
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early.
Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
如果服务器为语法有效但在执行期间失败的语句获取元数据锁,则不会提前释放锁。
锁释放仍然延迟到事务结束,因为失败的语句被写入二进制日志,锁保护日志一致性。