InnoDB 存儲引擎中的鎖等待

一般來說，存儲引擎級別的鎖，比服務(wù)器級別的鎖更難以調(diào)試，而且各種存儲引擎的鎖互不相同，有些存儲引擎甚至都不提供任何方法來查看鎖。本節(jié)重點介紹 InnoDB 存儲引擎的鎖等待。

1. show engine innodb status

show engine innodb status 命令包含了 InnoDB 存儲引擎的部分鎖信息，但很難確定哪個事務(wù)導(dǎo)致這個鎖的問題，因為 show engine innodb status 命令不會告訴你誰擁有鎖。

如果事務(wù)正在等待某個鎖，相關(guān)鎖信息會體現(xiàn)在 show engine innodb status 輸出的 TRANSACTION 部分中。在 MySQL 會話中執(zhí)行如下命令，拿到表 customer 中第一行的寫鎖。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from customer limit 1 for update;
+------+-----------+------------+------------+--------+
| id   | last_name | first_name | birth_date | gender |
+------+-----------+------------+------------+--------+
| NULL | 111       | 222        | NULL       | 1      |
+------+-----------+------------+------------+--------+
1 row in set (0.00 sec)

在 MySQL 另一個會話中，對表 customer 執(zhí)行相同的 select 命令，查詢會被阻塞。

mysql> select * from customer limit 1 for update;

這時執(zhí)行 show engine innodb status 命令能夠看到相關(guān)的鎖信息。

1 ---TRANSACTION 124178, ACTIVE 6 sec starting index read
2 mysql tables in use 1, locked 1
3 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
4 MySQL thread id 12570, OS thread handle 139642200024832, query id 48195 localhost root Sending data
5 select * from customer limit 1 for update
6 ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
7 RECORD LOCKS space id 829 page no 3 n bits 72 index GEN_CLUST_INDEX of table `tempdb`.`customer` trx id 124178 lock_mode X locks rec but not gap waiting

第 7 行表示 thread id 12570 這個查詢，在等待表 customer 中的 GEN_CLUST_INDEX 索引的第 3 頁上有一個排它鎖（lock_mode X）。最后，鎖等待超時，查詢返回錯誤信息。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2. imformation_schema

在 MySQL5.5 開始，一般通過 imformation_schema 的表來查詢相關(guān)的事務(wù)和鎖信息，通過imformation_schema 要比 show engine innodb status 命令要高效和全面。

在 MySQL 會話中執(zhí)行如下命令，能看到誰阻塞和誰在等待，以及等待多久的查詢。

mysql> SELECT
     IFNULL(wt.trx_mysql_thread_id, 1) BLOCKING_THREAD_ID,t.trx_mysql_thread_id WAITING_THREAD_ID, CONCAT(p. USER, '@', p. HOST) USER,
     p.info SQL_TEXT, l.lock_table LOCK_TABLE, l.lock_index LOCKED_INDEX, l.lock_type LOCK_TYPE, l.lock_mode LOCK_MODE,
     CONCAT(FLOOR(HOUR (TIMEDIFF(now(), t.trx_wait_started)) / 24),'day ',MOD (HOUR (TIMEDIFF(now(), t.trx_wait_started)),24),':',
     MINUTE (TIMEDIFF(now(), t.trx_wait_started)),':',SECOND (TIMEDIFF(now(), t.trx_wait_started))) AS WAIT_TIME,
     t.trx_started TRX_STARTED, t.trx_isolation_level TRX_ISOLATION_LEVEL, t.trx_rows_locked TRX_ROWS_LOCKED, t.trx_rows_modified TRX_ROWS_MODIFIED
     FROM INFORMATION_SCHEMA.INNODB_TRX t
     LEFT JOIN information_schema.innodb_lock_waits w ON t.trx_id = w.requesting_trx_id
     LEFT JOIN information_schema.innodb_trx wt ON wt.trx_id = w.blocking_trx_id
     INNER JOIN information_schema.innodb_locks l ON l.lock_trx_id = t.trx_id
     INNER JOIN information_schema. PROCESSLIST p ON t.trx_mysql_thread_id = p.id
     ORDER BY 1\G
*************************** 1. row ***************************
 BLOCKING_THREAD_ID: 1
  WAITING_THREAD_ID: 62751
               USER: root@localhost
           SQL_TEXT: NULL
         LOCK_TABLE: `tempdb`.`customer`
       LOCKED_INDEX: GEN_CLUST_INDEX
          LOCK_TYPE: RECORD
          LOCK_MODE: X
          WAIT_TIME: NULL
        TRX_STARTED: 2020-06-22 06:52:14
TRX_ISOLATION_LEVEL: READ COMMITTED
    TRX_ROWS_LOCKED: 1
  TRX_ROWS_MODIFIED: 0
*************************** 2. row ***************************
 BLOCKING_THREAD_ID: 62751
  WAITING_THREAD_ID: 62483
               USER: root@localhost
           SQL_TEXT: select * from customer limit 1 for update
         LOCK_TABLE: `tempdb`.`customer`
       LOCKED_INDEX: GEN_CLUST_INDEX
          LOCK_TYPE: RECORD
          LOCK_MODE: X
          WAIT_TIME: 0day 0:0:5
        TRX_STARTED: 2020-06-22 07:01:49
TRX_ISOLATION_LEVEL: READ COMMITTED
    TRX_ROWS_LOCKED: 1
  TRX_ROWS_MODIFIED: 0
2 rows in set, 2 warnings (0.00 sec)

從結(jié)果顯示線程 62483 等待表 customer 中的鎖已經(jīng) 5s，它被線程 62751 所阻塞。

下面這個查詢可以告訴你有多少查詢被哪些線程鎖阻塞。

mysql> select concat('thread ', b.trx_mysql_thread_id, ' from ', p.host) as who_blocks,
       if(p.command = "Sleep", p.time, 0) as idle_in_trx, 
       max(timestampdiff(second, r.trx_wait_started, now())) as max_wait_time, 
       count(*) as num_waiters
     from information_schema.innodb_lock_waits as w
     inner join information_schema.innodb_trx as b on b.trx_id = w.blocking_trx_id
     inner join information_schema.innodb_trx as r on r.trx_id = w.requesting_trx_id
     left join information_schema.processlist as p on p.id = b.trx_mysql_thread_id
     group by who_blocks order by num_waiters desc\G
*************************** 1. row ***************************
   who_blocks: thread 62751 from localhost
  idle_in_trx: 1206
max_wait_time: 20
  num_waiters: 5
1 row in set, 1 warning (0.00 sec)

從結(jié)果顯示線程 62751 已經(jīng)空閑了一段時間，有 5 個線程在等待線程 62751 完成提交并釋放鎖，有一個線程已經(jīng)等待線程 62751 釋放鎖長達(dá) 20s。

5. 小結(jié)

本小節(jié)以InnoDB存儲引擎為例，介紹了存儲引擎級別的鎖：show engine innodb status 和 imformation_schema。

show engine innodb status 僅包含了 InnoDB 存儲引擎的部分鎖信息，但不會告訴你誰擁有鎖。通過imformation_schema 可以高效和全面定位到誰阻塞和誰在等待，以及等待多久的查詢。