InnoDB加鎖分析

在事務(wù)的并發(fā)控制帮非，MySQL使用MVCC來支持快照讀和使用加鎖來支持鎖定讀兩種方式，鎖定通過行鎖和間隙鎖锋边。

鎖定表：

鎖定規(guī)則：

1. MySQL的行鎖(包括recordLock, gapLock, nextKeyLock)是鎖定在索引上的，加鎖是在使用索引掃描數(shù)據(jù)時(shí)加的刨摩。
2. 如果掃描的是聚簇索引，則直接在聚簇索引上加record+gap鎖，如果掃描的是二級索引，則先在二級索引上加record+gap鎖有缆，然后在聚簇索引上加record鎖。
3. 當(dāng)掃描索引時(shí)温亲，在RR級別會(huì)加recordLock和gapLock棚壁，在RU/RC級別只會(huì)加recordLock，recordLock用于鎖定對已存在的記錄的讀取和寫入栈虚，gapLock用于鎖定對索引區(qū)間的插入袖外。
4. 記錄的前一gap鎖和本條記錄的record會(huì)合并成nextKeyLock。
5. 如果是唯一索引魂务，且任一側(cè)查詢條件的邊界匹配到了記錄曼验，可將該側(cè)的gap鎖去除。
6. mysql可能通過講gap鎖升級nextKey鎖來減少鎖的數(shù)量粘姜。
7. 同一個(gè)查詢?nèi)绻褂貌煌乃饕拚眨赡苕i定的范圍不通！
8. 對于delete相艇，where條件中的加鎖和selectX一致，對于所有的二級索引纯陨，加隱式鎖坛芽。
9. 對于update留储，where條件中的加鎖和selectX一致，對于涉及到的二級索引咙轩，加隱式鎖获讳。
10. 對于insert，加隱式鎖活喊。
11. 隱式鎖: 通過比較索引記錄的trx_id是否是當(dāng)前活躍的事務(wù)丐膝，如果是，則說明此時(shí)有事務(wù)(記為1)正在寫該記錄钾菊，當(dāng)其他事務(wù)(記為2)想要獲取該記錄的鎖時(shí)帅矗，則先為事務(wù)1獲取X鎖，再為事務(wù)2獲取對應(yīng)的鎖且等待煞烫。

加鎖流程

測試數(shù)據(jù)

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `country` int(10) unsigned NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

數(shù)據(jù)：
INSERT INTO `test` (`id`, `name`, `country`)
VALUES
    (1, 'a', 1, 1),
    (3, 'c', 3, 1),
    (5, 'e', 5, 0),
    (7, 'g', 5, 0),
    (9, 'i', 7, 0);

索引：
id: 1,3,5,7,9
name: a,c,e,g,i
country: 1,3,5,5,7

無腦分析MySQL鎖定范圍

步驟0-8為二級索引的全步驟加鎖分析浑此，9為聚簇索引的加鎖分析，10為update語句的加鎖分析滞详，11為delete語句的加鎖分析凛俱。

1. 準(zhǔn)備表格，填入待分析語句

分析語句：select * from test where `name`>"c" and `name`<="g" for update;
隔離級別：
使用索引：
索引排列：
    二級索引：
    聚簇索引：
二級索引：
    gap:
    record:
    最終lock:
聚簇索引：
    gap:
    record:
    最終lock:

1. 檢查隔離級別

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

分析語句：select * from test where `name`>"c" and `name`<="g" for update;
隔離級別：REPEATABLE-READ
掃描索引：
索引排列：
    二級索引：
    聚簇索引：
二級索引：
    gap:
    record:
    最終lock:
聚簇索引：
    gap:
    record:
    最終lock:

2. 通過使用explain或者optimizer_trace查看使用的索引料饥，使用show create tabel檢查索引類型蒲犬；

mysql> explain select * from test where `name`>"c" and `name`<="g" for update;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | idx_name      | idx_name | 98      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

分析語句：select * from test where `name`="e" for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：
    聚簇索引：
二級索引：
    gap：
    record:
    最終lock:
聚簇索引：
    gap：
    record:
    最終lock:

3. 通過構(gòu)造索引排列，可以幫助我們更好的理解鎖定范圍岸啡，掃描的索引是二級非唯一索引原叮，()表示間隙

分析語句：select * from test where `name`>"c" and `name`<="g" for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：() a () c () e () g () i ()
    聚簇索引：() 1 () 3 () 5 () 7 () 9 ()
二級索引：
    gap:
    record:
    最終lock:
聚簇索引：
    gap:
    record:
    最終lock:

4. 確定二級索引掃描到的記錄范圍，使用''表示

分析語句：select * from test where `name`>"c" and `name`<="g" for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：() a () c () 'e () g' () i ()
    聚簇索引：() 1 () 3 () 5 () 7 () 9 ()
二級索引：
    gap: 
    record: 
    最終lock: 
聚簇索引：
    gap:
    record:
    最終lock:

5. 確定二級索引鎖定范圍:
   • record鎖：
     • RU或者RC級別凰狞，則在''中間的所有匹配的記錄加recordLock篇裁，即記錄e,g
     • RR級別，則在''中間的所有記錄加recordLock赡若，即記錄e,g
   • gap鎖：
     • RU或者RC級別达布，不用填寫
     • RR級別，如果是二級非唯一索引逾冬，在''中間及兩側(cè)的所有記錄加gap鎖黍聂，即e兩側(cè)的間隙，我們使用(c,e)和(e,g)表示身腻，如果是唯一索引产还，且任一側(cè)查詢條件的邊界匹配到了記錄，可將該側(cè)的gap鎖去除

分析語句：select * from test where `name`>"c" and `name`<="g" for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：() a () c () 'e () g' () i ()
    聚簇索引：() 1 () 3 () 5 () 7 () 9 ()
二級索引：
    gap: (c,e),(e,g),(g,i)
    record: e,g
    最終lock: 
聚簇索引:
    gap:
    record:
    最終lock: 

6. 確定聚簇索引鎖定范圍：
   • 找到二級索引中的鎖定的所有記錄嘀趟，對聚簇索引中的響應(yīng)記錄加record鎖

分析語句：select * from test where `name`>"c" and `name`<="g" for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：() a () c () 'e () g' () i ()
    聚簇索引：() 1 () 3 () 5 () 7 () 9 ()
二級索引：
    gap: (c,e),(e,g),(g,i)
    record: e,g
    最終lock: 
聚簇索引:
    gap:
    record: 5,7
    最終lock: 

7. 合并鎖區(qū)間
   • 將record鎖和gap進(jìn)行合并脐区，使用(c,e]表示
   • 同一事務(wù)同一個(gè)頁同一類型同一狀態(tài)的鎖可以被存儲(chǔ)在同一個(gè)內(nèi)存結(jié)構(gòu)中從而節(jié)約存儲(chǔ)空間，mysql可能通過講gap鎖升級nextKey鎖來減少鎖的數(shù)量她按，會(huì)擴(kuò)大鎖定范圍牛隅，但是可以節(jié)約空間

分析語句：select * from test where `name`>"c" and `name`<="g" for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：() a () c () 'e () g' () i ()
    聚簇索引：() 1 () 3 () 5 () 7 () 9 ()
二級索引：
    gap: (c,e),(e,g),(g,i)
    record: e,g
    最終lock: (c,e],(e,g],(g,i) -> (c,e],(e,g],(g,i]
聚簇索引:
    gap:
    record: 5,7
    最終lock: 5,7

8. 驗(yàn)證：8.0以后可以通過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 | 140312364539208:1208:140312627602512    |                 76590 |        58 |       58 | expert        | test        | NULL           | NULL              | NULL       |       140312627602512 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 140312364539208:226:5:4:140312639144480 |                 76590 |        58 |       58 | expert        | test        | NULL           | NULL              | idx_name   |       140312639144480 | RECORD    | X             | GRANTED     | 'e', 5    |
| INNODB | 140312364539208:226:5:5:140312639144480 |                 76590 |        58 |       58 | expert        | test        | NULL           | NULL              | idx_name   |       140312639144480 | RECORD    | X             | GRANTED     | 'g', 7    |
| INNODB | 140312364539208:226:5:6:140312639144480 |                 76590 |        58 |       58 | expert        | test        | NULL           | NULL              | idx_name   |       140312639144480 | RECORD    | X             | GRANTED     | 'i', 9    |
| INNODB | 140312364539208:226:4:4:140312639144824 |                 76590 |        58 |       58 | expert        | test        | NULL           | NULL              | PRIMARY    |       140312639144824 | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
| INNODB | 140312364539208:226:4:5:140312639144824 |                 76590 |        58 |       58 | expert        | test        | NULL           | NULL              | PRIMARY    |       140312639144824 | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+    

9. 聚簇索引：掃描索引為聚簇索引炕柔，則該索引的分析同二級唯一索引
   • 確定掃描范圍
   • 為掃描范圍中的記錄加gap鎖和record鎖，同二級唯一索引
   • 將record鎖和gap進(jìn)行合并

分析語句：select * from test where `id`>=3 and `id`<6 for update;
隔離級別：REPEATABLE-READ
掃描索引：聚簇索引
索引排列：
    二級索引：
    聚簇索引：() 1 () '3 () 5 ( ' ) 7 () 9 ()
二級索引:
    gap鎖:
    record鎖:
    最終lock: 
聚簇索引：
    gap鎖:  (3,5), (5,7)
    record鎖: 3媒佣，5
    最終lock: 3, (3,5], (5,7)

10. 刪除語句：顯式鎖同selectX匕累，在所有二級索引上加隱式鎖

分析語句：delete from test where `id`>=3 and `id`<6;
隔離級別：REPEATABLE-READ
掃描索引：聚簇索引
索引排列：
    二級索引：
    聚簇索引：() 1 () '3 () 5 ( ' ) 7 () 9 ()
二級索引:
    gap鎖:
    record鎖:
    最終lock: 
聚簇索引：
    gap鎖:  (3,5), (5,7)
    record鎖: 3，5
    最終lock: 3, (3,5], (5,7)
隱式：
    idx_name: c,e
    idx_country: 3,5

11. 更新語句：顯式鎖同selectX默伍，在涉及的二級索引上加隱式鎖

分析語句：update test set `name`="t" where `id`>=3 and `id`<6;
隔離級別：REPEATABLE-READ
掃描索引：聚簇索引
索引排列：
    二級索引：
    聚簇索引：() 1 () '3 () 5 ( ' ) 7 () 9 ()
二級索引:
    gap鎖:
    record鎖:
    最終lock: 
聚簇索引：
    gap鎖:  (3,5), (5,7)
    record鎖: 3欢嘿，5
    最終lock: 3, (3,5], (5,7)
隱式：
    idx_name隱式: c,e,t

12. 插入語句：顯式鎖同selectX，在所有二級索引上加隱式鎖

分析語句：insert test value(4,"d", 1, 0);
隔離級別：REPEATABLE-READ
掃描索引：聚簇索引
索引排列：
    二級索引：
    聚簇索引：() 1 () '3 () 4 () 5 ( ' ) 7 () 9 ()
二級索引:
    gap鎖:
    record鎖:
    最終lock: 
聚簇索引：
    gap鎖:
    record鎖:
    最終lock:
隱式：
    idx_name: d
    idx_country: 1

更多例子

1. 使用主鍵鎖定一個(gè)已存在的記錄:

分析語句：select * from test where `id`=5 for update;
隔離級別：REPEATABLE-READ
掃描索引：聚簇索引
索引排列：
    二級索引：
    聚簇索引：() 1 () 3 () '5' () 7 () 9 ()
二級索引：
    gap鎖：
    record鎖: 
    最終lock：
聚簇索引：
    gap鎖：
    record鎖：5
    最終lock：5

2. 使用主鍵鎖定一個(gè)不存在的記錄

分析語句：select * from test where `id`=4 for update;
隔離級別：REPEATABLE-READ
掃描索引：聚簇索引
索引排列：
    二級索引：
    聚簇索引：() 1 () 3 ( '' ) 5 () 7 () 9 ()
二級索引：
    gap鎖：
    record鎖: 
    最終lock：
聚簇索引：
    gap鎖：(3,5)
    record鎖：
    最終lock：(3,5)

3. 使用主鍵鎖定范圍并條件過濾：
   為什么在RR級別下id=5的記錄加了record鎖也糊，但是在RC級別下沒有加record鎖炼蹦？
   因?yàn)榇藭r(shí)如果不對id=5的記錄加record鎖，則其他事務(wù)可能通過update語句將id=5的status修改為1显设，當(dāng)前事務(wù)再次讀取將會(huì)產(chǎn)生幻讀框弛。

分析語句：select * from test where `id`>=3 and `id`<6 and `status`= 1 for update;
隔離級別：REPEATABLE-READ
掃描索引：聚簇索引
索引排列：
    二級索引：
    聚簇索引：() 1 () '3 () 5 ( ' ) 7 () 9 ()
二級索引：
    gap鎖：
    record鎖: 
聚簇索引：
    gap鎖：(1,3), (3,5), (5,7)
    record鎖：3，5
    最終lock：3, (3,5], (5,7) 
    
分析語句：select * from test where `id`>=3 and `id`<6 and `status`= 1 for update;
隔離級別：READ-COMMITTED
掃描索引：聚簇索引
索引排列：
    二級索引：
    聚簇索引：() 1 () '3 () 5 ( ' ) 7 () 9 ()
二級索引：
    gap鎖：
    record鎖: 
聚簇索引：
    gap鎖：
    record鎖：3
    最終lock：3 

4. 使用二級索引掃描無數(shù)據(jù)的區(qū)間：

分析語句：select * from test where `name`="f" for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：() a () c () e ('') g () i ()
    聚簇索引：() 1 () 3 () 5 (  ) 7 () 9 ()
二級索引:
    gap鎖: (e,g)
    record鎖: 
    最終lock: (e,g)
聚簇索引:
    gap鎖:
    record鎖:
    最終lock:

5. 使用二級索引掃描一個(gè)范圍并過濾部分記錄：

分析語句：select * from test where `name`>"c" and `name`<="g" and status=0 for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：() a () c ( ' ) e () g' () i ()
    聚簇索引：() 1 () 3  ()   5 () 7  () 9 ()
二級索引：
    gap鎖：(c,e),(e,g),(g,i)
    record鎖: e,g
    最終lock：
聚簇索引：
    gap鎖：
    record鎖：3,5
    最終lock：3,5

6. 相同的條件捕捂，不同的索引瑟枫，不同的鎖定范圍

分析語句：select * from test force index(`idx_name`) where `name`="e" and `country`=5 for update;
隔離級別：REPEATABLE-READ
掃描索引：idx_name/二級非唯一索引
索引排列：
    二級索引：() a () c () 'e' () g () i ()
    聚簇索引：() 1 () 3 ()  5  () 7  () 9 ()
二級索引：
    gap鎖：(c,e),(e,g)
    record鎖: e
    最終lock：(c,e],(e,g)
聚簇索引：
    gap鎖：
    record鎖：5
    最終lock：5

分析語句：select * from test force index(`idx_country`) where `name`="e" and `country`=5 for update;;
隔離級別：REPEATABLE-READ
掃描索引：idx_country/二級非唯一索引
索引排列：
    二級索引：() 1 () 3 () '5 () 5' () 7 ()
    聚簇索引：() 1 () 3 ()  5 () 7  () 9 ()
二級索引：
    gap鎖：(3,5),(5,5),(5,7)
    record鎖: 5,5
    最終lock：(3,5],(5,5],(5,7)
聚簇索引：
    gap鎖：
    record鎖：5,7
    最終lock：5,7

鎖的內(nèi)存結(jié)構(gòu)

以下條件相同的公用同一個(gè)鎖結(jié)構(gòu)：

• 在同一個(gè)事務(wù)中進(jìn)行加鎖操作
• 被加鎖的記錄在同一個(gè)頁面中
• 加鎖的類型是一樣的
• 等待狀態(tài)是一樣的

type Lock struct {
    TxInfo // 事務(wù)信息
    IndexInfo // 索引信息
    TableOrRecordInfo // 表鎖信息或者行鎖信息
    TypeMode // 鎖類型
    Others   // 其他信息
    Bits
}

type RecordInfo struct {
    SpaceID int // 表空間
    PageNumber int // 頁號
    Nbits // Bits占用位數(shù)，每個(gè)索引記錄占用1位
}

type TypeMode struct {
    recLockType [24]bit // 僅行鎖時(shí)有意義指攒，nextKey鎖慷妙，record鎖，gap鎖允悦，插入意向鎖 ... 是否正在等待
    lockType [4]bit // 表鎖膝擂、行鎖
    lockMode [4]bit // 0:IS 1:IX 2:S 3:X 4:AutoInc
}

該如何理解Mysql加鎖的邏輯

數(shù)據(jù)結(jié)構(gòu)

我們從數(shù)據(jù)結(jié)構(gòu)的角度來理解MySQL，則MySQL的數(shù)據(jù)結(jié)構(gòu)如圖所示

• 一個(gè)表是由多個(gè)索引組成的隙弛，每一個(gè)索引都是一個(gè)有序鏈表
• 每一個(gè)索引有兩種類型的節(jié)點(diǎn)組成架馋，分別是Gap節(jié)點(diǎn)和Record節(jié)點(diǎn)
• 對于Record類型的節(jié)點(diǎn)，有三種鎖定狀態(tài)全闷，未鎖叉寂，X鎖和S鎖，對應(yīng)為讀寫
• 對于Gap類型的節(jié)點(diǎn)总珠，也有三種鎖定狀態(tài)屏鳍，未鎖，X鎖和S鎖局服，對應(yīng)為讀寫钓瞭，只有獲取到了X鎖才能對節(jié)點(diǎn)進(jìn)行操作
• MySQL將Gap節(jié)點(diǎn)和Record節(jié)點(diǎn)放在了一起來進(jìn)行表示，每一個(gè)Gap節(jié)點(diǎn)被合并到了后繼的Record節(jié)點(diǎn)中來進(jìn)行表示淫奔。
• 對于這個(gè)數(shù)據(jù)結(jié)構(gòu)山涡，MySQL提供了在掃描時(shí)的多種鎖定范圍：
  • 鎖定整個(gè)數(shù)據(jù)結(jié)構(gòu)，即表鎖定，通過表鎖實(shí)現(xiàn)
  • 鎖定一個(gè)索引中的多行記錄鸭丛，即行鎖定霍殴，通過Record鎖實(shí)現(xiàn)，用于實(shí)現(xiàn)RU/RC的鎖定讀系吩。

  • 鎖定一個(gè)索引中的一個(gè)范圍，通過Record鎖和Gap鎖實(shí)現(xiàn)妒蔚，用于實(shí)現(xiàn)RR/S+的鎖定讀穿挨。

    
    
    InnoLock

操作

Select操作

• 如果是對直接對主索引進(jìn)行掃描，則鎖只需要加在主索引上
• 如果需要對二級索引加鎖肴盏，則還要為對對應(yīng)的主索引記錄加Record鎖科盛。

Insert操作

當(dāng)插入一個(gè)節(jié)點(diǎn)時(shí)，需要在主索引記錄和所有二級索引記錄上插入該數(shù)據(jù)：

• 獲取新插入的數(shù)據(jù)的X鎖
• 獲取插入位置所在的Gap的X鎖菜皂，然后將一個(gè)Gap分裂為兩個(gè)Gap贞绵，并獲取這兩個(gè)Gap的X鎖

但是，MySQL取了個(gè)巧恍飘，這個(gè)技巧叫做隱式鎖榨崩，可以減少加鎖的操作。

• 在插入時(shí)章母，不獲取任何鎖
• 在其他事務(wù)掃描時(shí)母蛛，判斷這個(gè)記錄是否正在被其他是否修改（索引記錄的trx_id對應(yīng)的事務(wù)是否正在活躍），如果是乳怎，則這時(shí)候再為插入記錄的事務(wù)獲取應(yīng)有的X鎖彩郊。

Delete操作

當(dāng)刪除一個(gè)節(jié)點(diǎn)時(shí)，需要在主索引記錄和所有二級索引記錄上刪除該數(shù)據(jù)：

• 獲取刪除的數(shù)據(jù)的X鎖
• 獲取待刪除數(shù)據(jù)周圍的兩個(gè)Gap的X鎖蚪缀，然后將兩個(gè)Gap合并為一個(gè)Gap秫逝，并獲取X鎖

但是，MySQL仍然可以使用隱式鎖询枚。

Update操作

當(dāng)更新一個(gè)節(jié)點(diǎn)時(shí)违帆，需要在主索引記錄和所有涉及到的二級索引記錄上更新該數(shù)據(jù)：
如果新的記錄更新后存儲(chǔ)空間和位置都不變，則可以進(jìn)行原地更新：

• 獲取更新的記錄的X鎖
  否則哩盲，則
• 將原記錄進(jìn)行Delete前方，并Insert一條新的記錄