os:debian 8.2
mysql:5.5.49
nodeb 为 nodea 的一个slave,在nodea上删除一个无效的索引,发现nodeb上出现长时间的 Waiting for table metadata lock。
分析的过程如下:
环境
# lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 8.2 (jessie)
Release: 8.2
Codename: jessie
# mysql
root@localhost> select version();
+---------------------+
| version() |
+---------------------+
| 5.5.49-0+deb8u1-log |
+---------------------+
1 row in set (0.00 sec)
nodea 操作
root@localhost> drop index idx_abc_x1 on abc;
nodeb 操作
root@localhost> show processlist;
+---------+-------------+-------------------+--------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+-------------------+--------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 48 | system user | | NULL | Connect | 10446048 | Waiting for master to send event | NULL |
| 49 | system user | | EMdata | Connect | 1543 | Waiting for table metadata lock | DROP INDEX IDX_ABC_X1 on ABC
~~~
~~~
~~~
| 2427727 | emsel | 10.10.2.51:59835 | EMdata | Query | 134 | Waiting for table metadata lock | select * from abc
查询锁信息
root@localhost> SELECT * FROM information_schema.innodb_lock_waits;
root@localhost> SELECT * FROM information_schema.innodb_locks;
root@localhost> SELECT * FROM information_schema.innodb_trx;
因为slave都是select操作,和开发人员沟通后可以使用 kill trx_mysql_thread_id 杀掉进程。
所以尽量避免在高并发的时候做DDL操作。
lock_wait_timeout
还有一个参数 lock_wait_timeout 用来控制metadata lock的超时(单位为秒),允许的值范围为1到31536000(1年)。 默认值为31536000。
root@localhost> show global variables like 'lock_wait_timeout';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set (0.00 sec)
参考:
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_lock_wait_timeout