我所理解的MySQL(五)鎖及加鎖規則

語言: CN / TW / HK

你好,有幸相見。

從九月開始,我決定發起「每週一博」的目標:每週至少釋出一篇部落格,可以是各種原始碼分析研讀,也可以是記錄工作中遇到的難題。

在經過了一段時間漫無目的的學習之後,我發現那樣用處好像不大,看過的東西過段時間就忘了,而且也沒有做什麼筆記。

“凡所學,必有所輸出。”我認為這才是最適合我的學習方式,這也是「每週一博」活動的來由,朋友們,如果你也覺得經常會忘記以前看過的東西,一起加入這個活動吧。

這是十一月的第一篇部落格,同時也是 MySQL 系列的第五篇。


MySQL 系列的第五篇,主要內容是鎖(Lock),包括鎖的粒度分類、行鎖、間隙鎖以及加鎖規則等。

MySQL 引入鎖的目的是為了解決併發寫的問題,比如兩個事務同時對同一條記錄進行寫操作,如果允許它們同時進行,那就會產生髒寫的問題,這是任何一種隔離級別都不允許發生的異常情況,而鎖的作用就是讓兩個併發寫操作按照一定的順序執行,避免髒寫問題。

首先申明本文中所使用到的示例

CREATE TABLE `user`  (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) NULL DEFAULT NULL,
  `age` int(12) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;

insert into user values (5,'重塑',5),(10,'達達',10),(15,'刺蝟',15);
複製程式碼

本文所述示例都是在 MySQL InnoDB 儲存引擎以及可重複讀(Repeatable Read)隔離級別下。

1. 鎖的粒度分類

從鎖的粒度來看,MySQL 中的鎖可以分為全域性鎖、表級鎖和行鎖三種。

1.1 全域性鎖

全域性鎖會將整個資料庫都加上鎖,此時資料庫將處於只讀狀態,任何修改資料庫的語句,包括 DDL(Data Definition Language)及增刪改的 DML(Data Manipulation Language)語句都將被阻塞,直到資料庫全域性鎖釋放。

最常使用到全就鎖的地方就是進行全庫備份,我們可以通過以下的語句實現全域性鎖的加鎖與釋放鎖操作:

-- 加全域性鎖
flush tables with read lock;

-- 釋放全域性鎖
unlock table;
複製程式碼

若客戶端連結斷開,也會自動釋放全域性鎖。

1.2 表級鎖

表級鎖會將整張表加上鎖,MySQL 中的表級鎖有:表鎖元資料鎖(Meta Data Lock)、意向鎖(Intention Lock)和自增鎖(AUTO-INC Lock)。

1.2.1 表鎖

表鎖的加鎖和釋放鎖方式:

  • 加鎖:lock table tableName read/write;
  • 釋放鎖:unlock table;

需要注意的是,表鎖的加鎖也限制了同一個客戶端連結的操作許可權,如加了表級讀鎖(lock table user read),那麼在同一個客戶端連結中在釋放表級讀鎖以前,對同一張表(user 表)也只能進行讀操作,無法進行寫操作,而其他客戶端連結對該表(user 表)只能進行讀操作,無法進行寫操作。

如加了表級寫鎖(lock table user write),在同一個客戶端連結中可對錶進行讀寫操作,而其他客戶端連結既無法進行讀操作也無法進行寫操作。

1.2.2 元資料鎖

第二種表級鎖是元資料鎖(MDL, Meta Data Lock),元資料鎖會在客戶端訪問表的時候自動加鎖,在客戶端提交事務時釋放鎖,它防止了以下場景出現的問題:

sessionAsessionB
begin;
select * from user;
alter table user add column birthday datetime;
select * from user;

如上表,sessionA 開啟了一個事務,並進行一次查詢,在這之後另外一個客戶端 sessionBuser 表新增了一個 birthday 欄位,然後 sessionA 再進行一次查詢,如果沒有元資料鎖,就可能會出現在同一個事務中,前後兩次查詢到的記錄,表字段列數不一致的情況,這顯然是需要避免的。

DDL 操作對錶加的是元資料寫鎖,對其他事務的元資料讀寫鎖都不相容;DML 操作對錶加的是元資料讀鎖,可與其他事務的元資料讀鎖共享,但與其他事務的元資料寫鎖不相容。

1.2.3 意向鎖

第三種表級鎖是意向鎖,它表示事務想要獲取一張表中某幾行的鎖(共享鎖或排它鎖)。

意向鎖是為了避免在表中已經存在行鎖的情況下,另一個事務去申請表鎖而掃描表中的每一行是否存在行鎖的系統消耗。

sessionAsessionB
begin;
select * from user where id=5 for update;
flush table user read;

例如,sessionA 開啟了一個事務,並對 id=5 這一行加上了行級排它鎖,此時 sessionB 將對 user 表加上表級排它鎖(只要 user 表中有一行被其他事務持有讀鎖或寫鎖即加鎖失敗)。

如果沒有意向鎖,sessionB 將掃描 user 表中的每一行,判斷它們是否被其他事務加鎖,然後才能得出 sessionB 的此次表級排它鎖加鎖是否成功。

而有了意向鎖之後,在 sessionB 將對 user 表加鎖時,會直接判斷 user 表是否被其他事務加上了意向鎖,若有則加鎖失敗,若無則可以加上表級排它鎖。

意向鎖的加鎖規則

  • 事務在獲取行級共享鎖(S鎖)前,必須獲取表的意向共享鎖(IS鎖)或意向排它鎖(IX鎖)
  • 事務在獲取行級排它鎖(X鎖)前,必須獲取表的意向排它鎖(IX鎖)

1.2.4 自增鎖

第四種表級鎖是自增鎖,這是一種特殊的表級鎖,只存在於被設定為 AUTO_INCREMENT 自增列,如 user 表中的 id 列。

自增鎖會在 insert 語句執行完成後立即釋放。同時,自增鎖與其他事務的意向鎖可共享,與其他事務的自增鎖、共享鎖和排它鎖都是不相容的。

1.3 行鎖

行鎖是由儲存引擎實現的,從行鎖的相容性來看,InnoDB 實現了兩種標準行鎖:共享鎖(Shared Locks,簡稱S鎖)和排它鎖(Exclusive Locks,簡稱X鎖)。

這兩種行鎖的相容關係與上面元資料鎖的相容關係是一樣的,可以用下面的表格表示。

事務A\事務B共享鎖(S鎖)排它鎖(X鎖)
共享鎖(S鎖)相容衝突
排它鎖(X鎖)衝突衝突

而從行鎖的粒度繼續細分,又可以分為記錄鎖(Record Lock)、間隙鎖(Gap Lock)、Next-key Lock

1.3.1 記錄鎖(Record Lock)

我們一般所說的行鎖都是指記錄鎖,它會把資料庫中的指定記錄行加上鎖。

假設事務A中執行以下語句(未提交):

begin;
update user set name='達聞西' where id=5;
複製程式碼

InnoDB 至少會在 id=5 這一行上加一把行級排它鎖(X鎖),不允許其他事務操作 id=5 這一行。

需要注意的是,這把鎖是加在 id 列的主鍵索引上的,也就是說行級鎖是加在索引上的。

假設現在有另一個事務B想要執行一條更新語句:

update user set name='**浪' where id=5;
複製程式碼

這時候,這條更新語句將被阻塞,直到事務A提交以後,事務B才能繼續執行。

記錄鎖示意圖

1.3.2 間隙鎖(Gap Lock)

間隙鎖,顧名思義就是給記錄之間的間隙加上鎖。

需要注意的是,間隙鎖只存在於可重複讀(Repeatable Read)隔離級別下。

不知道大家還記不記得幻讀?

幻讀是指在同一事務中,連續執行兩次同樣的查詢語句,第二次的查詢語句可能會返回之前不存在的行。

間隙鎖的提出正是為了防止幻讀中描述的幻影記錄的插入而提出的,舉個例子。

sessionAsessionB
begin;
select * from user where age=5;(N1)
insert into user values(2, '**浪', 5)
update user set name='達聞西' where age=5;
select * from user where age=5;(N2)

sessionA 中有兩處查詢N1和N2,它們的查詢條件都是 age=5,唯一不同的是在N2處的查詢前有一條更新語句。

照理說在 RR 隔離級別下,同一個事務中兩次查詢相同的記錄,結果應該是一樣的。但是在經過更新語句的當前讀查詢後(更新語句的影響行數是2),N1和N2的查詢結果並不相同,N2的查詢將 sessionB 插入的資料也查出來了,這就是幻讀。

而如果在 sessionA 中的兩次次查詢都用上間隙鎖,比如都改為select * from user where age=5 for update。那麼 sessionA 中的當前讀查詢語句至少會將id在(-∞, 5)和(5, 10)之間的間隙加上間隙鎖,不允許其他事務插入主鍵id屬於這兩個區間的記錄,即會將 sessionB 的插入語句阻塞,直到 sessionA 提交之後,sessionB 才會繼續執行。

也就是說,當N2處的查詢執行時,sessionB 依舊是被阻塞的狀態,所以N1和N2的查詢結果是一樣的,都是(5,重塑,5),也就解決了幻讀的問題。

間隙鎖示意圖

1.3.3 Next-key Lock

Next-key Lock 其實就是行鎖與間隙鎖組合的產物,它既阻止了其他事務在間隙的插入操作,也阻止了其他事務對記錄的修改操作。

Next-key Lock鎖示意圖

2. 加鎖規則

不知道大家有沒有注意到,我在行鎖部分描述記錄鎖、間隙鎖加鎖的具體記錄時,用的是「至少」二字,並沒有詳細說明具體加鎖的是哪些記錄,這是因為記錄鎖、間隙鎖和 Next-key Lock 的加鎖規則是十分複雜的,這也是本文主要討論的內容。

關於加鎖規則的敘述將分為三個方面:唯一索引列、普通索引列和普通列,每一方面又將細分為等值查詢和範圍查詢兩方面。

需要注意的是,這裡加的鎖都是指排它鎖。

在開始之前,先來回顧一下示例表以及表中可能存在的行級鎖。

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  5 | 重塑   |    5 |
| 10 | 達達   |   10 |
| 15 | 刺蝟   |   15 |
+----+--------+------+
3 rows in set (0.00 sec)
複製程式碼

表中可能包含的行級鎖首先是每一行的記錄鎖——(5,重塑,5),(10,達達,5),(15,刺蝟,15)。

假設 user 表的索引值有最大值 maxIndex 和最小值 minIndex,user 表還可能存在間隙鎖(minIndex,5),(5,10),(10,15),(15,maxIndex)。

共三個記錄鎖和四個間隙鎖。

2.1 唯一索引列等值查詢

首先來說唯一索引列的等值查詢,這裡的等值查詢可以分為兩種情況:命中與未命中。

當唯一索引列的等值查詢命中時:

sessionAsessionB
begin;
select * from user where id=5 for update;
insert into user values(1,'斯斯與帆',1),(6,'夏日陽光',6),(11,'告五人',11),(16,'面孔',16);
update user set age=18 where id=5;(Blocked
update user set age=18 where id=10;
update user set age=18 where id=15;

上表中 sessionB 的執行結果是除了 id=5 行的更新語句被阻塞,其他語句都正常執行。

sessionB 中的 insert 語句是為了檢查間隙鎖,update 語句是為了檢查記錄鎖(行鎖)。執行結果表明 user 表的所有間隙都沒有被上鎖,記錄鎖中只有 id=5 這一行被上鎖了。

select * from user where id=5 for update 加鎖區域示意圖

所以,當唯一索引列的等值查詢命中時,只會給命中的記錄加鎖


當唯一索引列的等值查詢未命中時:

sessionAsessionB
begin;
select * from user where id=3 for update;
insert into user values (2,'反光鏡',2);(Blocked
update user set age=18 where id=5;
insert into user values (6,'夏日陽光',6);
update user set age=18 where id=10;
insert into user values (11,'告五人',11);
update user set age=18 where id=15;
insert into user values (16,'面孔',16);

上表的執行結果是 sessionB 中 id=2 的記錄插入被阻塞,其他語句正常執行。

根據執行結果可以知道 sessionA 給 user 表加的鎖是間隙鎖(1,5)。

select * from user where id=3 for update 加鎖區域示意圖

所以,當唯一索引列的等值查詢未命中時,會給id值所在的間隙加上間隙鎖

2.2 唯一索引列範圍查詢

範圍查詢比等值查詢要更復雜一些,它需要考慮到邊界值存在於表中,以及是否命中邊界值。

首先來看邊界值存在於表中,但未命中的情況:

sessionAsessionB
begin;
select * from user where id<10 for update;
insert into user values (1,'斯斯與帆',1);(Blocked
update user set age=18 where id=5;(Blocked
insert into user values (6,'夏日陽光',6);(Blocked
update user set age=18 where id=10;(Blocked
insert into user values (11,'告五人',11);
update user set age=18 where id=15;
insert into user values (16,'面孔',16) ;

此時 sessionA 給 user 表加上的鎖是記錄鎖 id=5,id=10 以及間隙鎖(minIndex,5),(5,10)。

我們知道間隙鎖+記錄鎖就是 Next-key Lock,所以上述的加鎖情況可以看作是兩條 Next-key Lock:(minIndex, 5],(5,10],即 Next-key Lock —— (minIndex,10]。

select * from user where id<10 for update 加鎖區域示意圖


當邊界值存在於表中,同時命中的情況:

sessionAsessionB
begin;
select * from user where id<=10 for update;
insert into user values (1,'斯斯與帆',1);(Blocked
update user set age=18 where id=5;(Blocked
insert into user values (6,'夏日陽光',6);(Blocked
update user set age=18 where id=10;(Blocked
insert into user values (11,'告五人',11);(Blocked
update user set age=18 where id=15;(Blocked
insert into user values (16,'面孔',16) ;

此時 sessionA 給 user 表加上的鎖是Next-key Lock —— (minIndex,15]。

select * from user where id<=10 for update 加鎖區域示意圖


當邊界值不存在於表中時,不可能命中,故只有未命中一種情況:

sessionAsessionB
begin;
select * from user where id<=9 for update;
insert into user values (1,'斯斯與帆',1);(Blocked
update user set age=18 where id=5;(Blocked
insert into user values (6,'夏日陽光',6);(Blocked
update user set age=18 where id=10;(Blocked
insert into user values (11,'告五人',11);
update user set age=18 where id=15;
insert into user values (16,'面孔',16) ;

此時 sessionA 給 user 表加上的鎖是 Next-key Lock —— (minIndex,10],與第一種情況一樣。

select * from user where id<=9 for update 加鎖區域示意圖

綜上所述,在對唯一索引進行範圍查詢時:

  1. 會給範圍中的記錄加上記錄鎖,間隙加上間隙鎖
  2. 對於右邊屆的範圍查詢(小於/小於等於)是比較特殊的,它會將記錄鎖加到第一個邊界之外的記錄上,若其中有額外的間隙也會加上間隙鎖(即會將 Next-key Lock 加到第一個邊界之外的記錄上)

需要注意的是,第一條中所說的間隙指的是,邊界值所在的間隙,如間隙為(5,10),查詢條件為 id>7 時,這個間隙鎖就是(5,10),而不是(7,10)。

第二條舉例1:查詢條件為 id<10,第一個邊界之外的記錄是 id=10,所以 Next-key Lock 鎖會加到 id=10 的記錄上,被鎖住的範圍是(minIndex,10]。

第二條舉例2:查詢條件為 id<=10,第一個邊界之外的記錄是 id=15,所以 Next-key Lock 鎖會加到 id=15 的記錄上,被鎖住的範圍是(minIndex,15]。

第二條舉例3:查詢條件為 id>10,第一個邊界之外的記錄是 id=10,Next-key Lock 鎖會加到 id=10 的記錄上,由於 Next-key Lock 鎖指的是記錄以左的部分,所以被鎖住的範圍是(5,maxIndex]。

2.3 普通索引列等值查詢

普通索引與唯一索引的區別就在於唯一索引可以根據索引列確定唯一性,所以等值查詢的加鎖規則也有不同之處。

給 user 表再加一條記錄:

INSERT INTO user VALUES (11, '達達2.0', 10);
複製程式碼

這時 user 表的索引 age 結構如下圖所示:

索引 age 結構

在索引 age 中可能存在的行鎖是4個記錄鎖以及5個間隙鎖。

先來看索引 age 上的加鎖情況:

sessionAsessionB
begin;
select * from user where age=10 for update;
insert into user values (2,'達達',2);
update user set name='痛仰' where age=5;
insert into user values (6,'達達',6);(Blocked
update user set name='痛仰' where age=10 and id=10;(Blocked
update user set name='痛仰' where age=10 and id=16;)(Blocked
insert into user values (17,'達達',10);(Blocked
insert into user values (11,'達達',11);(Blocked
update user set name='痛仰' where age=15;
insert into user values (16,'面孔',16) ;

由上表的語句及執行結果來看,索引 age 上的加鎖情況是:

select * from user where age=10 for update 索引age上的加鎖情況

即索引 age 上的加鎖區域為(5, 15)。

由於普通索引無法確定記錄的唯一性,所以普通索引列等值查詢中,為索引 age 加鎖時,會找到第一個age小於10的值(即5)和第一個age大於10的值(即15),在這個範圍內的間隙加上間隙鎖,記錄加上記錄鎖

這是索引 age 上的加鎖情況,由於查詢語句是查詢記錄的所有列,根據查詢規則,會通過索引 age 上對應的 id 值到主鍵索引樹上進行回表操作,得到所有列,所以主鍵索引上也會加鎖。在這裡,滿足 age=10 的記錄的主鍵id分別是10和16,所以在主鍵索引上這兩行也會被加上排它鎖。

即,普通索引列等值查詢如果需要回表,滿足條件的記錄對應的主鍵也會被加上記錄鎖

這裡如果把 sessionA 中的查詢改為 select id from user where age=10 lock in share mode; ,則會因為覆蓋索引優化而不進行回表操作,所以主鍵索引上也不會加鎖。

2.4 普通索引列等值查詢+limit

這裡需要額外提一提 limit 這個語法,它的加鎖範圍(只討論普通索引)要更小一些,請看示例:

sessionAsessionB
begin;
select * from user where age=10 limit 1 for update;
insert into user values (2,'達達',2);
update user set name='痛仰' where age=5;
insert into user values (6,'達達',6);(Blocked
update user set name='痛仰' where age=10 and id=10;(Blocked
update user set name='痛仰' where age=10 and id=16;)
insert into user values (17,'達達',10);
insert into user values (11,'達達',11);
update user set name='痛仰' where age=15;
insert into user values (16,'面孔',16) ;

可以看到,與沒有加 limit 相比,多了兩條 insert 語句順利執行了。

由上表的語句及執行結果來看,索引 age 上的加鎖情況是:

select * from user where age=10 limit 1 for update 索引age上的加鎖情況

由此可見:limit 語法只會將鎖加到滿足條件的記錄,能夠減小加鎖範圍。

2.5 普通索引列範圍查詢

接下來看普通索引列上的範圍查詢(這裡只討論索引 age 的加鎖範圍,主鍵索引的加鎖如果存在回表會鎖住對應的id值):

sessionAsessionB
begin;
select * from user where age>8 and age<=12 for update;
insert into user values (2,'達達',2);
update user set name='痛仰' where age=5;
insert into user values (6,'達達',6);(Blocked
update user set name='痛仰' where age=10 and id=10;(Blocked
update user set name='痛仰' where age=10 and id=16;(Blocked
insert into user values (17,'達達',10);(Blocked
insert into user values (11,'達達',11);(Blocked
update user set name='痛仰' where age=15;(Blocked
insert into user values (16,'面孔',16) ;

與普通索引列等值查詢不同的是,範圍查詢比等值查詢多了一個 age=15 的記錄鎖。

select * from user where age>8 and age<=12 for update 索引age上的加鎖情況

這個邊界值與唯一索引列範圍查詢的原理是一樣的,可以參照上文所述來理解,這裡不多加贅述了。

《MySQL實戰45講》的作者丁奇認為這是一個 BUG,但並未被官方接收,如果要深究這個邊界值的原理,可能就需要看 MySQL 的原始碼了。

3. 溫故知新

  1. MySQL 中的鎖按粒度來分可以分為幾種?分別描述一下。
  2. MySQL 中行鎖的加鎖規則?
  3. 請說出下面幾條 SQL 的加鎖區域:
select * from user where c=10 for update;
select * from user where c>=10 and c<11 for update;
select id from user where c>=10 and c<11 for update;
複製程式碼

4. 參考資料