记一次MySQL中Waiting for table metadata lock的解决方法

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u013235478/article/details/68062939

最近项目中的数据库查询经常挂起,应用程序启动后也报操作超时。测试人员就说数据库又挂了(貌似他们眼中的连接失败,查询无果都是挂了),通过 show processlist 一看,满屏都是 Waiting for table metadata lock 状态的连接。第一反应就是kill掉这些连接,奈何连接实在太多,实在kill不过来,于是重启服务,貌似重启果真能解决90%的问题,但如果不找到问题原因,问题也肯定会再次出现。

在网上查询得知MySQL在进行一些alter table等DDL操作时,如果该表上有未提交的事务则会出现 Waiting for table metadata lock ,而一旦出现metadata lock,该表上的后续操作都会被阻塞(详见 http://www.bubuko.com/infodetail-1151112.html)。所以这个问题需从两方面解决:

1. 查看未提交事务

从 information_schema.innodb_trx 表中查看当前未提交的事务

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。

2. 调整锁超时阈值

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 。默认值为一年!!!已哭瞎!将其调整为30分钟

set session lock_wait_timeout = 1800;
set global lock_wait_timeout = 1800;

好让出现该问题时快速故障(failfast)

主从复制出现metadata lock

05-05

现象:主从复制的时候,如果在Master顺序执行建表(test),insert数据,commit。然后在Slave查询test,再返回Master进行drop test操作,这个时候发现Slave中出现Waiting for table metadata lock 的情况,但是如果不进行Slave的查询操作的话,Slave上能正常安装Master的操作复制。rnrn我是关闭的自动提交的,难道是因为查询也会默认开始一个事务?rnrnMaster操作rn[code=java]rnMariaDB [rmsdb]> create table test(a int);rnQuery OK, 0 rows affected (0.62 sec)rnrnMariaDB [rmsdb]> insert into test values(1);rnQuery OK, 1 row affected (0.01 sec)rnrnMariaDB [rmsdb]> commit;rnQuery OK, 0 rows affected (0.17 sec)rn[/code]rnrn然后Slave操作rn[code=java]rnMariaDB [rmsdb]> select * from test;rn+------+rn| a |rn+------+rn| 1 |rn+------+rn1 row in set (0.00 sec)rn[/code]rnrn接着Master操作rn[code=java]rnMariaDB [rmsdb]> drop table test;rnQuery OK, 0 rows affected (0.02 sec)rn[/code]rnrn然后Slave查看rn[code=java]rnMariaDB [rmsdb]> show processlist;rn+----+-------------+-----------+-------+---------+-------+----------------------------------+---------------------------------------------+----------+rn| Id | User | Host | db | Command | Time | State | Info | Progress |rn+----+-------------+-----------+-------+---------+-------+----------------------------------+---------------------------------------------+----------+rn| 4 | system user | | rmsdb | Connect | -511 | Waiting for table metadata lock | DROP TABLE `test` /* generated by server */ | 0.000 |rn| 14 | system user | | NULL | Connect | 73740 | Waiting for master to send event | NULL | 0.000 |rn| 15 | root | localhost | rmsdb | Query | 0 | init | show processlist | 0.000 |rn+----+-------------+-----------+-------+---------+-------+----------------------------------+-----------------------------------------rn[/code]rnrnrnPS:楼主用的mariadb,这个应该不影响吧?

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试