MySQL 5.7 查詢InnoDB鎖表

InnoDB INFORMATION_SCHEMA 裡有三張表可以用來監控和診斷鎖的問題。

INNODB_TRX
包含正在InnoDB裡執行的每個事務的相關資訊，包括事務是否在等待鎖，事務的開始時間和事務正在執行的SQL語句。

INNODB_LOCKS
記錄InnoDB裡每個正在等待另一個事務釋放鎖(INNODB_TRX.TRX_STATE='LOCK WAIT')的事務的相關資訊，這些事務被“blocking lock request”事件阻塞，這些鎖的請求為被另一個事務佔用的行鎖或表鎖。
等待或阻塞的事務不能進行，直到佔有鎖的事務提交或回滾。這張表記錄事務請求的鎖，佔有鎖的事務資訊，佔有鎖的事務的狀態('RUNNING', 'LOCK WAIT', 'ROLLING BACK' or 'COMMITTING')，佔有鎖的模式(read vs. write, shared vs. exclusive)。

INNODB_LOCK_WAITS
記錄哪些事務在等待鎖以及等待的鎖的型別，REQUESTED_LOCK_ID代表事務請求的鎖的ID，BLOCKING_LOCK_ID代表佔有鎖的ID。

事務1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MSISDN FROM t50 FOR UPDATE;
+----------------+
| MSISDN         |
+----------------+
| +3301000000011 |
| +3301000000013 |
| +3301000000015 |
| +3301000000015 |
| +3301000000017 |
| +3301000000019 |
+----------------+
6 rows in set (0.00 sec)

mysql> SELECT SLEEP(1000);

事務2
mysql> SELECT IMEI FROM t50 FOR UPDATE;

事務3
mysql> SELECT IMSI FROM t50 FOR UPDATE;

再開一個會話，檢視執行緒資訊
mysql> show processlist;
+----+------+-----------+------+---------+------+--------------+---------------------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time | State        | Info                            | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+--------------+---------------------------------+-----------+---------------+
| 70 | root | localhost | test | Query   |    8 | Sending data | SELECT IMEI FROM t50 FOR UPDATE |         0 |             0 |
| 71 | root | localhost | test | Query   |  310 | User sleep   | SELECT SLEEP(1000)              |         0 |             0 |
| 72 | root | localhost | test | Query   |    6 | Sending data | SELECT IMSI FROM t50 FOR UPDATE |         0 |             0 |
| 73 | root | localhost | test | Query   |    0 | init         | show processlist                |         0 |             0 |
+----+------+-----------+------+---------+------+--------------+---------------------------------+-----------+---------------+
4 rows in set (0.03 sec)

檢視鎖的資訊
mysql> SELECT r.trx_id waiting_trx_id,
    ->        r.trx_mysql_thread_id waiting_thread,
    ->        r.trx_query waiting_query,
    ->        b.trx_id blocking_trx_id,
    ->        b.trx_mysql_thread_id blocking_thread,
    ->        b.trx_query blocking_query
    ->    FROM       information_schema.innodb_lock_waits w
    ->    INNER JOIN information_schema.innodb_trx b  ON
    ->     b.trx_id = w.blocking_trx_id
    ->   INNER JOIN information_schema.innodb_trx r  ON
    ->     r.trx_id = w.requesting_trx_id;
+----------------+----------------+---------------------------------+-----------------+-----------------+---------------------------------+
| waiting_trx_id | waiting_thread | waiting_query                   | blocking_trx_id | blocking_thread | blocking_query                  |
+----------------+----------------+---------------------------------+-----------------+-----------------+---------------------------------+
| 6288648        |             72 | SELECT IMSI FROM t50 FOR UPDATE | 6288647         |              70 | SELECT IMEI FROM t50 FOR UPDATE |
| 6288648        |             72 | SELECT IMSI FROM t50 FOR UPDATE | 6288638         |              71 | SELECT SLEEP(1000)              |
| 6288647        |             70 | SELECT IMEI FROM t50 FOR UPDATE | 6288638         |              71 | SELECT SLEEP(1000)              |
+----------------+----------------+---------------------------------+-----------------+-----------------+---------------------------------+
3 rows in set (0.00 sec)

可以看到，最初執行SQL的執行緒是 71，執行緒 70 等待執行緒 71 ，執行緒 72 在等待執行緒 70、71

mysql> select * from information_schema.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+--------------+-----------------+------------+-----------+----------+----------------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table   | lock_index      | lock_space | lock_page | lock_rec | lock_data      |
+----------------+-------------+-----------+-----------+--------------+-----------------+------------+-----------+----------+----------------+
| 6288651:78:3:2 | 6288651     | X         | RECORD    | `test`.`t50` | GEN_CLUST_INDEX |         78 |         3 |        2 | 0x000000000607 |
| 6288650:78:3:2 | 6288650     | X         | RECORD    | `test`.`t50` | GEN_CLUST_INDEX |         78 |         3 |        2 | 0x000000000607 |
| 6288638:78:3:2 | 6288638     | X         | RECORD    | `test`.`t50` | GEN_CLUST_INDEX |         78 |         3 |        2 | 0x000000000607 |
+----------------+-------------+-----------+-----------+--------------+-----------------+------------+-----------+----------+----------------+
3 rows in set (0.00 sec)

mysql> select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_mysql_thread_id,trx_query from information_schema.INNODB_TRX;
+---------+-----------+---------------------+-----------------------+---------------------+---------------------+---------------------------------+
| trx_id  | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_mysql_thread_id | trx_query                       |
+---------+-----------+---------------------+-----------------------+---------------------+---------------------+---------------------------------+
| 6288669 | LOCK WAIT | 2016-09-05 14:14:28 | 6288669:78:3:2        | 2016-09-05 14:14:28 |                  72 | SELECT IMSI FROM t50 FOR UPDATE |
| 6288668 | LOCK WAIT | 2016-09-05 14:14:26 | 6288668:78:3:2        | 2016-09-05 14:14:26 |                  70 | SELECT IMEI FROM t50 FOR UPDATE |
| 6288638 | RUNNING   | 2016-09-05 11:41:59 | NULL                  | NULL                |                  71 | SELECT SLEEP(1000)              |
+---------+-----------+---------------------+-----------------------+---------------------+---------------------+---------------------------------+
3 rows in set (0.00 sec)

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 6288671           | 6288671:78:3:2    | 6288670         | 6288670:78:3:2   |
| 6288671           | 6288671:78:3:2    | 6288638         | 6288638:78:3:2   |
| 6288670           | 6288670:78:3:2    | 6288638         | 6288638:78:3:2   |
+-------------------+-------------------+-----------------+------------------+
3 rows in set (0.00 sec)

檢查Innodb_row_lock狀態變數來分析系統上的行鎖的爭奪情況
mysql> show global status like '%innodb%row%lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_current_row_locks      | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
6 rows in set (0.00 sec)