mysql 出现了 Waiting for table metadata lock的解决方案

妖狐艹你老母 2023-01-03 11:26 134阅读 0赞

在使用pt进行ddl 的时候需要创建触发器，然后在创建的过程中，出现了`Waiting for table metadata lock`, 下面是一些解决办法，本质是一样的，这里总结来说就是：

1.  **kill 掉某些事物占用的锁，使DDL成功，然后进而不阻塞其他DML操作。**
2.  **设置锁超时短些** `lock_wait_timeout`

另外可以参考以下链接：

*  [https://www.cnblogs.com/digdeep/p/4892953.html][https_www.cnblogs.com_digdeep_p_4892953.html]
 *  [https://www.jianshu.com/p/e14c39e430bf][https_www.jianshu.com_p_e14c39e430bf]

文章一：

> 在网上查询得知MySQL在进行一些alter table等DDL操作时，如果该表上有未提交的事务则会出现 Waiting for table  
> metadata lock ，而一旦出现metadata lock，该表上的后续操作都会被阻塞（详见  
> [http://www.bubuko.com/infodetail-1151112.html][http_www.bubuko.com_infodetail-1151112.html]  
> 。所以这个问题需从两方面解决：
> 
> 1.  查看未提交事务 从 information\_schema.innodb\_trx 表中查看当前未提交的事务
> 
> `sql select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx\G`  
> （\\G作为结束符时，MySQL Client会把结果以列模式展示，对于列比较长的表，展示更直观）
> 
> 字段意义：
> 
>  *  trx\_state: 事务状态，一般为RUNNING
>  *  trx\_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理
>  *  trx\_mysql\_thread\_id: MySQL的线程ID，用于kill
>  *  trx\_query: 事务中的sql 一般只要kill掉这些线程，DDL操作就不会Waiting for table metadata lock。
> 
> 1.  调整锁超时阈值 lock\_wait\_timeout 表示获取metadata lock的超时（单位为秒），允许的值范围为1到31536000（1年）。 默认值为31536000。详见  
>     [https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html\#sysvar\_lock\_wait\_timeout][https_dev.mysql.com_doc_refman_5.6_en_server-system-variables.html_sysvar_lock_wait_timeout]  
>     。默认值为一年！！！已哭瞎！将其调整为30分钟
> 
> `sql set session lock_wait_timeout = 1800; set global lock_wait_timeout = 1800;`  
> 好让出现该问题时快速故障（failfast）

另外：一些扩展内容

*  DML（data manipulation language）数据操纵语言：就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作
 *  DDL（data definition language）数据库定义语言：其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上
 *  DCL（Data Control Language）数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。这个比较少用到

# `执行SQL“select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx\G”后，kill掉事务id，即可` #

[https_www.cnblogs.com_digdeep_p_4892953.html]: https://www.cnblogs.com/digdeep/p/4892953.html
[https_www.jianshu.com_p_e14c39e430bf]: https://www.jianshu.com/p/e14c39e430bf
[http_www.bubuko.com_infodetail-1151112.html]: http://www.bubuko.com/infodetail-1151112.html
[https_dev.mysql.com_doc_refman_5.6_en_server-system-variables.html_sysvar_lock_wait_timeout]: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lock_wait_timeout