個(gè)人中心
退出
通過(guò)各種簡(jiǎn)單案例，讓你徹底搞懂 MySQL 中的鎖機(jī)制與 MVCC
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
鎖的分類
表級(jí)鎖與行級(jí)鎖
共享鎖與排他鎖
意向鎖
行級(jí)鎖實(shí)現(xiàn)
記錄鎖
通過(guò)主鍵操作單個(gè)值
通過(guò)唯一索引操作單個(gè)值
間隙鎖
通過(guò)主鍵操作范圍值
通過(guò)唯一索引操作范圍值
Next-key 鎖
通過(guò)普通索引操作單個(gè)值
通過(guò)普通索引操作范圍值
無(wú)索引操作單個(gè)值或范圍值
插入意向鎖
鎖（Locking）是數(shù)據(jù)庫(kù)在并發(fā)訪問(wèn)時(shí)保證數(shù)據(jù)一致性和完整性的主要機(jī)制。在 MySQL 中，不同存儲(chǔ)引擎使用不同的加鎖方式；我們以 InnoDB 存儲(chǔ)引擎為例介紹 MySQL 中的鎖機(jī)制，其他存儲(chǔ)引擎中的鎖相對(duì)簡(jiǎn)單一些。
鎖的分類
表級(jí)鎖與行級(jí)鎖
MySQL 中的鎖可以按照粒度分為鎖定整個(gè)表的表級(jí)鎖（table-level locking）和鎖定數(shù)據(jù)行的行級(jí)鎖（row-level locking）：
表級(jí)鎖具有開(kāi)銷小、加鎖快的特性；表級(jí)鎖的鎖定粒度較大，發(fā)生鎖沖突的概率高，支持的并發(fā)度低；
行級(jí)鎖具有開(kāi)銷大，加鎖慢的特性；行級(jí)鎖的鎖定粒度較小，發(fā)生鎖沖突的概率低，支持的并發(fā)度高。
InnoDB 存儲(chǔ)引擎同時(shí)支持行級(jí)鎖（row-level locking）和表級(jí)鎖（table-level locking），默認(rèn)情況下采用行級(jí)鎖。
表級(jí)鎖適用于并發(fā)較低、以查詢?yōu)橹鞯膽?yīng)用，例如中小型的網(wǎng)站；MyISAM 和 MEMORY 存儲(chǔ)引擎采用表級(jí)鎖。
行級(jí)鎖適用于按索引條件高并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并發(fā)查詢的應(yīng)用，例如 OLTP 系統(tǒng)；InnoDB 和 NDB 存儲(chǔ)引擎實(shí)現(xiàn)了行級(jí)鎖。
共享鎖與排他鎖
InnoDB 實(shí)現(xiàn)了以下兩種類型的行鎖：
共享鎖（S）：允許獲得該鎖的事務(wù)讀取數(shù)據(jù)行（讀鎖），同時(shí)允許其他事務(wù)獲得該數(shù)據(jù)行上的共享鎖，并且阻止其他事務(wù)獲得數(shù)據(jù)行上的排他鎖。
排他鎖（X）：允許獲得該鎖的事務(wù)更新或刪除數(shù)據(jù)行（寫(xiě)鎖），同時(shí)阻止其他事務(wù)取得該數(shù)據(jù)行上的共享鎖和排他鎖。
這兩種行鎖之間的兼容性如下：
共享鎖和共享鎖可以兼容，排他鎖和其它鎖都不兼容。例如，事務(wù) A 獲取了一行數(shù)據(jù)的共享鎖，事務(wù) B 可以立即獲得該數(shù)據(jù)行的共享鎖，也就是鎖兼容；但是此時(shí)事務(wù) B 如果想獲得該數(shù)據(jù)行的排他鎖，則必須等待事務(wù) A 釋數(shù)據(jù)行上的共享鎖，此種情況存在鎖沖突。
默認(rèn)情況下，數(shù)據(jù)庫(kù)中的鎖都可以自動(dòng)獲?。坏且部梢允謩?dòng)為數(shù)據(jù)進(jìn)行加鎖。我們來(lái)看一個(gè)示例，首先創(chuàng)建一個(gè)表：
create table t(
id int auto_increment primary key,
c1 int,
c2 int,
c3 int
);
create unique index idx_t_c1 on t(c1);
create index idx_t_c2 on t(c2);
insert into t(c1,c2,c3) values (1,1,1),(2,3,4),(3,6,9);
其中，id 是主鍵；c1 上創(chuàng)建了一個(gè)唯一索引；c2 上創(chuàng)建了一個(gè)非唯一索引；c3 上沒(méi)有索引。
接下來(lái)的示例都使用 MySQL 默認(rèn)的隔離級(jí)別 Repeatable Read，除非另有說(shuō)明。
然后創(chuàng)建兩個(gè)數(shù)據(jù)庫(kù)連接 T1 和 T2，先在 T1 中鎖定一行數(shù)據(jù)：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 1 for share;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
+----+------+------+------+
1 row in set (0.00 sec)
我們?cè)谑聞?wù)中使用select ... for share語(yǔ)句獲得了數(shù)據(jù)行 id = 1 上的共享鎖；對(duì)于 MySQL 8.0 之前的版本，可以使用select ... lock in share mode命令。
由于 InnoDB 中的自動(dòng)提交 autocommit 默認(rèn)設(shè)置為 ON，我們必須在事務(wù)中為數(shù)據(jù)行加鎖；或者將 autocommit 設(shè)置為 OFF。
然后在 T2 中執(zhí)行以下語(yǔ)句：
-- T2
mysql> select * from t where id = 1 for share;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
+----+------+------+------+
1 row in set (0.00 sec)
結(jié)果顯示，在 T2 中成功獲取改行數(shù)據(jù)上的共享鎖。然后嘗試獲取排他鎖：
-- T2
mysql> select * from t where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
使用select ... for update命令獲取排他鎖，此時(shí)該命令會(huì)一直處于等待狀態(tài)并且最終超時(shí)。也就是說(shuō)，共享鎖和排他鎖不兼容。
最后，在 T1 中提交或者回滾事務(wù)：
-- T1
mysql> commit;
意向鎖
InnoDB 除了支持行級(jí)鎖，還支持由 MySQL 服務(wù)層實(shí)現(xiàn)的表級(jí)鎖（LOCK TABLES ... WRITE在指定的表加上表級(jí)排他鎖）。當(dāng)這兩種鎖同時(shí)存在時(shí)，可能導(dǎo)致沖突。例如，事務(wù) A 獲取了表中一行數(shù)據(jù)的讀鎖；然后事務(wù) B 申請(qǐng)?jiān)摫淼膶?xiě)鎖（例如修改表的結(jié)構(gòu)）。如果事務(wù) B 加鎖成功，那么它就應(yīng)該能修改表中的任意數(shù)據(jù)行，但是 A 持有的行鎖不允許修改鎖定的數(shù)據(jù)行。顯然數(shù)據(jù)庫(kù)需要避免這種問(wèn)題，B 的加鎖申請(qǐng)需要等待 A 釋放行鎖。
那么如何判斷事務(wù) B 是否應(yīng)該獲取表級(jí)鎖呢？首先需要看該表是否已經(jīng)被其他事務(wù)加上了表級(jí)鎖，然后依次查看該表中的每一行是否已經(jīng)被其他事務(wù)加上了行級(jí)鎖。這種方式需要遍歷整個(gè)表中的記錄，效率很低。為此，InnoDB 引入了另外一種鎖：意向鎖（Intention Lock）。
意向鎖屬于表級(jí)鎖，由 InnoDB 自動(dòng)添加，不需要用戶干預(yù)。意向鎖也分為共享和排他兩種方式：
意向共享鎖（IS）：事務(wù)在給數(shù)據(jù)行加行級(jí)共享鎖之前，必須先取得該表的 IS 鎖。
意向排他鎖（IX）：事務(wù)在給數(shù)據(jù)行加行級(jí)排他鎖之前，必須先取得該表的 IX 鎖。
此時(shí)，事務(wù) A 必須先申請(qǐng)?jiān)摫淼囊庀蚬蚕礞i，成功后再申請(qǐng)數(shù)據(jù)行的行鎖。事務(wù) B 申請(qǐng)表鎖時(shí)，數(shù)據(jù)庫(kù)查看該表是否已經(jīng)被其他事務(wù)加上了表級(jí)鎖；如果發(fā)現(xiàn)該表上存在意向共享鎖，說(shuō)明表中某些數(shù)據(jù)行上存在共享鎖，事務(wù) B 申請(qǐng)的寫(xiě)鎖會(huì)被阻塞。
因此，意向鎖是為了使得行鎖和表鎖能夠共存，從而實(shí)現(xiàn)多粒度的鎖機(jī)制。以下是表級(jí)鎖和表級(jí)意向鎖的兼容性：
InnoDB 表存在兩種表級(jí)鎖，一種是LOCK TABLES語(yǔ)句手動(dòng)指定的鎖，另一種是由 InnoDB 自動(dòng)添加的意向鎖。
簡(jiǎn)單來(lái)說(shuō)，意向鎖和表鎖之間只有共享鎖兼容，意向鎖和意向鎖之間都可以兼容。意向鎖的主要作用是表明某個(gè)事務(wù)正在或者即將鎖定表中的數(shù)據(jù)行。
我們以意向排他鎖 IX 為例，繼續(xù)上面的實(shí)驗(yàn)。先在 T1 中執(zhí)行以下加鎖語(yǔ)句：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 1 for update;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
+----+------+------+------+
1 row in set (0.00 sec)
在事務(wù)中為表 t 中的數(shù)據(jù)行 id = 1 加上了排他鎖，同時(shí)會(huì)為表 t 加上意向排他鎖。然后在 T2 中執(zhí)行以下語(yǔ)句：
-- T2
mysql> lock tables t read; -- lock tables t write;
lock tables ... read語(yǔ)句用于為表 t 加上表級(jí)共享鎖；因?yàn)橐庀蚺潘i和表級(jí)共享鎖沖突，所以 T2 一直等待 T1 釋放鎖。
也可以使用lock tables ... write語(yǔ)句為表 t 加上表級(jí)排他鎖；因?yàn)橐庀蚺潘i和表級(jí)排他鎖沖突，所以該語(yǔ)句也會(huì)一直等待 T1 釋放鎖。
當(dāng)我們?cè)?T1 中提交或者回滾事務(wù)：
-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
T2 自動(dòng)獲得該表上的共享鎖：
-- T2
Query OK, 0 rows affected (1 min 43.17 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
以上的unlock tables語(yǔ)句用于釋放該表上的排他鎖。
我們?cè)賮?lái)驗(yàn)證一下兩個(gè)意向排他鎖之間鎖的兼容性，先在 T1 中執(zhí)行以下加鎖語(yǔ)句：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 1 for update;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
+----+------+------+------+
1 row in set (0.00 sec)
T1 為表 t 加上了意向排他鎖和數(shù)據(jù)行 id = 1 上的排他鎖。然后在 T2 中執(zhí)行以下語(yǔ)句：
-- T2
mysql> select * from t where id = 2 for update;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 2 | 2 | 3 | 4 |
+----+------+------+------+
1 row in set (0.00 sec)
T2 成功為數(shù)據(jù)行 id = 2 加上的排他鎖，同時(shí)為表 t 加上了意向排他鎖。也就是說(shuō)，T1 和 T2 同時(shí)獲得了表 t 上的意向排他鎖，以及不同數(shù)據(jù)行上的行級(jí)排他鎖。InnoDB 通過(guò)行級(jí)鎖，實(shí)現(xiàn)了更細(xì)粒度的控制，能夠支持更高的并發(fā)更新和查詢。
最后在 T1 中提交或者回滾事務(wù)：
-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
行級(jí)鎖實(shí)現(xiàn)
InnoDB 通過(guò)給索引上的索引記錄加鎖的方式實(shí)現(xiàn)行級(jí)鎖。具體來(lái)說(shuō)，InnoDB 實(shí)現(xiàn)了三種行鎖的算法：記錄鎖（Record Lock）、間隙鎖（Gap Lock）和 Next-key 鎖（Next-key Lock）。
記錄鎖
記錄鎖（Record Lock）是針對(duì)索引記錄（index record）的鎖定。例如，SELECT * FROM t WHERE id = 1 FOR UPDATE;會(huì)阻止其他事務(wù)對(duì)表 t 中 id = 1 的數(shù)據(jù)執(zhí)行插入、更新，以及刪除操作。
通過(guò)主鍵操作單個(gè)值
id 是表 t 的主鍵，我們先在 T1 中執(zhí)行以下命令：
-- T1
mysql> SET GLOBAL innodb_status_output=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE id = 1 FOR UPDATE;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
+----+------+------+------+
1 row in set (0.00 sec)
全局變量 innodb_status_output 和 innodb_status_output_locks 用于控制 InnoDB 標(biāo)準(zhǔn)監(jiān)控和鎖監(jiān)控，我們利用監(jiān)控查看鎖的使用情況。然后 T1 鎖定了 id = 1 的記錄，此時(shí) T2 無(wú)法修改該記錄：
-- T2
mysql> SELECT * FROM t WHERE id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
使用SHOW ENGINE INNODB STATUS命令查看 InnoDB 監(jiān)控中關(guān)于鎖的事務(wù)數(shù)據(jù)，可以看到以下內(nèi)容：
---TRANSACTION 43764, ACTIVE 4 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 103, OS thread handle 140437513750272, query id 23734 localhost root
TABLE LOCK table `hrdb`.`t` trx id 43764 lock mode IX
RECORD LOCKS space id 101 page no 4 n bits 72 index PRIMARY of table `hrdb`.`t` trx id 43764 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000aaec; asc ;;
2: len 7; hex 820000008f0110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 80000001; asc ;;
日志顯示存在 2 個(gè)鎖結(jié)構(gòu)，鎖定了一個(gè)記錄；表 t 上存在 IX 鎖，主鍵索引上存在一個(gè) X 記錄鎖，同時(shí)還顯示了記錄對(duì)應(yīng)的數(shù)據(jù)值。注意but not gap，下文我們會(huì)介紹間隙鎖（Gap Lock）。最后在 T1 中釋放鎖：
-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
通過(guò)唯一索引操作單個(gè)值
c1 字段上存在唯一索引，我們先在 T1 中執(zhí)行以下命令：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c1 = 1 FOR UPDATE;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
+----+------+------+------+
1 row in set (0.00 sec)
使用SHOW ENGINE INNODB STATUS命令查看 InnoDB 監(jiān)控中關(guān)于鎖的事務(wù)數(shù)據(jù)，可以看到以下內(nèi)容：
---TRANSACTION 43761, ACTIVE 47 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 103, OS thread handle 140437513750272, query id 23722 localhost root
TABLE LOCK table `hrdb`.`t` trx id 43761 lock mode IX
RECORD LOCKS space id 101 page no 5 n bits 72 index idx_t_c1 of table `hrdb`.`t` trx id 43761 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
RECORD LOCKS space id 101 page no 4 n bits 72 index PRIMARY of table `hrdb`.`t` trx id 43761 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000aaec; asc ;;
2: len 7; hex 820000008f0110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 80000001; asc ;;
日志顯示存在 3 個(gè)鎖結(jié)構(gòu)，鎖定了 2 個(gè)記錄；表 t 上存在 IX 鎖，索引 idx_t_c1 上存在一個(gè) X 記錄鎖，主鍵索引上存在一個(gè) X 記錄鎖。最后在 T1 中釋放鎖：
-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
記錄鎖永遠(yuǎn)都是鎖定索引記錄，鎖定非聚集索引會(huì)先鎖定聚集索引。如果表中沒(méi)有定義索引，InnoDB 默認(rèn)為表創(chuàng)建一個(gè)隱藏的聚簇索引，并且使用該索引鎖定記錄。
間隙鎖
間隙鎖（Gap Lock）鎖定的是索引記錄之間的間隙、第一個(gè)索引之前的間隙或者最后一個(gè)索引之后的間隙。例如，SELECT * FROM t
WHERE c1 BETWEEN 1 and 10 FOR UPDATE;會(huì)阻止其他事務(wù)將 1 到 10 之間的任何值插入到 c1
字段中，即使該列不存在這樣的數(shù)據(jù)；因?yàn)檫@些值都會(huì)被鎖定。
通過(guò)主鍵操作范圍值
首先在 T1 中執(zhí)行以下命令鎖住數(shù)據(jù)范圍：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE id BETWEEN 1 and 10 FOR UPDATE;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 3 | 4 |
| 3 | 3 | 6 | 9 |
+----+------+------+------+
3 rows in set (0.00 sec)
表 t 中只有 3 條記錄，id = 4 的記錄不存在；即便如此，T2 仍然無(wú)法插入該記錄：
-- T2
mysql> insert into t(c1,c2,c3) values (4,8,12);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
再次使用SHOW ENGINE INNODB STATUS命令查看 InnoDB 監(jiān)控中關(guān)于鎖的事務(wù)數(shù)據(jù)，可以看到以下內(nèi)容：
---TRANSACTION 43765, ACTIVE 4 sec
3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 103, OS thread handle 140437513750272, query id 23741 localhost root
TABLE LOCK table `hrdb`.`t` trx id 43765 lock mode IX
RECORD LOCKS space id 101 page no 4 n bits 72 index PRIMARY of table `hrdb`.`t` trx id 43765 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000aaec; asc ;;
2: len 7; hex 820000008f0110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 80000001; asc ;;
RECORD LOCKS space id 101 page no 4 n bits 72 index PRIMARY of table `hrdb`.`t` trx id 43765 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 00000000aaec; asc ;;
2: len 7; hex 820000008f011d; asc ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000003; asc ;;
5: len 4; hex 80000004; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 00000000aaec; asc ;;
2: len 7; hex 820000008f012a; asc *;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 80000009; asc ;;
日志顯示存在 3 個(gè)鎖結(jié)構(gòu)，鎖定了 4 個(gè)索引記錄；表 t 上存在 IX 鎖，主鍵索引上存在 1 個(gè) X 記錄鎖（id = 1）和 3 個(gè)間隙鎖（(1, 2]、(2, 3]、supremum）；其中 supremum 代表了大于 3 的間隙（(3, positive infinity)）。實(shí)際上這里的間隙鎖屬于 Next-key 鎖，相當(dāng)于間隙鎖加記錄鎖，下文將會(huì)介紹。
此時(shí)，我們可以插入 id 小于 1 的數(shù)據(jù)；但是不能插入 id 大于 10 的數(shù)據(jù)。
通過(guò)唯一索引操作范圍值
首先在 T1 中執(zhí)行以下命令鎖住數(shù)據(jù)范圍：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c1 BETWEEN 1 and 10 FOR UPDATE;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 3 | 4 |
| 3 | 3 | 6 | 9 |
+----+------+------+------+
3 rows in set (0.00 sec)
再次使用SHOW ENGINE INNODB STATUS命令查看 InnoDB 監(jiān)控中關(guān)于鎖的事務(wù)數(shù)據(jù)，可以看到以下內(nèi)容：
---TRANSACTION 43824, ACTIVE 153 sec
3 lock struct(s), heap size 1136, 7 row lock(s)
MySQL thread id 103, OS thread handle 140437513750272, query id 23852 localhost root
TABLE LOCK table `hrdb`.`t` trx id 43824 lock mode IX
RECORD LOCKS space id 102 page no 5 n bits 72 index idx_t_c1 of table `hrdb`.`t` trx id 43824 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 4; hex 80000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000003; asc ;;
RECORD LOCKS space id 102 page no 4 n bits 72 index PRIMARY of table `hrdb`.`t` trx id 43824 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000ab2b; asc +;;
2: len 7; hex 82000000a70110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 00000000ab2b; asc +;;
2: len 7; hex 82000000a7011d; asc ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000003; asc ;;
5: len 4; hex 80000004; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 00000000ab2b; asc +;;
2: len 7; hex 82000000a7012a; asc *;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 80000009; asc ;;
日志顯示存在 3 個(gè)鎖結(jié)構(gòu)，鎖定了 7 個(gè)索引記錄；表 t 上存在 IX 鎖，索引 idx_t_c1 上存在 4 個(gè)間隙鎖（(negative infinity, 1]、(1, 2]、(2, 3]、supremum），其中 supremum 代表了大于 3 的間隙（(3, positive infinity)）；主鍵索引上存在 3 個(gè) X 記錄鎖，鎖定了 3 個(gè)主鍵值。實(shí)際上這里的間隙鎖屬于 Next-key 鎖，相當(dāng)于間隙鎖加記錄鎖，下文將會(huì)介紹。
此時(shí)，我們無(wú)法插入任何數(shù)據(jù)。
間隙可能會(huì)包含單個(gè)索引值、多個(gè)索引值或者沒(méi)有索引值。間隙鎖是性能和并發(fā)之間的一種權(quán)衡，只會(huì)在某些事務(wù)隔離級(jí)別（Repeatable Read）使用。
使用唯一索引來(lái)搜索單個(gè)值的語(yǔ)句不會(huì)使用間隙鎖（不包括搜索條件只包含多列唯一索引中部分列的情況；在這種情況下，仍然會(huì)使用間隙鎖）。例如，SELECT * FROM t WHERE id = 1 FOR UPDATE;只會(huì)對(duì) id = 1 的索引記錄加上記錄鎖，而不關(guān)心其他事務(wù)是否會(huì)在前面的間隙中插入數(shù)據(jù)。但是，如果 id 列上沒(méi)有索引或者創(chuàng)建的是非唯一索引，則該語(yǔ)句會(huì)鎖定前面的間隙。
需要注意的是，不同事務(wù)可以獲取一個(gè)間隙上互相沖突的鎖。例如，事務(wù) A 在一個(gè)間隙上獲取了共享的間隙鎖（間隙 S 鎖），事務(wù) B 可以在同一間隙上獲取排他的間隙鎖（間隙 X 鎖）。允許存在互相沖突的間隙鎖的原因在于，如果從索引中清除某個(gè)記錄，必須合并不同事務(wù)在記錄上獲取的間隙鎖。
InnoDB 間隙鎖的唯一目的是阻止其他事務(wù)在間隙中插入數(shù)據(jù)。間隙鎖可以共存，一個(gè)事務(wù)的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一個(gè)間隙上獲取間隙鎖。共享間隙鎖和排他間隙鎖之間沒(méi)有區(qū)別，彼此不沖突，它們的作用相同。
間隙鎖可以顯式禁用，例如將事務(wù)隔離級(jí)別設(shè)置為 READ COMMITTED。此時(shí)，查找和索引掃描不會(huì)使用間隙鎖，間隙鎖只用于外鍵約束和重復(fù)鍵的檢查。
使用 READ COMMITTED 隔離級(jí)別還會(huì)帶來(lái)其他影響，MySQL 在判斷 WHERE
條件之后會(huì)釋放不滿足條件的數(shù)據(jù)行上的記錄鎖。對(duì)于 UPDATE 語(yǔ)句，InnoDB 執(zhí)行“半一致”讀??；以便將數(shù)據(jù)的最新版本返回給
MySQL，MySQL 就可以確定該行是否滿足 UPDATE 中的 WHERE 條件。
Next-key 鎖
Next-key 鎖（Next-key Lock）相當(dāng)于一個(gè)索引記錄鎖加上該記錄之前的一個(gè)間隙鎖。
InnoDB 實(shí)現(xiàn)行級(jí)鎖的方式如下：當(dāng)搜索或掃描表索引時(shí)，在遇到的索引記錄上設(shè)置共享鎖或排它鎖。因此，InnoDB 行級(jí)鎖實(shí)際上是索引記錄鎖。一個(gè)索引記錄上的 next-key 鎖也會(huì)影響該索引記錄之前的“間隙”，如果一個(gè)會(huì)話在索引中的記錄 R 上有共享鎖或排它鎖，則另一個(gè)會(huì)話不能在 R 之前的間隙中插入新的索引記錄。
假設(shè)一個(gè)索引中包含數(shù)據(jù) 10、11、13 和 20。該索引中可能的 next-key 鎖包含以下范圍，其中圓括號(hào)表示排除端點(diǎn)值，方括號(hào)表示包含端點(diǎn)值：
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity) -- 顯示為 supermum
對(duì)于最后一個(gè)間隔，next-key 鎖將會(huì)鎖定最大索引值（20）之后的間隙；偽記錄“supermum”的值大于索引中任何值，它不是真正的索引記錄。(10, 11) 是一個(gè)間隙鎖的鎖定范圍，(10, 11] 是一個(gè) next-key 鎖的鎖定范圍。
默認(rèn)隔離級(jí)別（REPEATABLE READ ）下，InnoDB 通過(guò) next-key 鎖進(jìn)行查找和索引掃描，用于防止幻讀；因?yàn)樗鼤?huì)鎖定范圍值，不會(huì)導(dǎo)致兩次查詢結(jié)果的數(shù)量不同。
通過(guò)普通索引操作單個(gè)值
c2 字段上存在非唯一索引，我們先在 T1 中執(zhí)行以下命令：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c2 = 1 FOR UPDATE;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
+----+------+------+------+
1 row in set (0.00 sec)
使用SHOW ENGINE INNODB STATUS命令查看 InnoDB 監(jiān)控中關(guān)于鎖的事務(wù)數(shù)據(jù)，可以看到以下內(nèi)容：
---TRANSACTION 43830, ACTIVE 6 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 103, OS thread handle 140437513750272, query id 23871 localhost root
TABLE LOCK table `hrdb`.`t` trx id 43830 lock mode IX
RECORD LOCKS space id 102 page no 6 n bits 72 index idx_t_c2 of table `hrdb`.`t` trx id 43830 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
RECORD LOCKS space id 102 page no 4 n bits 72 index PRIMARY of table `hrdb`.`t` trx id 43830 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000ab2b; asc +;;
2: len 7; hex 82000000a70110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 80000001; asc ;;
RECORD LOCKS space id 102 page no 6 n bits 72 index idx_t_c2 of table `hrdb`.`t` trx id 43830 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000002; asc ;;
日志顯示存在 4 個(gè)鎖結(jié)構(gòu)，鎖定了 3 個(gè)索引項(xiàng)；表 t 上存在 IX 鎖，索引 idx_t_c2 上存在一個(gè) next-key 鎖（c2 = 1，鎖定了 (negative infinity, 1]）和一個(gè) X 間隙鎖（（1, 3）），主鍵索引上存在一個(gè) X 記錄鎖（id = 1）。
此時(shí)其他事務(wù)無(wú)法在 c2 中插入小于 3 的值，但是可以插入大于等于 3 的值。最后在 T1 中釋放鎖：
-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
如果索引有唯一屬性，則 InnnoDB 會(huì)自動(dòng)將 next-key 鎖降級(jí)為記錄鎖。我們?cè)谇懊嬉呀?jīng)給出了記錄鎖的示例。
通過(guò)普通索引操作范圍值
如果利用 c2 字段作為條件操作范圍值，加鎖情況與通過(guò)唯一索引（c1）操作范圍值相同?？梢詤⒖忌衔氖纠?。
無(wú)索引操作單個(gè)值或范圍值
c3 字段上沒(méi)有索引，我們先在 T1 中執(zhí)行以下命令：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c3 = 1 FOR UPDATE;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
+----+------+------+------+
1 row in set (0.00 sec)
使用SHOW ENGINE INNODB STATUS命令查看 InnoDB 監(jiān)控中關(guān)于鎖的事務(wù)數(shù)據(jù)，可以看到以下內(nèi)容：
---TRANSACTION 43848, ACTIVE 5 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 103, OS thread handle 140437513750272, query id 23917 localhost root
TABLE LOCK table `hrdb`.`t` trx id 43848 lock mode IX
RECORD LOCKS space id 102 page no 4 n bits 80 index PRIMARY of table `hrdb`.`t` trx id 43848 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000ab2b; asc +;;
2: len 7; hex 82000000a70110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 00000000ab2b; asc +;;
2: len 7; hex 82000000a7011d; asc ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000003; asc ;;
5: len 4; hex 80000004; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 00000000ab2b; asc +;;
2: len 7; hex 82000000a7012a; asc *;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 80000009; asc ;;
日志顯示存在 2 個(gè)鎖結(jié)構(gòu)，鎖定了 4 個(gè)索引項(xiàng)；表 t 上存在 IX 鎖，主鍵索引上存在 4 個(gè) next-key 鎖，鎖定了所有的主鍵范圍。此時(shí)其他事務(wù)無(wú)法插入任何數(shù)據(jù)。
最后在 T1 中釋放鎖：
-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
如果我們將語(yǔ)句修改為SELECT * FROM t WHERE c3 between 1 and 10 FOR UPDATE;，通過(guò)無(wú)索引的字段操作范圍值，也會(huì)鎖定主鍵的所有范圍。這也就是為什么 MySQL 推薦通過(guò)索引操作數(shù)據(jù)，最好是主鍵。
插入意向鎖
插入意向鎖（Insert Intention Lock）是在插入數(shù)據(jù)行之前，由 INSERT 操作設(shè)置的一種間隙鎖。插入意向鎖表示一種插入的意圖，如果插入到相同間隙中的多個(gè)事務(wù)沒(méi)有插入相同位置，則不需要互相等待。假設(shè)存在索引記錄 4 和 7。兩個(gè)事務(wù)分別嘗試插入 5 和 6，它們?cè)讷@取行排他鎖之前，分別使用插入意向鎖來(lái)鎖定 4 到 7 之間的間隙；但是不會(huì)相互阻塞，因?yàn)椴迦氲氖遣煌男小?/p>
在 T1 中對(duì) c2 大于 3 的索引記錄設(shè)置排它鎖，這個(gè)排它鎖包含記錄 6 之前的間隙鎖：
-- T1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE c2 > 3 FOR UPDATE;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 3 | 3 | 6 | 9 |
+----+------+------+------+
1 row in set (0.00 sec)
使用 SHOW ENGINE INNODB STATUS命令可以顯示鎖事務(wù)數(shù)據(jù)：
---TRANSACTION 43853, ACTIVE 38 sec
3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 103, OS thread handle 140437513750272, query id 23931 localhost root
TABLE LOCK table `hrdb`.`t` trx id 43850 lock mode IX
RECORD LOCKS space id 102 page no 6 n bits 72 index idx_t_c2 of table `hrdb`.`t` trx id 43850 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000003; asc ;;
RECORD LOCKS space id 102 page no 4 n bits 80 index PRIMARY of table `hrdb`.`t` trx id 43850 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 00000000ab2b; asc +;;
2: len 7; hex 82000000a7012a; asc *;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000006; asc ;;
5: len 4; hex 80000009; asc ;;
在 T2 中將 c2 = 4 插入間隙中，該事務(wù)在等待獲取獨(dú)占鎖時(shí)使用插入意向鎖：
-- T2
mysql> insert into t(c1,c2,c3) values (4,4,4);
使用 SHOW ENGINE INNODB STATUS命令可以顯示插入意向鎖事務(wù)數(shù)據(jù)：
---TRANSACTION 43854, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 106, OS thread handle 140437512111872, query id 23957 localhost root update
insert into t(c1,c2,c3) values (4,4,4)
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 102 page no 6 n bits 72 index idx_t_c2 of table
`hrdb`.`t` trx id 43854 lock_mode X locks gap before rec insert
intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000003; asc ;;
------------------
TABLE LOCK table `hrdb`.`t` trx id 43854 lock mode IX
RECORD LOCKS space id 102 page no 6 n bits 72 index idx_t_c2 of table
`hrdb`.`t` trx id 43854 lock_mode X locks gap before rec insert
intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000003; asc ;;
其中，locks gap before rec insert intention 表示插入意向鎖。由于 T1 鎖定了 3 和 6 之間的范圍，T2 需要等待；如果 T1 插入數(shù)據(jù) 4，T2 插入數(shù)據(jù) 5，互相之間不需要等待。
插入意向鎖的作用是為了提高并發(fā)插入的性能。間隙鎖不允許多個(gè)事務(wù)同時(shí)插入同一個(gè)索引間隙，但是插入意向鎖允許多個(gè)事務(wù)同時(shí)插入同一個(gè)索引間隙內(nèi)的不同數(shù)據(jù)值。
未完，待續(xù) 。。。
分類導(dǎo)航