MySQL中鎖及加鎖規則是什么

小編給大家分享一下MySQL中鎖及加鎖規則是什么，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

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），元數據鎖會在客戶端訪問表的時候自動加鎖，在客戶端提交事務時釋放鎖，它防止了以下場景出現的問題：

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

DDL 操作對表加的是元數據寫鎖，對其他事務的元數據讀寫鎖都不兼容；DML 操作對表加的是元數據讀鎖，可與其他事務的元數據讀鎖共享，但與其他事務的元數據寫鎖不兼容。

1.2.3 意向鎖

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

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

例如，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鎖）。

這兩種行鎖的兼容關系與上面元數據鎖的兼容關系是一樣的，可以用下面的表格表示。

而從行鎖的粒度繼續細分，又可以分為記錄鎖（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）隔離級別下。

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

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

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

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 其實就是記錄鎖與記錄鎖前面間隙的間隙鎖組合的產物，它既阻止了其他事務在間隙的插入操作，也阻止了其他事務對記錄的修改操作。

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 唯一索引列等值查詢

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

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

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

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

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

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

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

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

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

2.2 唯一索引列范圍查詢

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

首先來看邊界值存在于表中，但未命中的情況：

此時 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]。

當邊界值存在于表中，同時命中的情況：

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

當邊界值不存在于表中時，不可能命中，故只有未命中一種情況：

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

綜上所述，在對唯一索引進行范圍查詢時：

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 中可能存在的行鎖是4個記錄鎖以及5個間隙鎖。

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

由上表的語句及執行結果來看，索引 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 這個語法，它的加鎖范圍（只討論普通索引）要更小一些，請看示例：

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

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

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

2.5 普通索引列范圍查詢

接下來看普通索引列上的范圍查詢（這里只討論索引 age 的加鎖范圍，主鍵索引的加鎖如果存在回表會鎖住對應的id值）：

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

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

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

3. 溫故知新

1. MySQL 中的鎖按粒度來分可以分為幾種？分別描述一下。
2. MySQL 中行鎖的加鎖規則？
3. 請說出下面幾條 SQL 的加鎖區域：

select * from user where age=10 for update;select * from user where age>=10 and age<11 for update;select id from user where age>=10 and age<11 for update;復制代碼

以上是MySQL中鎖及加鎖規則是什么的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！