InnoDB 存儲(chǔ)引擎中的鎖等待

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

1. show engine innodb status

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

如果事務(wù)正在等待某個(gè)鎖，相關(guān)鎖信息會(huì)體現(xiàn)在 show engine innodb status 輸出的 TRANSACTION 部分中。在 MySQL 會(huì)話中執(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 另一個(gè)會(huì)話中，對表 customer 執(zhí)行相同的 select 命令，查詢會(huì)被阻塞。

mysql> select * from customer limit 1 for update;

這時(shí)執(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 這個(gè)查詢，在等待表 customer 中的 GEN_CLUST_INDEX 索引的第 3 頁上有一個(gè)排它鎖（lock_mode X）。最后，鎖等待超時(shí)，查詢返回錯(cuò)誤信息。

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 會(huì)話中執(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 所阻塞。

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

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)空閑了一段時(shí)間，有 5 個(gè)線程在等待線程 62751 完成提交并釋放鎖，有一個(gè)線程已經(jīng)等待線程 62751 釋放鎖長達(dá) 20s。

5. 小結(jié)

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

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