InnoDB 存储引擎中的锁等待

朱雀 2024-03-29 16:09 146阅读 0赞

一般来说，存储引擎级别的锁，比服务器级别的锁更难以调试，而且各种存储引擎的锁互不相同，有些存储引擎甚至都不提供任何方法来查看锁。本节重点介绍 InnoDB 存储引擎的锁等待。

### 1. show engine innodb status ###

show engine innodb status 命令包含了 InnoDB 存储引擎的部分锁信息，但很难确定哪个事务导致这个锁的问题，因为 show engine innodb status 命令不会告诉你谁拥有锁。

如果事务正在等待某个锁，相关锁信息会体现在 show engine innodb status 输出的 TRANSACTION 部分中。在 MySQL 会话中执行如下命令，拿到表 customer 中第一行的写锁。

在 MySQL 另一个会话中，对表 customer 执行相同的 select 命令，查询会被阻塞。

mysql> select * from customer limit 1 for update;

这时执行 show engine innodb status 命令能够看到相关的锁信息。

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 的表来查询相关的事务和锁信息，通过imformation\_schema 要比 show engine innodb status 命令要高效和全面。

在 MySQL 会话中执行如下命令，能看到谁阻塞和谁在等待，以及等待多久的查询。

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)

从结果显示线程 62483 等待表 customer 中的锁已经 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)
    本文来自：https://www.10zhan.com

从结果显示线程 62751 已经空闲了一段时间，有 5 个线程在等待线程 62751 完成提交并释放锁，有一个线程已经等待线程 62751 释放锁长达 20s。

### 5. 小结 ###

本小节以InnoDB存储引擎为例，介绍了存储引擎级别的锁：show engine innodb status 和 imformation\_schema。

show engine innodb status 仅包含了 InnoDB 存储引擎的部分锁信息，但不会告诉你谁拥有锁。通过imformation\_schema 可以高效和全面定位到谁阻塞和谁在等待，以及等待多久的查询。