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

mysql教學欄位介紹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 鎖）。

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
begin;
#select * from user;
	alter table user add column birthday datetime;
select * 從 user;

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

DDL 操作對錶加的是元資料寫鎖，對其他交易的元資料讀寫鎖都不相容；DML 操作對錶加的是元資料讀鎖，可與其他交易的元數據讀鎖共享，但與其他交易的元資料寫鎖不相容。

1.2.3 意向鎖定

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

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

select * from user where id=5 for update;

sessionA	sessionB
#begin;

###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）隔离级别下。

不知道大家还记不记得幻读？

幻读是指在同一事务中，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。

间隙锁的提出正是为了防止幻读中描述的幻影记录的插入而提出的，举个例子。

sessionA	sessionB
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 其实就是记录锁与记录锁前面间隙的间隙锁组合的产物，它既阻止了其他事务在间隙的插入操作，也阻止了其他事务对记录的修改操作。

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

首先来说唯一索引列的等值查询，这里的等值查询可以分为两种情况：命中与未命中。

当唯一索引列的等值查询命中时：

sessionA	sessionB
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 這一行被上鎖了。

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

---

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

##insert into user values (16,'面孔',16);

sessionA	sessionB
begin;
#select * from user where id=3 for update;
	#insert into user values (2,'反光鏡',2);（Blocked）
	update user set age=18 其中 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;

上表的執行結果是

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

根據執行結果可以知道

sessionA 給 user 表加的鎖定是間隙鎖定(1,5)。

所以，當唯一索引列的等值查詢未命中時，

會為id值所在的間隙加上間隙鎖定。

2.2 唯一索引列範圍查詢

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

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

#sessionAsessionBbegin;#select * from user where id insert into user values (1,'斯斯與帆',1);（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) ;

	Blocked）
	Blocked）
	Blocked）
	Blocked）

#此時

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## begin;select * from user where id））））））#

此时 sessionA 给 user 表加上的锁是Next-key Lock —— (minIndex,15]。

---

当边界值不存在于表中时，不可能命中，故只有未命中一种情况：

sessionB
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	sessionB
begin;
select * from user where id
	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]，与第一种情况一样。

综上所述，在对唯一索引进行范围查询时：

1. 会给范围中的记录加上记录锁，间隙加上间隙锁
2. 对于范围查询(大于/大于等于/小于/小于等于)是比较特殊的，它会将记录锁加到第一个边界之外的记录上，若其中有额外的间隙也会加上间隙锁（即会将 Next-key Lock 加到第一个边界之外的记录上）

需要注意的是，第一条中所说的间隙指的是，边界值所在的间隙，如间隙为(5,10)，查询条件为 id>7 时，这个间隙锁就是(5,10)，而不是(7,10)。

第二条举例1：查询条件为 idNext-key Lock 锁会加到 id=10 的记录上，被锁住的范围是(minIndex,10]。

第二条举例2：查询条件为 idNext-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 上的加锁情况：

sessionA	sessionB
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 上的加鎖情況是：

即索引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 這個語法，它的加鎖範圍（只討論普通索引）要更小一些，請看範例：

##update user set name ='痛仰' 其中 age=5;#insert into user values (6,'達達',6);（update user set name='痛仰' where age=10 and id=10;（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) ;

sessionA	sessionB
begin;
select * from user where age=10 limit 1 for update;
	insert into user values (2,'達達',2);
	Blocked）
	Blocked）

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

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

由此可見：

limit 語法只會將鎖定加到滿足條件的記錄，能夠減少加鎖範圍。

2.5 普通索引列範圍查詢

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

sessionAsessionBbegin; select * from user where age>8 and age#insert into user values (2,'達達',2);update user set name='痛仰' where age=5;insert into user values (6,'達達', 6);（#update user set name='痛仰' where age=10 and id=10 ;（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) ;

与普通索引列等值查询不同的是，范围查询比等值查询多了一个 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=10 and age<blockquote><p><strong>更多相关免费学习推荐：</strong><a href="https://www.php.cn/course/list/51.html" target="_blank"><strong>mysql教程</strong></a><strong>(视频)</strong></p></blockquote>

	Blocked）
	Blocked）
	Blocked）
	Blocked）
	Blocked）
	Blocked）

以上是我所理解的MySQL五：鎖及加鎖規則的詳細內容。更多資訊請關注PHP中文網其他相關文章！