大家好呀，我是樓仔。

對(duì)于這個(gè)問題，我 4 年前就專門研究過，最近看到網(wǎng)上很多相關(guān)的文章，要么總結(jié)得不全，要么存在很多問題。

感覺有必要自己寫一篇，一方面對(duì)網(wǎng)上的知識(shí)進(jìn)行糾偏，另一方面也想全面總結(jié)一下這塊知識(shí)，方便大家學(xué)習(xí)。

這篇文章應(yīng)該是全網(wǎng)總結(jié)最全的，如果有發(fā)現(xiàn)比我這篇寫得更好，更全，一定要私我哈。

不 BB，上文章目錄：

圖片

01 環(huán)境準(zhǔn)備

在驗(yàn)證之前，我們先準(zhǔn)備好具體的環(huán)境和數(shù)據(jù)，事務(wù)隔離級(jí)別 RR，數(shù)據(jù)庫版本 5.7.26。

為了方便測試，索引都是整型：

CREATE TABLE user (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  user_no int(11) NOT NULL COMMENT '用戶編號(hào)',
  user_name varchar(16) DEFAULT NULL COMMENT '用戶名',
  age int(3) DEFAULT NULL COMMENT '年齡',
  PRIMARY KEY (id),
  UNIQUE KEY un_idx_user_no (user_no),
  KEY idx_age (age)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

初始化數(shù)據(jù)：

insert into user values(1, 10, '樓仔', 18);
insert into user values(4, 15, '二哥', 28);
insert into user values(8, 20, '一灰', 38);

常用命令操作：

> start transaction; // 開啟事務(wù)
> commit; // 提交事務(wù)
> rollback; // 回滾事務(wù)
> select @@transaction_isolation; // 查看事務(wù)隔離級(jí)別
> select @@version; // 查看數(shù)據(jù)庫版本
> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; //  查詢鎖

02 場景分類

2.1 主鍵（有值）

說明：主鍵查詢，查詢數(shù)據(jù)存在。

執(zhí)行悲觀鎖查詢：

select * from user where id = 1 for update;

執(zhí)行更新操作，被鎖住了：

update user set user_name = "樓仔小弟" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

查看鎖信息：

圖片

• lock_mode 為 X（排他鎖）
• lock_type 為 RECORD，行級(jí)鎖

結(jié)論：查詢條件為主鍵，且有值，行鎖

2.2 主鍵（空值）

操作：主鍵查詢，查詢數(shù)據(jù)不存在。

執(zhí)行悲觀鎖查詢：

select * from user where id = 2 for update;

執(zhí)行插入操作，被鎖住了：

insert into user values(3, 14, '樓仔小弟', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

這里的間隙鎖，鎖住的區(qū)間是 id 字段的 (1，4) 區(qū)間，查看鎖信息：

圖片

• lock_mode 為 X（排他鎖）+ Gap（間隙鎖）
• lock_type 為 RECORD，行級(jí)鎖

結(jié)論：查詢條件為主鍵，且空值，間隙鎖

2.3 唯一索引（有值）

說明：唯一索引查詢，數(shù)據(jù)存在。

執(zhí)行悲觀鎖查詢：

select * from user where user_no = 10 for update;

執(zhí)行更新操作，被鎖住了：

update user set user_name = "樓仔小弟" where user_no = 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

圖片

結(jié)論：查詢條件為唯一索引，且有值，行鎖

2.4 唯一索引（空值）

說明：唯一索引查詢，數(shù)據(jù)不存在。

執(zhí)行悲觀鎖查詢：

select * from user where user_no = 11 for update;

執(zhí)行插入操作，被鎖住了：

insert into user values(3, 14, '樓仔小弟', 28);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

這里的間隙鎖，鎖住的區(qū)間是 user_no 字段的 (1，4) 區(qū)間。

圖片

結(jié)論：查詢條件為唯一索引，且空值，間隙鎖

2.5 普通索引（有值）

說明：普通索引，數(shù)據(jù)存在。

執(zhí)行悲觀鎖查詢：

select * from user where age = 18 for update;

執(zhí)行更新操作，被鎖住了：

update user set user_name = "樓仔小弟" where age = 18;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

圖片

執(zhí)行插入操作，被鎖住了：

insert into user values(3, 14, '樓仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

圖片

這里鎖住的是 age 字段的 [18, 28) 這區(qū)間。

結(jié)論：查詢條件為普通索引，且有值，間隙鎖

2.6 普通索引（空值）

說明：普通索引，數(shù)據(jù)不存在。

執(zhí)行悲觀鎖查詢：

select * from user where age = 19 for update;

執(zhí)行插入操作，被鎖住了：

insert into user values(3, 14, '樓仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

圖片

這里鎖住的是 age 字段的 (18, 28) 這區(qū)間。

結(jié)論：查詢條件為普通索引，且空值，間隙鎖

2.7 索引（范圍查詢）

說明：這里的索引，包括主鍵索引、唯一索引和普通索引。

執(zhí)行悲觀鎖查詢：

select * from user where id > 1 for update;

執(zhí)行插入操作，被鎖住了：

insert into user values(3, 14, '樓仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

圖片

這里其實(shí)可以對(duì) id = 1 的數(shù)據(jù)進(jìn)行更新，對(duì)于其它數(shù)據(jù)，都被鎖住，鎖住的范圍是 id 字段的 (1, 4],(4, 8],(8, 正無窮) 區(qū)間。

結(jié)論：查詢條件為索引，且是范圍查詢，間隙鎖。

2.8 無索引

執(zhí)行悲觀鎖查詢：

select * from user where user_name = "樓仔" for update;

執(zhí)行插入操作，被鎖住了：

insert into user values(3, 14, '樓仔小弟', 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

這里明顯是鎖表了，但是為什么鎖的信息還是行鎖呢，知道的同學(xué)，可以私我哈~~

結(jié)論：查詢條件為無索引，表鎖。

03 加鎖規(guī)則

3.1 規(guī)律總結(jié)

我們把上面的結(jié)論進(jìn)行匯總：

圖片

總結(jié)如下規(guī)律：

1. 當(dāng)查詢條件為主鍵和唯一索引，當(dāng)有值時(shí)，是行鎖；
2. 當(dāng)查詢條件為主鍵和唯一索引，當(dāng)為空值時(shí)，是間隙鎖；
3. 當(dāng)查詢條件為普通索引，是間隙鎖；
4. 當(dāng)查詢條件為索引，且為范圍查詢，是間隙鎖；
5. 當(dāng)查詢條件無索引，是表鎖。

3.2 加鎖規(guī)則

那是否有一套加鎖規(guī)則呢？

為了便于大家理解，我先普及 3 個(gè)概念：

• Record Lock：行鎖
• Gap Lock：間隙鎖，鎖定一個(gè)范圍，但不包含記錄本身
• Next-Key Lock：行鎖 + 間隙鎖，左開右閉，比如(1,5]

其實(shí) MySQL 大佬林曉斌在極客時(shí)間講過，后來也有很多博主轉(zhuǎn)發(fā)過他的加鎖規(guī)則，我直接把這套規(guī)則貼一下。

兩個(gè)“原則”：

• 原則 1：加鎖的基本單位是 next-key lock，其中 next-key lock 是前開后閉區(qū)間；
• 原則 2：查找過程中訪問到的對(duì)象才會(huì)加鎖。

兩個(gè)“優(yōu)化”：

• 優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時(shí)候，next-key lock退化為行鎖；
• 優(yōu)化 2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock 退化為間隙鎖。

3.3 分析一下

這里我們結(jié)合上面的案例，來解讀這套加鎖規(guī)則。

針對(duì)我們前面總結(jié)的 5 條規(guī)律，我們先分析這兩條：

• 當(dāng)查詢條件為主鍵和唯一索引，當(dāng)有值時(shí)，是行鎖；
• 當(dāng)查詢條件為主鍵和唯一索引，當(dāng)為空值時(shí)，是間隙鎖。

下面我們根據(jù) “兩個(gè)原則” + “兩個(gè)優(yōu)化” 來分析一下。

根據(jù) “原則 1”，加鎖的基本單位是 next-key lock，當(dāng) “索引上為等值查詢” 時(shí)（即能查到該數(shù)據(jù)），根據(jù) “優(yōu)化 1”，間隙鎖退化為行鎖。

同理，根據(jù) “優(yōu)化 2”，索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock 退化為間隙鎖。

再分析這兩條：

• 當(dāng)查詢條件為普通索引，是間隙鎖；
• 當(dāng)查詢條件為索引，且為范圍查詢，是間隙鎖；

同上，通過 “原則 1” 和 “優(yōu)化 2”，普通索引 是加的間隙鎖。

對(duì)于范圍查詢，個(gè)人認(rèn)為上面的規(guī)則還不能完全覆蓋，當(dāng)時(shí)林曉斌針對(duì)這些規(guī)則，舉了 4 個(gè)示例，然后進(jìn)行詳細(xì)剖析，包括間隙鎖的范圍區(qū)間計(jì)算。

因?yàn)槠?，這里就不再詳細(xì)展開，如果后續(xù)需要，我也可能會(huì)單獨(dú)出一篇。

04 寫在最后

最后我們再回顧一下（RR 隔離級(jí)別）：

1. 當(dāng)查詢條件為主鍵和唯一索引，當(dāng)有值時(shí)，是行鎖；
2. 當(dāng)查詢條件為主鍵和唯一索引，當(dāng)為空值時(shí)，是間隙鎖；
3. 當(dāng)查詢條件為普通索引，是間隙鎖；
4. 當(dāng)查詢條件為索引，且為范圍查詢，是間隙鎖；
5. 當(dāng)查詢條件無索引，是表鎖。

至于間隙鎖的范圍，如何計(jì)算，本文沒有詳細(xì)闡述，但是上面的這些規(guī)則，就能基本滿足我們?nèi)粘９ぷ餍枰?/p>