色哟哟视频在线观看-色哟哟视频在线-色哟哟欧美15最新在线-色哟哟免费在线观看-国产l精品国产亚洲区在线观看-国产l精品国产亚洲区久久

0
  • 聊天消息
  • 系統消息
  • 評論與回復
登錄后你可以
  • 下載海量資料
  • 學習在線課程
  • 觀看技術視頻
  • 寫文章/發帖/加入社區
會員中心
創作中心

完善資料讓更多小伙伴認識你,還能領取20積分哦,立即完善>

3天內不再提示

MySQL是怎么加行級鎖的?有什么規則?

小林coding ? 來源:小林coding ? 作者:小林coding ? 2022-11-17 09:28 ? 次閱讀

是不是很多人都對 MySQL 加行級鎖的規則搞的迷迷糊糊,對記錄一會加的是 next-key 鎖,一會加是間隙鎖,一會又是記錄鎖。

坦白說,確實還挺復雜的,但是好在我找點了點規律,也知道如何如何用命令分析加了什么類型的行級鎖。

為了說清楚這三件事情:

1、MySQL 是怎么加行級鎖的?有什么規則?

2、為什么MySQL 要這么加行級鎖?

3、如何用命令分析加了什么行級鎖?

什么 SQL 語句會加行級鎖?

InnoDB 引擎是支持行級鎖的,而 MyISAM 引擎并不支持行級鎖,所以后面的內容都是基于 InnoDB 引擎 的。

所以,在說 MySQL 是怎么加行級鎖的時候,其實是在說 InnoDB 引擎是怎么加行級鎖的。

普通的 select 語句是不會對記錄加鎖的,因為它屬于快照讀,是通過 MVCC(多版本并發控制)實現的。

如果要在查詢時對記錄加行級鎖,可以使用下面這兩個方式,這兩種查詢會加鎖的語句稱為鎖定讀。

//對讀取的記錄加共享鎖(S型鎖)
select...lockinsharemode;

//對讀取的記錄加獨占鎖(X型鎖)
select...forupdate;

上面這兩條語句必須在一個事務中,因為當事務提交了,鎖就會被釋放,所以在使用這兩條語句的時候,要加上 begin 或者 start transaction 開啟事務的語句。

**除了上面這兩條鎖定讀語句會加行級鎖之外,update 和 delete 操作都會加行級鎖,且鎖的類型都是獨占鎖(X型鎖)**。

//對操作的記錄加獨占鎖(X型鎖)
updaettable....whereid=1;

//對操作的記錄加獨占鎖(X型鎖)
deletefromtablewhereid=1;

共享鎖(S鎖)滿足讀讀共享,讀寫互斥。獨占鎖(X鎖)滿足寫寫互斥、讀寫互斥。

5fdf03e6-6613-11ed-8abf-dac502259ad0.png

行級鎖有哪些種類?

不同隔離級別下,行級鎖的種類是不同的。

在讀已提交隔離級別下,行級鎖的種類只有記錄鎖,也就是僅僅把一條記錄鎖上。

在可重復讀隔離級別下,行級鎖的種類除了有記錄鎖,還有間隙鎖(目的是為了避免幻讀),所以行級鎖的種類主要有三類:

Record Lock,記錄鎖,也就是僅僅把一條記錄鎖上;

Gap Lock,間隙鎖,鎖定一個范圍,但是不包含記錄本身;

Next-Key Lock:Record Lock + Gap Lock 的組合,鎖定一個范圍,并且鎖定記錄本身。

接下來,分別介紹這三種行級鎖。

Record Lock

Record Lock 稱為記錄鎖,鎖住的是一條記錄。而且記錄鎖是有 S 鎖和 X 鎖之分的:

當一個事務對一條記錄加了 S 型記錄鎖后,其他事務也可以繼續對該記錄加 S 型記錄鎖(S 型與 S 鎖兼容),但是不可以對該記錄加 X 型記錄鎖(S 型與 X 鎖不兼容);

當一個事務對一條記錄加了 X 型記錄鎖后,其他事務既不可以對該記錄加 S 型記錄鎖(S 型與 X 鎖不兼容),也不可以對該記錄加 X 型記錄鎖(X 型與 X 鎖不兼容)。

舉個例子,當一個事務執行了下面這條語句:

mysql>begin;
mysql>select*fromt_testwhereid=1forupdate;

事務會對表中主鍵 id = 1 的這條記錄加上 X 型的記錄鎖,如果這時候其他事務對這條記錄進行刪除或者更新操作,那么這些操作都會被阻塞。

注意,其他事務插入一條 id = 1 的新記錄并不會被阻塞,而是會報主鍵沖突的錯誤,這是因為主鍵有唯一性的約束。

5fe86ddc-6613-11ed-8abf-dac502259ad0.png

當事務執行 commit 后,事務過程中生成的鎖都會被釋放。

Gap Lock

Gap Lock 稱為間隙鎖,只存在于可重復讀隔離級別,目的是為了解決可重復讀隔離級別下幻讀的現象。

假設,表中有一個范圍 id 為(3,5)間隙鎖,那么其他事務就無法插入 id = 4 這條記錄了,這樣就有效的防止幻讀現象的發生。

5ff30bd4-6613-11ed-8abf-dac502259ad0.png

間隙鎖雖然存在 X 型間隙鎖和 S 型間隙鎖,但是并沒有什么區別,間隙鎖之間是兼容的,即兩個事務可以同時持有包含共同間隙范圍的間隙鎖,并不存在互斥關系,因為間隙鎖的目的是防止插入幻影記錄而提出的

Next-Key Lock

Next-Key Lock 稱為臨鍵鎖,是 Record Lock + Gap Lock 的組合,鎖定一個范圍,并且鎖定記錄本身。

假設,表中有一個范圍 id 為(3,5] 的 next-key lock,那么其他事務即不能插入 id = 4 記錄,也不能修改和刪除 id = 5 這條記錄。

6008fa3e-6613-11ed-8abf-dac502259ad0.png

所以,next-key lock 即能保護該記錄,又能阻止其他事務將新記錄插入到被保護記錄前面的間隙中。

next-key lock 是包含間隙鎖+記錄鎖的,如果一個事務獲取了 X 型的 next-key lock,那么另外一個事務在獲取相同范圍的 X 型的 next-key lock 時,是會被阻塞的。

比如,一個事務持有了范圍為 (1, 10] 的 X 型的 next-key lock,那么另外一個事務在獲取相同范圍的 X 型的 next-key lock 時,就會被阻塞。

雖然相同范圍的間隙鎖是多個事務相互兼容的,但對于記錄鎖,我們是要考慮 X 型與 S 型關系,X 型的記錄鎖與 X 型的記錄鎖是沖突的。

MySQL 是怎么加行級鎖的?

行級鎖加鎖規則比較復雜,不同的場景,加鎖的形式是不同的。

加鎖的對象是索引,加鎖的基本單位是 next-key lock,它是由記錄鎖和間隙鎖組合而成的,next-key lock 是前開后閉區間,而間隙鎖是前開后開區間

但是,next-key lock 在一些場景下會退化成記錄鎖或間隙鎖。

那到底是什么場景呢?總結一句,在能使用記錄鎖或者間隙鎖就能避免幻讀現象的場景下, next-key lock 就會退化成退化成記錄鎖或間隙鎖。

這次會以下面這個表結構來進行實驗說明:

CREATETABLE`user`(
`id`bigintNOTNULLAUTO_INCREMENT,
`name`varchar(30)COLLATEutf8mb4_unicode_ciNOTNULL,
`age`intNOTNULL,
PRIMARYKEY(`id`),
KEY`index_age`(`age`)USINGBTREE
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

其中,id 是主鍵索引(唯一索引),age 是普通索引(非唯一索引),name 是普通的列。

表中的有這些行記錄:

60128946-6613-11ed-8abf-dac502259ad0.png

這次實驗環境的 MySQL 版本是 8.0.26,隔離級別是「可重復讀」

不同版本的加鎖規則可能是不同的,但是大體上是相同的。

唯一索引等值查詢

當我們用唯一索引進行等值查詢的時候,查詢的記錄存不存在,加鎖的規則也會不同:

當查詢的記錄是「存在」的,在索引樹上定位到這一條記錄后,將該記錄的索引中的 next-key lock 會退化成「記錄鎖」

當查詢的記錄是「不存在」的,在索引樹找到第一條大于該查詢記錄的記錄后,將該記錄的索引中的 next-key lock 會退化成「間隙鎖」。

接下里用兩個案例來說明。

1、記錄存在的情況

假設事務 A 執行了這條等值查詢語句,查詢的記錄是「存在」于表中的。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid=1forupdate;
+----+--------+-----+
|id|name|age|
+----+--------+-----+
|1|路飛|19|
+----+--------+-----+
1rowinset(0.02sec)

那么,事務 A 會為 id 為 1 的這條記錄就會加上 X 型的記錄鎖。

60304602-6613-11ed-8abf-dac502259ad0.png

接下來,如果有其他事務,對 id 為 1 的記錄進行更新或者刪除操作的話,這些操作都會被阻塞,因為更新或者刪除操作也會對記錄加 X 型的記錄鎖,而 X 鎖和 X 鎖之間是互斥關系。

比如,下面這個例子:

609175f8-6613-11ed-8abf-dac502259ad0.png

因為事務 A 對 id = 1的記錄加了 X 型的記錄鎖,所以事務 B 在修改 id=1 的記錄時會被阻塞,事務 C 在刪除 id=1 的記錄時也會被阻塞。

有什么命令可以分析加了什么鎖?

我們可以通過 select * from performance_schema.data_locksG; 這條語句,查看事務執行 SQL 過程中加了什么鎖。

我們以前面的事務 A 作為例子,分析下下它加了什么鎖。

60afda70-6613-11ed-8abf-dac502259ad0.png

從上圖可以看到,共加了兩個鎖,分別是:

表鎖:X 類型的意向鎖;

行鎖:X 類型的記錄鎖;

這里我們重點關注行級鎖,圖中 LOCK_TYPE 中的 RECORD 表示行級鎖,而不是記錄鎖的意思。

通過 LOCK_MODE 可以確認是 next-key 鎖,還是間隙鎖,還是記錄鎖:

如果 LOCK_MODE 為 X,說明是 next-key 鎖;

如果 LOCK_MODE 為 X, REC_NOT_GAP,說明是記錄鎖;

如果 LOCK_MODE 為 X, GAP,說明是間隙鎖;

因此,此時事務 A 在 id = 1 記錄的主鍵索引上加的是記錄鎖,鎖住的范圍是 id 為 1 的這條記錄。這樣其他事務就無法對 id 為 1 的這條記錄進行更新和刪除操作了。

從這里我們也可以得知,加鎖的對象是針對索引,因為這里查詢語句掃描的 B+ 樹是聚簇索引樹,即主鍵索引樹,所以是對主鍵索引加鎖。將對應記錄的主鍵索引加 記錄鎖后,就意味著其他事務無法對該記錄進行更新和刪除操作了。

為什么唯一索引等值查詢并且查詢記錄存在的場景下,該記錄的索引中的 next-key lock 會退化成記錄鎖?

原因就是在唯一索引等值查詢并且查詢記錄存在的場景下,僅靠記錄鎖也能避免幻讀的問題。

幻讀的定義就是,當一個事務前后兩次查詢的結果集,不相同時,就認為發生幻讀。所以,要避免幻讀就是避免結果集某一條記錄被其他事務刪除,或者有其他事務插入了一條新記錄,這樣前后兩次查詢的結果集就不會出現不相同的情況。

由于主鍵具有唯一性,所以其他事務插入 id = 1 的時候,會因為主鍵沖突,導致無法插入 id = 1 的新記錄。這樣事務 A 在多次查詢 id = 1 的記錄的時候,不會出現前后兩次查詢的結果集不同,也就避免了幻讀的問題。

由于對 id = 1 加了記錄鎖,其他事務無法刪除該記錄,這樣事務 A 在多次查詢 id = 1 的記錄的時候,不會出現前后兩次查詢的結果集不同,也就避免了幻讀的問題。

2、記錄不存在的情況

假設事務 A 執行了這條等值查詢語句,查詢的記錄是「不存在」于表中的。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid=2forupdate;
Emptyset(0.03sec)

接下來,通過 select * from performance_schema.data_locksG; 這條語句,查看事務執行 SQL 過程中加了什么鎖。

60d10128-6613-11ed-8abf-dac502259ad0.png

從上圖可以看到,共加了兩個鎖,分別是:

表鎖:X 類型的意向鎖;

行鎖:X 類型的間隙鎖;

因此,此時事務 A 在 id = 5 記錄的主鍵索引上加的是間隙鎖,鎖住的范圍是 (1, 5)。

611e9d48-6613-11ed-8abf-dac502259ad0.png

接下來,如果有其他事務插入 id 值為 2、3、4 這一些記錄的話,這些插入語句都會發生阻塞。

注意,如果其他事務插入的 id = 1 或者 id = 5 的記錄話,并不會發生阻塞,而是報主鍵沖突的錯誤,因為表中已經存在 id = 1 和 id = 5 的記錄了。

比如,下面這個例子:

6139f548-6613-11ed-8abf-dac502259ad0.png

因為事務 A 在 id = 5 記錄的主鍵索引上加了范圍為 (1, 5) 的 X 型間隙鎖,所以事務 B 在插入一條 id 為 3 的記錄時會被阻塞住,即無法插入 id = 3 的記錄。

間隙鎖的范圍(1, 5) ,是怎么確定的?

根據我的經驗,如果 LOCK_MODE 是 next-key 鎖或者間隙鎖,那么 LOCK_DATA 就表示鎖的范圍「右邊界」,此次的事務 A 的 LOCK_DATA 是 5。

然后鎖范圍的「左邊界」是表中 id 為 5 的上一條記錄的 id 值,即 1。

因此,間隙鎖的范圍(1, 5)。

為什么唯一索引等值查詢并且查詢記錄「不存在」的場景下,在索引樹找到第一條大于該查詢記錄的記錄后,要將該記錄的索引中的 next-key lock 會退化成「間隙鎖」?

原因就是在唯一索引等值查詢并且查詢記錄不存在的場景下,僅靠間隙鎖就能避免幻讀的問題。

為什么 id = 5 記錄上的主鍵索引的鎖不可以是 next-key lock?如果是 next-key lock,就意味著其他事務無法刪除 id = 5 這條記錄,但是這次的案例是查詢 id = 2 的記錄,只要保證前后兩次查詢 id = 2 的結果集相同,就能避免幻讀的問題了,所以即使 id =5 被刪除,也不會有什么影響,那就沒必須加 next-key lock,因此只需要在 id = 5 加間隙鎖,避免其他事務插入 id = 2 的新記錄就行了。

為什么不可以針對不存在的記錄加記錄鎖?鎖是加在索引上的,而這個場景下查詢的記錄是不存在的,自然就沒辦法鎖住這條不存在的記錄。

唯一索引范圍查詢

范圍查詢和等值查詢的加鎖規則是不同的。

當唯一索引進行范圍查詢時,會對每一個掃描到的索引加 next-key 鎖,然后如果遇到下面這些情況,會退化成記錄鎖或者間隙鎖

情況一:針對「大于等于」的范圍查詢,因為存在等值查詢的條件,那么如果等值查詢的記錄是存在于表中,那么該記錄的索引中的 next-key 鎖會退化成記錄鎖

情況二:針對「小于或者小于等于」的范圍查詢,要看條件值的記錄是否存在于表中:

當條件值的記錄不在表中,那么不管是「小于」還是「小于等于」條件的范圍查詢,掃描到終止范圍查詢的記錄時,該記錄的索引的 next-key 鎖會退化成間隙鎖,其他掃描到的記錄,都是在這些記錄的索引上加 next-key 鎖。

當條件值的記錄在表中,如果是「小于」條件的范圍查詢,掃描到終止范圍查詢的記錄時,該記錄的索引的 next-key 鎖會退化成間隙鎖,其他掃描到的記錄,都是在這些記錄的索引上加 next-key 鎖;如果「小于等于」條件的范圍查詢,掃描到終止范圍查詢的記錄時,該記錄的索引 next-key 鎖不會退化成間隙鎖。其他掃描到的記錄,都是在這些記錄的索引上加 next-key 鎖。

接下來,通過幾個實驗,才驗證我上面說的結論。

1、針對「大于或者大于等于」的范圍查詢

實驗一:針對「大于」的范圍查詢的情況。

假設事務 A 執行了這條范圍查詢語句:

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid>15forupdate;
+----+-----------+-----+
|id|name|age|
+----+-----------+-----+
|20|香克斯|39|
+----+-----------+-----+
1rowinset(0.01sec)

事務 A 加鎖變化過程如下:

最開始要找的第一行是 id = 20,由于查詢該記錄不是一個等值查詢(不是大于等于條件查詢),所以對該主鍵索引加的是范圍為 (15, 20] 的 next-key 鎖;

由于是范圍查找,就會繼續往后找存在的記錄,雖然我們看見表中最后一條記錄是 id = 20 的記錄,但是實際在 Innodb 存儲引擎中,會用一個特殊的記錄來標識最后一條記錄,該特殊的記錄的名字叫 supremum pseudo-record ,所以掃描第二行的時候,也就掃描到了這個特殊記錄的時候,會對該主鍵索引加的是范圍為 (20, +∞] 的 next-key 鎖。

停止掃描。

可以得知,事務 A 在主鍵索引上加了兩個 X 型 的 next-key 鎖:

615201b0-6613-11ed-8abf-dac502259ad0.png

在 id = 20 這條記錄的主鍵索引上,加了范圍為 (15, 20] 的 next-key 鎖,意味著其他事務即無法更新或者刪除 id = 20 的記錄,同時無法插入 id 值為 16、17、18、19 的這一些新記錄。

在特殊記錄( supremum pseudo-record)的主鍵索引上,加了范圍為 (20, +∞] 的 next-key 鎖,意味著其他事務無法插入 id 值大于 20 的這一些新記錄。

我們也可以通過 select * from performance_schema.data_locksG; 這條語句來看看事務 A 加了什么鎖。

輸出結果如下,我這里只截取了行級鎖的內容。

616f1d0e-6613-11ed-8abf-dac502259ad0.png

從上圖中的分析中,也可以得到事務 A 在主鍵索引上加了兩個 X 型 的next-key 鎖:

在 id = 20 這條記錄的主鍵索引上,加了范圍為 (15, 20] 的 next-key 鎖,意味著其他事務即無法更新或者刪除 id = 20 的記錄,同時無法插入 id 值為 16、17、18、19 的這一些新記錄。

在特殊記錄( supremum pseudo-record)的主鍵索引上,加了范圍為 (20, +∞] 的 next-key 鎖,意味著其他事務無法插入 id 值大于 20 的這一些新記錄。

實驗二:針對「大于等于」的范圍查詢的情況。

假設事務 A 執行了這條范圍查詢語句:

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid>=15forupdate;
+----+-----------+-----+
|id|name|age|
+----+-----------+-----+
|15|烏索普|20|
|20|香克斯|39|
+----+-----------+-----+
2rowsinset(0.00sec)

事務 A 加鎖變化過程如下:

最開始要找的第一行是 id = 15,由于查詢該記錄是一個等值查詢(等于 15),所以該主鍵索引的 next-key 鎖會退化成記錄鎖,也就是僅鎖住 id = 15 這一行記錄。

由于是范圍查找,就會繼續往后找存在的記錄,掃描到的第二行是 id = 20,于是對該主鍵索引加的是范圍為 (15, 20] 的 next-key 鎖;

接著掃描到第三行的時候,掃描到了特殊記錄( supremum pseudo-record),于是對該主鍵索引加的是范圍為 (20, +∞] 的 next-key 鎖。

停止掃描。

可以得知,事務 A 在主鍵索引上加了三個 X 型 的鎖,分別是:

61a27744-6613-11ed-8abf-dac502259ad0.png

在 id = 15 這條記錄的主鍵索引上,加了記錄鎖,范圍是 id = 15 這一行記錄;意味著其他事務無法更新或者刪除 id = 15 的這一條記錄;

在 id = 20 這條記錄的主鍵索引上,加了 next-key 鎖,范圍是 (15, 20] 。意味著其他事務即無法更新或者刪除 id = 20 的記錄,同時無法插入 id 值為 16、17、18、19 的這一些新記錄。

在特殊記錄( supremum pseudo-record)的主鍵索引上,加了 next-key 鎖,范圍是 (20, +∞] 。意味著其他事務無法插入 id 值大于 20 的這一些新記錄。

我們也可以通過 select * from performance_schema.data_locksG; 這條語句來看看事務 A 加了什么鎖。

輸出結果如下,我這里只截取了行級鎖的內容。

61bfbe58-6613-11ed-8abf-dac502259ad0.png

通過前面這個實驗,我們證明了:

針對「大于等于」條件的唯一索引范圍查詢的情況下, 如果條件值的記錄存在于表中,那么由于查詢該條件值的記錄是包含一個等值查詢的操作,所以該記錄的索引中的 next-key 鎖會退化成記錄鎖

2、針對「小于或者小于等于」的范圍查詢

實驗一:針對「小于」的范圍查詢時,查詢條件值的記錄「不存在」表中的情況。

假設事務 A 執行了這條范圍查詢語句,注意查詢條件值的記錄(id 為 6)并不存在于表中。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid

事務 A 加鎖變化過程如下:

最開始要找的第一行是 id = 1,于是對該主鍵索引加的是范圍為 (-∞, 1] 的 next-key 鎖;

由于是范圍查找,就會繼續往后找存在的記錄,掃描到的第二行是 id = 5,所以對該主鍵索引加的是范圍為 (1, 5] 的 next-key 鎖;

由于掃描到的第二行記錄(id = 5),滿足 id < 6 條件,而且也沒有達到終止掃描的條件,接著會繼續掃描。

掃描到的第三行是 id = 10,該記錄不滿足 id < 6 條件的記錄,所以 id = 10 這一行記錄的鎖會退化成間隙鎖,于是對該主鍵索引加的是范圍為 (5, 10) 的間隙鎖。

由于掃描到的第三行記錄(id = 10),不滿足 id < 6 條件,達到了終止掃描的條件,于是停止掃描。

從上面的分析中,可以得知事務 A 在主鍵索引上加了三個 X 型的鎖:

62027efa-6613-11ed-8abf-dac502259ad0.png

在 id = 1 這條記錄的主鍵索引上,加了范圍為 (-∞, 1] 的 next-key 鎖,意味著其他事務即無法更新或者刪除 id = 1 的這一條記錄,同時也無法插入 id 小于 1 的這一些新記錄。

在 id = 5 這條記錄的主鍵索引上,加了范圍為 (1, 5] 的 next-key 鎖,意味著其他事務即無法更新或者刪除 id = 5 的這一條記錄,同時也無法插入 id 值為 2、3、4 的這一些新記錄。

在 id = 10 這條記錄的主鍵索引上,加了范圍為 (5, 10) 的間隙鎖,意味著其他事務無法插入 id 值為 6、7、8、9 的這一些新記錄。

我們也可以通過 select * from performance_schema.data_locksG; 這條語句來看看事務 A 加了什么鎖。

輸出結果如下,我這里只截取了行級鎖的內容。

62209cbe-6613-11ed-8abf-dac502259ad0.png

從上圖中的分析中,也可以得知事務 A 在主鍵索引加的三個鎖,就是我們前面分析出那三個鎖。

雖然這次范圍查詢的條件是「小于」,但是查詢條件值的記錄不存在于表中( id 為 6 的記錄不在表中),所以如果事務 A 的范圍查詢的條件改成 <= 6 的話,加的鎖還是和范圍查詢條件為 < 6 是一樣的。大家自己也驗證下這個結論。

因此,針對「小于或者小于等于」的唯一索引范圍查詢,如果條件值的記錄不在表中,那么不管是「小于」還是「小于等于」的范圍查詢,掃描到終止范圍查詢的記錄時,該記錄中索引的 next-key 鎖會退化成間隙鎖,其他掃描的記錄,則是在這些記錄的索引上加 next-key 鎖。

實驗二:針對「小于等于」的范圍查詢時,查詢條件值的記錄「存在」表中的情況。

假設事務 A 執行了這條范圍查詢語句,注意查詢條件值的記錄(id 為 5)存在于表中。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereid<=?5?for?update;
+----+--------+-----+
|?id?|?name???|?age?|
+----+--------+-----+
|??1?|?路飛???|??19?|
|??5?|?索隆???|??21?|
+----+--------+-----+
2?rows?in?set?(0.00?sec)

事務 A 加鎖變化過程如下:

最開始要找的第一行是 id = 1,于是對該記錄加的是范圍為 (-∞, 1] 的 next-key 鎖;

由于是范圍查找,就會繼續往后找存在的記錄,掃描到的第二行是 id = 5,于是對該記錄加的是范圍為 (1, 5] 的 next-key 鎖。

由于主鍵索引具有唯一性,不會存在兩個 id = 5 的記錄,所以不會再繼續掃描,于是停止掃描。

從上面的分析中,可以得到事務 A 在主鍵索引上加了 2 個 X 型的鎖

62a47994-6613-11ed-8abf-dac502259ad0.png

在 id = 1 這條記錄的主鍵索引上,加了范圍為 (-∞, 1] 的 next-key 鎖。意味著其他事務即無法更新或者刪除 id = 1 的這一條記錄,同時也無法插入 id 小于 1 的這一些新記錄。

在 id = 5 這條記錄的主鍵索引上,加了范圍為 (1, 5] 的 next-key 鎖。意味著其他事務即無法更新或者刪除 id = 5 的這一條記錄,同時也無法插入 id 值為 2、3、4 的這一些新記錄。

我們也可以通過 select * from performance_schema.data_locksG; 這條語句來看看事務 A 加了什么鎖。

輸出結果如下,我這里只截取了行級鎖的內容。

62c05b3c-6613-11ed-8abf-dac502259ad0.png

從上圖中的分析中,可以得到事務 A 在主鍵索引上加了兩個 X 型 next-key 鎖,分別是:

在 id = 1 這條記錄的主鍵索引上,加了范圍為 (-∞, 1] 的 next-key 鎖;

在 id = 5 這條記錄的主鍵索引上,加了范圍為(1, 5 ] 的 next-key 鎖。

實驗三:再來看針對「小于」的范圍查詢時,查詢條件值的記錄「存在」表中的情況。

如果事務 A 的查詢語句是小于的范圍查詢,且查詢條件值的記錄(id 為 5)存在于表中。

select*fromuserwhereid

事務 A 加鎖變化過程如下:

最開始要找的第一行是 id = 1,于是對該記錄加的是范圍為 (-∞, 1] 的 next-key 鎖;

由于是范圍查找,就會繼續往后找存在的記錄,掃描到的第二行是 id = 5,該記錄是第一條不滿足 id < 5 條件的記錄,于是**該記錄的鎖會退化為間隙鎖,鎖范圍是 (1,5)**。

由于找到了第一條不滿足 id < 5 條件的記錄,于是停止掃描。

可以得知,此時事務 A 在主鍵索引上加了兩種 X 型鎖:

62f511f6-6613-11ed-8abf-dac502259ad0.png

在 id = 1 這條記錄的主鍵索引上,加了范圍為 (-∞, 1] 的 next-key 鎖,意味著其他事務即無法更新或者刪除 id = 1 的這一條記錄,同時也無法插入 id 小于 1 的這一些新記錄。

在 id = 5 這條記錄的主鍵索引上,加了范圍為 (1,5) 的間隙鎖,意味著其他事務無法插入 id 值為 2、3、4 的這一些新記錄。

我們也可以通過 select * from performance_schema.data_locksG; 這條語句來看看事務 A 加了什么鎖。

輸出結果如下,我這里只截取了行級鎖的內容。

631213be-6613-11ed-8abf-dac502259ad0.png

從上圖中的分析中,可以得到事務 A 在主鍵索引上加了 X 型的范圍為 (-∞, 1] 的 next-key 鎖,和 X 型的范圍為 (1, 5) 的間隙鎖。

因此,通過前面這三個實驗,可以得知。

在針對「小于或者小于等于」的唯一索引(主鍵索引)范圍查詢時,存在這兩種情況會將索引的 next-key 鎖會退化成間隙鎖的:

當條件值的記錄「不在」表中時,那么不管是「小于」還是「小于等于」條件的范圍查詢,掃描到終止范圍查詢的記錄時,該記錄的主鍵索引中的 next-key 鎖會退化成間隙鎖,其他掃描到的記錄,都是在這些記錄的主鍵索引上加 next-key 鎖。

當條件值的記錄「在」表中時:

如果是「小于」條件的范圍查詢,掃描到終止范圍查詢的記錄時,該記錄的主鍵索引中的 next-key 鎖會退化成間隙鎖,其他掃描到的記錄,都是在這些記錄的主鍵索引上,加 next-key 鎖。

如果是「小于等于」條件的范圍查詢,掃描到終止范圍查詢的記錄時,該記錄的主鍵索引中的 next-key 鎖「不會」退化成間隙鎖,其他掃描到的記錄,都是在這些記錄的主鍵索引上加 next-key 鎖。

非唯一索引等值查詢

當我們用非唯一索引進行等值查詢的時候,因為存在兩個索引,一個是主鍵索引,一個是非唯一索引(二級索引),所以在加鎖時,同時會對這兩個索引都加鎖,但是對主鍵索引加鎖的時候,只有滿足查詢條件的記錄才會對它們的主鍵索引加鎖

針對非唯一索引等值查詢時,查詢的記錄存不存在,加鎖的規則也會不同:

當查詢的記錄「存在」時,由于不是唯一索引,所以肯定存在索引值相同的記錄,于是非唯一索引等值查詢的過程是一個掃描的過程,直到掃描到第一個不符合條件的二級索引記錄就停止掃描,然后在掃描的過程中,對掃描到的二級索引記錄加的是 next-key 鎖,而對于第一個不符合條件的二級索引記錄,該二級索引的 next-key 鎖會退化成間隙鎖。同時,在符合查詢條件的記錄的主鍵索引上加記錄鎖。

當查詢的記錄「不存在」時,掃描到第一條不符合條件的二級索引記錄,該二級索引的 next-key 鎖會退化成間隙鎖。因為不存在滿足查詢條件的記錄,所以不會對主鍵索引加鎖

接下里用兩個實驗來說明。

1、記錄不存在的情況

實驗一:針對非唯一索引等值查詢時,查詢的值不存在的情況。

先來說說非唯一索引等值查詢時,查詢的記錄不存在的情況,因為這個比較簡單。

假設事務 A 對非唯一索引(age)進行了等值查詢,且表中不存在 age = 25 的記錄。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereage=25forupdate;
Emptyset(0.00sec)

事務 A 加鎖變化過程如下:

定位到第一條不符合查詢條件的二級索引記錄,即掃描到 age = 39,于是**該二級索引的 next-key 鎖會退化成間隙鎖,范圍是 (22, 39)**。

停止查詢

事務 A 在 age = 39 記錄的二級索引上,加了 X 型的間隙鎖,范圍是 (22, 39)。意味著其他事務無法插入 age 值為 23、24、25、26、....、38 這些新記錄。不過對于插入 age = 22 和 age = 39 記錄的語句,在一些情況是可以成功插入的,而一些情況則無法成功插入,具體哪些情況,會在后面說。

6337ac0a-6613-11ed-8abf-dac502259ad0.png

我們也可以通過 select * from performance_schema.data_locksG; 這條語句來看看事務 A 加了什么鎖。

輸出結果如下,我這里只截取了行級鎖的內容。

635d1f4e-6613-11ed-8abf-dac502259ad0.png

從上圖的分析,可以看到,事務 A 在 age = 39 記錄的二級索引上(INDEX_NAME: index_age ),加了范圍為 (22, 39) 的 X 型間隙鎖。

此時,如果有其他事務插入了 age 值為 23、24、25、26、....、38 這些新記錄,那么這些插入語句都會發生阻塞。不過對于插入 age = 39 記錄的語句,在一些情況是可以成功插入的,而一些情況則無法成功插入,具體哪些情況,接下來我們就說!

當有一個事務持有二級索引的間隙鎖 (22, 39) 時,什么情況下,可以讓其他事務的插入 age = 22 或者 age = 39 記錄的語句成功?又是什么情況下,插入 age = 22 或者 age = 39 記錄時的語句會被阻塞?

我們先要清楚,什么情況下插入語句會發生阻塞。

插入語句在插入一條記錄之前,需要先定位到該記錄在 B+樹 的位置,如果插入的位置的下一條記錄的索引上有間隙鎖,才會發生阻塞。

在分析二級索引的間隙鎖是否可以成功插入記錄時,我們要先要知道二級索引樹是如何存放記錄的?

二級索引樹是按照二級索引值(age列)按順序存放的,在相同的二級索引值情況下, 再按主鍵 id 的順序存放。知道了這個前提,我們才能知道執行插入語句的時候,插入的位置的下一條記錄是誰。

基于前面的實驗,事務 A 是在 age = 39 記錄的二級索引上,加了 X 型的間隙鎖,范圍是 (22, 39)。

插入 age = 22 記錄的成功和失敗的情況分別如下:

當其他事務插入一條 age = 22,id = 3 的記錄的時候,在二級索引樹上定位到插入的位置,而該位置的下一條是 id = 10、age = 22 的記錄,該記錄的二級索引上沒有間隙鎖,所以這條插入語句可以執行成功

當其他事務插入一條 age = 22,id = 12 的記錄的時候,在二級索引樹上定位到插入的位置,而該位置的下一條是 id = 20、age = 39 的記錄,正好該記錄的二級索引上有間隙鎖,所以這條插入語句會被阻塞,無法插入成功。

插入 age = 39 記錄的成功和失敗的情況分別如下:

當其他事務插入一條 age = 39,id = 3 的記錄的時候,在二級索引樹上定位到插入的位置,而該位置的下一條是 id = 20、age = 39 的記錄,正好該記錄的二級索引上有間隙鎖,所以這條插入語句會被阻塞,無法插入成功。

當其他事務插入一條 age = 39,id = 21 的記錄的時候,在二級索引樹上定位到插入的位置,而該位置的下一條記錄不存在,也就沒有間隙鎖了,所以這條插入語句可以插入成功。

所以,當有一個事務持有二級索引的間隙鎖 (22, 39) 時,插入 age = 22 或者 age = 39 記錄的語句是否可以執行成功,關鍵還要考慮插入記錄的主鍵值,因為「二級索引值(age列)+主鍵值(id列)」才可以確定插入的位置,確定了插入位置后,就要看插入的位置的下一條記錄是否有間隙鎖,如果有間隙鎖,就會發生阻塞,如果沒有間隙鎖,則可以插入成功。

知道了這個結論之后,我們再回過頭看,非唯一索引等值查詢時,查詢的記錄不存在時,執行select * from performance_schema.data_locksG; 輸出的結果。

637e4728-6613-11ed-8abf-dac502259ad0.png

在前面分析輸出結果的時候,我說的結論是:「事務 A 在 age = 39 記錄的二級索引上(INDEX_NAME: index_age ),加了范圍為 (22, 39) 的 X 型間隙鎖」。這個結論其實還不夠準確,因為只考慮了 LOCK_DATA 第一個數值(39),沒有考慮 LOCK_DATA 第二個數值(20)。

那 LOCK_DATA:39,20 是什么意思?

LOCK_DATA 第一個數值,也就是 39, 它代表的是 age 值。從前面我們也知道了,LOCK_DATA 第一個數值是 next-key 鎖和間隙鎖鎖住的范圍的右邊界值。

LOCK_DATA 第二個數值,也就是 20, 它代表的是 id 值。

之所以 LOCK_DATA 要多顯示一個數值(ID值),是因為針對「當某個事務持有非唯一索引的 (22, 39) 間隙鎖的時候,其他事務是否可以插入 age = 39 新記錄」的問題,還需要考慮插入記錄的 id 值。而 LOCK_DATA 的第二個數值,就是說明在插入 age = 39 新記錄時,哪些范圍的 id 值是不可以插入的。

因此, LOCK_DATA:39,20 + LOCK_MODE : X, GAP 的意思是,事務 A 在 age = 39 記錄的二級索引上(INDEX_NAME: index_age ),加了 age 值范圍為 (22, 39) 的 X 型間隙鎖,**同時針對其他事務插入 age 值為 39 的新記錄時,不允許插入的新記錄的 id 值小于 20 **。如果插入的新記錄的 id 值大于 20,則可以插入成功。

但是我們無法從select * from performance_schema.data_locksG; 輸出的結果分析出「在插入 age =22 新記錄時,哪些范圍的 id 值是可以插入成功的」,這時候就得自己畫出二級索引的 B+ 樹的結構,然后確定插入位置后,看下該位置的下一條記錄是否存在間隙鎖,如果存在間隙鎖,則無法插入成功,如果不存在間隙鎖,則可以插入成功。

2、記錄存在的情況

實驗二:針對非唯一索引等值查詢時,查詢的值存在的情況。

假設事務 A 對非唯一索引(age)進行了等值查詢,且表中存在 age = 22 的記錄。

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereage=22forupdate;
+----+--------+-----+
|id|name|age|
+----+--------+-----+
|10|山治|22|
+----+--------+-----+
1rowinset(0.00sec)

事務 A 加鎖變化過程如下:

由于不是唯一索引,所以肯定存在值相同的記錄,于是非唯一索引等值查詢的過程是一個掃描的過程,最開始要找的第一行是 age = 22,于是對該二級索引記錄加上范圍為 (21, 22] 的 next-key 鎖。同時,因為 age = 22 符合查詢條件,于是對 age = 22 的記錄的主鍵索引加上記錄鎖,即對 id = 10 這一行加記錄鎖。

接著繼續掃描,掃描到的第二行是 age = 39,該記錄是第一個不符合條件的二級索引記錄,所以該二級索引的 next-key 鎖會退化成間隙鎖,范圍是 (22, 39)。

停止查詢。

可以看到,事務 A 對主鍵索引和二級索引都加了 X 型的鎖:

63a27f6c-6613-11ed-8abf-dac502259ad0.png

主鍵索引:

在 id = 10 這條記錄的主鍵索引上,加了記錄鎖,意味著其他事務無法更新或者刪除 id = 10 的這一行記錄。

二級索引(非唯一索引):

在 age = 22 這條記錄的二級索引上,加了范圍為 (21, 22] 的 next-key 鎖,意味著其他事務無法更新或者刪除 age = 22 的這一些新記錄,不過對于插入 age = 20 和 age = 21 新記錄的語句,在一些情況是可以成功插入的,而一些情況則無法成功插入,具體哪些情況,會在后面說。

在 age = 39 這條記錄的二級索引上,加了范圍 (22, 39) 的間隙鎖。意味著其他事務無法插入 age 值為 23、24、..... 、38 的這一些新記錄。不過對于插入 age = 22 和 age = 39 記錄的語句,在一些情況是可以成功插入的,而一些情況則無法成功插入,具體哪些情況,會在后面說。

我們也可以通過 select * from performance_schema.data_locksG; 這條語句來看看事務 A 加了什么鎖。

輸出結果如下,我這里只截取了行級鎖的內容。

63c80b42-6613-11ed-8abf-dac502259ad0.png

從上圖的分析,可以看到,事務 A 對二級索引(INDEX_NAME: index_age )加了兩個 X 型鎖,分別是:

在 age = 22 這條記錄的二級索引上,加了范圍為 (21, 22] 的 next-key 鎖,意味著其他事務無法更新或者刪除 age = 22 的這一些新記錄,針對是否可以插入 age = 21 和 age = 22 的新記錄,分析如下:

是否可以插入 age = 21 的新記錄,還要看插入的新記錄的 id 值,如果插入 age = 21 新記錄的 id 值小于 5,那么就可以插入成功,因為此時插入的位置的下一條記錄是 id = 5,age = 21 的記錄,該記錄的二級索引上沒有間隙鎖。如果插入 age = 21 新記錄的 id 值大于 5,那么就無法插入成功,因為此時插入的位置的下一條記錄是 id = 20,age = 39 的記錄,該記錄的二級索引上有間隙鎖。

是否可以插入 age = 22 的新記錄,還要看插入的新記錄的 id 值,從 LOCK_DATA : 22, 10 可以得知,其他事務插入 age 值為 22 的新記錄時,如果插入的新記錄的 id 值小于 10,那么插入語句會發生阻塞;如果插入的新記錄的 id 大于 10,還要看該新記錄插入的位置的下一條記錄是否有間隙鎖,如果沒有間隙鎖則可以插入成功,如果有間隙鎖,則無法插入成功。

在 age = 39 這條記錄的二級索引上,加了范圍 (22, 39) 的間隙鎖。意味著其他事務無法插入 age 值為 23、24、..... 、38 的這一些新記錄,針對是否可以插入 age = 22 和 age = 39 的新記錄,分析如下:

是否可以插入 age = 22 的新記錄,還要看插入的新記錄的 id 值,如果插入 age = 22 新記錄的 id 值小于 10,那么插入語句會被阻塞,無法插入,因為此時插入的位置的下一條記錄是 id = 10,age = 22 的記錄,該記錄的二級索引上有間隙鎖( age = 22 這條記錄的二級索引上有 next-key 鎖)。如果插入 age = 21 新記錄的 id 值大于 10,也無法插入,因為此時插入的位置的下一條記錄是 id = 20,age = 39 的記錄,該記錄的二級索引上有間隙鎖。

是否可以插入 age = 39 的新記錄,還要看插入的新記錄的 id 值,從 LOCK_DATA : 39, 20 可以得知,其他事務插入 age 值為 39 的新記錄時,如果插入的新記錄的 id 值小于 20,那么插入語句會發生阻塞,如果插入的新記錄的 id 大于 20,則可以插入成功

同時,事務 A 還對主鍵索引(INDEX_NAME: PRIMARY )加了X 型的記錄鎖:

在 id = 10 這條記錄的主鍵索引上,加了記錄鎖,意味著其他事務無法更新或者刪除 id = 10 的這一行記錄。

為什么這個實驗案例中,需要在二級索引索引上加范圍 (22, 39) 的間隙鎖?

要找到這個問題的答案,我們要明白 MySQL 在可重復讀的隔離級別場景下,為什么要引入間隙鎖?其實是為了避免幻讀現象的發生。

如果這個實驗案例中:

select*fromuserwhereage=22forupdate;

如果事務 A 不在二級索引索引上加范圍 (22, 39) 的間隙鎖,只在二級索引索引上加范圍為 (21, 22] 的 next-key 鎖的話,那么就會有幻讀的問題。

前面我也說過,在非唯一索引上加了范圍為 (21, 22] 的 next-key 鎖,是無法完全鎖住 age = 22 新記錄的插入,因為對于是否可以插入 age = 22 的新記錄,還要看插入的新記錄的 id 值,從 LOCK_DATA : 22, 10 可以得知,其他事務插入 age 值為 22 的新記錄時,如果插入的新記錄的 id 值小于 10,那么插入語句會發生阻塞,如果插入的新記錄的 id 值大于 10,則可以插入成功。

也就是說,只在二級索引索引(非唯一索引)上加范圍為 (21, 22] 的 next-key 鎖,其他事務是有可能插入 age 值為 22 的新記錄的(比如插入一個 age = 22,id = 12 的新記錄),那么如果事務 A 再一次查詢 age = 22 的記錄的時候,前后兩次查詢 age = 22 的結果集就不一樣了,這時就發生了幻讀的現象。

那么當在 age = 39 這條記錄的二級索引索引上加了范圍為 (22, 39) 的間隙鎖后,其他事務是無法插入一個 age = 22,id = 12 的新記錄,因為當其他事務插入一條 age = 22,id = 12 的新記錄的時候,在二級索引樹上定位到插入的位置,而該位置的下一條是 id = 20、age = 39 的記錄,正好該記錄的二級索引上有間隙鎖,所以這條插入語句會被阻塞,無法插入成功,這樣就避免幻讀現象的發生。

所以,為了避免幻讀現象的發生,就需要在二級索引索引上加范圍 (22, 39) 的間隙鎖。

非唯一索引范圍查詢

非唯一索引和主鍵索引的范圍查詢的加鎖也有所不同,不同之處在于非唯一索引范圍查詢,索引的 next-key lock 不會有退化為間隙鎖和記錄鎖的情況,也就是非唯一索引進行范圍查詢時,對二級索引記錄加鎖都是加 next-key 鎖。

就帶大家簡單分析一下,事務 A 的這條范圍查詢語句:

mysql>begin;
QueryOK,0rowsaffected(0.00sec)

mysql>select*fromuserwhereage>=22forupdate;
+----+-----------+-----+
|id|name|age|
+----+-----------+-----+
|10|山治|22|
|20|香克斯|39|
+----+-----------+-----+
2rowsinset(0.01sec)

事務 A 的加鎖變化:

最開始要找的第一行是 age = 22,雖然范圍查詢語句包含等值查詢,但是這里不是唯一索引范圍查詢,所以是不會發生退化鎖的現象,因此對該二級索引記錄加 next-key 鎖,范圍是 (21, 22]。同時,對 age = 22 這條記錄的主鍵索引加記錄鎖,即對 id = 10 這一行記錄的主鍵索引加記錄鎖。

由于是范圍查詢,接著繼續掃描已經存在的二級索引記錄。掃面的第二行是 age = 39 的二級索引記錄,于是對該二級索引記錄加 next-key 鎖,范圍是 (22, 39],同時,對 age = 39 這條記錄的主鍵索引加記錄鎖,即對 id = 20 這一行記錄的主鍵索引加記錄鎖。

雖然我們看見表中最后一條二級索引記錄是 age = 39 的記錄,但是實際在 Innodb 存儲引擎中,會用一個特殊的記錄來標識最后一條記錄,該特殊的記錄的名字叫 supremum pseudo-record ,所以掃描第二行的時候,也就掃描到了這個特殊記錄的時候,會對該二級索引記錄加的是范圍為 (39, +∞] 的 next-key 鎖。

停止查詢

可以看到,事務 A 對主鍵索引和二級索引都加了 X 型的鎖:

643ea90a-6613-11ed-8abf-dac502259ad0.png

主鍵索引(id 列):

在 id = 10 這條記錄的主鍵索引上,加了記錄鎖,意味著其他事務無法更新或者刪除 id = 10 的這一行記錄。

在 id = 20 這條記錄的主鍵索引上,加了記錄鎖,意味著其他事務無法更新或者刪除 id = 20 的這一行記錄。

二級索引(age 列):

在 age = 22 這條記錄的二級索引上,加了范圍為 (21, 22] 的 next-key 鎖,意味著其他事務無法更新或者刪除 age = 22 的這一些新記錄,不過對于是否可以插入 age = 21 和 age = 22 的新記錄,還需要看新記錄的 id 值,有些情況是可以成功插入的,而一些情況則無法插入,具體哪些情況,我們前面也講了。

在 age = 39 這條記錄的二級索引上,加了范圍為 (22, 39] 的 next-key 鎖,意味著其他事務無法更新或者刪除 age = 39 的這一些記錄,也無法插入 age 值為 23、24、25、...、38 的這一些新記錄。不過對于是否可以插入 age = 22 和 age = 39 的新記錄,還需要看新記錄的 id 值,有些情況是可以成功插入的,而一些情況則無法插入,具體哪些情況,我們前面也講了。

在特殊的記錄(supremum pseudo-record)的二級索引上,加了范圍為 (39, +∞] 的 next-key 鎖,意味著其他事務無法插入 age 值大于 39 的這些新記錄。

在 age >= 22 的范圍查詢中,明明查詢 age = 22 的記錄存在并且屬于等值查詢,為什么不會像唯一索引那樣,將 age = 22 記錄的二級索引上的 next-key 鎖退化為記錄鎖?

因為 age 字段是非唯一索引,不具有唯一性,所以如果只加記錄鎖(記錄鎖無法防止插入,只能防止刪除或者修改),就會導致其他事務插入一條 age = 22 的記錄,這樣前后兩次查詢的結果集就不相同了,出現了幻讀現象。

沒有加索引的查詢

前面的案例,我們的查詢語句都有使用索引查詢,也就是查詢記錄的時候,是通過索引掃描的方式查詢的,然后對掃描出來的記錄進行加鎖。

如果鎖定讀查詢語句,沒有使用索引列作為查詢條件,或者查詢語句沒有走索引查詢,導致掃描是全表掃描。那么,每一條記錄的索引上都會加 next-key 鎖,這樣就相當于鎖住的全表,這時如果其他事務對該表進行增、刪、改操作的時候,都會被阻塞

不只是鎖定讀查詢語句不加索引才會導致這種情況,update 和 delete 語句如果查詢條件不加索引,那么由于掃描的方式是全表掃描,于是就會對每一條記錄的索引上都會加 next-key 鎖,這樣就相當于鎖住的全表。

因此,在線上在執行 update、delete、select ... for update 等具有加鎖性質的語句,一定要檢查語句是否走了索引,如果是全表掃描的話,會對每一個索引加 next-key 鎖,相當于把整個表鎖住了,這是挺嚴重的問題。

總結

這次我以 MySQL 8.0.26 版本,在可重復讀隔離級別之下,做了幾個實驗,讓大家了解了唯一索引和非唯一索引的行級鎖的加鎖規則。

我這里總結下, MySQL 行級鎖的加鎖規則。

唯一索引等值查詢:

當查詢的記錄是「存在」的,在索引樹上定位到這一條記錄后,將該記錄的索引中的 next-key lock 會退化成「記錄鎖」

當查詢的記錄是「不存在」的,在索引樹找到第一條大于該查詢記錄的記錄后,將該記錄的索引中的 next-key lock 會退化成「間隙鎖」。

非唯一索引等值查詢:

當查詢的記錄「存在」時,由于不是唯一索引,所以肯定存在索引值相同的記錄,于是非唯一索引等值查詢的過程是一個掃描的過程,直到掃描到第一個不符合條件的二級索引記錄就停止掃描,然后在掃描的過程中,對掃描到的二級索引記錄加的是 next-key 鎖,而對于第一個不符合條件的二級索引記錄,該二級索引的 next-key 鎖會退化成間隙鎖。同時,在符合查詢條件的記錄的主鍵索引上加記錄鎖。

當查詢的記錄「不存在」時,掃描到第一條不符合條件的二級索引記錄,該二級索引的 next-key 鎖會退化成間隙鎖。因為不存在滿足查詢條件的記錄,所以不會對主鍵索引加鎖。

非唯一索引和主鍵索引的范圍查詢的加鎖規則不同之處在于:

唯一索引在滿足一些條件的時候,索引的 next-key lock 退化為間隙鎖或者記錄鎖。

非唯一索引范圍查詢,索引的 next-key lock 不會退化為間隙鎖和記錄鎖。

其實理解 MySQL 為什么要這樣加鎖,主要要以避免幻讀角度去分析,這樣就很容易理解這些加鎖的規則了。

還有一件很重要的事情,在線上在執行 update、delete、select ... for update 等具有加鎖性質的語句,一定要檢查語句是否走了索引,如果是全表掃描的話,會對每一個索引加 next-key 鎖,相當于把整個表鎖住了,這是挺嚴重的問題。







審核編輯:劉清

聲明:本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人,不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用,如有內容侵權或者其他違規問題,請聯系本站處理。 舉報投訴
  • MySQL
    +關注

    關注

    1

    文章

    804

    瀏覽量

    26531
  • MVCC
    +關注

    關注

    0

    文章

    13

    瀏覽量

    1465

原文標題:保姆級教程!2 萬字 + 30 張圖搞懂 MySQL 是怎么加行級鎖的?

文章出處:【微信號:小林coding,微信公眾號:小林coding】歡迎添加關注!文章轉載請注明出處。

收藏 人收藏

    評論

    相關推薦

    基于MySQL機制

    在數據庫系統中,為了保證數據的一致性和并發控制,機制發揮著至關重要的作用。尤其在關系型數據庫MySQL中,其獨特的機制設計更是贏得了許多開發者的喜愛。 本文將詳細探討MySQL
    的頭像 發表于 09-30 11:16 ?876次閱讀

    MySQL數據庫進階篇-71. 進階--行-介紹_ne

    MySQL
    電子學習
    發布于 :2023年01月07日 16:07:23

    labview調用mysql數據庫問題????

    labview調用mysql數據庫,請問labview打包成exe安裝檔,怎么把mysql數據庫打包進來,是mysql數據庫,不是其他的?。壳蟾呤?/div>
    發表于 05-19 16:17

    MySQL表分區類型及介紹

    表分區是將一個表的數據按照一定規則水平劃分成不同的邏輯塊,并分別進行物理存儲,這個規則就叫做分區函數,可以不同的分區規則。通過show plugins語句查看當前
    發表于 06-29 16:31

    鋪銅和Clearance規則的優先

    新來論壇,原創求支持~我也算是新手,一切還在摸索中。。。AD版本19.0.6,我只找到各個規則大類下調整優先,比如Clearance下不同規則可以調優先,但Clearance和其他
    發表于 02-12 22:26

    MySQL存儲引擎簡析

    MySQL存儲引擎InnoDB??InnoDB 的存儲文件兩個,后綴名分別是.frm和.idb,其中.frm是表的定義文件,而.idb是數據文件。InnoDB 中存在表和行,不過
    發表于 09-06 06:07

    安裝MySql鏡像的步驟

    安裝MySql鏡像> docker search mysql #查找MySql鏡像版本> docker pull mysql:tag #安裝指定版本的
    發表于 10-19 14:55

    》/《無》/《簽約》/《解鎖》/《越獄》/《激活》專

    》/《無》/《簽約》/《解鎖》/《越獄》/《激活》專業技術詞解析 在討論區里,大家看到:《版》,《無
    發表于 02-03 11:05 ?954次閱讀

    最有用的mysql問答

    、壓縮、空間函數等,但是不支持事務和行,所以一般用于大量查詢少量插入的場景來使用,而且myisam不支持外鍵,并且索引和數據是分開存儲的。 innodb是基于聚簇索引建立的,和myisam相反它支持事務、外鍵,并且通過MV
    的頭像 發表于 09-30 17:43 ?1694次閱讀
    最有用的<b class='flag-5'>mysql</b>問答

    MySQL中的高級內容詳解

    MySQL 進階?。。?本文思維導圖如下。 事務控制和鎖定語句 我們知道,MyISAM 和 MEMORY 存儲引擎支持表鎖定(table-level locking),InnoDB 存儲引擎支持行鎖定
    的頭像 發表于 03-11 16:55 ?2214次閱讀
    <b class='flag-5'>MySQL</b>中的高級內容詳解

    說說MySQL哪些

    增加自增為 innodb_autoinc_lock_mode = 2 模式時,為什么主從環境會有不安全問題的說明
    的頭像 發表于 10-24 10:15 ?867次閱讀

    一文徹底搞懂MySQL究竟的啥1

    MySQL系列文章已經鴿了挺久了,最近趕緊擠了擠時間,和大家聊一聊MySQL。 只要學計算機,「``」永遠是一個繞不過的話題。
    的頭像 發表于 03-03 10:12 ?464次閱讀
    一文徹底搞懂<b class='flag-5'>MySQL</b><b class='flag-5'>鎖</b>究竟<b class='flag-5'>鎖</b>的啥1

    一文徹底搞懂MySQL究竟的啥2

    MySQL系列文章已經鴿了挺久了,最近趕緊擠了擠時間,和大家聊一聊MySQL。 只要學計算機,「``」永遠是一個繞不過的話題。
    的頭像 發表于 03-03 10:13 ?431次閱讀
    一文徹底搞懂<b class='flag-5'>MySQL</b><b class='flag-5'>鎖</b>究竟<b class='flag-5'>鎖</b>的啥2

    讀寫的實現原理規則

    )和不加鎖狀態(見),一次只有一個線程可以占有寫模式的讀寫,但是可以多個線程同時占有讀模式的讀寫。因此可知,讀寫比互斥鎖具有更高的并行性! 讀寫
    的頭像 發表于 07-21 11:21 ?907次閱讀
    讀寫<b class='flag-5'>鎖</b>的實現原理<b class='flag-5'>規則</b>

    阿里二面:了解MySQL事務底層原理嗎

    MySQL 是如何來解決臟寫這種問題的?沒錯,就是MySQL 在開啟一個事務的時候,他會將某條記錄和事務做一個綁定。這個其實和 JVM 是類似的。
    的頭像 發表于 01-18 16:34 ?332次閱讀
    阿里二面:了解<b class='flag-5'>MySQL</b>事務底層原理嗎
    主站蜘蛛池模板: 超碰caoporen国产| 国产精品久久久久成人免费| 四虎永久精品视频在线| 亚洲高清国产拍精品影院| 亚洲成人综合在线| 7723日本高清完整版在线观看| 岳打开双腿开始配合日韩视频| 伊人yinren6综合网色狠狠| A级毛片高清免费网站不卡| 被公疯狂玩弄的漂亮人妻| 第一次处破女完整版电影| 黄色三级视频在线观看| 久久国语精品| 色大姐综合网| 性XXXXX搡XXXXX搡景甜| 18动漫在线观看| 澳大利亚剧满足在线观看| 国内精品免费视频精选在线观看| 男男高h浪荡受h| 亚洲精品久久久久久蜜臀 | 樱桃bt在线www| 2022国产91精品久久久久久| 高清欧美videos sexo| 麻豆蜜桃国语精品无码视频| 欧美激情视频二区| 亚洲欭美日韩颜射在线二| 超碰国产亚洲人人| 久久精品国产亚洲AV妓女不卡| 色偷偷男人的天堂a v| 亚洲色欲色欲无码AV| 粗大分开挺进内射| 两个客户一起吃我的奶| 妻中蜜在线播放| 日日噜噜夜夜狠狠扒开双腿| 天天啪免费视频在线看| 亚洲qingse中文字幕久久| 英国video性精品高清最新| 抽插嫩B乳无码漫| 国产精品96久久久久久AV网址| 伧理片午夜伧理片| 精品国产三级a|