innodb儲存引擎鎖的實現(一)

沃趣科技發表於2018-07-23
儲存引擎

| 概述

通常，我們在95%以上的MySQL使用場景中，從一定程度上來講，就是在使用InnoDB儲存引擎，很多時候我們選擇使用InnoDB儲存引擎的原因，就是因為它支援高併發，而高併發的實現很大程度上得益於細粒度的鎖實現（行級鎖），不僅如此，MySQL 還支援更多靈活多變的鎖型別，例如：按照鎖型別可分為共享鎖(S鎖)和排它鎖(X鎖)，按照鎖範圍可分為記錄鎖(record lock)、間隙鎖(gap lock)、next-key lock。以及一些其他特殊用途的鎖。這些鎖在不同的隔離級別下存在著不同的表現形式。尤其在RR隔離級別下鎖的型別最為豐富、靈活多變。下面我們將為大家一一介紹這些鎖的含義和用途。 

PS：由於本文側重點在於介紹鎖型別，所以下文中的示例統一使用RR隔離級別進行演示，其他隔離級別下的鎖表現形式如有興趣請自行研究。


|  innodb 儲存引擎行鎖的演算法

資料庫對鎖的使用是為了支援對共享資源進行併發的訪問，提供資料的完整性和一致性。innodb儲存引擎提供了表鎖、行鎖和意向鎖用來實現事物在不同粒度上進行上鎖，從而提高資料庫的併發訪問，並且保證資料的完整性和一致性。

innodb儲存引擎的鎖型別

innodb儲存引擎是通過給索引上的索引項加鎖來實現行鎖，這種特點也就意味著，只要通過索引條件檢索資料，innodb才會使用行級鎖，否則會使用表鎖。innodb儲存引擎有以下鎖型別：

  • 1.共享鎖和排他鎖（Shared and Exclusive Locks）

  • 2.意向鎖（Intention Locks）

  • 3.記錄鎖（Record Locks）

  • 4.間隙鎖（Gap Locks）

  • 5.Next-Key Locks

  • 6.插入意向鎖（Insert Intention Locks）

  • 7.自增鎖（AUTO-INC Locks）

  • 8.空間索引謂詞鎖（Predicate Locks for Spatial Indexes）

（1）共享鎖和排他鎖（Shared and Exclusive Locks）

  • innodb實現標準行級鎖，其中有兩種型別的鎖，共享鎖（S）和獨佔鎖（X）。

    • 1.共享鎖，允許一個事務去讀一行，阻止其他事務獲得相同資料集的排它鎖。

    • 2.排它鎖，允許獲取排他鎖的事務更新資料，阻止其他事務取得相同的資料集的共享讀鎖和排他鎖。

  • 如果事務T1持有一行記錄的共享鎖，那麼另一個不同的事務T2對該行記錄的鎖定如下：

    • 1.如果事務T2對該行的請求是一個S鎖，那麼事務T1和事務T2可以共同對該行記錄持有同一把S鎖。

    • 2.如果事務T2對該行的請求是一個X鎖，那麼事務T2不可能馬上獲得對該行記錄的X鎖，必須要等到事務T1將該記錄的S鎖釋放，才可以對該行記錄持有X鎖。

    • 3.如果事務T1持有第 r 行的獨佔（X）鎖，那麼對於事務T2對該行記錄的任何一種請求的鎖都不能立即授予。相反，事務T2必須要等到事務T1釋放在r 行上的鎖。

（2）意向鎖（Intention Locks）

innodb儲存引擎支援多種粒度鎖，允許行鎖和表鎖共存。為了在多個粒度級別上進行鎖定，innodb儲存引擎使用意向鎖來實現。意向鎖是表級鎖，它先指明瞭該事物是那種型別的鎖（共享鎖或者獨佔鎖），然後去鎖定表中某行記錄。（我們可以在後面的Gap lock 和 Next-Key Locks 的演示中MySQL 8.0 的information_schema.data_locks 表中顯示的資訊看到） 

有兩種型別的意向鎖：

  • 1.意向共享鎖（IS），表明事務在一個表中的單個行上設定共享鎖。

  • 2.意向獨佔鎖（IX），表明事務在表中的某行設定獨佔鎖。 
    例如，SELECT … LOCK IN SHARE MODE 是IS，而 SELECT … FOR UPDATE 是IX鎖。

  • 意向鎖的新增方式：

    • 1.在一個事務對一張表的某行新增S鎖之前，它必須對該表獲取一個IS鎖或者優先順序更高的鎖。

    • 2.在一個事務對一張表的某行新增X鎖之前，它必須對該表獲取一個IX鎖。

  • 表級鎖型別相容性如下圖所示： 

  • 如果與現有鎖相相容，則授予事務請求的鎖，但如果它與之衝突，則不會，並且該事務一直等待直到衝突的現有鎖被釋放。如果所請求的鎖與持有的鎖衝突是不可能被授予，因為這將會導致死鎖，並且返回錯誤。

  • 意向鎖不會阻塞任何請求，除非將這個表鎖住，例如，LOCK TABLE …. WRITE。意向鎖的主要目的是顯示某人正在鎖定一行，或者在鎖定表中的一行資料。

（3）記錄鎖（Record Locks）

Record Lock總是會去鎖定主鍵、非空的唯一性索引對應的索引記錄，如果在建innodb表時並沒有建立任何索引，innodb會對6位元組的rowid的主鍵來進行鎖定。Read-Uncommited/RC級別都是使用該方式來進行加鎖。 

Record Lock的主要目的：行鎖可以防止不同事務版本的資料修改提交時造成資料衝突的情況。 

admin@localhost : test 10:53:27> select * from test;
+------+------+
| id  | xid  |
+------+------+
|    1 |    2 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)
admin@localhost : test 10:53:45> show index from test;
Empty set (0.01 sec)
admin@localhost : test 10:54:05> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`xid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


下面我們開啟2個會話session A 、session B 、session C進行測試



可以通過INFORMATION_SCHEMA中的innodb_lock_waits、innodb_locks、innodb_trx檢視到鎖的詳細資訊 


admin@localhost : test 11:10:26> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 17660049          | 17660049:589:3:4  | 17660047        | 17660047:589:3:4 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.02 sec)
Warning (Code 1681): 'INFORMATION_SCHEMA.INNODB_LOCK_WAITS' is deprecated and will be removed in a future release.
admin@localhost : test 11:10:35> select * from information_schema.innodb_locks;
+------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table    | lock_index      | lock_space | lock_page | lock_rec | lock_data      |
+------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+
| 17660049:589:3:4 | 17660049    | X        | RECORD    | `test`.`test` | GEN_CLUST_INDEX |        589 |        3 |        4 | 0x000000000400 |
| 17660047:589:3:4 | 17660047    | X        | RECORD    | `test`.`test` | GEN_CLUST_INDEX |        589 |        3 |        4 | 0x000000000400 |
+------------------+-------------+-----------+-----------+---------------+-----------------+------------+-----------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)
admin@localhost : test 11:11:31> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                trx_id: 17660049
            trx_state: LOCK WAIT
          trx_started: 2018-05-24 11:10:33
trx_requested_lock_id: 17660049:589:3:4
      trx_wait_started: 2018-05-24 11:10:33
            trx_weight: 3
  trx_mysql_thread_id: 15
            trx_query: update test set xid=5 where id=3
  trx_operation_state: fetching rows
    trx_tables_in_use: 1
    trx_tables_locked: 1
      trx_lock_structs: 3
trx_lock_memory_bytes: 1136
      trx_rows_locked: 2
    trx_rows_modified: 0
trx_concurrency_tickets: 0
  trx_isolation_level: READ COMMITTED
    trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
      trx_is_read_only: 0
trx_autocommit_non_locking: 0


從information_schema.innodb_locks可以看出session A的事務17660047 與 session C 的事務 17660049 都是對聚集索引上的id=3記錄是上排它鎖，並且session C 的事務 17660049 在等待session A的事務17660047 的X鎖對 id = 3記錄的釋放。

（4）間隙鎖（Gap Locks）

  • Gap Lock：間隙鎖只存在於RR隔離級別下的輔助索引中，只鎖定一個範圍，但不包含記錄本身。如果只鎖定一個範圍，那這個範圍是怎樣的？

  • Gap Lock的主要目的：間隙鎖避免了別的事務插入資料，從而避免了不可重複讀現象。

  • Gap Lock的特點：

    • 一個間隙鎖可能間隔一個索引值、多個索引值或者是無窮

    • 間隙鎖是為了平衡效能和併發的一部分，並且間隙鎖只能在RR隔離級別下使用

    • 對於使用唯一索引查詢資料， 是 不需要使用間隙鎖，但是並不包含查詢條件中只包含多列中的某些列，唯一索引在這樣的情況下，會使用間隙鎖來鎖定。

    • 不同的事務可以在一個間隙鎖中持有衝突的鎖。如果事務A在一個間隙鎖中持有的是共享的間隙鎖（gap S-lock），而事務B持有事務A在相同間隙的獨佔間隙鎖（gap X-lock）。該型別的鎖衝突間隙鎖是允許的，如果a記錄被從索引上刪除，不同事務在該記錄上的間隙鎖將被合併。

    • innodb儲存引擎的間隙鎖範圍是完全禁止操作的，這將意味著其他事務無法對間隙鎖範圍進行插入操作。間隙鎖不會阻止不同的事務去獲取同樣的間隙鎖範圍，因此間隙鎖 gap X-lock 和 gap S-lock 的效果是一樣的。

    • 間隙鎖可以被顯式的禁用。將事務的隔離級別設定為 READ COMMITTED 或者開啟 innodb_locks_unsafe_for_binlog=ON （已經被棄用）。在這樣的情況下，在查詢和索引掃描中禁用間隙鎖，並且只適用於外來鍵約束和主鍵檢查。

    • 在使用READ COMMITTED隔離級別或者開啟 innodb_locks_unsafe_for_binlog=ON 都可以顯式的禁用間隙鎖。開啟“semi-consistent” 半一致行讀取後，MySQL 會過濾掉不匹配的行，並且釋放不匹配的行的鎖，並且將過濾後資料返回到儲存引擎層去更新。

  • 測試步驟 
    在RR隔離級別下，建立一張只有輔助索引的t3表，並且對輔助索引的一個範圍使用 for update 查詢，插入包含在範圍中的值，然後分別對範圍的上確界和下確界進行update操作。 

admin@localhost : test 03:55:34> set session transaction_isolation='REPEATABLE-READ' ;
Query OK, 0 rows affected (0.00 sec)
admin@localhost : test 03:29:44> show variables like '%lation';
+---------------+-----------------+
| Variable_name | Value          |
+---------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)
root@localhost : test1 12:12:40> select * from t3;
+------+------+
| id | xid |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 4 | 3 |
| 7 | 7 |
| 10 | 9 |
+------+------+
5 rows in set (0.00 sec)
root@localhost : test1 12:08:24> show index from t3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t3 | 1 | xid | 1 | xid | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)



接下來使用mysql 8.0 中的performance_schema.data_locks 來輔助我們檢視這2個事務具體鎖的那條記錄，和鎖的模式等相關資訊。 


root@localhost : (none) 04:48:29> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| INNODB | 1609:1059 | 1609 | 64 | 63 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL |
| INNODB | 1609:2:5:4 | 1609 | 64 | 63 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X,GAP | WAITING | 3, 0x000000000202 |
| INNODB | 1608:1059 | 1608 | 63 | 88 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1608:2:5:4 | 1608 | 63 | 88 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 1608:2:4:4 | 1608 | 63 | 88 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
| INNODB | 1608:2:5:5 | 1608 | 63 | 88 | test | t | NULL | NULL | xid | 140152030660584 | RECORD | X,GAP | GRANTED | 7, 0x000000000203 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
6 rows in set (0.00 sec)



事務A 造成的鎖 


1、  | INNODB | 1608:1059 | 1608 | 63 | 88 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
2、 | INNODB | 1608:2:5:4 | 1608 | 63 | 88 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 
事務A對test庫的t表上的輔助索引xid  對xid=3記錄加 RECORD 鎖，0x000000000202 表示其對應的6位元組的rowid的位置指標。
3、| INNODB | 1608:2:4:4 | 1608 | 63 | 88 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
由於test庫的t表只有一個非唯一的輔助索引xid，會使用6位元組的rowid來作為聚集索引，事務A造成的鎖會對 輔助索引xid=3 記錄對應的聚集索引也加X鎖，相當於將輔助索引xid=3的整條行記錄都上X鎖
4、| INNODB | 1608:2:5:5 | 1608 | 63 | 88 | test | t | NULL | NULL | xid | 140152030660584 | RECORD | X,GAP | GRANTED | 7, 0x000000000203 |
記錄xid=7 上雖然新增的鎖為X，GAP鎖，但是並沒有對輔助索引xid=7對應的聚集索引新增X鎖，所以輔助索引xid=7這條記錄並沒有上X鎖，實際上只是用來表示與前一行記錄在 (3,7)區間構成間隙鎖。


事務B造成的鎖 


| INNODB | 1609:1059 | 1609 | 64 | 63 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL |
| INNODB | 1609:2:5:4 | 1609 | 64 | 63 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X,GAP | WAITING | 3, 0x000000000202 |
事務B在事務A已經持有鎖的情況下，進行插入(id，xid) ====>(1,2) 發現(1,3)存在間隙鎖。無法進行插入操作。

（5）Next-Key Lock

Next-Key Lock 是結合了Gap Lock和Record Lock的合併，其設計目的主要是為解決RR級別下的幻讀問題。該鎖定方式相對於Gap Lock和Record Lock是帶閉合區間的範圍鎖定。 

以下介紹其特點：

  • Innodb儲存引擎使用Next-Key Locks 只在 REPEATABLE READ 隔離級別下。

  • 當進行查詢或者索引掃描時，innodb儲存引擎以行鎖的方式進行鎖定，它會將符合條件的索引記錄使用S鎖或者X鎖。因此，行級鎖實際上就是對索引記錄上鎖。Next-Key Locks會影響 gap鎖 的上一個索引記錄，也就是Next-Key Locks是由索引記錄鎖加上gap 鎖組成。如果一個會話，在索引記錄R上有一個共享或者獨佔鎖，在索引記錄R與上一個索引記錄之間的間隙，另一個會話不可能插入一個新的索引記錄。

  • Next-Key Locks在某些情況下可以鎖住索引記錄的最大值和大於最大值的範圍，大於最大索引記錄的範圍稱為 "supremum pseudo-record"偽記錄。 
    注：官方文件並沒有介紹對Next-Key Lock介紹太多，所以我們通過以下場景來對Next-Key Lock的特點進行說明。

  • 場景一：RR隔離級別下對一張只有主鍵的表進行操作

    • 主鍵只是由一列構成 

root@localhost : test1 07:58:18> select * from t1;
+----+------+
| id | xid |
+----+------+
| 1 | 1 |
| 2 | 1 |
| 4 | 3 |
| 7 | 7 |
| 10 | 9 |
+----+------+
5 rows in set (0.00 sec)
root@localhost : test1 07:58:10> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)




root@localhost : (none) 05:37:52> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 1611:1060 | 1611 | 63 | 94 | test | t1 | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1611:3:4:4 | 1611 | 63 | 94 | test | t1 | NULL | NULL | PRIMARY | 140152030659896 | RECORD | X | GRANTED | 4 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)


事務A造成的鎖 


| INNODB | 1611:1060 | 1611 | 63 | 94 | test | t1 | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1611:3:4:4 | 1611 | 63 | 94 | test | t1 | NULL | NULL | PRIMARY | 140152030659896 | RECORD | X | GRANTED | 4 |
由於我們顯示的定義了主鍵可以看到這裡LOCK_DATA=4就是這個表內建的rowid ，說明聚集索引上的id=4的行記錄直接被加了X鎖，也並沒有用到GAP鎖。


該場景與GAP鎖的場景相比較可以發現，在RR隔離級別下當過濾條件的列是主鍵或者唯一索引的情況下，因為該列值都是唯一值，innodb儲存引擎會對Next-Key Lock進行優化，Next-Key Lock會降級成為Record Lock。換句話說gap鎖只存在於RR隔離級別下的輔助索引中，主鍵和唯一索引由於本身具有唯一約束，不需要gap鎖，只有record lock

  • 如果主鍵由多列構成，但是隻使用其中的一列進行查詢呢？ 

root@localhost : test 05:03:13> select * from t3;
+----+-----+------+
| id | xid | name |
+----+-----+------+
| 1 | 1 | a |
| 2 | 1 | b |
| 4 | 3 | c |
| 7 | 7 | d |
| 10 | 9 | e |
+----+-----+------+
5 rows in set (0.00 sec)
root@localhost : test 05:03:18> show index from t3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t3 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES |
| t3 | 0 | PRIMARY | 2 | xid | A | 5 | NULL | NULL | | BTREE | | | YES |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.29 sec)



root@localhost : (none) 05:05:51> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2123:1062 | 2123 | 68 | 40 | test | t3 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2123:5:4:4 | 2123 | 68 | 40 | test | t3 | NULL | NULL | PRIMARY | 139846618225784 | RECORD | X | GRANTED | 4, 3 |
| INNODB | 2123:5:4:5 | 2123 | 68 | 40 | test | t3 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X,GAP | GRANTED | 7, 7 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
3 rows in set (0.00 sec)


我們看到了什麼？可以發現當主鍵由多列構成時，我們只使用主鍵列中的一列進行查詢時，依然使用到了Next_Key Lock ，為什麼這樣？ 

我們都知道主鍵的鍵值是唯一的，但是我們這裡定義的主鍵是primary key(id,xid) 表示的是(id,xid) 組成的鍵值是唯一的，並不能保證id或者xid的鍵值是唯一的，所以這裡依然使用Next_Key Lock 來進行加鎖並沒有降級使用Record lock 來進行加鎖。

  • 那當使用主鍵所有列進行查詢時是什麼樣子的？ 

root@localhost : (none) 05:08:52> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2125:1062 | 2125 | 68 | 44 | test | t3 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2125:5:4:4 | 2125 | 68 | 44 | test | t3 | NULL | NULL | PRIMARY | 139846618225784 | RECORD | X | GRANTED | 4, 3 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)


可以看到當我們使用主鍵的所有列進行查詢時Next_Key Lock 降級為Record Lock 。

  • 場景二：RR隔離級別下對一張只有非唯一索引的表做操作 
    在RR隔離級別下，當表中只有一個索引並且為非唯一索引條件上進行等值當前讀或者範圍當前讀時，其加鎖是怎樣的？ 

admin@localhost : test 03:55:34> set session transaction_isolation='REPEATABLE-READ' ;
Query OK, 0 rows affected (0.00 sec)
admin@localhost : test 03:29:44> show variables like '%lation';
+---------------+-----------------+
| Variable_name | Value          |
+---------------+-----------------+
| transaction_isolation| REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)
root@localhost : test1 12:12:40> select * from t;
+------+------+
| id | xid |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 4 | 3 |
| 7 | 7 |
| 10 | 9 |
+------+------+
5 rows in set (0.00 sec)
root@localhost : test1 12:08:24> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t3 | 1 | xid | 1 | xid | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)


  • 等值當前讀 

root@localhost : (none) 06:51:25> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| INNODB | 1622:1059 | 1622 | 64 | 86 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL |
| INNODB | 1622:2:5:5 | 1622 | 64 | 86 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X,GAP | WAITING | 7, 0x000000000203 |
| INNODB | 1621:1059 | 1621 | 63 | 111 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1621:2:5:4 | 1621 | 63 | 111 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 1621:2:4:4 | 1621 | 63 | 111 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
| INNODB | 1621:2:5:5 | 1621 | 63 | 111 | test | t | NULL | NULL | xid | 140152030660584 | RECORD | X,GAP | GRANTED | 7, 0x000000000203 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
6 rows in set (0.00 sec)


事務A造成的鎖 


| INNODB | 1621:1059 | 1621 | 63 | 111 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1621:2:5:4 | 1621 | 63 | 111 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 1621:2:4:4 | 1621 | 63 | 111 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
| INNODB | 1621:2:5:5 | 1621 | 63 | 111 | test | t | NULL | NULL | xid | 140152030660584 | RECORD | X,GAP | GRANTED | 7, 0x000000000203 |


事務B造成的鎖 


| INNODB | 1622:1059 | 1622 | 64 | 86 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL |
| INNODB | 1622:2:5:5 | 1622 | 64 | 86 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X,GAP | WAITING | 7, 0x000000000203 |
由於事務A在輔助索引記錄 (3,7)之間是間隙鎖，而事務B插入的資料(id,xid) ===>(5,5) 中的xid=5在(3,7) 間隙鎖範圍中，所以才會顯示LOCK_MODE =X,GAP   LOCK_STATUS=WAITING      LOCK_DATA = 7, 0x000000000203   會等待事務A將鎖釋放直至超時。


事務C說明事務A造成的間隙鎖實際上並沒有將輔助索引記錄xid=7也鎖住，不包含記錄xid=7。

事務A的加鎖方式下圖所示：


  • 範圍當前讀 


root@localhost : (none) 07:08:05> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| INNODB | 1624:1059 | 1624 | 64 | 93 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL |
| INNODB | 1624:2:5:5 | 1624 | 64 | 93 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X | WAITING | 7, 0x000000000203 |
| INNODB | 1623:1059 | 1623 | 63 | 115 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1623:2:5:4 | 1623 | 63 | 115 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 1623:2:5:5 | 1623 | 63 | 115 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 1623:2:4:4 | 1623 | 63 | 115 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
6 rows in set (0.00 sec)


事務A 造成的鎖 


1、| INNODB | 1623:1059 | 1623 | 63 | 115 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
2、| INNODB | 1623:2:5:4 | 1623 | 63 | 115 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
3、| INNODB | 1623:2:5:5 | 1623 | 63 | 115 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 7, 0x000000000203 |
與 select * from t where xid=3 for update; 當前讀相比通過mysql 8.0的performance_schema.data_locks表可以看到輔助索引記錄xid=7 的LOCK_MODE =X,GAP  其不會鎖定輔助索引xid=7記錄，而範圍當前讀會將其鎖住
4、| INNODB | 1623:2:4:4 | 1623 | 63 | 115 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
。



  • 如果是select * from t where xid>1 and xid<=7 for update; 又是怎麼樣的呢？ 


root@localhost : (none) 09:49:17> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| INNODB | 2061:1059 | 2061 | 64 | 24 | test | t | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2061:2:5:4 | 2061 | 64 | 24 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 2061:2:5:5 | 2061 | 64 | 24 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 2061:2:5:6 | 2061 | 64 | 24 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9, 0x000000000205 |
| INNODB | 2061:2:4:4 | 2061 | 64 | 24 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000202 |
| INNODB | 2061:2:4:5 | 2061 | 64 | 24 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
6 rows in set (0.62 sec)


其加鎖下圖所示：  



說明：由於該表沒有主鍵只有一個非唯一的輔助索引key（xid ），使用6位元組rowid做聚集索引。



  • 如果是select * from t where xid>3 for update; 又是怎麼樣的呢？ 


root@localhost : test 03:25:06> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 2100:1059 | 2100 | 65 | 46 | test | t | NULL | NULL | NULL | 139846618246760 | TABLE | IX | GRANTED | NULL |
| INNODB | 2100:2:5:1 | 2100 | 65 | 46 | test | t | NULL | NULL | xid | 139846618243720 | RECORD | X | WAITING | supremum pseudo-record |
| INNODB | 2099:1059 | 2099 | 67 | 28 | test | t | NULL | NULL | NULL | 139846618240760 | TABLE | IX | GRANTED | NULL |
| INNODB | 2099:2:5:6 | 2099 | 67 | 28 | test | t | NULL | NULL | xid | 139846618237880 | RECORD | X,GAP | WAITING | 9, 0x000000000205 |
| INNODB | 2098:1059 | 2098 | 66 | 40 | test | t | NULL | NULL | NULL | 139846618234776 | TABLE | IX | GRANTED | NULL |
| INNODB | 2098:2:5:5 | 2098 | 66 | 40 | test | t | NULL | NULL | xid | 139846618231848 | RECORD | X,GAP | WAITING | 7, 0x000000000203 |
| INNODB | 2097:1059 | 2097 | 64 | 75 | test | t | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2097:2:5:1 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 2097:2:5:5 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 2097:2:5:6 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9, 0x000000000205 |
| INNODB | 2097:2:4:5 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 |
| INNODB | 2097:2:4:6 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000205 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
12 rows in set (0.00 sec)


事務A造成的鎖 


| INNODB | 2097:1059 | 2097 | 64 | 75 | test | t | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2097:2:5:1 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | supremum pseudo-record |
這裡可以看到與之前的不同，上確界新增了X鎖，由於使用 select * from t where xid>3 for update; 該表的xid的上確界為9，它需要將9到正無窮也要鎖住，supremum pseudo-record 上確界偽記錄
| INNODB | 2097:2:5:5 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 2097:2:5:6 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9, 0x000000000205 |
| INNODB | 2097:2:4:5 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 |
| INNODB | 2097:2:4:6 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000205 |


下圖所示： 



在RR隔離級別下，對一張只有非唯一輔助索引等值當前讀和範圍當前讀造成的鎖，以t表為例。


  • 我們可以得出以下結論： 
    在RR隔離級別下，如果表中只有一個非唯一的輔助索引，當進行等值當前讀時會與下一行記錄形成間隙鎖，但不會鎖住下一行記錄；範圍當前讀時會與下一行記錄形成間隙鎖並且會鎖住該行。

    • 那麼可能有人會問在RR隔離級別下，為什麼在對輔助索引做範圍當前讀時會與下一行記錄形成間隙鎖並且還會鎖住該行呢？ 
      因為對於非唯一的輔助索引不能保證鍵值的唯一，所以需要鎖住滿足條件的下一行進行判斷，當然這裡還有查詢優化器的功勞。

  • 場景三：RR隔離級別下對一張有主鍵和輔助索引的表做操作 

root@localhost : test 10:20:09> select * from t2;
+----+------+
| id | xid |
+----+------+
| 1 | 1 |
| 2 | 1 |
| 4 | 3 |
| 7 | 7 |
| 10 | 9 |
+----+------+
5 rows in set (0.00 sec)
root@localhost : test 10:20:02> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t2 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES |
| t2 | 1 | xid | 1 | xid | A | 4 | NULL | NULL | YES | BTREE | | | YES |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.04 sec)


  • 等值當前讀 



root@localhost : (none) 10:29:14> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2070:1061 | 2070 | 65 | 14 | test | t2 | NULL | NULL | NULL | 139846618246760 | TABLE | IX | GRANTED | NULL |
| INNODB | 2070:4:4:5 | 2070 | 65 | 14 | test | t2 | NULL | NULL | PRIMARY | 139846618243720 | RECORD | X | GRANTED | 7 |
| INNODB | 2069:1061 | 2069 | 67 | 18 | test | t2 | NULL | NULL | NULL | 139846618240760 | TABLE | IX | GRANTED | NULL |
| INNODB | 2069:4:5:5 | 2069 | 67 | 18 | test | t2 | NULL | NULL | xid | 139846618237880 | RECORD | X,GAP | WAITING | 7, 7 |
| INNODB | 2068:1061 | 2068 | 66 | 22 | test | t2 | NULL | NULL | NULL | 139846618234776 | TABLE | IX | GRANTED | NULL |
| INNODB | 2068:4:5:4 | 2068 | 66 | 22 | test | t2 | NULL | NULL | xid | 139846618231848 | RECORD | X,GAP | WAITING | 3, 4 |
| INNODB | 2065:1061 | 2065 | 64 | 43 | test | t2 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2065:4:5:4 | 2065 | 64 | 43 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3, 4 |
| INNODB | 2065:4:4:4 | 2065 | 64 | 43 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 |
| INNODB | 2065:4:5:5 | 2065 | 64 | 43 | test | t2 | NULL | NULL | xid | 139846618226472 | RECORD | X,GAP | GRANTED | 7, 7 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
10 rows in set (0.01 sec)


事務A造成的鎖下圖所示： 


| INNODB | 2065:1061 | 2065 | 64 | 43 | test | t2 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2065:4:5:4 | 2065 | 64 | 43 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3, 4 |
| INNODB | 2065:4:4:4 | 2065 | 64 | 43 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 |
| INNODB | 2065:4:5:5 | 2065 | 64 | 43 | test | t2 | NULL | NULL | xid | 139846618226472 | RECORD | X,GAP | GRANTED | 7, 7 |




分析：在RR隔離級別下，一張帶有主鍵和非唯一的輔助索引表，使用輔助索引進行當前讀時與只有非唯一的輔助索引表進行等值當前讀時，都不會將輔助索引的下一行記錄進行加鎖，只會將下一行記錄與前一行記錄新增間隙鎖。

  • 範圍當前讀


事務A的造成的鎖： 


root@localhost : test 11:07:47> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2086:1061 | 2086 | 64 | 52 | test | t2 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2086:4:5:4 | 2086 | 64 | 52 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3, 4 |
| INNODB | 2086:4:5:5 | 2086 | 64 | 52 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 7 |
| INNODB | 2086:4:5:6 | 2086 | 64 | 52 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9, 10 |
| INNODB | 2086:4:4:4 | 2086 | 64 | 52 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 |
| INNODB | 2086:4:4:5 | 2086 | 64 | 52 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 7 |
| INNODB | 2086:4:4:6 | 2086 | 64 | 52 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 10 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
7 rows in set (0.00 sec)




總結

綜上所述，在RR隔離級別下的加鎖方式：


說明：對於唯一鍵值的鎖定，next_key lock 降級為 Record lock 僅存在於查詢所有的唯一索引列。如果唯一索引有多個列組成，而查詢僅是多個唯一索引列中的一個，那麼查詢其實是rang型別的查詢，而不是point型別的查詢，故innodb儲存引擎依然使用next_key lock進行鎖定。


該篇文章包含了在RR隔離級別下的DML語句的加鎖情況，DML語句都是當前讀，可能有人會問那insert是如何進行加鎖的，其實在整個過程中可以發現，在innodb儲存引擎中，對於insert操作，其都會檢查插入記錄的下一條記錄是否已經被鎖定，若被鎖定，則不允許查詢，所以我們的每次插入都會失敗，返回請求鎖超時。下篇將為大家詳細描述insert是如何加鎖！


參考資料： 
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html 
http://mysql.taobao.org/monthly/2018/05/04/ 
http://mysql.taobao.org/monthly/2016/01/01/ 
http://hedengcheng.com/?p=771


|  作者簡介

王波，沃趣科技資料庫技術專家

熟悉MySQL內部機制、豐富的主從複製故障診斷解決、效能優化、不同場景下資料庫備份恢復及遷移經驗。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2158361/，如需轉載，請註明出處，否則將追究法律責任。

相關文章