InnoDB的七種鎖

​

InnoDB的七種鎖

1. 自增鎖（Auto-inc Locks）

2. 共享/排他鎖（Shared and Exclusive Locks）

3. 意向鎖（Intention Locks）

4. 插入意向鎖（Insert Intention Locks）

5. 記錄鎖（Record Locks）

6. 間隙鎖（Gap Locks）

7. 臨鍵鎖（Next-Key Locks）

相關名詞

|--表級鎖（鎖定整個表）

|--頁級鎖（鎖定一頁）

|--行級鎖（鎖定一行）

|--共享鎖（S鎖，MyISAM 叫做讀鎖）

|--排他鎖（X鎖，MyISAM 叫做寫鎖）

|--悲觀鎖（抽象性，不真實存在這個鎖）

|--樂觀鎖（抽象性，不真實存在這個鎖）

預設事務隔離級别為可重複讀（Repeated Read, RR）

InnoDB的鎖，與索引類型，事務的隔離級别相關

自增鎖

    自增鎖是一種特殊的表級别鎖（table-level lock）

    從MySQL 5.1開始，InnoDB中提供了一種輕量級互斥量的自增長實作機制

    同時InnoDB存儲引擎提供了一個參數innodb_autoinc_lock_mode來控制自增長的模式

    進而提高自增長值插入的性能

innodb_autoinc_lock_mode和插入類型有關，在介紹它之前，我們先來看看都有哪些插入類型

+ **insert-like**  任何會産生新記錄的語句，都叫上insert-like，

比如：INSERT,INSERT ...SELECT,REPLACE,REPLACE ...SELECT,and  LOAD DATA，總之包括：simple-inserts,bulk-inserts,mixed-mode inserts.

+ **simple inserts**

插入的記錄行數是确定的：比如：insert into values，replace

但是不包括： INSERT ... ON DUPLICATE KEY UPDATE.

+ **bulk inserts**

插入的記錄行數不能馬上确定的，比如： INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA

+ **mixed-mode inserts**

這些都是simple-insert，但是部分auto increment值給定或者不給定. 例子如下(where c1 is an AUTO_INCREMENT column of table t1):

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 

+ 另外一種 mixed-mode insert 就是 INSERT ... ON DUPLICATE KEY UPDATE

#### innodb_autoinc_lock_mode 的說明

    0 這個表示tradition 傳統

    1 這個表示consecutive 連續 (預設)

    2 這個表示interleaved 交錯 （MySQL 8.0下預設值為2）

1.1 tradition(innodb_autoinc_lock_mode=0) 模式:

　　

+ 它提供了一個向後相容的能力

+ 在這一模式下，**所有的insert語句("insert like")** 都要在語句開始的時候得到一個表級的auto_inc鎖，在語句結束的時候才釋放這把鎖，**注意呀，這裡說的是語句級而不是事務級的，** 一個事務可能包含有一個或多個語句。

+ 它能保證值配置設定的可預見性，與連續性，可重複性，這個也就保證了insert語句在複制到slave的時候還能生成和master那邊一樣的值(它保證了基于語句複制的安全)。

+ 由于在這種模式下auto_inc鎖一直要保持到語句的結束，是以這個就影響到了并發的插入。

1.2 consecutive(innodb_autoinc_lock_mode=1) 模式:

+ 對simple insert做了優化，由于simple insert一次性插入值的個數可以立馬得到确定，是以mysql可以一次生成幾個連續的值，用于這個insert語句，**該值會用互斥量mutex去對記憶體（dict_table_struct.autoinc）中的計數器進行累加操作。**mysqld重新開機後，從哪裡得到AUTO_INCREMENT呢？記憶體值肯定是丢失了，實際上MySQL采用執行類似select max(id)+1 from t1;方法來得到AUTO_INCREMENT

+ **Mysql8.0優化:将自增主鍵的計數器持久化到redo log中。每次計數器發生改變，都會将其寫入到redo log中。如果資料庫發生重新開機，InnoDB會根據redo log中的計數器資訊來初始化其記憶體值。**

+ 對于bulk inserts 還是使用傳統表鎖，該配置下如果不考慮復原，對于自增列的增長還是連續的。

+ 深入思考：為什麼這個模式要産生表級别的鎖呢？因為：他要保證bulk insert自增id的連續性，防止在bulk insert的時候，被其他的insert語句搶走auto increment值。

+ 這個模式的好處是auto_inc鎖不要一直保持到語句的結束，隻要語句得到了相應的值後就可以提前釋放鎖

1.3 interleaved(innodb_autoinc_lock_mode=2) 模式

+ 由于這個模式下已經沒有了auto_inc鎖，對于所有insert-like 自增長值得産生都是通過互斥量mutex,是以這個模式下的性能是最好的

+ 但是它也有一個問題，就是對于同一個語句來說它所得到的auto_incremant值可能不是連續的，是以基于statement-base replication會出現問題，是以使用這個模式，任何時候都應該使用row-base replication,才能保證最大的并發性能和主從一緻性

    不要沒事去更新一個auto_increment列的值，否則自增時，有可能跟你更新的值沖突，導緻插入失敗

共享/排他鎖

    共享/排它鎖是标準的行級鎖(row-level locking)

1. 事務拿到某一行記錄的共享S鎖，才可以讀取這一行；

2. 事務拿到某一行記錄的排它X鎖，才可以修改或者删除這一行；

3. 多個事務可以拿到一把S鎖，讀讀可以并行；

4. 而隻有一個事務可以拿到X鎖，寫寫/讀寫必須互斥；

5. 共享/排它鎖的潛在問題是，不能充分的并行，解決思路是資料多版本

意向鎖

    意向鎖，是一個表級别的鎖(table-level locking)，但是卻表示事務正在讀或寫某一行記錄，而不是整個表。是以意向鎖之間不會産生沖突，真正的沖突在加行鎖時檢查。

    為了友善檢測表級鎖和行級鎖之間的沖突，就引入了意向鎖，主要目的是解決表鎖和行鎖共存的問題。

1. 意向共享鎖(intention shared lock, IS)，它預示着，事務有意向對表中的某些行加共享S鎖

2. 意向排它鎖(intention exclusive lock, IX)，它預示着，事務有意向對表中的某些行加排它X鎖

##### 例如

```

select ... lock in share mode，要設定IS鎖；

select ... for update，要設定IX鎖；

```

    事務要獲得某些行的S鎖，必須先獲得表的IS鎖

    事務要獲得某些行的X鎖，必須先獲得表的IX鎖

+ 意向鎖之間互不排斥，但除了 IS 與 S相容外，意向鎖會與共享鎖/排他鎖互斥

+ **IX，IS是表級鎖，不會和行級的X，S鎖發生沖突。隻會和表級的X，S發生沖突**

+ InnoDB支援多粒度鎖，意向鎖在保證并發性的前提下，實作了行鎖和表鎖共存

##### 解決的問題

+ 如果另一個任務試圖在該表級别上應用共享或排它鎖，則受到由第一個任務控制的表級别意向鎖的阻塞。第二個任務在鎖定該表前不必檢查各個頁或行鎖，而隻需檢查表上的意向鎖。

插入意向鎖

    插入意向鎖，是間隙鎖(Gap Locks)的一種（是以，也是實施在索引上的）

    它是專門針對insert操作的。

+ 普通的Gap Lock 不允許 在 （上一條記錄，本記錄） 範圍内插入資料

+ 插入意向鎖Gap Lock 允許 在 （上一條記錄，本記錄） 範圍内插入資料

+ 如果多個事務插入到相同的索引間隙中，如果它們不在間隙中的相同位置插入，則無需等待其他事務。比如說有索引記錄4和7，有兩個事務想要分别插入5，6，在擷取插入行上的獨占鎖之前，每個鎖都使用插入意圖鎖鎖定4和7之間的間隙，但是不要互相阻塞，因為行是不沖突的，意向鎖的涉及是為了插入的正确和高效。

##### 插入的過程

假設現在有記錄 10， 30， 50 且為主鍵 ，需要插入記錄 25 

1. 找到 小于等于25的記錄 ，這裡是 10

2. 找到 記錄10的下一條記錄 ，這裡是 30

3. 判斷 下一條記錄30 上是否有鎖

+ 判斷 30 上面如果 沒有鎖 ，則可以插入

+ 判斷 30 上面如果有Record Lock，則可以插入

+ 判斷 30 上面如果有Gap Lock/Next-Key Lock，則無法插入，因為鎖的範圍是 (10, 30) /（10, 30] ；在30上增加insert intention lock（ 此時處于waiting狀态），當 Gap Lock / Next-Key Lock 釋放時，等待的事物（ transaction）将被 喚醒 ，此時 記錄30 上才能獲得 insert intention lock ，然後再插入 記錄25

4. 注意：一個事物 insert 25 且沒有送出，另一個事物 delete 25 時，記錄25上會有 Record Lock

```

select * from a;

+----+

| a  |

+----+

| 5 |

| 10 |

| 13 |

| 20 |

+----+

```

```

//事務A

select * from a where a<=13 for update

```

```

//事務B

insert into a values (12)

```

```

//事務C

insert into a values (11)

```

事務A不送出，事務B，C被阻塞了，這時候檢視

```

show engine innodb status\G

```

```

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 3, OS thread handle 140018685810432, query id 240 localhost root update

--等待插入的SQL

insert into a values(12)

------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:

--插入記錄12的事物等待中，等待獲得插入意向鎖(lock_mode X locks gap before rec insert intention waiting)

```

此時事務A commit

+ 事務B輸出：Query OK, 1 row affected (17.40 sec)

前提條件是insert操作的鎖沒有逾時

+ 事務B未送出，再執行：show engine innodb status\G

```

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 3, OS thread handle 140018685810432, query id 247 localhost root

```

記錄鎖

    記錄鎖，它封鎖索引記錄，例如： select * from t where id=1 for update;

+ 記錄鎖鎖住的永遠是索引，而非記錄本身，即使該表上沒有任何索引，那麼innodb會在背景建立一個隐藏的聚集主鍵索引，那麼鎖住的就是這個隐藏的聚集主鍵索引。

+ 是以說當一條sql沒有走任何索引時，那麼将會在每一條聚合索引後面加X鎖，這個類似于表鎖，但原理上和表鎖應該是完全不同的。

間隙鎖

    它封鎖索引記錄中的間隔，或者第一條索引記錄之前的範圍，又或者最後一條索引記錄之後的範圍。

    開區間，不包括雙端端點

```

select * from t 

    where id between 7 and 15 

    for update;

```

這個SQL語句會封鎖區間，以阻止其他事務id=10的記錄插入。

    間隙鎖的主要目的，就是為了防止其他事務在間隔中插入資料，以導緻“不可重複讀”

    如果把事務的隔離級别降級為讀送出(Read Committed, RC)，間隙鎖則會自動失效。

臨鍵鎖

    臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖範圍，既包含索引記錄，又包含索引區間。

    左閉右開區間

+ 但當查詢唯一索引的時候，且記錄存在，Next-Key Lock 會進行優化，将其降級為RecordLock，即僅鎖住索引本身，不是範圍。

+ 但當查詢唯一索引的時候，且記錄不存在，使用Gap Lock

+ 但當查詢唯一索引的時候，使用範圍查詢 > <，使用Gap Lock + Record Lock,鎖上界，不鎖下界

    臨鍵鎖的主要目的，也是為了避免幻讀(Phantom Read)。如果把事務的隔離級别降級為RC，臨鍵鎖則也會失效。

### 總結：

    InnoDB使用共享鎖，可以提高讀讀并發；

    排他鎖，為了保證資料強一緻，InnoDB使用強互斥鎖，保證同一行記錄修改與删除的串行性；

    InnoDB使用插入意向鎖，可以提高插入并發；

    記錄鎖鎖定索引記錄；

    間隙鎖鎖定間隔，防止間隔中被其他事務插入；

    臨鍵鎖鎖定索引記錄+間隔，防止幻讀；

普通select

加鎖select

update與delete

insert

各類SQL語句分别加了什麼鎖？

select

普通的select是快照讀，而select ... for update或select ... in share mode則會根據情況加不同的鎖

如果在唯一索引上用唯一的查詢條件時（ where id=1），加記錄鎖

否則，其他的查詢條件和索引條件，加間隙鎖（BETWEEN AND ）或Next-Key 鎖（可重複隔離級别）

update與delete

如果在唯一索引上使用唯一的查詢條件來update/delete，加記錄鎖

否則，符合查詢條件的索引記錄之前，都會加Next-Key 鎖

注：如果update的是聚集索引，則對應的普通索引記錄也會被隐式加鎖，這是由InnoDB索引的實作機制決定的：普通索引存儲PK的值，檢索普通索引本質上要二次掃描聚集索引。

insert

insert和update與delete不同，它會用排它鎖封鎖被插入的索引記錄，同時，會在插入區間加插入意向鎖，但這個并不會真正封鎖區間，也不會阻止相同區間的不同KEY插入。

### 檢視鎖等待情況

```

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

select * from information_schema.innodb_trx;

```

##### 顯示鎖 vs 隐示鎖

1. 顯示鎖(explicit lock)

    顯示的加鎖，在show engine innoDB status 中能夠看到  ，會在記憶體中産生對象，占用記憶體

    eg: select ... for update , select ... lock in share mode

2. 隐示鎖(implicit lock)

    implicit lock 是在索引中對記錄邏輯的加鎖，但是實際上不産生鎖對象，不占用記憶體空間

3. 哪些語句會産生implicit lock 呢？

   eg: insert into xx values(xx)

   eg: update xx set t=t+1 where id = 1 ; 會對輔助索引加implicit lock

4. implicit lock 在什麼情況下會轉換成 explicit lock

  eg： 隻有implicit lock 産生沖突的時候，會自動轉換成explicit lock,這樣做的好處就是降低鎖的開銷

  eg: 比如：我插入了一條記錄10，本身這個記錄加上implicit lock，如果這時候有人再去更新這條10的記錄，那麼就會自動轉換成explicit lock

5. 資料庫怎麼知道implicit lock的存在呢？如何實作鎖的轉化呢？

  1. 對于聚集索引上面的記錄，有db_trx_id,如果該事務id在活躍事務清單中，那麼說明還沒有送出，那麼implicit則存在

  2. 對于非聚集索引：由于上面沒有事務id，那麼可以通過上面的主鍵id，再通過主鍵id上面的事務id來判斷，不過算法要非常複雜，這裡不做介紹

##### metadata lock