InnoDB引擎中查询锁的信息

2020-04-16 本文已影响0人 ntjsz

InnoDB中查询锁的信息主要基于三个查询语句

MySQL 5.7

select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_locks;
show engine innodb status\G

MySQL 8.0

select * from performance_schema.data_lock_waits;
select * from performance_schema.data_locks;
show engine innodb status\G

本文将分别基于 MySQL 5.7 和 MySQL 8.0 两个版本进行叙述。两个版本间的差异并不太大，但 MySQL 8.0 中的锁信息更为详尽。

MySQL 5.7

innodb_lock_waits 表

This table is deprecated as of MySQL 5.7.14 and is removed in MySQL 8.0.
此表在MySQL 5.7.14中被标为已废弃，MySQL 8.0中将会移除此表。
transaction A持有某锁，并且transaction B在请求锁时阻塞(block)，表内才有数据。
如果transaction A持有锁，但没有其它transaction因此而阻塞(block)等待，select的结果将会是empty set。
也就是说，有block发生，此表才有数据。
此表没有数据并不意味着没有transaction持有锁，只表示没有阻塞(block)的transaction。MySQL 8.0会有更完整的信息展示。

示例

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 4189              | 4189:84:4:4       | 4187            | 4187:84:4:4      |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

此表中的每一行数据都代表一对阻塞等待关系。
id为4187的transaction 持有 id为4187:84:4:4的锁。
id为4189的transaction 请求 id为4189:84:4:4的锁。
由于4187持有着锁4187:84:4:4，4189无法获取锁4189:84:4:4，4189只能阻塞并等待4187释放锁。

关于此表的更多详细信息，请参考官方文档 24.32.14 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table

innodb_locks 表

同上表一样，此表在MySQL 5.7.14中被标为已废弃，MySQL 8.0中将会移除此表。
同上表一样，，有block发生，此表才有数据。

示例

mysql> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 4189:84:4:4
lock_trx_id: 4189
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test`.`test`
 lock_index: k
 lock_space: 84
  lock_page: 4
   lock_rec: 4
  lock_data: 10, 9
*************************** 2. row ***************************
    lock_id: 4187:84:4:4
lock_trx_id: 4187
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test`
 lock_index: k
 lock_space: 84
  lock_page: 4
   lock_rec: 4
  lock_data: 10, 9
2 rows in set, 1 warning (0.00 sec)

lock_id 和 lock_trx_id 分别对应上表的锁id和transaction id
lock_mode
lock_mode的描述符包括S, X, IS, IX, GAP, AUTO_INC, UNKNOWN，描述符可以单独或组合地表示锁的具体类别。锁的具体类别请参考官方文档14.7.1 InnoDB Locking，本文不详细展开。
结合本节示例和上一节的innodb_lock_waits表示例，可以了解到：
4187持有exclusive (X) lock，4189请求exclusive (X) gap (GAP) lock。
明确标识了GAP，才是 gap lock ，如4189的X,GAP；没有明确标识GAP，则有可能是 record lock 或是 next-key lock，如4187的X。具体是 record lock 还是 next-key lock，需要通过下一节的show engine innodb status\G信息来确定。
lock_type
RECORD - 行级别的锁，TABLE - 表级别的锁。
lock_index
如果有索引，则此列为PRIMARY或 secondary index 的名称；否则为 NULL
示例中是 secondary index ，索引名称为 k
lock_data
如果 lock_type: TABLE，此列为 NULL
如果 lock_type: RECORD，显示主键的数据，或是 secondary index 的数据，或是引擎内部分配的 row ID
示例中是名称为 k 的 secondary index，本列的值为10, 9，其中10是secondary index的值，9是主键。（InnoDB中，secondary index 中也会记录主键信息，详见官方文档）

注意本表中仅展示 innodb_lock_waits 表中发生阻塞关系的锁，其它被持有却未产生阻塞的锁无法从表中查出。MySQL 8.0将会完善锁数据的展示。

关于此表的更多详细信息，请参考官方文档 24.32.13 The INFORMATION_SCHEMA INNODB_LOCKS Table

SHOW ENGINE INNODB STATUS

详细信息请参考官方文档 14.18.3 InnoDB Standard Monitor and Lock Monitor Output
此语句打印出的信息很多，详情参考上述官方文档内的Example。本文不再展示全部打印信息，仅摘要其中的 TRANSACTIONS 小节。

示例

（节选自自官方文档的Example）

mysql> SHOW ENGINE INNODB STATUS\G
...
...
------------
TRANSACTIONS
------------
Trx id counter 1950
Purge done for trx's n:o < 1933 undo n:o < 0 state: running but idle
History list length 23
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421949033065200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421949033064280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1949, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 1850 row lock(s), undo log entries 17415
MySQL thread id 4, OS thread handle 140474041235200, query id 176 localhost
root update
INSERT INTO `salaries` VALUES (55723,39746,'1997-02-25','1998-02-25'),
(55723,40758,'1998-02-25','1999-02-25'),(55723,44559,'1999-02-25','2000-02-25'),
(55723,44081,'2000-02-25','2001-02-24'),(55723,44112,'2001-02-24','2001-08-16'),
(55724,46461,'1996-12-06','1997-12-06'),(55724,48916,'1997-12-06','1998-12-06'),
(55724,51269,'1998-12-06','1999-12-06'),(55724,51932,'1999-12-06','2000-12-05'),
(55724,52617,'2000-12-05','2001-12-05'),(55724,56658,'2001-12-05','9999-01-01'),
(55725,40000,'1993-01-30','1994-01-30'),(55725,41472,'1994-01-30','1995-01-30'),
(55725,45293,'1995-01-30','1996-01-30'),(55725,473
...
...

上一节中我们提到，从innodb_locks表中我们可以知晓锁的类型信息。唯一无法区分的类型是 record lock 还是 next-key lock。本节的show engine innodb status将会协助我们区分。

从官方文档的示例中我们可以看到 transaction 1949 持有了一些锁，如下的1850 row lock(s)

mysql> SHOW ENGINE INNODB STATUS\G
...
...
---TRANSACTION 1949, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 1850 row lock(s), undo log entries 17415
...
...

很遗憾，这个示例中没有更多的锁信息。因为我们无法从show engine innodb status中获得持有方的锁的类型线索，我们只能获得阻塞方的锁的类型线索。见如下示例

mysql> show engine innodb status\G
...
...
------------
TRANSACTIONS
------------
Trx id counter 4190
Purge done for trx's n:o < 4187 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284063839988640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284063839983408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284063839986896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 284063839986024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 4189, ACTIVE 23 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 128, OS thread handle 16920, query id 10195 localhost ::1 root executing
insert into test select 6, 6, 6
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 4 n bits 72 index k of table `test`.`test` trx id 4189 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 80000009; asc     ;;

------------------
---TRANSACTION 4187, ACTIVE 166 sec
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 104, OS thread handle 16096, query id 10190 localhost ::1 root
...
...

这个示例中我们可以看到transaction 4187持有一些锁，而transaction 4189正在阻塞并等待某锁的释放。
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:展示该transaction已经等待了23秒。
紧接其后的RECORD LOCKS space id 84 page no 4 n bits 72 index k of table `test`.`test` trx id 4189 lock_mode X locks gap before rec insert intention waiting中末尾lock_mode X后的locks gap before rec insert intention waiting为我们提供了线索。我们借此可以推断出transaction想要获取的是 insert intention lock。INSERT操作会在执行插入前请求这种gap lock，来表明自己的 insert intention。
insert intention lock 的线索以及record lock 和 next-key lock 的线索具体可以阅读官方文档14.7.1 InnoDB Locking

record lock 和 next-key lock 的线索现节选如下

record lock
如下信息中lock_mode X后的locks rec but not gap表示 record lock

mysql> show engine innodb status\G
...
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table 
`test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info 
bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;
...

next-key lock
如下信息中的lock_mode X后没有接任何额外信息，表示 next-key lock

mysql> show engine innodb status\G
...
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table 
`test`.`t` 
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info 
bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info 
bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;
...

至此，结合前文所有的信息，我们可以明确区分 shared lock, exclusive lock, record lock, gap lock, next-key lock, insert intention lock 等锁的类型。
不过MySQL 5.7中只能查询部分的锁信息，MySQL 8.0将展示更完整的锁信息。

MySQL 8.0

data_lock_waits 表

此表用于替换 MySQL 5.7 中已废弃的 innodb_lock_waits 表。
同 MySQL 5.7 中的 innodb_lock_waits 表一样，有block发生，此表才有数据。

示例

mysql> select * from performance_schema.data_lock_waits\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 2592700024720:2:5:4:2592665898440
REQUESTING_ENGINE_TRANSACTION_ID: 1571
            REQUESTING_THREAD_ID: 59
             REQUESTING_EVENT_ID: 8
REQUESTING_OBJECT_INSTANCE_BEGIN: 2592665898440
         BLOCKING_ENGINE_LOCK_ID: 2592700023024:2:5:4:2592665888488
  BLOCKING_ENGINE_TRANSACTION_ID: 1569
              BLOCKING_THREAD_ID: 58
               BLOCKING_EVENT_ID: 7
  BLOCKING_OBJECT_INSTANCE_BEGIN: 2592665888488
1 row in set (0.00 sec)

相比于 innodb_lock_waits 表，此表展示了更多的信息。
更多详细信息请参考官方文档 25.46.21 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table 和 26.12.13.2 The data_lock_waits Table

data_locks 表

此表用于替换 MySQL 5.7 中已废弃的 innodb_locks 表。
与 MySQL 5.7 中的 innodb_locks 表不同，此表展示了当前所有的锁信息。

示例

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2592700024720:1059:2592665901224
ENGINE_TRANSACTION_ID: 1571
            THREAD_ID: 59
             EVENT_ID: 8
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2592665901224
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2592700024720:2:5:4:2592665898440
ENGINE_TRANSACTION_ID: 1571
            THREAD_ID: 59
             EVENT_ID: 8
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: k
OBJECT_INSTANCE_BEGIN: 2592665898440
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: 16, 15
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2592700023024:1059:2592665891272
ENGINE_TRANSACTION_ID: 1569
            THREAD_ID: 58
             EVENT_ID: 7
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2592665891272
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2592700023024:2:5:4:2592665888488
ENGINE_TRANSACTION_ID: 1569
            THREAD_ID: 58
             EVENT_ID: 7
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: k
OBJECT_INSTANCE_BEGIN: 2592665888488
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 16, 15
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2592700023024:2:4:4:2592665888832
ENGINE_TRANSACTION_ID: 1569
            THREAD_ID: 58
             EVENT_ID: 7
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2592665888832
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2592700023024:2:5:5:2592665889176
ENGINE_TRANSACTION_ID: 1569
            THREAD_ID: 58
             EVENT_ID: 7
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: k
OBJECT_INSTANCE_BEGIN: 2592665889176
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 21, 20
6 rows in set (0.00 sec)

相比于 innodb_locks 表，此表同样展示了更多的信息。
并且值得注意的是 LOCK_MODE 列。相比于 innodb_locks 表，示例中可见data_locks新增了REC_NOT_GAP和INSERT_INTENTION的描述符。（新增描述符并未在官方文档中作出明确声明）
凭借新的描述符，可以明确区分出锁的分类是 record lock, gap lock, next-key lock, insert intention lock 中的哪一个。无需再像 MySQL 5.7 中那样，去show engine innodb status;中寻找锁类型的线索。

LOCK_MODE 列的描述符	锁类型
REC_NOT_GAP	record lock
GAP	gap lock
INSERT_INTENTION	insert intention lock
未声明上述任一描述符	next-key lock

更多详细信息请参考官方文档 25.46.20 The INFORMATION_SCHEMA INNODB_LOCKS Table 和 26.12.13.1 The data_locks Table

SHOW ENGINE INNODB STATUS

同 MySQL 5.7 中的信息相同，不再赘述。

至此我们发现，MySQL 8.0 中的 data_locks 表展示了详尽的锁信息，我们可以从此获得绝大部分信息。在学习 InnoDB 引擎中的语句如何加锁时，极力推荐使用 MySQL 8.0 中的 data_locks 表。

InnoDB引擎中查询锁的信息

MySQL 5.7

innodb_lock_waits 表

示例

innodb_locks 表

示例

SHOW ENGINE INNODB STATUS

示例

MySQL 8.0

data_lock_waits 表

示例

data_locks 表

示例

SHOW ENGINE INNODB STATUS

猜你喜欢

热点阅读