MySQL鎖：03.InnoDB行鎖

孔個個發表於2020-12-14

MySql

傳送門：MySQL鎖：01.總覽
 傳送門：MySQL鎖：02.InnoDB鎖
 傳送門：MySQL鎖：03.InnoDB行鎖

通過索引實現行鎖，在索引記錄上加鎖。

沒有索引就無法實現行鎖，升級成全表記錄鎖，等同於表鎖。

理解InnoDB獨特的行鎖執行機制，認識特有的四種行鎖粒度——lock_ordinary、lock_gap、lock_rec_not_gap、lock_insert_intention

InnoDB 行鎖

預設都是加lock_ordinary鎖
如果是唯一索引列上的等值查詢，則退化成lock_rec_not_gap
所有版本，非唯一索引列上的範圍查詢，遇到第一個不符合條件的記錄也會加上lock_ordinary。
8.0.18版本以前，主要指<=場景：唯一索引列上的範圍查詢，遇到第一個不符合條件的記錄也會加上lock_ordinary ，在RC下會釋放，RR下不會釋放。
8.0.18版本以前，非唯一索引列上的等值查詢，向右遍歷遇到第一個不符合條件的記錄時，先加上lock_ordinary，再退化成lock_gap。

鎖排查可以用的檢視和資料字典

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

InnoDB 行鎖相容性

		請求的鎖型別	請求的鎖型別	請求的鎖型別	請求的鎖型別
		lock_ordinary	lock_rec_not_gap	lock_gap	lock_insert_intention
已獲得的鎖型別	lock_ordinary	X	X	O	X
已獲得的鎖型別	lock_rec_not_gap	X	X	O	O
已獲得的鎖型別	lock_gap	O	O	O	X
已獲得的鎖型別	lock_insert_intention	O	O	O	O

gap只和insert intention鎖衝突
insert intention和任何鎖都不衝突，除非也在相同位置做意向插入鎖
先獲得意向插入鎖的，再嘗試上gap lock是可以的
但是反過來，先獲得gap lock的，再嘗試加上意向插入鎖便會阻塞，
原因是：先獲得意向插入鎖時，實際上插入已經成功，意向插入鎖會被轉變為對具體記錄的ordinary 或 rec_not_gap ，此時二者都與lock gap相容。

InnoDB行鎖之共享鎖

共享鎖：

不允許其他事務修改被鎖定的行，只能讀
select .. for share/ lock in share mode
自動提交模式下的普通select是一致性非鎖定讀，不加鎖。

自動提交模式下，不使用begin開啟事務，直接select的話：

select * from xxx where .. 不加鎖

select * from xxx where .. for share ，也查詢不到加鎖，但是實際上是加鎖的，只不過鎖的時間及其的短暫。

驗證：

此時，用排他鎖來驗證自動提交模式的for share究竟是否產生鎖動作。

可以看出，自動提交模式下select（不加for share）是一致性非鎖定讀，但是加for share後，是會有鎖定動作的，只不過沒有阻塞的情況下，鎖的持續時間是非常短暫的。

檢視InnoDB鎖

開啟引數：innodb_status_output_locks=1; 以支援使用 show engine innodb status 檢視鎖詳情。

mysql> begin ; select * from k1 where id=4 for update;
Query OK, 0 rows affected (0.00 sec)

mysql> show engine innodb status \G
..
MySQL thread id 31, OS thread handle 139620328457984, query id 1297 localhost root
**TABLE LOCK table `kk`.`k1` trx id 1901 lock mode IX**
RECORD LOCKS space id 3 page no 10 n bits 1056 index id of table `kk`.`k1` trx id 1901 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc   ;;
 1: len 6; hex 000000000902; asc    ;;
 
RECORD LOCKS space id 3 page no 9 n bits 320 index GEN_CLUST_INDEX of table `kk`.`k1` trx id 1901 lock_mode X locks rec but not gap
Record lock, heap no 248 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 6; hex 000000000902; asc    ;;
 1: len 6; hex 000000000663; asc   c;;
 2: len 7; hex 82000000940110; asc    ;;
 3: len 4; hex 80000004; asc   ;;
 4: SQL NULL;
 5: SQL NULL;
 
RECORD LOCKS space id 3 page no 10 n bits 1056 index id of table `kk`.`k1` trx id 1901 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000016; asc   ;;
 1: len 6; hex 000000000200; asc    ;;
..

- space id 3 表的表空間ID
- page no 10 鎖所在datapage的ID
- heap no 4，slot no，記錄在page物理上的第幾個位置。

也可以查詢P_S表

mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID             | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE   | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139620969519720:1072:139620864029240  |         2211 |    85 |    79 | kk      | k1     | NULL      | NULL       | NULL    |    139620864029240 | TABLE   | IX      | GRANTED   | NULL   |
| INNODB | 139620969519720:15:4:5:139620864026200 |         2211 |    85 |    79 | kk      | k1     | NULL      | NULL       | PRIMARY  |    139620864026200 | RECORD  | X,REC_NOT_GAP | GRANTED   | 4     |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

再看下IS鎖的情況

mysql> begin ; select * from k1 where id=4 for share;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show engine innodb status \G
看不到IS鎖資訊。
1 row in set (0.00 sec)
 
mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID             | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE   | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139620969519720:1072:139620864029240  |    421095946230376 |    85 |    83 | kk      | k1     | NULL      | NULL       | NULL    |    139620864029240 | TABLE   | IS      | GRANTED   | NULL   |
| INNODB | 139620969519720:15:4:5:139620864026200 |    421095946230376 |    85 |    83 | kk      | k1     | NULL      | NULL       | PRIMARY  |    139620864026200 | RECORD  | S,REC_NOT_GAP | GRANTED   | 4     |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

驗證一下IS和IX的相容

t1:
mysql> begin ; select * from k1 where id=4 for share;
Query OK, 0 rows affected (0.00 sec)
 
+------+------+------+
| id  | dtl | name |
+------+------+------+
|  4 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)
t2:
mysql> begin ; select * from k1 where id=11 for update;
Query OK, 0 rows affected (0.00 sec)
 
Empty set (0.00 sec)
 
--注意:加鎖加不在相同行，否則hang。
 
t3:
mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID             | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE   | LOCK_STATUS | LOCK_DATA       |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
| INNODB | 139620969519720:1072:139620864029240  |         2212 |    85 |    89 | kk      | k1     | NULL      | NULL       | NULL    |    139620864029240 | TABLE   | IX      | GRANTED   | NULL          |
| INNODB | 139620969519720:15:4:1:139620864026200 |         2212 |    85 |    89 | kk      | k1     | NULL      | NULL       | PRIMARY  |    139620864026200 | RECORD  | X       | GRANTED   | supremum pseudo-record |
| INNODB | 139620969521464:1072:139620864041176  |    421095946232120 |    84 |   110 | kk      | k1     | NULL      | NULL       | NULL    |    139620864041176 | TABLE   | IS      | GRANTED   | NULL          |
| INNODB | 139620969521464:15:4:5:139620864038296 |    421095946232120 |    84 |   110 | kk      | k1     | NULL      | NULL       | PRIMARY  |    139620864038296 | RECORD  | S,REC_NOT_GAP | GRANTED   | 4           |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
4 rows in set (0.00 sec)

InnoDB行鎖實現機制

基於索引實現，逐行檢查，逐行加鎖
沒有索引的列上需要加鎖時，會先對所有記錄加鎖，再根據實際情況決定是否釋放鎖。
輔助索引上加鎖時，同時要回溯到主鍵索引上再加一次鎖。
加鎖的基本單位預設時lock_ordinary，當索引就具有唯一性的時候退化為lock_rec_not_gap
等值條件逐行加鎖時，會向右遍歷到第一個不滿足條件的記錄，然後lock_ordinary退化為lock_gap
如果發生唯一性檢測（insert\update動作），那麼會發生lock_ordinary ，再退化成lock_rec_not_gap
唯一索引的範圍條件加鎖時，也會對第一個不滿足條件的記錄加鎖

對普通索引上鎖

普通索引next-key lock + 主鍵 not gap + 普通索引的下一個記錄的gap lock（見示意圖）。

mysql> select * from k2;
+----+------+------+
| id | dtl  | un   |
+----+------+------+
| 1  |  1   |  1   |
| 2  |  2   |  2   |
| 3  |  5   |  5   |  (回溯到pk上鎖)
------------------------------------------  <- gap (同時鎖住普通索引下一個記錄前的gap)
| 4  |  7   |  7   |*
| 5  |  11  |  11  |
+----+------+------+
5 rows in set (0.00 sec)

輔助索引上鎖的驗證實驗

mysql> show create table k2\G
*************************** 1. row ***************************
    Table: k2
Create Table: CREATE TABLE `k2` (
 `id` int NOT NULL AUTO_INCREMENT,
 `dtl` int DEFAULT NULL,
 `un` int DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `dtl` (`dtl`),
 KEY `un` (`un`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
 
mysql> select * from k2;
+----+------+------+
| id | dtl | un  |
+----+------+------+
| 1 |  1 |  1 |
| 2 |  2 |  2 |
| 3 |  5 |  5 |
|  4 |  7 |  7 |*
| 5 |  11 |  11 |
+----+------+------+
5 rows in set (0.00 sec)
 
mysql> begin ; select * from k2 where un=5 for update;
Query OK, 0 rows affected (0.00 sec)
 
+----+------+------+
| id | dtl | un  |
+----+------+------+
| 3 |  5 |  5 |
+----+------+------+
1 row in set (0.00 sec)
 
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
        ENGINE: INNODB
    ENGINE_LOCK_ID: 139620969521464:1061:139620864041176
ENGINE_TRANSACTION_ID: 1944
      THREAD_ID: 70
       EVENT_ID: 65
    OBJECT_SCHEMA: kk
     OBJECT_NAME: k2
    PARTITION_NAME: NULL
  SUBPARTITION_NAME: NULL
      INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139620864041176
       LOCK_TYPE: TABLE*
      LOCK_MODE: IX*
     LOCK_STATUS: GRANTED
      LOCK_DATA: NULL
*************************** 2. row ***************************
        ENGINE: INNODB
    ENGINE_LOCK_ID: 139620969521464:4:6:9:139620864038296
ENGINE_TRANSACTION_ID: 1944
      THREAD_ID: 70
       EVENT_ID: 65
    OBJECT_SCHEMA: kk
     OBJECT_NAME: k2
    PARTITION_NAME: NULL
  SUBPARTITION_NAME: NULL
      INDEX_NAME: un*
OBJECT_INSTANCE_BEGIN: 139620864038296
      LOCK_TYPE: RECORD （nextkey-lock）*
      LOCK_MODE: X
     LOCK_STATUS: GRANTED
       LOCK_DATA: 5, 3    （un key value, primary key value, index Condion特性）*
*************************** 3. row ***************************
        ENGINE: INNODB
    ENGINE_LOCK_ID: 139620969521464:4:4:11:139620864038640
ENGINE_TRANSACTION_ID: 1944
      THREAD_ID: 70
       EVENT_ID: 65
    OBJECT_SCHEMA: kk
     OBJECT_NAME: k2
    PARTITION_NAME: NULL
  SUBPARTITION_NAME: NULL
      INDEX_NAME: PRIMARY*
OBJECT_INSTANCE_BEGIN: 139620864038640
      LOCK_TYPE: RECORD
       LOCK_MODE: X,REC_NOT_GAP*
     LOCK_STATUS: GRANTED
      LOCK_DATA: 3*
*************************** 4. row ***************************
        ENGINE: INNODB
    ENGINE_LOCK_ID: 139620969521464:4:6:10:139620864038984
ENGINE_TRANSACTION_ID: 1944
      THREAD_ID: 70
       EVENT_ID: 65
    OBJECT_SCHEMA: kk
     OBJECT_NAME: k2
    PARTITION_NAME: NULL
  SUBPARTITION_NAME: NULL
      INDEX_NAME: un*
OBJECT_INSTANCE_BEGIN: 139620864038984
      LOCK_TYPE: RECORD
      LOCK_MODE: X,GAP*      確認下一個記錄不符合條件，回退nextkey-lock 為 lock-gap.
     LOCK_STATUS: GRANTED
      LOCK_DATA: 7, 4   （un key value, primary key value, index Condion特性）*
4 rows in set (0.00 sec)

InnoDB隱式、顯式鎖

顯式鎖(explicit-lock)
- select .. from .. where .. for update / for share
隱式鎖(implicit-lock)
- update set .. where ..
- 任何輔助索引上鎖，或非索引列上鎖，都要回溯到主鍵上再加鎖。
- 和其他session有衝突時，隱式鎖轉換為顯式鎖。（沒實驗驗證出來。）

如果發生唯一性檢測（insert\update動作），那麼會發生lock_ordinary ，再退化成lock_rec_not_gap

MySQL全域性鎖、表鎖以及行鎖
2019-02-11
MySql
MySQL -- 行鎖
2018-07-24
MySql
MySQL 行鎖
2024-10-15
MySql
MySQL 行級鎖之間隙鎖、臨鍵鎖
2024-04-05
MySql
mysql行鎖和死鎖檢測
2019-05-12
MySql
MySQL索引失效行鎖變表鎖
2020-10-03
MySql索引
MySQL鎖（四）行鎖的加鎖規則和案例
2020-12-18
MySql
【問答分享第一彈】MySQL鎖總結:MySQL行鎖、表鎖、排他鎖、共享鎖的特點
2021-07-09
MySql
MySQL鎖：InnoDB行鎖需要避免的坑
2020-04-17
MySql
MySQL 5.5 InnoDB表鎖行鎖測試
2016-05-30
MySql
mysql for update是鎖表還是鎖行
2024-03-12
MySql
MySQL/InnoDB中，樂觀鎖、悲觀鎖、共享鎖、排它鎖、行鎖、表鎖、死鎖概念的理解
2018-07-30
MySql
MySQL行鎖、表鎖、間隙鎖，你都瞭解嗎
2020-12-14
MySql
mysql innodb的行鎖
2017-03-14
MySql
mysql鎖之死鎖
2019-07-19
MySql
mysql鎖之三種行級鎖介紹
2019-07-12
MySql
你真的會用mysql行級鎖嗎？mysql 行級鎖全解析
2019-07-08
MySql
一文搞懂MySQL行鎖、表鎖、間隙鎖詳解
2020-10-20
MySql
mysql innodb的行鎖(5) --next-Key 鎖
2017-03-14
MySql
【鎖】MySQL間隙鎖
2017-08-03
MySql
mysql innodb的行鎖(2)
2017-03-14
MySql
mysql innodb的行鎖(3)
2017-03-14
MySql
mysql innodb的行鎖(4)
2017-03-14
MySql
MySQL行級鎖測試
2016-01-08
MySql
MySQL行鎖深入研究
2015-10-30
MySql
MySQL複習筆記(05)：MySQL表級鎖和行級鎖
2020-09-26
MySql筆記
MySQL鎖(樂觀鎖、悲觀鎖、多粒度鎖)
2022-06-06
MySql
全域性鎖、表鎖、行鎖
2022-06-12
詳解 MySql InnoDB 中的三種行鎖（記錄鎖、間隙鎖與臨鍵鎖）
2020-10-14
MySql
《淺入淺出MySQL》表鎖行鎖併發插入
2020-10-22
MySql
Java鎖？分散式鎖？樂觀鎖？行鎖？
2019-04-23
Java分散式
MySQL 死鎖和鎖等待
2020-04-13
MySql
MySQL鎖
2021-03-06
MySql
mysql 鎖
2024-05-14
MySql
[Mysql]鎖
2024-07-16
MySql
Mysql鎖之行級鎖和表級意向鎖
2019-07-08
MySql
Mysql研磨之InnoDB行鎖模式
2018-05-08
MySql模式
Mysql不鎖表進行Mysqldump
2014-05-27
MySql