MySQL鎖類型解讀

鎖的分類圖，如下：

鎖操作類型劃分

• 讀鎖 : 也稱為共享鎖 、英文用S表示。針對(duì)同一份數(shù)據(jù)，多個(gè)事務(wù)的讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響，相互不阻塞的。
• 寫鎖 : 也稱為排他鎖 、英文用X表示。當(dāng)前寫操作沒有完成前，它會(huì)阻斷其他寫鎖和讀鎖。這樣就能確保在給定的時(shí)間里，只有一個(gè)事務(wù)能執(zhí)行寫入，并防止其他用戶讀取正在寫入的同一資源。
• 對(duì)于InnoDB引擎，讀鎖和寫鎖可以加在表上，或者行上

鎖定讀

對(duì)讀取的記錄加S鎖:

select...lock in share mode;
#或
select...for share;#(8.0新增語法)。。

若當(dāng)前事務(wù)執(zhí)行了該語句，則會(huì)給該記錄加S鎖，并允許別的事務(wù)繼續(xù)獲取該記錄的S鎖，比如別的事務(wù)也使用 SELECT... LOCK IN SHAREMODE 語句來讀取這些記錄

但是不能獲取這些記錄的X鎖，比如別的事務(wù)不能直接修改這些記錄，會(huì)阻塞直到當(dāng)前事務(wù)提交之后將這些記錄上的S鎖釋放掉。

對(duì)讀取的記錄加X鎖:

select...for update;

如果當(dāng)前事務(wù)執(zhí)行了該語句，則會(huì)給該記錄加X鎖，即不允許別的事務(wù)獲取這些事務(wù)S鎖，也不允許獲取X鎖...

MySQL8新特性

在8.0版本中，SELECT ... FOR UPDATE, SELECT .. FOR SHARE 添加NOWAIT、 SKIP LOCKED 語法，跳過鎖等待，或者跳過鎖定。

通過添加NOWAIT、SKIP LOCKED語法，能夠立即返回。如果查詢的行已經(jīng)加鎖:

• 那么NOWAIT會(huì)立即報(bào)錯(cuò)返回
• 而SKIP LOCKED也會(huì)立即返回，只是返回的結(jié)果中不包含被鎖定的行

寫操作

寫操作無非是delete，update，insert

• DELETE

底層是先在 B+ 樹中定位到這條記錄的位置，然后獲取這條記錄的X鎖，再執(zhí)行 delete操作。

• UPDATE

情況1: 未修改該記錄的鍵值，并且被更新的列占用的存儲(chǔ)空間在修改前后未發(fā)生變化。

則在 B+ 樹中定位到這條記錄的位置，然后獲取記錄的 X鎖，最后在原記錄的位置進(jìn)行修改操作。

情況2: 修改了該記錄的鍵值，則相當(dāng)于在原記錄上做 DELETE 操作之后再來一次INSERT操作，加鎖操作。需要按照 DELETE 和 INSERT 的規(guī)則進(jìn)行了。

2.意向鎖

InnoDB 支持多粒度鎖，它允許行級(jí)鎖與表級(jí)鎖共存，而意向鎖就是其中的一種表鎖

• 意向鎖的存在是為了協(xié)調(diào)行鎖和表鎖的關(guān)系
• 意向鎖是一種不與行級(jí)鎖沖突表級(jí)鎖 ，這一點(diǎn)非常重要
• 意向鎖是自動(dòng)創(chuàng)建的，自動(dòng)聲明其上級(jí)獲取過鎖這一動(dòng)作，輪到時(shí)就會(huì)有排隊(duì)權(quán)

意向鎖分為兩種：

意向共享鎖: 事務(wù)有意向?qū)Ρ碇械哪承┬屑?code>共享鎖(S鎖)

#事務(wù)要獲取某些行的S鎖，必須先獲得表的IS 鎖。
select column from table ... lock in share mode;

意向排他鎖: 事務(wù)有意向?qū)Ρ碇械哪承┬屑?code>排他鎖(X鎖)

#事務(wù)要獲取某些行的X鎖，必須先獲得表的 IX 鎖
select column from table ... for update;

意向鎖是由存儲(chǔ)引擎自己維護(hù)的 ，用戶無法手動(dòng)操作意向鎖，在為數(shù)據(jù)行加共享/排他鎖之前，InooDB 會(huì)先獲取該數(shù)據(jù)行所在數(shù)據(jù)表的對(duì)應(yīng)意向鎖

意向鎖作用

現(xiàn)在有兩個(gè)事務(wù)T1和T2，其中T2試圖在該表級(jí)別上應(yīng)用共享或排它鎖

• 如果沒有意向鎖存在，那么T2就需要去檢查各個(gè)頁(yè)或行是否存在鎖
• 如果存在意向鎖，那么此時(shí)就會(huì)受到由T1控制的表級(jí)別意向鎖的阻塞，T2在鎖定該表前不必檢查各個(gè)頁(yè)或行鎖，而只需檢查表上的意向鎖。其實(shí)就是在更大一級(jí)別的空間示意里面是否已經(jīng)上過鎖!
• 在數(shù)據(jù)表的場(chǎng)景中，如果我們給某一行數(shù)據(jù)加上了排它鎖，數(shù)據(jù)庫(kù)會(huì)自動(dòng)給更大一級(jí)的空間，比如數(shù)據(jù)頁(yè)或數(shù)據(jù)表加上意向鎖，告訴其他人這個(gè)數(shù)據(jù)頁(yè)或數(shù)據(jù)表已經(jīng)有人上過排它鎖了，這樣當(dāng)其他人想要獲取數(shù)據(jù)表排它鎖的時(shí)候，只需要看是否有人已獲取這個(gè)數(shù)據(jù)表的意向排他鎖即可

假設(shè)事務(wù)A獲取了某一行的排他鎖，并未提交

begin;
select * from teacher where id=6 for update;

事務(wù)B想要獲取teacher表的讀鎖，語句如下

begin;
lock tables teacher read;

因?yàn)楣蚕礞i與排他鎖互斥，所以事務(wù)B在試圖對(duì) teacher 表加共享鎖的時(shí)候，必須保證兩個(gè)條件。

• 當(dāng)前沒有其他事務(wù)持有 teacher 表的排他鎖
• 當(dāng)前沒有其他事務(wù)持有 teacher 表中任意一行的排他鎖

為了檢測(cè)是否滿足第二個(gè)條件，事務(wù)B必須在確保 teacber 表不存在任何排他鎖的前提下，去檢測(cè)表中的每一行是否存在排他鎖。很明顯這是一個(gè)效率很差的做法，但是有了意向鎖之后，情況就不一樣了。

總結(jié)：

意向鎖是一個(gè)虛擬的鎖，只是為了讓別的事務(wù)知道 這里有行級(jí)鎖 所以不能加某些表級(jí)鎖

3.自增鎖

在使用MySQL過程中，我們可以為表的某個(gè)列添加 AUTO_INCREMENT屬性。舉例：

CREATE TABLE `teacher` (
     `id` int NOT NULL AUTO_INCREMENT,
     `name` varchar(255) NOT NULL,
     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

這意味著插入語句時(shí)不需要為其賦值，SQL語句修改如下所示

INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');
?
#上邊的插入語句并沒有為id列顯式賦值，所以系統(tǒng)會(huì)自動(dòng)為它賦上遞增的值，結(jié)果如下所示
mysql> select * from teacher;
+----+----------+
| id | name   |
+----+----------+
| 1 | zhangsan |
| 2 | lisi   |
+----+----------+
2 rows in set (0.00 sec)

所有插入數(shù)據(jù)的方式總共分為三類，分別是:“ Simple inserts ”，“ Bulk inserts ”和“ Mixed-mode inserts ”。

對(duì)于上面案例，MySQL中采用了自增鎖 的方式來實(shí)現(xiàn)，自增鎖是向含有AUTO_INCREMENT列的表中插入數(shù)據(jù)時(shí)的一種特殊的表級(jí)鎖，在執(zhí)行插入語句時(shí)加一個(gè)AUTO-INC鎖，分配遞增的值，執(zhí)行結(jié)束后，再把AUTO-INC鎖釋放掉。

一個(gè)事務(wù)在持有AUTO-INC鎖的過程中，其他事務(wù)的插入語句都要被阻塞，可以保證一個(gè)語句中分配的遞增值是連續(xù)的。也正因?yàn)榇?，其并發(fā)性不高，當(dāng)我們向一個(gè)有AUTO_INCREMENT關(guān)鍵字的主鍵插入值的時(shí)候，每條語句都要對(duì)這個(gè)表鎖進(jìn)行競(jìng)爭(zhēng)，這樣的并發(fā)潛力其實(shí)是很低下的，所以innodb通過 innodb_autoinc_lock_mode的不同取值來提供不同的鎖定機(jī)制，來顯著提高SQL語句的可伸縮性和性能。

#innodb_autoinc_lock_mode=0(傳統(tǒng)鎖定模式)
所有insert語句都會(huì)獲得自增鎖，這種鎖定是全局性的，即它會(huì)阻止其他事務(wù)同時(shí)進(jìn)行插入操作，直到當(dāng)前插入完成，即上面例子，并發(fā)性差
?
#innodb_autoinc_lock_mode=1(連續(xù)鎖定模式)
mysql8之前的默認(rèn)模式，當(dāng)執(zhí)行 INSERT 時(shí)，InnoDB 會(huì)先檢查是否有可用的自增值，如果有，則立即分配該值給新行，然后才加鎖
?
#innodb_autoinc_lock_mode=2(交錯(cuò)鎖定模式)
InnoDB 會(huì)預(yù)先分配一組自增ID（數(shù)量由 innodb_autoinc_cache 控制），然后將這些ID分配給后續(xù)的插入操作，多個(gè)客戶端可以同時(shí)從預(yù)分配的ID池中獲取ID并插入新行，從而大大減少了鎖等待的時(shí)間，但由于多個(gè)語句會(huì)同時(shí)需要數(shù)字，所以任何給定插入的行生成的值可能不是連續(xù)的

4.元數(shù)據(jù)鎖（MDL鎖）

MDL 的作用是，保證讀寫的正確性。比如，如果一個(gè)查詢正在遍歷一個(gè)表中的數(shù)據(jù)，而執(zhí)行期間另一個(gè)線程對(duì)這個(gè)表結(jié)構(gòu)做變更 ，增加了一列，那么查詢線程拿到的結(jié)果跟表結(jié)構(gòu)對(duì)不上，肯定是不行的。

因此，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加MDL讀鎖；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL寫鎖。解決DML和DDL操作之間的一致性問題，不需要顯式使用，在訪問一個(gè)表時(shí)會(huì)自動(dòng)加上

行級(jí)鎖

顧名思義，就是鎖住頁(yè)中某一行記錄，注意點(diǎn)是 行級(jí)鎖只在存儲(chǔ)引擎層實(shí)現(xiàn)，鎖的力度小，發(fā)生鎖沖突概率低，并發(fā)度高

缺點(diǎn)是對(duì)于鎖的開銷比較大，加鎖會(huì)比較慢，容易出現(xiàn)死鎖情況

• 數(shù)據(jù)準(zhǔn)備

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);
?
INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 2000);

• 記錄鎖

記錄鎖僅僅把一條記錄 鎖上，官方的類型名稱為:LOCK_REC_NOT_GAP。比如我們把id值為8的那條記錄加一個(gè)記錄鎖如圖所示，僅僅是鎖住了id值為8的記錄，對(duì)周圍的數(shù)據(jù)沒有影響。

記錄鎖是有S鎖和X鎖之分的，稱之為 S型(讀)記錄鎖 和 X型(寫)記錄鎖

• 當(dāng)一個(gè)事務(wù)獲取了一條記錄的讀鎖后，其他事務(wù)可以繼續(xù)獲取該記錄的讀鎖，但不可以繼續(xù)獲取寫鎖；
• 當(dāng)一個(gè)事務(wù)獲取了一條記錄的寫鎖后，其他事務(wù)既不可以獲取該記錄的讀鎖，也不可以繼續(xù)獲取寫鎖。

死鎖

接下來，我們來看一個(gè)可能引起死鎖的情況，假設(shè)事務(wù) D 和事務(wù) E 同時(shí)運(yùn)行，并且它們都試圖更新兩個(gè)賬戶的余額：

-- 啟動(dòng)事務(wù) D
START TRANSACTION;
-- 獲取賬戶1的排他鎖
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
?
-- 啟動(dòng)事務(wù) E
START TRANSACTION;
-- 獲取賬戶2的排他鎖
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
?
-- 事務(wù) D 試圖獲取賬戶 2 的排他鎖
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
?
-- 事務(wù) E 試圖獲取賬戶 1 的排他鎖
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

• 事務(wù)D已經(jīng)獲得了賬戶1的排他鎖，而事務(wù)E已經(jīng)獲得了賬戶2的排他鎖。
• 當(dāng)事務(wù)D試圖獲取賬戶2的排他鎖時(shí)，它會(huì)被阻塞，同樣地，當(dāng)事務(wù) E 試圖獲取賬戶 1 的排他鎖時(shí)也會(huì)被阻塞。這就會(huì)形成一個(gè)死鎖的情況。
• InnoDB 會(huì)檢測(cè)到這種情況，并自動(dòng)解決死鎖。它會(huì)選擇一個(gè)事務(wù)回滾，以便另一個(gè)事務(wù)可以繼續(xù)執(zhí)行。
• 可以通過查看 INFORMATION_SCHEMA.INNODB_TRX 表來了解當(dāng)前的事務(wù)狀態(tài)，包括死鎖信息

間隙鎖

MySQL 在可重復(fù)讀隔離級(jí)別下是可以解決幻讀問題的，解決方案有兩種

• 可以使用 MVCC方案解決
• 也可以采用加鎖方案解決。

但是事務(wù)在第一次執(zhí)行讀取操作時(shí)，那些幻影記錄尚不存在，我們無法給這些幻影記錄加上記錄鎖 ，所以InnoDB提出了一種稱之為Gap Locks 的鎖，官方的類型名稱為： LOCK_GAP ，我們可以簡(jiǎn)稱為 gap 鎖 （間隙鎖）

• 間隙鎖是在行級(jí)別的鎖定之上的一種擴(kuò)展，它不僅鎖定具體的行的兩邊，還鎖定行之間的間隙
• gap鎖的提出僅僅是為了防止插入幻影記錄而提出的

這種鎖定是為了防止其他事務(wù)插入新的行到已經(jīng)被鎖定的數(shù)據(jù)行之間，從而保證了事務(wù)的隔離性和一致性。

圖中id值為8的記錄加了gap鎖，意味著 不允許別的事務(wù)在id為3記錄后的間隙，即不允許（3，15）之間插入新記錄

類型

插入意向間隙鎖：

• 插入意向間隙鎖告訴其他事務(wù)這里即將發(fā)生插入操作，因此其他事務(wù)不應(yīng)該在該位置進(jìn)行插入

普通間隙鎖：

• 執(zhí)行 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 查詢時(shí)，InnoDB 會(huì)在查詢范圍內(nèi)的所有數(shù)據(jù)行上放置鎖，并在這些行之間的間隙上放置間隙鎖
• 普通間隙鎖用于防止其他事務(wù)在已鎖定的數(shù)據(jù)行之間插入新行

舉例

-- 有一個(gè)表 orders，其中包含 order_id 和 order_amount 字段
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_amount DECIMAL(10, 2)
);
?
INSERT INTO orders (order_amount) VALUES (100), (500), (1000);

現(xiàn)在，我們有兩個(gè)事務(wù) A 和 B。事務(wù) A 執(zhí)行一個(gè)范圍查詢：

-- 啟動(dòng)事務(wù) A
START TRANSACTION;
?
-- 獲取訂單金額在200 到 600之間的排他鎖
SELECT * FROM orders WHERE order_amount BETWEEN 200 AND 600 FOR UPDATE;
?
-- 事務(wù)A在200和600之間的間隙上放置了間隙鎖。這意味著其他事務(wù)不能在這個(gè)范圍內(nèi)插入新的行

示例 2: 插入意向間隙鎖

-- 啟動(dòng)事務(wù) B
START TRANSACTION;
-- 嘗試插入一個(gè)新的訂單
INSERT INTO orders (order_amount) VALUES (300);
#事務(wù)B將被阻塞，因?yàn)樗噲D在事務(wù)A已經(jīng)鎖定的間隙內(nèi)插入新的行

臨鍵鎖

官方的類型名稱為： LOCK_ORDINARY，我們也可以簡(jiǎn)稱為next-key鎖 。Next-Key Locks是在存儲(chǔ)引擎 innodb 、事務(wù)級(jí)別在可重復(fù)讀的情況下使用的數(shù)據(jù)庫(kù)鎖，innodb默認(rèn)的鎖就是臨鍵鎖

Next-Key Locks 是一種組合鎖，它同時(shí)包含了記錄鎖和間隙鎖。簡(jiǎn)單來說，會(huì)在一個(gè)記錄上放置一個(gè)記錄鎖，并且在該記錄間隙上放置一個(gè)間隙鎖。

舉例

-- 有一個(gè)表 orders，其中包含 order_id 和 order_amount 字段
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_amount DECIMAL(10, 2)
);
INSERT INTO orders (order_amount) VALUES (100), (500), (1000);

現(xiàn)在，我們有兩個(gè)事務(wù)A和B。事務(wù)A執(zhí)行一個(gè)范圍查詢：

-- 啟動(dòng)事務(wù) A
START TRANSACTION;
?
-- 獲取訂單金額在200到600 之間的排他鎖
SELECT * FROM orders WHERE order_amount BETWEEN 200 AND 600 FOR UPDATE;
-- 事務(wù)A會(huì)在 order_amount 為 500 的行上放置一個(gè)排他鎖，并在200和600之間的所有間隙上放置間隙鎖。這意味著其他事務(wù)不能在這個(gè)范圍內(nèi)插入新的行

現(xiàn)在，事務(wù) B 嘗試插入一個(gè)新的訂單：

-- 啟動(dòng)事務(wù) B
START TRANSACTION;
?
-- 嘗試插入一個(gè)新的訂單
INSERT INTO orders (order_amount) VALUES (300);
-- 事務(wù) B 將被阻塞，因?yàn)樗噲D在事務(wù) A 已經(jīng)鎖定的間隙內(nèi)插入新的行

如果您希望避免臨鍵鎖，可以將事務(wù)隔離級(jí)別設(shè)置為 READ COMMITTED：

-- 設(shè)置事務(wù)隔離級(jí)別為 READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 啟動(dòng)事務(wù) A
START TRANSACTION;
?
-- 獲取訂單金額在 200 到 600 之間的排他鎖
SELECT * FROM orders WHERE order_amount BETWEEN 200 AND 600 FOR UPDATE;
-- 在這種情況下，事務(wù) A 僅在匹配的行上放置排他鎖，而不會(huì)在行之間的間隙上放置間隙鎖

插入意向鎖

InnoDB規(guī)定：事務(wù)在等待的時(shí)候也需要在內(nèi)存中生成一個(gè)鎖結(jié)構(gòu)，表明有事務(wù)想在某個(gè)間隙中插入新記錄，但是現(xiàn)在在等待。InnoDB就把這種類型的鎖命名為 Insert Intention Locks ，官稱為插入意向鎖

工作原理：

假設(shè)事務(wù) T1 對(duì)區(qū)間 [10, 20] 之間的所有行以及這個(gè)區(qū)間的間隙持有 Next-Key Locks。這時(shí)，事務(wù) T2 嘗試在區(qū)間 [10, 20] 內(nèi)插入一行數(shù)據(jù)，比如插入 15。

• T2 產(chǎn)生插入意向鎖：由于 T1 持有該間隙上的鎖，T2 無法立即插入數(shù)據(jù)，但它會(huì)在內(nèi)存中創(chuàng)建一個(gè)插入意向鎖，表示它想要在間隙 [10, 20] 中插入數(shù)據(jù)。
• T2 等待：T2 的插入操作被阻塞，等待 T1 的事務(wù)結(jié)束
• T1 提交或回滾：當(dāng) T1 完成并釋放其鎖后，T2 的插入意向鎖變?yōu)橛行?/code>，T2 可以繼續(xù)插入數(shù)據(jù)。

-- 讀取數(shù)據(jù)并加鎖
SELECT id, name FROM users WHERE id = 1 FOR UPDATE;
?
-- 執(zhí)行更新操作
UPDATE users SET name = 'new_name' WHERE id = 1;

-- 假設(shè)有一張用戶表 users，包含 id、name 和 version 字段
-- 讀取數(shù)據(jù)
SELECT id, name, version FROM users WHERE id = 1;
?
-- 更新數(shù)據(jù)時(shí)檢查版本號(hào)
UPDATE users
SET name = 'new_name', version = version + 1
WHERE id = 1 AND version = current_version;

-- session 1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert INTO student VALUES(34,"周八","二班");
Query OK, 1 row affected (0.00 sec)
-- session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student lock in share mode;   #執(zhí)行完，當(dāng)前事務(wù)被阻塞
mysql> SELECT * FROM performance_schema.data_lock_waits\G;
*************************** 1. row ***************************
             ENGINE: INNODB
   REQUESTING_ENGINE_LOCK_ID: 140562531358232:7:4:9:140562535668584
REQUESTING_ENGINE_TRANSACTION_ID: 422037508068888
     REQUESTING_THREAD_ID: 64
      REQUESTING_EVENT_ID: 6
REQUESTING_OBJECT_INSTANCE_BEGIN: 140562535668584
    BLOCKING_ENGINE_LOCK_ID: 140562531351768:7:4:9:140562535619104
BLOCKING_ENGINE_TRANSACTION_ID: 15902
      BLOCKING_THREAD_ID: 64
       BLOCKING_EVENT_ID: 6
BLOCKING_OBJECT_INSTANCE_BEGIN: 140562535619104
1 row in set (0.00 sec)

#顯示加共享鎖
select ....  lock in share mode
#顯示加排它鎖
select ....  for update

Flush tables with read lock