在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

通過各種簡單案例，讓你徹底搞懂 MySQL 中的鎖機制與 MVCC

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        鎖的分類
            表級鎖與行級鎖
            共享鎖與排他鎖
            意向鎖
        行級鎖實現(xiàn)
            記錄鎖
                通過主鍵操作單個值
                通過唯一索引操作單個值
            間隙鎖
                通過主鍵操作范圍值
                通過唯一索引操作范圍值
            Next-key 鎖
                通過普通索引操作單個值
                通過普通索引操作范圍值
                無索引操作單個值或范圍值
            插入意向鎖

鎖（Locking）是數(shù)據(jù)庫在并發(fā)訪問時保證數(shù)據(jù)一致性和完整性的主要機制。在 MySQL 中，不同存儲引擎使用不同的加鎖方式；我們以 InnoDB 存儲引擎為例介紹 MySQL 中的鎖機制，其他存儲引擎中的鎖相對簡單一些。
鎖的分類
表級鎖與行級鎖

MySQL 中的鎖可以按照粒度分為鎖定整個表的表級鎖（table-level locking）和鎖定數(shù)據(jù)行的行級鎖（row-level locking）：

    表級鎖具有開銷小、加鎖快的特性；表級鎖的鎖定粒度較大，發(fā)生鎖沖突的概率高，支持的并發(fā)度低；
    行級鎖具有開銷大，加鎖慢的特性；行級鎖的鎖定粒度較小，發(fā)生鎖沖突的概率低，支持的并發(fā)度高。

InnoDB 存儲引擎同時支持行級鎖（row-level locking）和表級鎖（table-level locking），默認(rèn)情況下采用行級鎖。

    表級鎖適用于并發(fā)較低、以查詢?yōu)橹鞯膽?yīng)用，例如中小型的網(wǎng)站；MyISAM 和 MEMORY 存儲引擎采用表級鎖。
    行級鎖適用于按索引條件高并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用，例如 OLTP 系統(tǒng)；InnoDB 和 NDB 存儲引擎實現(xiàn)了行級鎖。

共享鎖與排他鎖

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

    共享鎖（S）：允許獲得該鎖的事務(wù)讀取數(shù)據(jù)行（讀鎖），同時允許其他事務(wù)獲得該數(shù)據(jù)行上的共享鎖，并且阻止其他事務(wù)獲得數(shù)據(jù)行上的排他鎖。
    排他鎖（X）：允許獲得該鎖的事務(wù)更新或刪除數(shù)據(jù)行（寫鎖），同時阻止其他事務(wù)取得該數(shù)據(jù)行上的共享鎖和排他鎖。

這兩種行鎖之間的兼容性如下：
 

共享鎖和共享鎖可以兼容，排他鎖和其它鎖都不兼容。例如，事務(wù) A 獲取了一行數(shù)據(jù)的共享鎖，事務(wù) B 可以立即獲得該數(shù)據(jù)行的共享鎖，也就是鎖兼容；但是此時事務(wù) B 如果想獲得該數(shù)據(jù)行的排他鎖，則必須等待事務(wù) A 釋數(shù)據(jù)行上的共享鎖，此種情況存在鎖沖突。

默認(rèn)情況下，數(shù)據(jù)庫中的鎖都可以自動獲??；但是也可以手動為數(shù)據(jù)進(jìn)行加鎖。我們來看一個示例，首先創(chuàng)建一個表：

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)建了一個唯一索引；c2 上創(chuàng)建了一個非唯一索引；c3 上沒有索引。

    接下來的示例都使用 MySQL 默認(rèn)的隔離級別 Repeatable Read，除非另有說明。

然后創(chuàng)建兩個數(shù)據(jù)庫連接 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)

 

我們在事務(wù)中使用select ... for share語句獲得了數(shù)據(jù)行 id = 1 上的共享鎖；對于 MySQL 8.0 之前的版本，可以使用select ... lock in share mode命令。

    由于 InnoDB 中的自動提交 autocommit 默認(rèn)設(shè)置為 ON，我們必須在事務(wù)中為數(shù)據(jù)行加鎖；或者將 autocommit 設(shè)置為 OFF。

然后在 T2 中執(zhí)行以下語句：

-- 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命令獲取排他鎖，此時該命令會一直處于等待狀態(tài)并且最終超時。也就是說，共享鎖和排他鎖不兼容。

最后，在 T1 中提交或者回滾事務(wù)：

-- T1
mysql> commit;

  

意向鎖

InnoDB 除了支持行級鎖，還支持由 MySQL 服務(wù)層實現(xiàn)的表級鎖（LOCK TABLES ... WRITE在指定的表加上表級排他鎖）。當(dāng)這兩種鎖同時存在時，可能導(dǎo)致沖突。例如，事務(wù) A 獲取了表中一行數(shù)據(jù)的讀鎖；然后事務(wù) B 申請該表的寫鎖（例如修改表的結(jié)構(gòu)）。如果事務(wù) B 加鎖成功，那么它就應(yīng)該能修改表中的任意數(shù)據(jù)行，但是 A 持有的行鎖不允許修改鎖定的數(shù)據(jù)行。顯然數(shù)據(jù)庫需要避免這種問題，B 的加鎖申請需要等待 A 釋放行鎖。

那么如何判斷事務(wù) B 是否應(yīng)該獲取表級鎖呢？首先需要看該表是否已經(jīng)被其他事務(wù)加上了表級鎖，然后依次查看該表中的每一行是否已經(jīng)被其他事務(wù)加上了行級鎖。這種方式需要遍歷整個表中的記錄，效率很低。為此，InnoDB 引入了另外一種鎖：意向鎖（Intention Lock）。

意向鎖屬于表級鎖，由 InnoDB 自動添加，不需要用戶干預(yù)。意向鎖也分為共享和排他兩種方式：

    意向共享鎖（IS）：事務(wù)在給數(shù)據(jù)行加行級共享鎖之前，必須先取得該表的 IS 鎖。
    意向排他鎖（IX）：事務(wù)在給數(shù)據(jù)行加行級排他鎖之前，必須先取得該表的 IX 鎖。

此時，事務(wù) A 必須先申請該表的意向共享鎖，成功后再申請數(shù)據(jù)行的行鎖。事務(wù) B 申請表鎖時，數(shù)據(jù)庫查看該表是否已經(jīng)被其他事務(wù)加上了表級鎖；如果發(fā)現(xiàn)該表上存在意向共享鎖，說明表中某些數(shù)據(jù)行上存在共享鎖，事務(wù) B 申請的寫鎖會被阻塞。

因此，意向鎖是為了使得行鎖和表鎖能夠共存，從而實現(xiàn)多粒度的鎖機制。以下是表級鎖和表級意向鎖的兼容性：
 

    InnoDB 表存在兩種表級鎖，一種是LOCK TABLES語句手動指定的鎖，另一種是由 InnoDB 自動添加的意向鎖。

簡單來說，意向鎖和表鎖之間只有共享鎖兼容，意向鎖和意向鎖之間都可以兼容。意向鎖的主要作用是表明某個事務(wù)正在或者即將鎖定表中的數(shù)據(jù)行。

我們以意向排他鎖 IX 為例，繼續(xù)上面的實驗。先在 T1 中執(zhí)行以下加鎖語句：

-- 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 加上了排他鎖，同時會為表 t 加上意向排他鎖。然后在 T2 中執(zhí)行以下語句：

-- T2
mysql> lock tables t read; -- lock tables t write;

   

lock tables ... read語句用于為表 t 加上表級共享鎖；因為意向排他鎖和表級共享鎖沖突，所以 T2 一直等待 T1 釋放鎖。

    也可以使用lock tables ... write語句為表 t 加上表級排他鎖；因為意向排他鎖和表級排他鎖沖突，所以該語句也會一直等待 T1 釋放鎖。

當(dāng)我們在 T1 中提交或者回滾事務(wù)：

-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

T2 自動獲得該表上的共享鎖：

-- T2
Query OK, 0 rows affected (1 min 43.17 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

 

以上的unlock tables語句用于釋放該表上的排他鎖。

我們再來驗證一下兩個意向排他鎖之間鎖的兼容性，先在 T1 中執(zhí)行以下加鎖語句：

-- 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í)行以下語句：

-- 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 加上的排他鎖，同時為表 t 加上了意向排他鎖。也就是說，T1 和 T2 同時獲得了表 t 上的意向排他鎖，以及不同數(shù)據(jù)行上的行級排他鎖。InnoDB 通過行級鎖，實現(xiàn)了更細(xì)粒度的控制，能夠支持更高的并發(fā)更新和查詢。

最后在 T1 中提交或者回滾事務(wù)：

-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 

行級鎖實現(xiàn)

InnoDB 通過給索引上的索引記錄加鎖的方式實現(xiàn)行級鎖。具體來說，InnoDB 實現(xiàn)了三種行鎖的算法：記錄鎖（Record Lock）、間隙鎖（Gap Lock）和 Next-key 鎖（Next-key Lock）。
記錄鎖

記錄鎖（Record Lock）是針對索引記錄（index record）的鎖定。例如，SELECT * FROM t WHERE id = 1 FOR UPDATE;會阻止其他事務(wù)對表 t 中 id = 1 的數(shù)據(jù)執(zhí)行插入、更新，以及刪除操作。
通過主鍵操作單個值

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 的記錄，此時 T2 無法修改該記錄：

-- 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 個鎖結(jié)構(gòu)，鎖定了一個記錄；表 t 上存在 IX 鎖，主鍵索引上存在一個 X 記錄鎖，同時還顯示了記錄對應(yīng)的數(shù)據(jù)值。注意but not gap，下文我們會介紹間隙鎖（Gap Lock）。最后在 T1 中釋放鎖：

-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

   

通過唯一索引操作單個值

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 個鎖結(jié)構(gòu)，鎖定了 2 個記錄；表 t 上存在 IX 鎖，索引 idx_t_c1 上存在一個 X 記錄鎖，主鍵索引上存在一個 X 記錄鎖。最后在 T1 中釋放鎖：

-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 

    記錄鎖永遠(yuǎn)都是鎖定索引記錄，鎖定非聚集索引會先鎖定聚集索引。如果表中沒有定義索引，InnoDB 默認(rèn)為表創(chuàng)建一個隱藏的聚簇索引，并且使用該索引鎖定記錄。

間隙鎖

間隙鎖（Gap Lock）鎖定的是索引記錄之間的間隙、第一個索引之前的間隙或者最后一個索引之后的間隙。例如，SELECT * FROM t WHERE c1 BETWEEN 1 and 10 FOR UPDATE;會阻止其他事務(wù)將 1 到 10 之間的任何值插入到 c1 字段中，即使該列不存在這樣的數(shù)據(jù)；因為這些值都會被鎖定。
通過主鍵操作范圍值

首先在 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 仍然無法插入該記錄：

-- 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 個鎖結(jié)構(gòu)，鎖定了 4 個索引記錄；表 t 上存在 IX 鎖，主鍵索引上存在 1 個 X 記錄鎖（id = 1）和 3 個間隙鎖（(1, 2]、(2, 3]、supremum）；其中 supremum 代表了大于 3 的間隙（(3, positive infinity)）。實際上這里的間隙鎖屬于 Next-key 鎖，相當(dāng)于間隙鎖加記錄鎖，下文將會介紹。

    此時，我們可以插入 id 小于 1 的數(shù)據(jù)；但是不能插入 id 大于 10 的數(shù)據(jù)。

通過唯一索引操作范圍值

首先在 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 個鎖結(jié)構(gòu)，鎖定了 7 個索引記錄；表 t 上存在 IX 鎖，索引 idx_t_c1 上存在 4 個間隙鎖（(negative infinity, 1]、(1, 2]、(2, 3]、supremum），其中 supremum 代表了大于 3 的間隙（(3, positive infinity)）；主鍵索引上存在 3 個 X 記錄鎖，鎖定了 3 個主鍵值。實際上這里的間隙鎖屬于 Next-key 鎖，相當(dāng)于間隙鎖加記錄鎖，下文將會介紹。

    此時，我們無法插入任何數(shù)據(jù)。

間隙可能會包含單個索引值、多個索引值或者沒有索引值。間隙鎖是性能和并發(fā)之間的一種權(quán)衡，只會在某些事務(wù)隔離級別（Repeatable Read）使用。

使用唯一索引來搜索單個值的語句不會使用間隙鎖（不包括搜索條件只包含多列唯一索引中部分列的情況；在這種情況下，仍然會使用間隙鎖）。例如，SELECT * FROM t WHERE id = 1 FOR UPDATE;只會對 id = 1 的索引記錄加上記錄鎖，而不關(guān)心其他事務(wù)是否會在前面的間隙中插入數(shù)據(jù)。但是，如果 id 列上沒有索引或者創(chuàng)建的是非唯一索引，則該語句會鎖定前面的間隙。

需要注意的是，不同事務(wù)可以獲取一個間隙上互相沖突的鎖。例如，事務(wù) A 在一個間隙上獲取了共享的間隙鎖（間隙 S 鎖），事務(wù) B 可以在同一間隙上獲取排他的間隙鎖（間隙 X 鎖）。允許存在互相沖突的間隙鎖的原因在于，如果從索引中清除某個記錄，必須合并不同事務(wù)在記錄上獲取的間隙鎖。

InnoDB 間隙鎖的唯一目的是阻止其他事務(wù)在間隙中插入數(shù)據(jù)。間隙鎖可以共存，一個事務(wù)的間隙鎖不會阻止另一個事務(wù)在同一個間隙上獲取間隙鎖。共享間隙鎖和排他間隙鎖之間沒有區(qū)別，彼此不沖突，它們的作用相同。

間隙鎖可以顯式禁用，例如將事務(wù)隔離級別設(shè)置為 READ COMMITTED。此時，查找和索引掃描不會使用間隙鎖，間隙鎖只用于外鍵約束和重復(fù)鍵的檢查。

使用 READ COMMITTED 隔離級別還會帶來其他影響，MySQL 在判斷 WHERE 條件之后會釋放不滿足條件的數(shù)據(jù)行上的記錄鎖。對于 UPDATE 語句，InnoDB 執(zhí)行“半一致”讀??；以便將數(shù)據(jù)的最新版本返回給 MySQL，MySQL 就可以確定該行是否滿足 UPDATE 中的 WHERE 條件。
Next-key 鎖

Next-key 鎖（Next-key Lock）相當(dāng)于一個索引記錄鎖加上該記錄之前的一個間隙鎖。

InnoDB 實現(xiàn)行級鎖的方式如下：當(dāng)搜索或掃描表索引時，在遇到的索引記錄上設(shè)置共享鎖或排它鎖。因此，InnoDB 行級鎖實際上是索引記錄鎖。一個索引記錄上的 next-key 鎖也會影響該索引記錄之前的“間隙”，如果一個會話在索引中的記錄 R 上有共享鎖或排它鎖，則另一個會話不能在 R 之前的間隙中插入新的索引記錄。

假設(shè)一個索引中包含數(shù)據(jù) 10、11、13 和 20。該索引中可能的 next-key 鎖包含以下范圍，其中圓括號表示排除端點值，方括號表示包含端點值：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity) -- 顯示為 supermum

 

對于最后一個間隔，next-key 鎖將會鎖定最大索引值（20）之后的間隙；偽記錄“supermum”的值大于索引中任何值，它不是真正的索引記錄。(10, 11) 是一個間隙鎖的鎖定范圍，(10, 11] 是一個 next-key 鎖的鎖定范圍。

    默認(rèn)隔離級別（REPEATABLE READ ）下，InnoDB 通過 next-key 鎖進(jìn)行查找和索引掃描，用于防止幻讀；因為它會鎖定范圍值，不會導(dǎo)致兩次查詢結(jié)果的數(shù)量不同。

通過普通索引操作單個值

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 個鎖結(jié)構(gòu)，鎖定了 3 個索引項；表 t 上存在 IX 鎖，索引 idx_t_c2 上存在一個 next-key 鎖（c2 = 1，鎖定了 (negative infinity, 1]）和一個 X 間隙鎖（（1, 3）），主鍵索引上存在一個 X 記錄鎖（id = 1）。

此時其他事務(wù)無法在 c2 中插入小于 3 的值，但是可以插入大于等于 3 的值。最后在 T1 中釋放鎖：

-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 

    如果索引有唯一屬性，則 InnnoDB 會自動將 next-key 鎖降級為記錄鎖。我們在前面已經(jīng)給出了記錄鎖的示例。

通過普通索引操作范圍值

如果利用 c2 字段作為條件操作范圍值，加鎖情況與通過唯一索引（c1）操作范圍值相同?？梢詤⒖忌衔氖纠?。
無索引操作單個值或范圍值

c3 字段上沒有索引，我們先在 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 個鎖結(jié)構(gòu)，鎖定了 4 個索引項；表 t 上存在 IX 鎖，主鍵索引上存在 4 個 next-key 鎖，鎖定了所有的主鍵范圍。此時其他事務(wù)無法插入任何數(shù)據(jù)。

最后在 T1 中釋放鎖：

-- T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 

如果我們將語句修改為SELECT * FROM t WHERE c3 between 1 and 10 FOR UPDATE;，通過無索引的字段操作范圍值，也會鎖定主鍵的所有范圍。這也就是為什么 MySQL 推薦通過索引操作數(shù)據(jù)，最好是主鍵。
插入意向鎖

插入意向鎖（Insert Intention Lock）是在插入數(shù)據(jù)行之前，由 INSERT 操作設(shè)置的一種間隙鎖。插入意向鎖表示一種插入的意圖，如果插入到相同間隙中的多個事務(wù)沒有插入相同位置，則不需要互相等待。假設(shè)存在索引記錄 4 和 7。兩個事務(wù)分別嘗試插入 5 和 6，它們在獲取行排他鎖之前，分別使用插入意向鎖來鎖定 4 到 7 之間的間隙；但是不會相互阻塞，因為插入的是不同的行。

在 T1 中對 c2 大于 3 的索引記錄設(shè)置排它鎖，這個排它鎖包含記錄 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ù)在等待獲取獨占鎖時使用插入意向鎖：

-- 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ā)插入的性能。間隙鎖不允許多個事務(wù)同時插入同一個索引間隙，但是插入意向鎖允許多個事務(wù)同時插入同一個索引間隙內(nèi)的不同數(shù)據(jù)值。

未完，待續(xù) 。。。