美團問數(shù)據(jù)庫應(yīng)該是非常多的，尤其喜歡考手寫 SQL 然后問你這個 SQL 語句上面加了哪些鎖，你會發(fā)現(xiàn)其他廠面試基本很少會這樣考，所以很多小伙伴遇到這種問題的時候都是一臉懵逼，這篇文章就來詳細總結(jié)下 InnoDB 存儲引擎中的行鎖的加鎖規(guī)則，并輔以實例解釋。

首先眾所周知，InnoDB 三種行鎖：

• Record Lock（記錄鎖）：鎖住某一行記錄
• Gap Lock（間隙鎖）：鎖住一段左開右開的區(qū)間
• Next-key Lock（臨鍵鎖）：鎖住一段左開右閉的區(qū)間

哪些語句上面會加行鎖？

1）對于常見的 DML 語句（如 UPDATE、DELETE? 和 INSERT ），InnoDB 會自動給相應(yīng)的記錄行加寫鎖

2）默認情況下對于普通 SELECT 語句，InnoDB 不會加任何鎖，但是在 Serializable 隔離級別下會加行級讀鎖

上面兩種是隱式鎖定，InnoDB 也支持通過特定的語句進行顯式鎖定：

3）SELECT * FROM table_name WHERE ... FOR UPDATE，加行級寫鎖

4）SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE，加行級讀鎖

前置知識就不過多介紹了，在學(xué)習(xí)具體行鎖加鎖規(guī)則之前，小伙伴們需要記住加鎖規(guī)則的兩條核心：

1）查找過程中訪問到的對象才會加鎖

這句話該怎么理解？比如有主鍵 id 為 1 2 3 4 5 ... 10? 的10 條記錄，我們要找到 id = 7? 的記錄。注意，查找并不是從第一行開始一行一行地進行遍歷，而是根據(jù) B+ 樹的特性進行二分查找，所以一般存儲引擎只會訪問到要找的記錄行（id = 7）的相鄰區(qū)間

2）加鎖的基本單位是 Next-key Lock

下面結(jié)合實例幫助大伙分析一條 SQL 語句上面究竟被 InnoDB 自動加上了多少個鎖

假設(shè)有這么一張 user? 表，id? 為主鍵（唯一索引），a? 是普通索引（非唯一索引），b 都是普通的列，其上沒有任何索引：

案例 1：唯一索引等值查詢

當(dāng)我們用唯一索引進行等值查詢的時候，根據(jù)查詢的記錄是否存在，加鎖的規(guī)則會有所不同：

1. 當(dāng)查詢的記錄是存在的，Next-key Lock 會退化成記錄鎖
2. 當(dāng)查詢的記錄是不存在的，Next-key Lock 會退化成間隙鎖

查詢的記錄存在

先來看個查詢的記錄存在的案例：

select * from user
where id = 25
for update;

結(jié)合加鎖的兩條核心：查找過程中訪問到的對象才會加鎖 + 加鎖的基本單位是 Next-key Lock（左開右閉），我們可以分析出，這條語句的加鎖范圍是 (20, 25]

不過，由于這個唯一索引等值查詢的記錄 id = 25? 是存在的，因此，Next-key Lock 會退化成記錄鎖，因此最終的加鎖范圍是 id = 25 這一行

查詢的記錄不存在

再來看查詢的記錄不存在的案例：

select * from user
where id = 22
for update;

結(jié)合加鎖的兩條核心：查找過程中訪問到的對象才會加鎖 + 加鎖的基本單位是 Next-key Lock（左開右閉），我們可以分析出，這條語句的加鎖范圍是 (20, 25]

這里為什么是 (20，25]? 而不是 (20, 22]?，因為 id = 22 的記錄不存在呀，InnoDB 先找到 id = 20 的記錄，發(fā)現(xiàn)不匹配，于是繼續(xù)往下找，發(fā)現(xiàn) id = 25，因此，id = 25 的這一行被掃描到了，所以整體的加鎖范圍是 (20, 25]

由于這個唯一索引等值查詢的記錄 id = 22? 是不存在的，因此，Next-key Lock 會退化成間隙鎖，因此最終在主鍵 id 上的加鎖范圍是 Gap Lock (20, 25)

案例 2：唯一索引范圍查詢

唯一索引范圍查詢的規(guī)則和等值查詢的規(guī)則一樣，只有一個區(qū)別，就是唯一索引的范圍查詢需要一直向右遍歷到第一個不滿足條件的記錄，下面結(jié)合案例來分析：

select * from user
where id >= 20 and id < 22
for update;

先來看語句查詢條件的前半部分 id >= 20?，因此，這條語句最開始要找的第一行是 id = 20，結(jié)合加鎖的兩個核心，需要加上 Next-key Lock (15,20]?。又由于 id 是唯一索引，且 id = 20 的這行記錄是存在的，因此會退化成記錄鎖，也就是只會對 id = 20 這一行加鎖。

再來看語句查詢條件的后半部分 id < 22?，由于是范圍查找，就會繼續(xù)往后找第一個不滿足條件的記錄，也就是會找到 id = 25? 這一行停下來，然后加 Next-key Lock (20, 25]?，重點來了，但由于 id = 25? 不滿足 id < 22?，因此會退化成間隙鎖，加鎖范圍變?yōu)?nbsp;(20, 25)。

所以，上述語句在主鍵 id 上的最終的加鎖范圍是 Record Lock id = 20? 以及  Gap Lock (20, 25)

案例 3：非唯一索引等值查詢

當(dāng)我們用非唯一索引進行等值查詢的時候，根據(jù)查詢的記錄是否存在，加鎖的規(guī)則會有所不同：

• 當(dāng)查詢的記錄是存在的，除了會加 Next-key Lock 外，還會額外加間隙鎖（規(guī)則是向下遍歷到第一個不符合條件的值才能停止），也就是會加兩把鎖

很好記憶，就是要查找記錄的左區(qū)間加 Next-key Lock，右區(qū)間加 Gap lock

• 當(dāng)查詢的記錄是不存在的，Next-key Lock 會退化成間隙鎖（這個規(guī)則和唯一索引的等值查詢是一樣的）

查詢的記錄存在

先來看個查詢的記錄存在的案例：

select * from user
where a = 16
for update;

結(jié)合加鎖的兩條核心，這條語句首先會對普通索引 a 加上 Next-key Lock，范圍是 (8,16]

又因為是非唯一索引等值查詢，且查詢的記錄 a= 16? 是存在的，所以還會加上間隙鎖，規(guī)則是向下遍歷到第一個不符合條件的值才能停止，因此間隙鎖的范圍是 (16,32)

所以，上述語句在普通索引 a 上的最終加鎖范圍是 Next-key Lock (8,16]? 以及  Gap Lock (16,32)

查詢的記錄不存在

再來看查詢的記錄不存在的案例：

select * from user
where a = 18
for update;

結(jié)合加鎖的兩條核心，這條語句首先會對普通索引 a 加上 Next-key Lock，范圍是 (16,32]

但是由于查詢的記錄 a = 18? 是不存在的，因此 Next-key Lock 會退化為間隙鎖，即最終在普通索引 a 上的加鎖范圍是 (16,32)。

案例 4：非唯一索引范圍查詢

范圍查詢和等值查詢的區(qū)別在上面唯一索引章節(jié)已經(jīng)介紹過了，就是范圍查詢需要一直向右遍歷到第一個不滿足條件的記錄，和唯一索引范圍查詢不同的是，非唯一索引的范圍查詢并不會退化成 Record Lock 或者 Gap Lock。

select * from user
where a >= 16 and a < 18
for update;

先來看語句查詢條件的前半部分 a >= 16?，因此，這條語句最開始要找的第一行是 a = 16，結(jié)合加鎖的兩個核心，需要加上 Next-key Lock (8,16]?。雖然非唯一索引 a = 16 的這行記錄是存在的，但此時并不會像唯一索引那樣退化成記錄鎖。

再來看語句查詢條件的后半部分 a < 18?，由于是范圍查找，就會繼續(xù)往后找第一個不滿足條件的記錄，也就是會找到 id = 32? 這一行停下來，然后加 Next-key Lock (16, 32]?。雖然 id = 32? 不滿足 id < 18，但此時并不會向唯一索引那樣退化成間隙鎖。

所以，上述語句在普通索引 a 上的最終的加鎖范圍是 Next-key Lock (8, 16]? 和  (16, 32]?，也就是 (8, 32]。