?前言

大家好，我是田螺。

金三銀四很快就要來啦，準備了數據庫鎖的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加鎖情況
• 模擬死鎖案發
• 分析死鎖日志
• 分析死鎖結果