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 小节。
提示
需要设置数据库参数：innodb_status_output_locks = on
才会打印额外的锁信息，仅在测试环境设置此参数
示例
（节选自自官方文档的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 lockmysql> 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 lockmysql> 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 表。
