1. 鎖概述

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制

在數(shù)據(jù)庫(kù)中，除了傳統(tǒng)的共享計(jì)算資源（CPU、RAM、IO）以外，數(shù)據(jù)也是需要爭(zhēng)搶的共享資源

如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問性能的一個(gè)重要因素，鎖對(duì)數(shù)據(jù)庫(kù)尤其重要

---

MySQL 中的鎖，按照鎖的粒度分，分為以下三類

1. 全局鎖：鎖定數(shù)據(jù)庫(kù)中的所有表
2. 表級(jí)鎖：每次操作鎖住整張表
3. 行級(jí)鎖：每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)

2. 全局鎖

2.1 介紹

全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖，加上全局鎖后，整個(gè)數(shù)據(jù)庫(kù)將處于只讀狀態(tài)，后續(xù)的 DML 語(yǔ)句、DDL 語(yǔ)句，以及更新操作的事務(wù)提交語(yǔ)句都將被阻塞

全局鎖的典型的使用場(chǎng)景是做全庫(kù)的數(shù)據(jù)備份，需要對(duì)所有的表進(jìn)行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的一致性和完整性

2.2 數(shù)據(jù)備份

接下來我們演示一下數(shù)據(jù)庫(kù)的數(shù)據(jù)備份操作

第一步：獲取全局鎖

flush tables with read lock;

第二步：使用 mysqldump 工具做數(shù)據(jù)庫(kù)的備份（注意，是在 Linux 終端中運(yùn)行）

mysqldump -u root -p123456 > /tmp/blog.sql

運(yùn)行指令后會(huì)有一個(gè)警告，因?yàn)槲覀儗⒚艽a顯式地展現(xiàn)出來了

mysqldump: [Warning] Using a password on the command line interface can be insecure.

第三步：釋放全局鎖

unlock tables;

2.3 使用全局鎖造成的問題

數(shù)據(jù)庫(kù)中加全局鎖，是一個(gè)比較重的操作，存在以下問題：

1. 如果在主庫(kù)上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就得停止
2. 如果在從庫(kù)上備份，那么在備份期間從庫(kù)不能執(zhí)行主庫(kù)同步過來的二進(jìn)制日志（binlog），會(huì)導(dǎo)致主從延遲現(xiàn)象的發(fā)生

---

在 InnoDB 引擎中，我們可以在備份時(shí)加上參數(shù) --single-transaction 參數(shù)來完成不加全局鎖的一致性數(shù)據(jù)備份

mysqldump --single-transaction -u root -p123456 > /tmp/blog.sql

3. 表級(jí)鎖

表級(jí)鎖，每次操作都會(huì)鎖住整張表，鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低，應(yīng)用在 MyISAM、InnoDB、BDB 等存儲(chǔ)引擎中

對(duì)于表級(jí)鎖，主要分為以下三類:

1. 表鎖
2. 元數(shù)據(jù)鎖（Meta Data Lock，MDL)
3. 意向鎖

3.1 表鎖

對(duì)于表鎖，分為兩類：

1. 表共享讀鎖（Read Lock，讀鎖）

2. 表獨(dú)占寫鎖（Write Lock，寫鎖）

3.1.1 語(yǔ)法

使用表鎖的語(yǔ)法：

• 加鎖：lock table 表01 read 表02 write;
• 釋放鎖：unlock tables;（與 MySQL 服務(wù)器斷開連接也會(huì)釋放鎖）

3.1.2 讀鎖

讀鎖的特點(diǎn)：

• 阻塞其他寫操作：如果一個(gè)事務(wù)已經(jīng)獲得了某個(gè)表的讀鎖，其他任何試圖對(duì)該表進(jìn)行寫操作的事務(wù)將會(huì)被阻塞，直到持有讀鎖的事務(wù)釋放鎖
• 不會(huì)阻塞其他讀操作：表鎖的讀鎖不會(huì)阻塞其他事務(wù)對(duì)同一表的讀操作。這意味著多個(gè)事務(wù)可以同時(shí)獲取表鎖的讀鎖，并且它們之間不會(huì)相互阻塞

---

讀鎖的示例圖

如果在開啟讀鎖的事務(wù)中執(zhí)行 DML 或 DDL 語(yǔ)句，會(huì)報(bào)錯(cuò)

lock tables tb_user read;

unlock tables;

3.1.3 寫鎖

寫鎖的特點(diǎn)：

• 排他性：寫鎖是排他的，這意味著在同一時(shí)刻，只有一個(gè)事務(wù)可以獲得給定資源上的寫鎖。其他任何事務(wù)或會(huì)話都不能同時(shí)對(duì)該資源進(jìn)行寫操作
• 阻塞其他寫操作：如果一個(gè)事務(wù)已經(jīng)獲得了某個(gè)資源上的寫鎖，其他任何試圖對(duì)該資源進(jìn)行寫操作的事務(wù)將會(huì)被阻塞，直到持有寫鎖的事務(wù)釋放鎖
• 阻塞讀操作：同樣，如果一個(gè)事務(wù)獲得了寫鎖，其他試圖讀取該資源的操作（在某些事務(wù)隔離級(jí)別下）也會(huì)被阻塞，直到寫鎖被釋放

---

寫鎖的示例圖

3.1.4 讀鎖和寫鎖的區(qū)別

讀鎖不會(huì)阻塞其他客戶端的讀，但是會(huì)阻塞其它客戶端的寫

寫鎖既會(huì)阻塞其他客戶端的讀，也會(huì)阻塞其他客戶端的寫

3.2 元數(shù)據(jù)鎖（Meta Data Lock，MDL）

元數(shù)據(jù)鎖的加鎖過程是系統(tǒng)自動(dòng)控制，無需顯式使用，在訪問一張表的時(shí)候會(huì)自動(dòng)加上

元數(shù)據(jù)鎖的主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動(dòng)事務(wù)的時(shí)候，不可以對(duì)元數(shù)據(jù)進(jìn)行寫入操作（為了避免 DML 語(yǔ)句和 DDL 語(yǔ)句之間的沖突，保證讀寫操作的正確性）

更簡(jiǎn)單的理解方式就是：在對(duì)表進(jìn)行增刪查改操作的時(shí)候，不能更改表的結(jié)構(gòu)

那什么是元數(shù)據(jù)呢，大家可以簡(jiǎn)單地理解為表結(jié)構(gòu)

---

MySQL 5.5 引入了元數(shù)據(jù)鎖，當(dāng)對(duì)一張表進(jìn)行增刪改查的時(shí)候，加元數(shù)據(jù)讀鎖（共享鎖），當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行變更操作的時(shí)候，加元數(shù)據(jù)寫鎖（排它鎖）

---

如何查看元數(shù)據(jù)鎖呢，可以運(yùn)行以下指令

select object_type, object_schema, object_name, lock_type, lock_duration, lock_status
from performance_schema.metadata_locks;

如果出現(xiàn)以下錯(cuò)誤，說明當(dāng)前用戶沒有訪問 performance_schema 數(shù)據(jù)庫(kù)的權(quán)限

SELECT command denied to user ‘wuyanzu’@‘127.0.0.1’ for table ‘metadata_locks’

3.3 意向鎖

3.3.1 案例引入

我們先來看以下場(chǎng)景

現(xiàn)在有一張 employee 表，線程 A 想更新 id 為 3 的記錄，在默認(rèn)的 MySQL 隔離級(jí)別下，執(zhí)行 update 語(yǔ)句，而且是根據(jù)主鍵更新，會(huì)自動(dòng)對(duì) id 為 3 的記錄加上行鎖

此時(shí)又來了一個(gè)線程 B，線程 B 想要獲取表鎖，大家想一下，線程 B 能直接拿到表鎖嗎？實(shí)際上，線程 B 不能直接拿到表鎖，因?yàn)楸礞i與行鎖之間會(huì)有沖突

線程 B 在獲取表鎖的時(shí)候，需要先根據(jù) employee 表中是否有行鎖以及行鎖的類型來判斷能不能加表鎖，要判斷是否有行鎖，需要一條一條數(shù)據(jù)掃描下來，看看有沒有行鎖以及有行鎖的話是什么類型的行鎖，性能比較低

為了避免執(zhí)行 DML 語(yǔ)句時(shí)，加的行鎖與加的表鎖沖突，InnoDB 引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少取得表鎖之前的檢查

我們來看一下，有了意向鎖之后，加鎖過程是怎樣的

線程 A 在執(zhí)行 update 語(yǔ)句的時(shí)候，會(huì)先針對(duì)這一行加上行鎖，接著再為表加上一個(gè)意向鎖，線程 B 要想獲取表鎖的話，只需要檢查表中意向鎖的情況，通過意向鎖的情況來判定是否能夠獲取表鎖，如果當(dāng)前表的意向鎖是與線程 B 要獲取的表鎖是兼容的話，直接獲取表鎖，如果當(dāng)前表的意向鎖是與線程 B 要獲取的表鎖不兼容，線程 B 就會(huì)一直處于阻塞狀態(tài)，直到線程 A 釋放行鎖和意向鎖

大家會(huì)發(fā)現(xiàn)，當(dāng)引入了意向鎖之后，線程 B 想要獲取表鎖的話，就不用逐行掃描數(shù)據(jù)，可以直接根據(jù)表的意向鎖的情況來判斷能否獲取表鎖

3.3.2 意向鎖的分類

意向鎖分為兩種：

1.意向共享鎖（IS，Intention Shared Lock）：由語(yǔ)句 select … lock in share mode 添加

2.意向排他鎖（IX，Intention Exclusive Lock）：由 insert、update、delete、select … for update 添加

---

意向鎖與表鎖的互斥情況：

• 意向共享鎖：與表鎖的共享鎖兼容，與表鎖的排它鎖互斥
• 意向排他鎖：與表鎖的共享鎖及表鎖的排它鎖都互斥

但意向鎖之間不會(huì)互斥，怎么理解呢

理解意向鎖之間不互斥的關(guān)鍵在于了解不同類型的意向鎖以及它們的作用：

1. 意向共享鎖 (IS): 當(dāng)事務(wù)想要獲取一個(gè)表上的共享鎖（讀取數(shù)據(jù)）時(shí)，它會(huì)先申請(qǐng)一個(gè)意向共享鎖。這個(gè)鎖表示事務(wù)有意向在表的某一行或某些行上獲取共享鎖
2. 意向排他鎖 (IX): 當(dāng)事務(wù)想要獲取一個(gè)表上的排他鎖（寫入數(shù)據(jù)）時(shí)，它會(huì)先申請(qǐng)一個(gè)意向排他鎖。這個(gè)鎖表示事務(wù)有意向在表的某一行或某些行上獲取排他鎖

意向鎖的特點(diǎn)：

• 非獨(dú)占性：多個(gè)事務(wù)可以同時(shí)持有同一表上的 IS 鎖，因?yàn)檫@些事務(wù)都只是表明了讀取某些行的意圖，并沒有實(shí)際鎖定任何特定的行
• 兼容性：IS 鎖和 IX 鎖之間是不互斥的。這意味著如果一個(gè)事務(wù)已經(jīng)持有了 IS 鎖，其他事務(wù)仍然可以獲取 IX 鎖，反之亦然
• 輔助作用：意向鎖本身并不直接鎖定任何行，而是作為輔助鎖來幫助系統(tǒng)決定是否授予更具體的鎖類型（如 S 鎖或 X 鎖）

例子說明：

• 假設(shè)事務(wù) T1 獲取了一個(gè)表上的 IS 鎖，這表示 T1 有意向讀取該表的某些行
• 另一個(gè)事務(wù) T2 也可以獲取該表上的 IX 鎖，表示 T2 有意向更新該表的某些行
• 在這種情況下，T1 和 T2 都可以在不沖突的情況下進(jìn)行操作，只要他們不嘗試在同一行上執(zhí)行互斥的操作

總結(jié)：

意向鎖之間不互斥是因?yàn)樗鼈儍H僅表達(dá)了事務(wù)對(duì)于數(shù)據(jù)操作的“意向”，而不是直接對(duì)數(shù)據(jù)進(jìn)行鎖定。意向鎖的存在是為了后續(xù)更細(xì)粒度的鎖請(qǐng)求做準(zhǔn)備，并且它們的設(shè)計(jì)目標(biāo)是為了減少鎖之間的等待，提高并發(fā)處理能力

---

可以通過以下 SQL 語(yǔ)句查看意向鎖和行鎖的加鎖情況

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

4. 行級(jí)鎖

4.1 介紹

行級(jí)鎖，每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度最高，應(yīng)用在 InnoDB 存儲(chǔ)引擎中

InnoDB 的數(shù)據(jù)是基于索引組織的，行鎖是通過對(duì)索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，而不是對(duì)記錄加的鎖，對(duì)于行級(jí)鎖，主要分為三類

第一類：行鎖（Record Lock），鎖定單個(gè)行記錄的鎖，防止其他事務(wù)對(duì)此行進(jìn)行 update 和 delete 操作，在 RC、RR 隔離級(jí)別下都支持

第二類：間隙鎖（Gap Lock）：鎖定索引記錄間隙（不含該記錄），確保案引記錄間隙不變，防止其他事務(wù)在這個(gè)間隙進(jìn)行 insert 操作，產(chǎn)生幻讀，在 RR 隔離級(jí)別下都支持

第三類：臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，同時(shí)鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙 Gap，在 RR 隔離級(jí)別下支持

4.2 行鎖

InnoDB 實(shí)現(xiàn)了以下兩種類型的行鎖：

1. 共享鎖(S)：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排它鎖
2. 排他鎖(X)：允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖

---

共享鎖和排他鎖之間的兼容情況

---

在進(jìn)行增刪查改語(yǔ)句的時(shí)候，所加的行鎖類型情況如下

---

默認(rèn)情況下，InnoDB 引擎在 REPEATABLE READ 事務(wù)隔離級(jí)別運(yùn)行，InnoDB 使用 Next-Key Locks 鎖（臨鍵鎖）進(jìn)行搜索和索引掃描，以防止幻讀

• 針對(duì)唯一索引進(jìn)行檢索時(shí)，對(duì)已存在的記錄進(jìn)行等值匹配時(shí)，將會(huì)自動(dòng)優(yōu)化為行鎖
• lnnoDB 的行鎖是針對(duì)于索引加的鎖，不通過索引條件檢索數(shù)據(jù)，那么 InnoDB 將對(duì)表中的所有記錄加鎖，此時(shí)就會(huì)升級(jí)為表鎖

4.2.1 測(cè)試行鎖之間的互斥性

接下來我們來演示一下行鎖，用兩個(gè)客戶端分別連接 MySQL 服務(wù)器，用 student 表來測(cè)試

student 表的數(shù)據(jù)如下，其中 id 為主鍵，name 字段和 no 字段沒有建立索引

在第一個(gè)連接中開啟一個(gè)新事務(wù)

begin;

接著運(yùn)行以下 SQL 語(yǔ)句

select *
from student
where id = 1;

當(dāng)執(zhí)行以上 SQL 語(yǔ)句后，到底有沒有對(duì) id 為 1 的這一行數(shù)據(jù)加鎖呢

我們?cè)诘诙€(gè)連接中運(yùn)行以下 SQL 語(yǔ)句查看表的加鎖情況

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

可以看到 data_locks 表中沒有任何數(shù)據(jù)，說明簡(jiǎn)單的 select 語(yǔ)句不會(huì)加上行鎖

---

我們?cè)诘谝粋€(gè)連接中執(zhí)行以下 SQL 語(yǔ)句

select *
from student
where id = 1 lock in share mode;

在第二個(gè)連接中查看是否加了鎖

可以看到 data_locks 表中有兩條數(shù)據(jù)，第一條數(shù)據(jù)的 lock_type 字段為 TABLE，表明加的是表鎖， lock_mode 字段是 IS，表明加的是意向共享鎖

我們重點(diǎn)關(guān)注第二條數(shù)據(jù)，第二條數(shù)據(jù)的 lock_type 字段為 RECORD，表明加的是行鎖，lock_mode 字段是 S 和 REC_NOT_GAP，S 代表共享鎖，REC_NOT_GAP代表沒有間隙

---

那共享鎖和共享鎖之間能不能兼容呢，可以

我們?cè)诘诙€(gè)連接中開啟一個(gè)新事務(wù)

begin;

執(zhí)行同樣的 SQL 語(yǔ)句

select *
from student
where id = 1 lock in share mode;

接著在第二個(gè)連接中再次查看 student 表的加鎖情況

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data
from performance_schema.data_locks;

可以看到，此時(shí) student 表中有四條記錄，說明兩個(gè)共享鎖之間是兼容的

---

我們提交第二個(gè)連接的事務(wù)后，再次在第二個(gè)連接中開啟一個(gè)新事務(wù)

接著在第二個(gè)連接中運(yùn)行以下 SQL 語(yǔ)句

update student
set name = '李四'
where id = 3;

可以發(fā)現(xiàn)，執(zhí)行 SQL 語(yǔ)句時(shí)并沒有阻塞，SQL 語(yǔ)句也執(zhí)行成功了

我們?cè)诘诙€(gè)連接中運(yùn)行以下 SQL 語(yǔ)句（操作 id = 1 的數(shù)據(jù)）

update student
set name = '王五'
where id = 1;

可以發(fā)現(xiàn)，光標(biāo)一直在閃動(dòng)，說明處于阻塞狀態(tài)，因?yàn)榈谝粋€(gè)連接已經(jīng)對(duì) id 為 1 的數(shù)據(jù)添加了共享鎖，第二個(gè)連接想要對(duì) id 為 1 的數(shù)據(jù)添加排他鎖，而共享鎖和排他鎖之間是有沖突的，所以第二個(gè)連接需要等待第一個(gè)連接釋放共享鎖后才能獲取到排他鎖

由于等待獲取排他鎖的時(shí)間過長(zhǎng)，第二個(gè)連接出現(xiàn)了以下錯(cuò)誤（如果光標(biāo)一直在閃爍，可以通過 CTRL + C 快捷鍵中斷 SQL 語(yǔ)句操作）

---

我們提交第一個(gè)連接中的事務(wù)后，在第二個(gè)連接中再次查看 student 表中的加鎖情況

可以發(fā)現(xiàn)，第一個(gè)連接提交事務(wù)后，第二個(gè)連接獲取到了 id 為 3 這一行數(shù)據(jù)的排他鎖

在第二個(gè)連接中提交事務(wù)，再次查看 student 表中的加鎖情況

發(fā)現(xiàn)鎖已經(jīng)釋放了，但 id 為 1 的這一行數(shù)據(jù)的 name 字段并沒有被修改成王五

4.2.2 測(cè)試行鎖升級(jí)為表鎖的情況

我們分別在第一個(gè)連接和第二個(gè)連接中開啟事務(wù)，接著在第一個(gè)連接中執(zhí)行以下 SQL 語(yǔ)句

update student
set name = 'Lei'
where name = '李四';

按理說，執(zhí)行 SQL 語(yǔ)句后會(huì)對(duì) id 為 3 的這一行數(shù)據(jù)添加一個(gè)行鎖

我們?cè)诘诙€(gè)連接中執(zhí)行以下 SQL 語(yǔ)句

update student
set name = '吳彥祖'
where id = 4;

發(fā)現(xiàn) SQL 語(yǔ)句在執(zhí)行時(shí)被阻塞了，按理說，第一個(gè)連接鎖的是 id 為 3 的這一行數(shù)據(jù)，為什么會(huì)影響到第二個(gè)連接修改 id 為 4 的數(shù)據(jù)呢

因?yàn)榈谝粋€(gè)連接更新數(shù)據(jù)的時(shí)候，是根據(jù) name 字段進(jìn)行更新的，而 name 字段沒有建立索引，所以會(huì)鎖住表中的所有記錄，導(dǎo)致行鎖升級(jí)為表鎖

我們提交第一個(gè)連接的事務(wù)后，第二個(gè)連接的更新操作就立刻完成了

---

接下來我們提交第二個(gè)連接的事務(wù)，然后為 name 字段建立一個(gè)索引

create index index_student_name on student (name);

再次重復(fù)以上測(cè)試過程，發(fā)現(xiàn)第一個(gè)連接更新 id 為 3 的這一行數(shù)據(jù)時(shí)不會(huì)再影響到第二個(gè)連接修改 id 為 4 的數(shù)據(jù)

4.3 間隙鎖&臨鍵鎖

默認(rèn)情況下，InnoDB 在 REPEATABLE READ 事務(wù)隔離級(jí)別運(yùn)行，InnoDB 使用 Next-Key Locks 鎖進(jìn)行搜索和索引掃描，以防止幻讀

1. 間隙鎖（Gap Locks）：用于防止幻讀，對(duì)表中的一個(gè)范圍進(jìn)行鎖定
2. 臨鍵鎖（Next-Key Locks）：是間隙鎖和行鎖的組合，用于防止幻讀、修改或刪除范圍內(nèi)的行

---

• 間隙鎖的唯一目的就是防止其他事務(wù)插入間隙
• 間隙鎖可以共存，一個(gè)事務(wù)采用的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一間隙上采用間隙鎖

針對(duì)索引上的等值查詢、唯一索引上的范圍查詢以及普通索引上的范圍查詢的三種情況，間隙鎖和臨鍵鎖的具體情況有所不同：

1. 索引上的等值查詢（唯一索引）：給不存在的記錄加鎖時(shí)，優(yōu)化為間隙鎖
2. 索引上的等值查詢（普通索引）：向右遍歷至最后一個(gè)值不滿足查詢需求時(shí)，臨鍵鎖（Next-Key Locks）退化為間隙鎖
3. 索引上的范圍查詢（唯一索引）：遍歷到不滿足條件的第一個(gè)值為止

需要注意的是，間隙鎖和臨鍵鎖的產(chǎn)生還取決于事務(wù)的隔離級(jí)別，在某些隔離級(jí)別下，如 READ COMMITTED，間隙鎖和臨鍵鎖可能不會(huì)自動(dòng)產(chǎn)生

---

在演示間隙鎖和臨鍵鎖之前，我們先準(zhǔn)備一個(gè)新的 teacher 表，表結(jié)構(gòu)和數(shù)據(jù)如下

/*Navicat Premium Data TransferSource Server         : Source Server Type    : MySQLSource Server Version : 80037 (8.0.37-0ubuntu0.22.04.3)Source Host           : Source Schema         : blogTarget Server Type    : MySQLTarget Server Version : 80037 (8.0.37-0ubuntu0.22.04.3)File Encoding         : 65001Date: 30/08/2024 19:01:15
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`age` int NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `teacher_id_index`(`id` ASC) USING BTREE,INDEX `index_teacher_age`(`age` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '張三', 1);
INSERT INTO `teacher` VALUES (3, '李四', 3);
INSERT INTO `teacher` VALUES (7, 'Amy', 7);
INSERT INTO `teacher` VALUES (8, '王五', 8);
INSERT INTO `teacher` VALUES (11, '趙六', 11);
INSERT INTO `teacher` VALUES (19, '錢七', 19);
INSERT INTO `teacher` VALUES (25, '老八', 25);SET FOREIGN_KEY_CHECKS = 1;

teacher 表的數(shù)據(jù)如下

---

接下來我們開始測(cè)試

在第一個(gè)連接中開啟事務(wù)，然后在第一個(gè)連接中運(yùn)行以下 SQL 語(yǔ)句

update teacher
set name = 'Jane Doe'
where id = 5;

由于 id 為 5 的這一行數(shù)據(jù)并不存在，InnoDB 會(huì)怎樣加鎖呢，是把 id 為 5 的這一行數(shù)據(jù)鎖住了嗎，實(shí)際上并不是

InnoDB 會(huì)對(duì) 3 和 8 之間的間隙加上一個(gè)間隙鎖，具體的范圍是(3, 8)，也就是左開右開的區(qū)間，不含包 3 和 8

在第二個(gè)連接中查看 teacher 表的加鎖情況

可以看到，teacher 表有兩把鎖，一把是表鎖，一把是間隙鎖，間隙鎖使用的索引為主鍵

既然鎖的是 id 范圍在 (3, 8) 之間的數(shù)據(jù)，那我插入一條 id 為 7 的數(shù)據(jù)會(huì)怎么樣呢

我們?cè)诘诙€(gè)連接中開啟一個(gè)事務(wù)，接著在第二個(gè)連接中執(zhí)行以下 SQL 語(yǔ)句

insert into teacher
values (7, 'Amy', 30);

可以發(fā)現(xiàn) SQL 處于阻塞狀態(tài)，因?yàn)榈谝粋€(gè)連接的事務(wù)給 id 范圍在 (3, 8) 之間的數(shù)據(jù)加了一個(gè)間隙鎖，在第一個(gè)連接的事務(wù)提交之前，第二個(gè)連接的插入操作都會(huì)被阻塞

---

當(dāng)我們進(jìn)行等值查詢的時(shí)候，如果不是唯一索引，會(huì)發(fā)生什么現(xiàn)象呢

我們?cè)诩有墟i的時(shí)候，是針對(duì)索引加的鎖，而索引是 B+Tree 數(shù)據(jù)結(jié)構(gòu)，B+Tree 的葉子結(jié)點(diǎn)形成的是一個(gè)雙向鏈表

以上圖為例，假如我們要根據(jù)二級(jí)索引的值是否等于 18 來查詢，并且給二級(jí)索引 等于 18 這行數(shù)據(jù)添加一個(gè)共享鎖，此時(shí)是僅僅把二級(jí)索引等于 18 的這條記錄給鎖住就完事了嗎？并不是的，因?yàn)楫?dāng)前的二級(jí)索引沒有唯一性約束，那以后是不是就有可能在 18 的左邊插入一條二級(jí)索引為 18 的記錄，也有可能在 18 的右邊插入一條二級(jí)索引為 18 的記錄

所以，InnoDB 會(huì)對(duì) (16, 18) 之間的間隙和 (18, 29) 之間的間隙加鎖

---

接下來我們用 teacher 表來演示一下

首先為 age 字段建立一個(gè)普通的非唯一索引

create index index_teacher_age on teacher (age);

我們?cè)诘谝粋€(gè)連接中開啟事務(wù)，接著運(yùn)行以下 SQL 語(yǔ)句

select *
from teacher
where age = 3 lock in share mode;

接著在第二個(gè)連接中查詢 teacher 表的加鎖情況

可以發(fā)現(xiàn)有四條記錄，第一行是表的意向鎖，我們重點(diǎn)關(guān)注后面三行

第二行的 lock_data 為 3, 3，lock_mode 為 S，是一個(gè)臨鍵鎖，表示要把 age 為 3 的這條記錄以及 age 在 (1,3) 區(qū)間的間隙鎖住（第一個(gè) 3 是 二級(jí)索引，也就是 age 字段，第二個(gè) 3 是主鍵索引，也就是 id 字段）

第三行的 lock_data 為 3，且索引類型為主鍵，表示鎖住了 id 為 3 對(duì)應(yīng)的記錄

第四行的 lock_data 為 7, 7，lock_mode 含有 GAP，是一個(gè)間隙，表示要把 age 在 (3, 7) 區(qū)間的間隙鎖住

最后提交第一個(gè)連接中的事務(wù)

---

我們?cè)诘谝粋€(gè)連接中開啟事務(wù)，然后在第一個(gè)連接中執(zhí)行以下 SQL 語(yǔ)句

select *
from teacher
where id >= 19 lock in share mode

接著在第二個(gè)連接中查詢 teacher 表的加鎖情況

可以看到有 4 行記錄，第一行是表的意向鎖，我們重點(diǎn)關(guān)注后面三行

第二行表明對(duì) id 為 19 的這一行記錄加了一個(gè)行鎖

第三行表示加了一個(gè)臨鍵鎖，lock_data 的 supremum pseudo-read 屬性可以理解為正無窮大，鎖的是 (25, +∞) 之間的間隙

第四行表示加了一個(gè)臨鍵鎖，鎖的是 (19, 25) 之間的間隙和 id = 25 對(duì)應(yīng)的記錄

---

間隙鎖鎖的是間隙，不包含對(duì)應(yīng)的數(shù)據(jù)記錄，而臨鍵鎖既會(huì)鎖住數(shù)據(jù)記錄，也會(huì)鎖定數(shù)據(jù)記錄之前（或之后）的間隙

其實(shí)間隙鎖和臨鍵鎖不用刻意記憶，關(guān)鍵是搞清楚 InnoDB 引擎為什么要加上間隙鎖和臨鍵鎖