InnoDB中查詢鎖的信息主要基于三個(gè)查詢語(yǔ)句
- 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 兩個(gè)版本進(jìn)行敘述策菜。兩個(gè)版本間的差異并不太大，但 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中被標(biāo)為已廢棄晨仑，MySQL 8.0中將會(huì)移除此表嘉赎。 - transaction A持有某鎖膝晾，并且transaction B在請(qǐng)求鎖時(shí)阻塞(block)，表內(nèi)才有數(shù)據(jù)胸完。
如果transaction A持有鎖书释，但沒(méi)有其它transaction因此而阻塞(block)等待，select的結(jié)果將會(huì)是empty set赊窥。
也就是說(shuō)爆惧，有block發(fā)生，此表才有數(shù)據(jù)锨能。
此表沒(méi)有數(shù)據(jù)并不意味著沒(méi)有transaction持有鎖扯再，只表示沒(méi)有阻塞(block)的transaction芍耘。MySQL 8.0會(huì)有更完整的信息展示。
示例
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)
此表中的每一行數(shù)據(jù)都代表一對(duì)阻塞等待關(guān)系熄阻。
id為4187的transaction 持有 id為4187:84:4:4的鎖斋竞。
id為4189的transaction 請(qǐng)求 id為4189:84:4:4的鎖。
由于4187持有著鎖4187:84:4:4秃殉，4189無(wú)法獲取鎖4189:84:4:4坝初，4189只能阻塞并等待4187釋放鎖。
關(guān)于此表的更多詳細(xì)信息钾军，請(qǐng)參考官方文檔 24.32.14 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
innodb_locks 表
- 同上表一樣鳄袍，此表在MySQL 5.7.14中被標(biāo)為已廢棄，MySQL 8.0中將會(huì)移除此表吏恭。
- 同上表一樣拗小，，有block發(fā)生砸泛，此表才有數(shù)據(jù)十籍。
示例
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 分別對(duì)應(yīng)上表的鎖id和transaction id
- lock_mode
lock_mode的描述符包括S,X,IS,IX,GAP,AUTO_INC,UNKNOWN，描述符可以單獨(dú)或組合地表示鎖的具體類別唇礁。鎖的具體類別請(qǐng)參考官方文檔14.7.1 InnoDB Locking，本文不詳細(xì)展開(kāi)惨篱。
結(jié)合本節(jié)示例和上一節(jié)的innodb_lock_waits表示例盏筐，可以了解到：
4187持有exclusive (X) lock，4189請(qǐng)求exclusive (X) gap (GAP) lock砸讳。
明確標(biāo)識(shí)了GAP琢融，才是 gap lock ，如4189的X,GAP簿寂；沒(méi)有明確標(biāo)識(shí)GAP漾抬，則有可能是 record lock 或是 next-key lock，如4187的X常遂。具體是 record lock 還是 next-key lock纳令，需要通過(guò)下一節(jié)的show engine innodb status\G信息來(lái)確定。 - lock_type
RECORD- 行級(jí)別的鎖克胳，TABLE- 表級(jí)別的鎖平绩。 - lock_index
如果有索引，則此列為PRIMARY或 secondary index 的名稱漠另；否則為NULL
示例中是 secondary index 捏雌，索引名稱為 k - lock_data
如果lock_type: TABLE，此列為NULL
如果lock_type: RECORD笆搓，顯示主鍵的數(shù)據(jù)性湿，或是 secondary index 的數(shù)據(jù)纬傲，或是引擎內(nèi)部分配的 row ID
示例中是名稱為 k 的 secondary index，本列的值為10, 9肤频，其中10是secondary index的值嘹锁，9是主鍵。（InnoDB中着裹，secondary index 中也會(huì)記錄主鍵信息领猾，詳見(jiàn)官方文檔）
注意 本表中僅展示 innodb_lock_waits 表中發(fā)生阻塞關(guān)系的鎖，其它被持有卻未產(chǎn)生阻塞的鎖無(wú)法從表中查出骇扇。MySQL 8.0將會(huì)完善鎖數(shù)據(jù)的展示摔竿。
關(guān)于此表的更多詳細(xì)信息，請(qǐng)參考官方文檔 24.32.13 The INFORMATION_SCHEMA INNODB_LOCKS Table
SHOW ENGINE INNODB STATUS
詳細(xì)信息請(qǐng)參考官方文檔 14.18.3 InnoDB Standard Monitor and Lock Monitor Output
此語(yǔ)句打印出的信息很多少孝，詳情參考上述官方文檔內(nèi)的Example继低。本文不再展示全部打印信息，僅摘要其中的 TRANSACTIONS 小節(jié)稍走。
提示
需要設(shè)置數(shù)據(jù)庫(kù)參數(shù)：innodb_status_output_locks = on
才會(huì)打印額外的鎖信息袁翁，僅在測(cè)試環(huán)境設(shè)置此參數(shù)
示例
（節(jié)選自自官方文檔的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
...
...
上一節(jié)中我們提到，從innodb_locks表中我們可以知曉鎖的類型信息婿脸。唯一無(wú)法區(qū)分的類型是 record lock 還是 next-key lock粱胜。本節(jié)的show engine innodb status將會(huì)協(xié)助我們區(qū)分。
從官方文檔的示例中我們可以看到 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
...
...
很遺憾焙压，這個(gè)示例中沒(méi)有更多的鎖信息。因?yàn)槲覀儫o(wú)法從show engine innodb status中獲得持有方的鎖的類型線索抑钟，我們只能獲得阻塞方的鎖的類型線索涯曲。見(jiàn)如下示例
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
...
...
這個(gè)示例中我們可以看到transaction 4187持有一些鎖，而transaction 4189正在阻塞并等待某鎖的釋放在塔。
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:展示該transaction已經(jīng)等待了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操作會(huì)在執(zhí)行插入前請(qǐng)求這種gap lock绰沥，來(lái)表明自己的 insert intention。
insert intention lock 的線索以及record lock 和 next-key lock 的線索具體可以閱讀官方文檔14.7.1 InnoDB Locking
record lock 和 next-key lock 的線索現(xiàn)節(jié)選如下
-
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后沒(méi)有接任何額外信息城榛，表示 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 ;; ...
至此揪利，結(jié)合前文所有的信息，我們可以明確區(qū)分 shared lock, exclusive lock, record lock, gap lock, next-key lock, insert intention lock 等鎖的類型狠持。
不過(guò)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發(fā)生绍撞，此表才有數(shù)據(jù)。
示例
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 表得院，此表展示了更多的信息傻铣。
更多詳細(xì)信息請(qǐng)參考官方文檔 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 表不同祥绞，此表展示了當(dāng)前所有的鎖信息非洲。
示例
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 表，示例中可見(jiàn)data_locks新增了REC_NOT_GAP和INSERT_INTENTION的描述符兜喻。（新增描述符并未在官方文檔中作出明確聲明）
憑借新的描述符梦染，可以明確區(qū)分出鎖的分類是 record lock, gap lock, next-key lock, insert intention lock 中的哪一個(gè)。無(wú)需再像 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 |
更多詳細(xì)信息請(qǐng)參考官方文檔 25.46.20 The INFORMATION_SCHEMA INNODB_LOCKS Table 和 26.12.13.1 The data_locks Table
SHOW ENGINE INNODB STATUS
同 MySQL 5.7 中的信息相同，不再贅述遂铡。
至此我們發(fā)現(xiàn)肮疗，MySQL 8.0 中的 data_locks 表展示了詳盡的鎖信息，我們可以從此獲得絕大部分信息忧便。在學(xué)習(xí) InnoDB 引擎中的語(yǔ)句如何加鎖時(shí)族吻，極力推薦使用 MySQL 8.0 中的 data_locks 表。
