資料庫鎖的12連問，抗住！

前言

大家好，我是田螺。

金三銀四很快就要來啦，準備了資料庫鎖的12連問，相信大家看完肯定會有幫助的。

1. 為什麼需要加鎖

在日常生活中，如果你心情不好想靜靜，不想被比別人打擾，你就可以把自己關進房間裡，並且反鎖。這就是生活中的加鎖。

同理，對於MySQL資料庫來說的話，一般的物件都是一個事務一個事務來說的。所以，如果一個事務內，一個SQL正在更新某條記錄，我們肯定不想它被別的事務影響到嘛？因此，資料庫設計大叔，給該行資料加上鎖(行鎖)。

專業一點的說法: 如果有多個併發請求存取資料，在資料就可能會產生多個事務同時操作同一行資料。如果併發操作不加控制，不加鎖的話，就可能寫入了不正確的資料，或者導致讀取了不正確的資料，破壞了資料的一致性。因此需要考慮加鎖。

2. InnoDB有哪些鎖？

2.1 共享/排他鎖

InnoDB呢實現了兩種標準的行級鎖：共享鎖（簡稱S鎖）、排他鎖（簡稱X鎖）。

• 共享鎖：簡稱為S鎖，在事務要讀取一條記錄時，需要先獲取該記錄的S鎖。
• 排他鎖：簡稱X鎖，在事務需要改動一條記錄時，需要先獲取該記錄的X鎖。

如果事務T1持有行R的S鎖，那麼另一個事務T2請求訪問這條記錄時，會做如下處理：

• T2 請求S鎖立即被允許，結果T1和T2都持有R行的S鎖
• T2 請求X鎖不能被立即允許,此操作會阻塞

如果T1持有行R的X鎖，那麼T2請求R的X、S鎖都不能被立即允許，T2必須等待T1釋放X鎖才可以，因為X鎖與任何的鎖都不相容。

S鎖和X鎖的相容關係如下圖表格：

X鎖和S鎖是對於行記錄來說的話，因此可以稱它們為行級鎖或者行鎖。我們認為行鎖的粒度就比較細，其實一個事務也可以在表級別下加鎖，對應的，我們稱之為表鎖。給表加的鎖，也是可以分為X鎖和S鎖的哈。

如果一個事務給表已經加了S鎖，則：

• 別的事務可以繼續獲得該表的S鎖，也可以獲得該表中某些記錄的S鎖。
• 別的事務不可以繼續獲得該表的X鎖，也不可以獲得該表中某些記錄的X鎖。

如果一個事務給表加了X鎖，那麼

• 別的事務不可以獲得該表的S鎖，也不可以獲得該表某些記錄的S鎖。
• 別的事務不可以獲得該表的X鎖，也不可以繼續獲得該表某些記錄的X鎖。

2.2 意向鎖

什麼是意向鎖呢？意向鎖是一種不與行級鎖衝突的表級鎖。未來的某個時刻，事務可能要加共享或者排它鎖時，先提前宣告一個意向。注意一下，意向鎖，是一個表級別的鎖哈。

為什麼需要意向鎖呢？ 或者換個通俗的說法，為什麼要加共享鎖或排他鎖時的時候，需要提前宣告個意向鎖呢呢？

因為InnoDB是支援表鎖和行鎖共存的，如果一個事務A獲取到某一行的排他鎖，並未提交，這時候事務B請求獲取同一個表的表共享鎖。因為共享鎖和排他鎖是互斥的，因此事務B想對這個表加共享鎖時，需要保證沒有其他事務持有這個表的表排他鎖，同時還要保證沒有其他事務持有表中任意一行的排他鎖。

然後問題來了，你要保證沒有其他事務持有表中任意一行的排他鎖的話，去遍歷每一行？這樣顯然是一個效率很差的做法。為了解決這個問題，InnoDb的設計大叔提出了意向鎖。

意向鎖是如何解決這個問題的呢？  我們來看下

意向鎖分為兩類：

• 意向共享鎖：簡稱IS鎖，當事務準備在某些記錄上加S鎖時，需要現在表級別加一個IS鎖。
• 意向排他鎖：簡稱IX鎖，當事務準備在某條記錄上加上X鎖時，需要現在表級別加一個IX鎖。

比如：

• select ... lock in share mode，要給表設定IS鎖;
• select ... for update，要給表設定IX鎖;

意向鎖又是如何解決這個效率低的問題呢：

如果一個事務A獲取到某一行的排他鎖，並未提交,這時候表上就有意向排他鎖和這一行的排他鎖。這時候事務B想要獲取這個表的共享鎖，此時因為檢測到事務A持有了表的意向排他鎖，因此事務A必然持有某些行的排他鎖，也就是說事務B對錶的加鎖請求需要阻塞等待，不再需要去檢測表的每一行資料是否存在排他鎖啦。

意向鎖僅僅表明意向的鎖，意向鎖之間不會互斥，是可以並行的，整體相容性如下：

2.3 記錄鎖（Record Lock）

記錄鎖是最簡單的行鎖，僅僅鎖住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果C1欄位是主鍵或者是唯一索引的話，這個SQL會加一個記錄鎖（Record Lock）

記錄鎖永遠都是加在索引上的，即使一個表沒有索引，InnoDB也會隱式的建立一個索引，並使用這個索引實施記錄鎖。它會阻塞其他事務對這行記錄的插入、更新、刪除。

一般我們看死鎖日誌時，都是找關鍵詞，比如lock_mode X locks rec but not gap），就表示一個X型的記錄鎖。記錄鎖的關鍵詞就是rec but not gap。以下就是一個記錄鎖的日誌：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.4 間隙鎖（Gap Lock）

為了解決幻讀問題，InnoDB引入了間隙鎖(Gap Lock)。間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最後一個索引之後的間隙。它鎖住的是一個區間，而不僅僅是這個區間中的每一條資料。

比如lock_mode X locks gap before rec表示X型gap鎖。以下就是一個間隙鎖的日誌：

RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

2.5 臨鍵鎖(Next-Key Lock)

Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。說得更具體一點就是:臨鍵鎖會封鎖索引記錄本身，以及索引記錄之前的區間，即它的鎖區間是前開後閉，比如(5,10]。

如果一個會話佔有了索引記錄R的共享/排他鎖，其他會話不能立刻在R之前的區間插入新的索引記錄。

If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

2.6 插入意向鎖

插入意向鎖,是插入一行記錄操作之前設定的一種間隙鎖，這個鎖釋放了一種插入方式的訊號。它解決的問題：多個事務，在同一個索引，同一個範圍區間插入記錄時，如果插入的位置不衝突，不會阻塞彼此。

假設有索引值4、7，幾個不同的事務準備插入5、6，每個鎖都在獲得插入行的獨佔鎖之前用插入意向鎖各自鎖住了4、7之間的間隙，但是不阻塞對方因為插入行不衝突。以下就是一個插入意向鎖的日誌：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

鎖模式相容矩陣（橫向是已持有鎖，縱向是正在請求的鎖）：

2.7 自增鎖

自增鎖是一種特殊的表級別鎖。它是專門針對AUTO_INCREMENT型別的列，對於這種列，如果表中新增資料時就會去持有自增鎖。簡言之，如果一個事務正在往表中插入記錄，所有其他事務的插入必須等待，以便第一個事務插入的行，是連續的主鍵值。

官方文件是這麼描述的：

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

假設有表：

mysql> create table t0 (id int NOT NULL AUTO_INCREMENT,name varchar(16),primary key ( id));

mysql> show variables like '%innodb_autoinc_lock_mode%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set, 1 warning (0.01 sec)

設定事務A和B交替執行流程如下：

透過上圖我們可以看到，當我們在事務A中進行自增列的插入操作時，另外會話事務B也進行插入操作，這種情況下會發生2個奇怪的現象：

• 事務A會話中的自增列好像直接增加了2個值。（如上圖中步驟7、8）
• 事務B會話中的自增列直接從2開始增加的。（如上圖步驟5、6）

自增鎖是一個表級別鎖，那為什麼會話A事務還沒結束，事務B可以執行插入成功呢？不是應該鎖表嘛？這是因為在引數innodb_autoinc_lock_mode上，這個引數設定為1的時候，相當於將這種auto_inc lock弱化為了一個更輕量級的互斥自增長機制去實現，官方稱之為mutex。

innodb_autoinc_lock_mode還可以設定為0或者2，

• 0：表示傳統鎖模式，使用表級AUTO_INC鎖。一個事務的INSERT-LIKE語句在語句執行結束後釋放AUTO_INC表級鎖，而不是在事務結束後釋放。
• 1: 連續鎖模式,連續鎖模式對於Simple inserts不會使用表級鎖，而是使用一個輕量級鎖來生成自增值，因為InnoDB可以提前直到插入多少行資料。自增值生成階段使用輕量級互斥鎖來生成所有的值，而不是一直加鎖直到插入完成。對於bulk inserts類語句使用AUTO_INC表級鎖直到語句完成。
• 2:交錯鎖模式,所有的INSERT-LIKE語句都不使用表級鎖，而是使用輕量級互斥鎖。

• INSERT-LIKE:指所有的插入語句，包括：INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等。
• Simple inserts:指在插入前就能確定插入行數的語句，包括：INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE這類語句。
• Bulk inserts: 指在插入錢不能確定行數的語句，包括：INSERT … SELECT/REPLACE … SELECT/LOAD DATA。

3. 什麼是死鎖？如何防止死鎖？

什麼是死鎖？

死鎖是指兩個或多個事務在同一資源上相互佔用，並請求鎖定對方的資源，從而導致惡性迴圈的現象。

如何防止死鎖？

• 儘量約定固定的順序訪問表，因為交叉訪問更容易造成事務等待迴路。
• 儘量避免大事務，建議拆成多個小事務。因為大事務佔用的鎖資源越多，越容易出現死鎖。
• 降低資料庫隔離級別，比如RR降低為RC，因為RR隔離級別，存在GAP鎖，死鎖機率大很多。
• 死鎖與索引是密不可分的，合理最佳化你的索引，死鎖機率降低。
• 如果業務處理不好可以用分散式事務鎖或者使用樂觀鎖

4.資料庫的樂觀鎖和悲觀鎖

悲觀鎖：

悲觀鎖她專一且缺乏安全感了，她的心只屬於當前事務，每時每刻都擔心著它 心愛的資料可能被別的事務修改，所以一個事務擁有（獲得）悲觀鎖後，其他任何事務都不能對資料進行修改啦，只能等待鎖被釋放才可以執行。select…for update就是MySQL悲觀鎖的應用。

樂觀鎖：

樂觀鎖的“樂觀情緒”體現在，它認為資料的變動不會太頻繁。因此，它允許多個事務同時對資料進行變動。實現方式：樂觀鎖一般會透過version版本號/時間戳判斷記錄是否被更改過，一般配合CAS演算法實現。

大家可以看下這篇文章哈：圖文並茂的帶你徹底理解悲觀鎖與樂觀鎖

5. select for update加的是表鎖還是行鎖

這道面試題，一般需要分兩種資料庫隔離級別（RR和RC），還需要分查詢條件是唯一索引、主鍵、一般索引、無索引等幾種情況分開討論

在RC隔離級別下

• 如果查詢條件是唯一索引，會加IX意向排他鎖（表級別的鎖，不影響插入）、兩把X排他鎖（行鎖，分別對應唯一索引，主鍵索引）
• 如果查詢條件是主鍵，會加IX意向排他鎖（表級別的鎖，不影響插入）、一把對應主鍵的X排他鎖（行鎖，會鎖住主鍵索引那一行）。
• 如果查詢條件是普通索引，如果查詢命中記錄，會加IX意向排他鎖（表鎖）、兩把X排他鎖（行鎖，分別對應普通索引的X鎖，對應主鍵的X鎖）;如果沒有命中資料庫表的記錄，只加了一把IX意向排他鎖（表鎖，不影響插入）
• 如果查詢條件是無索引，會加兩把鎖，IX意向排他鎖（表鎖）、一把X排他鎖（行鎖，對應主鍵的X鎖）。

查詢條件是無索引，為什麼不鎖表呢？MySQL會走聚簇(主鍵)索引進行全表掃描過濾。每條記錄都會加上X鎖。但是，為了效率考慮，MySQL在這方面進行了改進，在掃描過程中，若記錄不滿足過濾條件，會進行解鎖操作。同時最佳化違背了2PL原則。

在RR隔離級別

• 如果查詢條件是唯一索引，命中資料庫表記錄時，一共會加三把鎖：一把IX意向排他鎖 （表鎖，不影響插入），一把對應主鍵的X排他鎖（行鎖），一把對應唯一索引的X排他鎖 （行鎖）。
• 如果查詢條件是主鍵，會加IX意向排他鎖（表級別的鎖，不影響插入）、一把對應主鍵的X排他鎖（行鎖，會鎖住主鍵索引那一行）。
• 如果查詢條件是普通索引，命中查詢記錄的話，除了會加X鎖（行鎖），IX鎖（表鎖，不影響插入），還會加Gap 鎖（間隙鎖，會影響插入）。
• 如果查詢條件是無索引，會加一個IX鎖（表鎖，不影響插入），每一行實際記錄行的X鎖，還有對應於supremum pseudo-record的虛擬全錶行鎖。這種場景，通俗點講，其實就是鎖表了。

大家可以看下我之前寫的這篇文章哈：聊聊select for update到底加了什麼鎖

6.如何使用資料庫分散式鎖

一般可以使用select ... for update來實現資料庫的分散式鎖。它的優點是：簡單，使用方便，不需要引入Redis、zookeeper等中介軟體。缺點是：不適合高併發的場景，db操作效能較差。

我之前這篇文章，有詳細介紹資料庫分散式鎖的實現，大家可以看看哈：面試必備：聊聊分散式鎖的多種實現！

7.表級鎖和行級鎖，頁面鎖有什麼區別

不同的儲存引擎：

• MyISAM儲存引擎，它只支援表鎖，併發寫的時候，效能差。
• InnoDB儲存引擎，即支援表鎖，也支援行鎖，預設為行級鎖。
• BDB 儲存引擎，它支援表鎖和頁鎖。

表級鎖和行級鎖的區別：

• 表鎖：開銷小，加鎖快；鎖定力度大，發生鎖衝突機率高，併發度最低;不會出現死鎖。
• 行鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖衝突的機率低，併發度高，行級鎖能大大減少資料庫操作的衝突
• 頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

8. Mysql一條SQL是如何加鎖的？

一條SQL加鎖，可以分9種情況進行：

• 組合一：id 列是主鍵，RC 隔離級別
• 組合二：id 列是二級唯一索引，RC 隔離級別
• 組合三：id 列是二級非唯一索引，RC 隔離級別
• 組合四：id 列上沒有索引，RC 隔離級別
• 組合五：id 列是主鍵，RR 隔離級別
• 組合六：id 列是二級唯一索引，RR 隔離級別
• 組合七：id 列是二級非唯一索引，RR 隔離級別
• 組合八：id 列上沒有索引，RR 隔離級別
• 組合九：Serializable 隔離級別

可以看我這篇文章，第3小節有詳細講到哈：兩萬字詳解！InnoDB 鎖專題！

9. 併發情況下，如何做到安全的修改同一行資料

要安全的修改同一行資料，就要保證一個執行緒在修改時其它執行緒無法更新這行 記錄。其實一般有悲觀鎖和樂觀鎖兩種思想，

悲觀鎖思想就是，當前執行緒要進來修改資料時，別的執行緒都得拒之門外~ 比如， 可以使用 select…for update ~，

select * from User where name=‘jay’ for update

以上這條 sql 語句會鎖定了User表中所有符合檢索條件（name=‘jay’）的記 錄。本次事務提交之前，別的執行緒都無法修改這些記錄。

還可以使用樂觀鎖思想：

所謂樂觀鎖思想，就是有執行緒過來，先放過去修改，如果看到別的執行緒沒修改過， 就可以修改成功，如果別的執行緒修改過，就修改失敗或者重試。實現方式：樂 觀鎖一般會使用版本號機制或 CAS 演算法實現。可以看一下我這篇文章，主要是思路哈~ CAS 樂觀鎖解決併發問題的一次實踐

當然，回答這個問題的時候呢，你可以提到分散式鎖。分散式鎖有這三種實現方式：

• 資料庫分散式鎖
• Redis分散式鎖
• Zookeeper分散式鎖

大家可以看我這篇文章哈：面試必備：聊聊分散式鎖的多種實現！

10. RR隔離級別下的加鎖規則是怎麼樣的？

其實，極客時間丁奇的MySQL45講有講到，RR隔離級別，是如何加鎖的。大家有興趣可以去訂購看下哈，非常不錯的課程。

首先MySQL的版本，是5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。加鎖規則一共包括：兩個原則、兩個最佳化和一個bug。

• 原則1：加鎖的基本單位都是next-key lock。next-key lock（臨鍵鎖）是前開後閉區間。
• 原則2：查詢過程中訪問到的物件才會加鎖。
• 最佳化1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為行鎖（Record lock）。
• 最佳化 2：索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock退化為間隙鎖（Gap lock）。
• 一個 bug：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。

大家可以看我這篇文章，第4小節有詳細講到哈：兩萬字詳解！InnoDB 鎖專題！

11. InnoDB 中行級鎖時如何實現的？InnoDB三種行鎖的演算法

MySQL上的行級鎖是透過給索引上的索引項加鎖來實現，只有透過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。同時，當兩條不同行的記錄使用了相同的索引鍵時，也是會發生鎖衝突的。

比如這條SQL：

select * from t where id = 666 for update;複製程式碼
for update 

可以根據條件來完成行鎖鎖定，並且 id 是有索引鍵的列，如果 id 不是索引鍵那麼InnoDB將實行表鎖。

InnoDB行鎖的3種演算法：

• Record Lock：單個索引記錄上的鎖，
• Gap Lock ：間隙鎖，鎖定一個範圍，但不包含記錄本身
• Next-Key Lock：它等於Gap Lock + Record Lock，鎖定一個範圍，並且鎖定記錄本身。

如果查詢條件的是唯一索引，或者主鍵時，Next-Key Lock會降為Record Lock。如果是普通索引，將對下一個鍵值加上gap lock，其實就是對下一個鍵值的範圍為加鎖。gap lock間隙鎖，就是為了解決幻讀問題而設計出來的。

間隙鎖是RR隔離級別的，如果你想關閉間隙鎖，你可以修改隔離級別。也可以修改這個資料庫引數innodb_locks_unsafe_for_binlog為1.

12. MySQL 遇到過死鎖問題嗎，你是如何解決的？

我排查死鎖的一般步驟是醬紫的：

• 檢視死鎖日誌show engine innodb status;
• 找出死鎖Sql
• 分析sql加鎖情況
• 模擬死鎖案發
• 分析死鎖日誌
• 分析死鎖結果