MySQL 5.7鎖的問題一例

甲骨文技術支援發表於2017-12-06
MySql
今天晚上突然接到開發人員電話，說在查詢一個表的時候無法返回結果，也就是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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章