工作面試老大難-MySQL中的鎖類型
MySQL 是支持ACID特性的數據庫。我們都知道”C”代表Consistent，當不同事務操作同一行記錄時，為了保證一致性，需要對記錄加鎖。在MySQL 中，不同的引擎下的鎖行為也會不同，本文將重點介紹 MySQL InnoDB引擎中常見的鎖。
一. 準備
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`age` tinyint(4) DEFAULT '0',
`phone` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
create index test_age_index
on user (age);
#插入基礎數據
INSERT INTO `user` (`id`, `name`, `age`, `phone`)
VALUES
(1, '張三', 18, '13800138000'),
(2, '李四', 20, '13800138001'),
(3, '王五', 22, '13800138002'),
(4, '趙六', 26, '13800138003'),
(5, '孫七', 30, '13800138004');
為了方便講解，創建一張user表，設置age的字段為普通索引，并填充以下數據。本文所有的sql語句均基于這張表。
| id | name | age | phone |
|---|---|---|---|
| 1 | 張三 | 18 | 13800138000 |
| 2 | 李四 | 20 | 13800138001 |
| 3 | 王五 | 22 | 13800138002 |
| 4 | 趙六 | 26 | 13800138003 |
| 5 | 孫七 | 30 | 13800138004 |
二. 快照讀和當前讀
MySQL在REPEATABLE READ隔離級別下很大程度地避免了幻讀現象（很大程度是個啥意思?意思是在某些情況下其實還是可能出現幻讀現象的）。
怎么避免臟讀、不可重復讀、幻讀這些現象呢？其實有兩種可選的解決方案。
-
方案一：讀操作使用多版本并發控制(MVCC)，寫操作進行加鎖。
MVCC 在之前的文章有詳細的描述，就是通過生成一個 ReadView，然后通過ReadView找到符合條件的記錄版本(歷史版本是由undo日志構建的)。其實就像是在生成ReadView的那個時刻，時間靜止了(就像用相機拍了一個快照)，查詢語句只能讀到在生成ReadView之前已提交事務所做的更改，在生成ReadView之前未提交的事務或者之后才開啟的事務所做的更改則是看不到的。寫操作肯定針對的是最新版本的記錄，讀記錄的歷史版本和改動記錄的最新版本這兩者并不沖突，也就是采用MVCC時，讀-寫操作并不沖突。我們通常把MVCC實現的并發讀寫稱為“快照讀”。
MVCC無法完全避幻讀，參考：《什么是MVCC機制》
-
方案二：讀、寫操作都采用加鎖的方式。
如果我們的一些業務場不允許讀取記錄的舊版本，而是每次都必須去讀取記錄的最新版本。比如在銀行存款的事務中，我們需要先把賬戶的余額讀出來，然后將其加上本次存款的數額，最后再寫到數據庫中。在將賬戶余額讀取出來后，就不想讓別的事務再訪問該余額，直到本次存款事務執行完成后，其他事務才可以訪問賬戶的余額。這樣在讀取記錄的時候也就需要對其進行加鎖操作，這也就意味著讀操作和寫操作也得像寫寫操作 那樣排隊執行。我們通常將使用加鎖的方式實現的并發讀寫稱為“當前讀”。后文提到的
select ... for update和select ... lock in share mode就是典型的當前讀。
很明顯如果采用MVCC方式，讀-寫操作彼此并不沖突，性能更高；如果采用加鎖方式，讀-寫操作彼此需要排隊執行，從而影響性能。一般情況下，我們當然愿意采用MVCC來解決讀-寫操作并發執行的問題，但是在某些特殊業務場景中，要求必須采用加鎖的方式執行，那也是沒有辦法的事情。
三. 鎖的分類
3.1 行級鎖和表級鎖(Row-level and Table-level Locks)
按照鎖的粒度劃分，可分為行級鎖和表級鎖。表級鎖作用于數據庫表，不同的事務對同一個表加鎖，根據實際情況，后加鎖的事務可能會發生block，直到表鎖被釋放。表級鎖的優點是資源占用低，可防止死鎖等。缺點是鎖的粒度太高，不利于高并發的場景。
行級鎖行級鎖作用于數據庫行，它允許多個事務同時訪問同一個數據庫表。當多個事務操作同一行記錄時，沒獲得鎖的事務必須等持有鎖的事務釋放才能操作行數據。行級鎖的優點能支持較高的并發。缺點是資源占用較高，且會出現死鎖。
4.2 共享鎖排它鎖(Shared and Exclusive Locks)
InnoDB引擎的鎖分為兩類，分別是共享鎖和排他鎖。這些概念在很多領域都出現過，比如Java中的ReadWriteLock。
-
共享鎖(shared lock) 允許多個事務同時持有同一個資源的共享鎖，常用
S表示。#mysql 8.0之前的版本通過 lock in share mode給數據行添加share lock select * from user where id = 1 lock in share mode; #mysql 8.0以后的版本通過for share給數據行添加share lock select * from user where id = 1 for share；在普通的 SELECT 語句后邊加
LOCK IN SHARE MODE，如果當前事務執行了該語句，那么它會為讀取到的記錄加 S 鎖 這樣允許別的事務繼續獲取這些記錄的 S 鎖（比方說別的事務也使用SELECT ... LOCK IN SHARE MODE語句來讀取這些記錄），但是不能獲取這些記錄的 X 鎖（比方說使用SELECT ... FOR UPDATE語句來讀取這些記錄，或者直接修改這些記錄）。如果別的事務想要獲取這些記錄的 X 鎖，那么它們會阻塞，直到當前事務提交之后將這些記錄上的 S 鎖釋放掉。select默認情況下都是快照讀，除非顯式加鎖，實現當前讀。
-
排他鎖(exclusive lock)只允許一個事務持有某個資源的鎖，常用
X表示。# 通過for update可以給數據行加exclusive lock select * from user where id = 1 for update; # 通過update或delete同樣也可以 update user set age = 16 where id = 1;也就是在普通的 SELECT 語句后面加上
FOR UPDATE。如果當前事務執行了該語句，那么它會為讀取到的記錄加X鎖，這樣既不允許別的事務獲取這些記錄的S鎖(比如別的事務| 使用SELECT .. LOCK IN SHARE MODE語句來讀取這些記錄時)，也不允許獲取這些記錄的x鎖(比如說使用SELECT … FOR UPDATE語句來讀取這些記錄，或者直接改動這些記錄時）如果別的事務想要獲取這些記錄的s鎖或者X鎖，那么它們會被阻塞，直到當物事務提交之后將這些記錄上的X鎖釋放掉為止。update、delete語句默認會加排他行鎖
舉個例子，假如事務T1持有了某一行?的共享鎖(S)。當事務T2也想獲得該行的鎖，分為如下兩種情況:
- 如果T2申請的是行r的共享鎖(S)，會被立即允許，此時T1和T2同時持有行r的共享鎖。
- 如果T2申請的是排他鎖(X)，那么必須等T1釋放才能成功獲取。
反過來說，假如T1持有行r的排他鎖，那不管T2申請的是共享鎖還是排他鎖，都必須等待T1釋放才能成功。
總的來說，MySQL中的鎖有很多種，不過我們需要重點關注的就上面兩點，即鎖的作用域和鎖的類型。如上所述，鎖可以作用于行，也能作用于表，但不管他們的作用域是什么，鎖的類型只有兩種，即“共享”和“排他”。
不管是行級還是表級鎖，都遵循下列互斥關系：
| 排他鎖（X） | 共享鎖（S） | |
|---|---|---|
| 排他鎖（X） | 互斥 | 互斥 |
| 共享鎖（S） | 互斥 | 兼容 |
四. 數據庫鎖信息查看
如果我們需要查看MySQL目前的鎖持有狀態，我們可以使用下列語句查詢：
# 獲取 InnoDB 事務鎖的情況，MySQL 8.0 之前
select * from information_schema.INNODB_LOCKS
# MySQL 8.0 之后使用：
select * from performance_schema.data_locks;
例如下列SQL，
| Transaction 1 | Transaction 2 | Transaction 3 |
|---|---|---|
| select * from user where id=2 update; | ||
| update user set name=‘張三’ where id=2; | ||
| select * from performance_schema.data_locks; |
執行結果如下：
通過 performance_schema.data_locks 表的信息，我們可以輕松了解到系統目前的加鎖情況。
五. 意向鎖（Intention Locks）
5.1 意向鎖分類
InnoDB 支持多粒度鎖，允許行鎖和表鎖并存。例如， LOCK TABLES ... WRITE 之類的語句在指定的表上獲取排它鎖（X 鎖）。為了使多粒度級別的鎖定變得可行，InnoDB 使用了意向鎖。
意向鎖是一種特殊的表級鎖，指示事務稍后對表中的行需要哪種類型的鎖（共享或獨占）。意向鎖有兩種類型：
- 意向共享鎖（intention share lock）：簡稱
IS。事務在給一個數據行加共享鎖（S）前必須先取得該表的IS鎖，用于標記當前表有行級共享鎖存在，代表有事務準備讀取數據。 - 意向排他鎖（intention exclusive lock）：簡稱
IX。事務在給一個數據行加排他鎖（X）前必須先取得該表的IX鎖，用于標記當前表有行級排他鎖的存在，代表有事務準備寫入數據。
需要注意的是，意向鎖不會阻塞除全表請求（例如 LOCK TABLES ... WRITE ）之外的任何內容。意向鎖的主要目的是表明有人正在鎖定一行，或者將要鎖定表中的一行。IS 和 IX兩者之間并不互斥：
| 意向排他鎖（IX） | 意向共享鎖（IS） | |
|---|---|---|
| 意向排他鎖（IX） | 兼容 | 兼容 |
| 意向共享鎖（IS） | 兼容 | 兼容 |
也就是說，當 IX 被 T1事務獲取，并不影響其他事務獲取 IX 和 IS；同理當 IS 被 T1獲取時，其他事務也能獲取到 IX 和 IS。
只有當一個事務需要獲得表級X或S鎖時：
# 給user表加表級 S 鎖
lock tables user read;
# 給user表加表級 X 鎖
lock tables user write;
才會去判斷當前表是否有人占用 IX 和 IS 鎖，具體有兩種情況：
- 嘗試獲取表級S鎖時，如果 IX 被占用，這表明當前表有行級X鎖存在，會有事務寫入新數據，則獲取表級S鎖事務被阻塞；如果 IX 未被占用，這表明現在沒有行級X鎖存在，沒有事務寫入新數據，則成功獲取表級S鎖，。
- 嘗試獲取表級X鎖時，如果 IX 或 IS 被占用，這表明當前表有事務準備寫入或讀取某行數據，則獲取表級X鎖事務被阻塞。
表級鎖和意向鎖的互斥關系如下表：
| 意向共享鎖（IS） | 意向排他鎖（IX） | |
|---|---|---|
| 共享鎖（S） | 兼容 | 互斥 |
| 排他鎖（X） | 互斥 | 互斥 |
5.2 意向鎖存在的意義
有人可能會有疑問，MySQL為什么需要設計意向鎖呢？
那我們就需要來看看沒有意向鎖，MySQL該如何處理表級鎖和行級鎖共存。
假如事務 A 獲取了某一行的排他鎖，并未提交：
SELECT * FROM `user` WHERE id = 1 FOR UPDATE;
事務 B 想要獲取 users 表的表鎖：
LOCK TABLES `user` READ;
因為共享鎖與排他鎖互斥，所以事務 B 在試圖對 user 表加共享鎖的時候，必須保證：
- 當前沒有其他事務持有 user 表的表級排他鎖。
- 當前沒有其他事務持有 user 表中任意一行的行級排他鎖。
為了檢測是否滿足第二個條件，事務 B 必須在確保 user 表不存在任何排他鎖的前提下，去檢測表中的每一行是否存在排他鎖。很明顯這是一個效率很差的做法，但是有了意向鎖之后，情況就不一樣了：
因為行級鎖加鎖前，都會先獲取意向鎖，所以如果當前意向鎖沒有被占用，就代表當前表沒有行鎖占用，就不需要掃描整張表是否存在行級鎖占用，大大提高了表級鎖加鎖效率。
六. 行鎖算法
InnoDB引擎是MySQL非常重要的一部分，MySQL團隊為它開發了很多種類型的鎖，下面將逐一介紹。
6.1 記錄鎖（Record Locks）
Record Locks是作用于記錄的索引，可以鎖定單條或多條記錄，比如下面SQL語句:
# 鎖定id=1這條記錄，阻止任何其他事務插入、更新或刪除 user.id 值為 1 的行。
SELECT * FROM user WHERE id = 1 FOR UPDATE;
上面的sql給id為1的行加了X鎖。其他事務要對這行數據進行修改(update、insert、delete)都必須等待當前事務釋放X鎖(提交或回滾事務)。
記錄鎖總是鎖定索引記錄，即使表沒有定義索引。對于這種情況， InnoDB 創建一個隱藏的聚簇索引并使用該索引進行記錄鎖定。
6.2 間隙鎖（Gap Locks）
間隙鎖（Gap Lock）是InnoDB為了解決在“可重復讀”隔離級別下**“當前讀”的幻讀問題**引入的鎖機制。
6.2.1 什么是間隙鎖
間隙就是是指索引兩兩之間的一個左開右開區間。
在user表中，由于age字段加了普通索引，age字段存在以下的間隙：
(-∞,18), (18,20), (20,22), (22,26), (26,30), (30,+∞]
6.2.2 間隙鎖的行為
Record lock是作用于索引，而Gap locks 是作用于索引之間的間隙。比如下面的sql語句就會給(22,26)之間的索引間隙加鎖。
select * from user where age between 22 and 26 for update;
上面的語句執行過后，其他事務就無法往[22,26]之間的間隙插入數據。這樣做的目的是為了防止出現幻讀。假如沒有 Gap locks，下面sql會發生不同的行為：
| Transaction 1 | Transaction 2 |
|---|---|
| select * from user where age between 22 and 26 for update; | |
| Insert into user (name,age) values (‘bigbyto’,23); | |
| select * from user where age between 22 and 26; |
上面的sql可能會出現兩種情況
- 有Gap locks：T1的第二次查詢依然是查詢出兩個結果，即王五和趙六。 T2將會Block，直到T1事務結束。通過下面sql可以看到Gap lock阻止了T2插入內容。
- 沒有Gap locks：由于沒有 Gap locks，就只會給 id=3 和 id=4 加鎖，而不會給沒有的數據加鎖，這樣T2將會插入成功，數據庫多了一條bigbyto，age為25的數據; T1第二次查詢將會出現3條數據(幻讀)。
Gap locks的目的就是為了防止其他事務往索引的間隙插入數據，以此來避免出現幻讀。雖然gap鎖有共享gap鎖和排他gap鎖這樣的說法，但是他們起的作用都是相同的。而且如果對一條記錄加了gap鎖（無論是共享gap鎖還是排他gap鎖），并不會限制其它事務對這條記錄加 Record Lock 或繼續加 gap 鎖。再強調一遍，gap鎖的作用僅僅是為了防止插入幻影記錄而已。
在 MySQL 的REPEATABLE-READ隔離級別下，Gap locks默認啟用。禁用方式很簡單，把隔離級別設置為READ_COMMITTED即可。
需要注意的是，如果age列上沒有索引，SQL會走聚簇索引的全表掃描進行過濾，由于過濾是在MySQL Server層面進行的。因此每條記錄（無論是否滿足條件）都會被加上X鎖。
也就是說如果 user 表沒有 age 字段索引，T1 執行
select * from user where age between 22 and 26 for update;后，T2是無法插入任何數據。
6.2.3 鎖降級
在REPEATABLE-READ隔離級別下，當查詢一條記錄時，根據實際情況，mysql會對記錄加不同的鎖。比如下面的sql:
select * from user where id = 3 for update;
上面sql中，會給id=3的行加Record lock。
當where字段滿足唯一索引，主鍵其中之一時，mysql會使用Record lock給記錄加鎖。因為數據庫約束數據唯一，不會出現幻讀。如果字段是普通索引，情況會發生變化
select * from user where age = 22 for update;
上面的sql會使用Gap lock，(20,22)之間的間隙會被鎖定，其他事務無法往這個區間插入數據。
6.3 Next-Key Locks
Next-Key Locks實際上就是Gap lock和Record Lock的組合。Gap lock中的索引間隙是一個左開右開的區間，在next-key lock中，變成左開右閉，比如:
(-∞,18], (18,20], (20,22], (22,26], (26,30], (30,+∞)
Next-Key Locks同時給索引和索引之間的間隙加鎖(即組合Record lock和Gap lock)，例如:
select * from user where age = 22 for update;
對于這條sql，鎖定的范圍變成了(18,22], (22,26)，即Next-Key lock會鎖定索引前后的區間以及索引本身。同時，因為用到了Gap lock，這種鎖自然而然也是只有在 REPEATABLE-READ 的隔離級別下才能用。
6.4 Insert Intention Locks
Insert Intention Lock是MySQL中一種鎖類型，用于在多個事務同時向同一個表中插入新行時，保護對于同一索引鍵的插入操作。Insert Intention Lock的作用是在表級別上創建一個鎖定，以指示其他事務正在嘗試向表中插入新行。
當一個事務想要向表中插入新行時，它會先獲得一個Insert Intention Lock。然后，如果該事務需要向表中插入新行，它會在需要插入的索引鍵上獲得一個排他鎖（Exclusive Lock）。如果該事務需要向表中插入新行但沒有指定索引鍵，則會在表上獲得一個排他鎖。
Insert Intention Lock的作用是防止多個事務同時向同一索引鍵插入新行，從而保證數據的一致性和完整性。它只會在需要插入新行時才會被創建，不會對已經存在的行造成影響。需要注意的是，Insert Intention Lock只保護對于同一索引鍵的插入操作，對于不同索引鍵的插入操作沒有任何保護作用。
參考文章：
《MySQL是怎樣運行的-小孩子4919著》
MySQL :: MySQL 5.7 Reference Manual :: 14.7.1 InnoDB Locking
MySQL詳解－－鎖.md (xuzhongcn.github.io)
浙公網安備 33010602011771號