MySQL 5.7锁的问题一例

今天晚上突然接到开发人员电话，说在查询一个表的时候无法返回结果，也就是hang住了，表名为 oms_bdreturn_ref

我首先登录系统查了下日志，没有报错。

接着查询锁的情况

1. mysql> show OPEN TABLES where In_use > 0;
   
2. +----------+------------------------+--------+-------------+
   
3. | Database | Table | In_use | Name_locked |
   
4. +----------+------------------------+--------+-------------+
   
5. | cus_0042 | stat_staff_follow_week | 1 | 0 |
   
6. | cus_0042 | oms_bdreturn_ref | 1 | 0 |
   
7. +----------+------------------------+--------+-------------+
   
8. 2 rows in set (0.00 sec)

查看进程的情况

1. mysql> show processlist;
   
2. +---------+---------+--------------------+----------+------------------+--------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
   
3. | Id | User | Host | db | Command | Time | State | Info |
   
4. +---------+---------+--------------------+----------+------------------+--------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
   
5. | 21554 | repl | 192.168.2.73:52166 | NULL | Binlog Dump GTID | 271766 | Master has sent all binlog to slave; waiting for more updates | NULL |
   
6. | 27523 | repl | 192.168.2.72:51656 | NULL | Binlog Dump GTID | 266405 | Master has sent all binlog to slave; waiting for more updates | NULL |
   
7. | 2214658 | root | localhost | cus_0042 | Query | 0 | starting | show processlist |
   
8. | 2274002 | xxoms | 192.168.2.44:49516 | cus_0042 | Query | 1276 | Opening tables | SHOW SESSION VARIABLES LIKE 'FOREIGN_KEY_CHECKS' |

有个进程是Opening tables状态，并且持续了1276秒了。

询问开发人员得知， oms_bdreturn_ref表是刚创建的，查了下创建时间

1. mysql> select CREATE_TIME from INFORMATION_SCHEMA.TABLES where TABLE_NAME='oms_bdreturn_ref';
   
2. +---------------------+
   
3. | CREATE_TIME |
   
4. +---------------------+
   
5. | 2017-12-05 18:25:09 |
   
6. +---------------------+
   
7. 1 row in set (0.00 sec)

结合进程的time指标来看，问题是在创建这个表的同时，有人执行了show session语句，导致了这次的opening tables，kill 掉 2274002进程后，恢复正常。

补充两个关于锁的语句：

查看正在锁的事务

1. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

1. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20893244/viewspace-2148373/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20893244/viewspace-2148373/