β

Mysql的metadata lock处理

kenyon的个人页面 374 阅读
同事说开发机更改一个表结构,加字段,但是一直挂在那里,没反应。一开始以为表测试数据量很大,因为mysql增加表字段会重写表,后来看了下数据量很小,就另外查看过程、原因分析和处理如下:

一、环境
mysql 5.6.21
Red Hat 6.5

二、问题还原处理
--做了一个更改表结构加字段的操作,但是一直挂着不返回:
mysql> alter table tbl_test  add remark varchar(8)  after col_url; 

--开启另一个窗口看进程
mysql> show processlist;
+------+--------+------------------------+-------------+---------+-------+---------------
| Id   | User   | Host      | db    | Command | Time  | State       Info   
| 7113 | root   | localhost | testdb| Sleep   |  5512 |           | NULL                                                                                                 |
| 7134 | root   | localhost | testdb| Sleep   |  5513 |           | NULL                                                                                                 |
| 7141 | root   | localhost | NULL   | Sleep   |  7942 |          | NULL                                                                                                 |
| 7142 | root   | localhost | testdb| Sleep   |  1755 |           | NULL                                                                                                 |
| 7153 | root   | localhost | testdb| Sleep   |  6950 |            | NULL                                                                                                                    
| 7214 | root   | localhost | testdb| Sleep   |  4614 |            | NULL                                                                                                 |
| 7217 | root   | localhost | testdb| Sleep   |  4232 |            | NULL                                                                                                 |
| 7219 | root   | localhost | testdb| Sleep   |  4285 |             | NULL                                                                                                 |
| 7221 | root   | localhost | testdb| Sleep   |  4229 |             | NULL                                                                                                 |
| 7226 | root   | localhost | testdb| Sleep   |   878 |             | NULL                                                                                                 |
| 7254 | root   | localhost  | NULL        | Sleep   |  2137 |       | NULL                                                                                                 |
| 7256 | root   | localhost  | testdb| Sleep   |  1511 |             | NULL                                                                                                 |
| 7269 | root   | localhost | testdb| Query   |   129 | Waiting for table metadata lock | alter table tbl_test add  remark varchar(8)   comment |
| 7271 | root   | localhost  | testdb| Query   |     0 | init        | show processlist                                                                                     |
| 7276 | root   | localhost  | NULL        | Sleep   |  1000 |       | NULL                                                                                                 
--有收获,显示是Waiting for table metadata lock,元数据上锁了,但是没有更详细的信息,只是提示表被锁了。既然提示锁,那我们再深入看一下是哪把锁锁住了。mysql的可以通过information_schema.locks、information_schema.innodb_trx和information_schema.innodb_lock_waits来查询。

mysql>SELECT trx_id,trx_state,trx_mysql_thread_id,trx_isolation_level,FROM information_schema.innodb_trx;
trx_id     trx_state    trx_mysql_thread_id       trx_isolation_level 
971     RUNNING         7134        REPEATABLE READ

--7134在processlist里面,目前只发现了这个事务,innodb_trx是innodb引擎包含了各种内部事务的系统表,目前在list里面是sleep状态的

--手工kill掉进程,生产上kill进程需要和研发的同学确认
mysql> kill 7134;
Query OK, 0 rows affected (0.00 sec)

--然后重新执行
mysql>alter table tbl_test add  remark varchar(8)  after  col_url;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
三、分析
引起这个锁的原因很简单,就是有人开启了一个事务,然后对数据库对象做了操作,哪怕只是一个查询,但是事务一直挂在那里,不提交也不回滚,这样对其他Session的DDL事务就会造成排它锁。这有可能是开发人员的程序疏忽,也可能是某些工具使用不到位或者存在BUG,需要研发人员继续定位,此版本还可以开启profile来trace。

四、参考:
官网上的metadata lock说明,以及如何重现
http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html
以及系统表的说明:
http://dev.mysql.com/doc/refman/5.5/en/innodb-trx-table.html
作者:kenyon的个人页面
kenyon_君羊的博客
原文地址:Mysql的metadata lock处理, 感谢原作者分享。