一文搞懂鎖知識

一位風塵仆仆的男子走了過來，對我說：小伙子，我看你簡歷上寫的精通mysql，那我就問問你mysql的知識吧！

我：好的（千萬別多問，千萬別多問～～）

面試官：對mysql鎖了解的多嗎？

我：還行（其實就是很好），

面試官：那好，那我接下來主要問鎖相關的知識

我：好的好的（放馬過來吧，我也就是客氣一下，）

面試官： mysql支持哪些級別的鎖？
我：支持庫鎖、表鎖、行鎖，

面試官：那先說說庫鎖吧，鎖庫有幾種方式？

我：兩種，分別是FTWRL（Flush tables with read lock）和 set global readonly=true

面試官：這兩種有什么區別？

我：首先不管是誰，只要鎖庫了，那么整個庫都處于只讀狀態，所有的更新操作都將被阻塞，FTWRL模式風險稍微小點，如果客戶端例外斷開后，FTWRL鎖會自動釋放，但是global readonly=true不會自動釋放鎖，

面試官：那myisam和innodb的鎖有什么區別，

我：myisam不支持行鎖，只支持表級的鎖，innodb支持更細粒度的行鎖，
面試官：表級鎖使用過嗎?

我：沒有用過，表鎖性能差，

面試官：那你知道MDL鎖嗎？

我：了解，MDL（metadata lock）鎖是server層的鎖，表級鎖，它是隱式的，不需要顯式的使用，mysql每次讀寫資料時（insert、update、select、delete）都要先去獲取MDL讀鎖，只有獲取到了MDL讀鎖，才能進行接下來的操作，否則阻塞，其中MDL讀鎖之間是共享的，當對資料庫進行表結構變更的時候，會獲取MDL寫鎖，MDL寫鎖和任何MDL鎖都是互斥的，不管是MDL讀鎖還是MDL寫鎖，

面試官：那MDL鎖的作用是什么？

我：MDL鎖是為了解決DDL和DML之間沖突的問題，

1. 假設事務A先查詢得到一個資料，然后事務B執行了欄位修改，那么事務A再次去查的時候，發現資料對不上了，

1. 事務A先更新資料未提交，事務B修改欄位提交，slave就會先修改欄位，再更新資料，那么就會有問題

當使用了MDL鎖后，DDL操作必須要先獲得MDL寫鎖，我們知道寫鎖和寫鎖，寫鎖和讀鎖是沖突的，那么在DDL之前如果有任何查詢或者更新，都必須要阻塞等待，不會讓DDL執行的，從而解決了沖突問題，

面試官：那有了MDL鎖，在線DDL是不是就很安全？

我：不一定，

假設session1先執行查詢，但是不提交，session2緊接著執行添加欄位的DDL，最后session3再執行查詢，此時會發現session2和session3都是阻塞的，如果后面還來了sessionN的查詢，那么都將阻塞，嚴重時將造成大量執行緒阻塞，

面試官：那你能解釋下為什么這種情況下會阻塞嗎？

我：首先MDL鎖一定是在事務提交后才釋放的，session1在執行查詢后，并沒有commit，那么MDL讀鎖是沒有釋放的，session2緊接著執行DDL，執行DDL是要獲取MDL寫鎖的，由于寫鎖和讀鎖是互斥的，那么session2是卡住的，它在等待session1釋放讀鎖，session3在session2之后執行的，此時session3是需要一個讀鎖的，但是由于獲取鎖是有先后順序的，它們要排隊，并且寫鎖的優先級要高于讀鎖，這也是為什么session3會卡住，

面試官：所以是session1執行commit之后，然后session2先執行完，最后session3先執行完？我剛試了下你的例子，看著好像session2和session3幾乎同時運行完，你能否具體說說為什么呢？（看你進不進坑）

我：（想給我挖坑，沒門），不是的，并不是session2先執行完，其實是session3先執行完，但是session2會先獲得MDL寫鎖，由于session3沒有顯式的開啟一個事務，那么session3默認執行完畢之后自動commit，所以在session1 commit之后，看起來像session2和session3幾乎同時進行的，如果讓session3顯式的開啟事務，就能發現運行的細節了，

這樣當session1 commit之后可以發現session3先執行，session2依然是卡住的，只有當session3 commit之后，發現session2才能運行，所以真實情況應該是session3先執行，然后session2，

面試官：那這和你上面說的獲取MDL鎖排隊的問題是不是矛盾了？

我：不矛盾，這其實涉及到online DDL的知識了，我們知道mysql支持在線DDL，不阻塞用戶操作，當執行一個DDL，它的流程大概是這樣的：

1. 拿MDL寫鎖
2. 降級成MDL讀鎖
3. 真正做DDL
4. 升級成MDL寫鎖
5. 釋放MDL鎖

其中session2在拿到MDL寫鎖后，會降級成MDL讀鎖，降級后，session3拿到MDL讀鎖，然后執行select，但是沒有commit，這樣MDL讀鎖就沒釋放，然后session2在升級成MDL寫鎖的時候因為session3沒釋放讀鎖從而導致session2阻塞，

面試官：（這小子不錯呀），那你知道為什么DDL程序中1-2要降級，而3-4又要升級嗎？

我：（早知道就要問，還好我有準備），

首先在MDL寫鎖期間，干的事就是創建臨時的frm和idb檔案，這個程序要安全，是排他的，同時這個程序也是快速的，在臨時檔案創建好之后，就不需要排他了，那么就降級為讀鎖，支持正常的增刪改查，這也是為什么DDL支持online的原因之一，在新的資料檔案寫好之后，要替換老的資料檔案，這個程序要安全，所以在3執行完后，會嘗試升級成MDL寫鎖，這個程序也是快速的，也是支持online DDL的原因之二，

面試官：我們知道InnoDB支持行級鎖，行鎖還分兩類你知道嗎？

我：知道，S（共享鎖）和X（排他鎖），S鎖和S鎖是共享的，X鎖和任意鎖互斥，

面試官：既然X鎖和任意鎖互斥，那么如果存在兩個事務，事務A更新資料后，不提交，那么事務B去查詢這條資料是不是就阻塞？

我：不會的，因為InnoDB支持MVCC（多版本控制），當一個事務執行查詢的時候，它可以通過undo log查詢到一個快照，這樣就不用鎖，

面試官：那你知道IS（意向共享鎖）和IX（意向排他鎖）嗎？

我：首先它倆都是表級別的鎖，因為InnoDB是支持行鎖的，當某些行已經上了X鎖之后，再想對這個表上鎖的話，就得確認當前表中沒有任何X鎖，在沒有意向鎖的情況下，就得一行一行去判斷，這樣效率會非常低下，在有了意向鎖之后，就不需要一行一行判斷了，舉個例子：

select * from user where id=1 for update;

當一個事務對id=1這行資料上了X鎖之后，就會對user表也加一個IX鎖，

LOCK TABLES user READ;

這時想要給表加上讀鎖，但是發現表上有IX鎖，所以會阻塞無法執行，類似的如果一個事務對一行資料加上共享鎖

select * from user where id=1 lock in share mode;

就會給對應的表加上IS鎖，這時候如果執行

LOCK TABLES user WRITE;

也會因為表上有IS鎖而阻塞，

面試官：那我們來聊聊行鎖吧，InnoDB支持哪些行鎖？

我：有記錄鎖（Record Lock）、間隙鎖（Gap Lock）、Next-Key Lock

面試官：假設有一張表，表里有10條記錄，還有個欄位user_id，并且user_id是普通索引，

+----+---------+
| id | user_id |
+----+---------+
|  1 |      10 |
|  2 |      20 |
|  3 |      30 |
|  4 |      40 |
|  5 |      50 |
|  6 |      60 |
|  7 |      70 |
|  8 |      80 |
|  9 |      90 |
| 10 |     100 |
+----+---------+

如果事務A執行：

SELECT * FROM user WHERE user_id=50 FOR UPDATE;

緊接著事務B執行下面的sql會發生什么？：

INSERT INTO user set user_id=45;

我：阻塞，

面試官：能說下原因嗎？

我：因為InnoDB的Next-Key Lock演算法，不僅僅會鎖住user_id=50這條記錄，還會鎖住50左右的間隙，Next-Key Lock鎖定的范圍是左開右閉的，那么理論上最終(40,50],(50,60]的區間資料會被鎖定，

由于要插入的45在40-50之間，所以就會發生阻塞，

面試官：那按照你說的區間鎖法，是包含60這條資料的是吧，所以如果插入一條60的資料，就會發生阻塞？

INSERT INTO user set user_id=60;

我：其實不會，這里涉及到next key lock的優化，在等值查詢中，向右遍歷時且最后一個值不滿足等值條件的時候，Next-Key Lock會退化為Gap Lock，所以對于(50,60]這個區間最侄訓降級為(50,60)，那么60這條資料就不會被鎖住，是可以插入成功的，

面試官：那40這條資料是不在這個鎖區間的，所以可以插入40這條資料？

INSERT INTO user set user_id=40;

我：其實也不會，40這條資料的插入也會阻塞，首先對于非聚集索引user_id它的葉子節點一定是排序的，大概就像(40,4)，（50,5）這樣，其次因為主鍵id是自增的，那么對于再插入一條40的資料，它的主鍵id一定是大于4的，就目前10條資料來說，下一次插入的id肯定是11，那(40,11)這條資料肯定是要在(40,4)后面的，這樣的話，就落入到了間隙鎖中，所以會阻塞，其實上面60那條資料可以插入也是同樣的道理，

面試官：那如果我一開始不用select for update了，而用select lock in share mode，那么所有的插入會有什么變化嗎？

我：沒有變化，還是一樣，因為插入需要X鎖，X鎖和任何鎖都互斥，

面試官：如果user_id不是普通索引而是唯一索引，那會有什么變化？

我：當索引是唯一索引時，那么就會發生降級，Next Key Lock會降級成Record Lock，最終只會鎖住50這條記錄，

面試官：如果user_id沒有索引怎么辦？

我：那就所有的記錄都會鎖上，任何的插入都會阻塞，

面試官：那你知道為什么要有間隙鎖這個東西嗎？

我：為了解決幻讀，比如當事務A執行以下查詢時：

SELECT * FROM user WHERE id>=9 for update

應該回傳兩條記錄（id=9和id=10），這時候如果另一個事務B執行

INSERT INTO user set user_id=110;

在沒有間隙鎖的情況下，那么事務A再次查詢會發現多了一條記錄，就出現了幻讀，如果有了間隙鎖，那么[9，+∞)這個區間都會被鎖住，事務B的插入就會阻塞，但是只有在事務的隔離級別設定成可重復讀的時候，才支持間隙鎖，

面試官：如果某個上了鎖的事務一直不提交，那么后面需要獲取相關鎖的事務就會阻塞，這樣會有什么問題？

我：如果阻塞的事務越來越多，那么阻塞的執行緒也會越來越多，嚴重時會造成連接池滿了，mysql不能提供服務了，但是InnoDB支持阻塞超時后，會自動放棄這個等待鎖的sql命令，這個值默認是50s，

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

面試官看了看我：那你知道AUTO-INC Locking嗎？

我：知道，自增長鎖，在InnoDB引擎中，每個表都會維護一個表級別的自增長計數器，當對表進行插入的時候，會通過以下的命令來獲取當前的自增長的值，

SELECT MAX(auto_inc_col)  FROM user FOR UPDATE;

插入操作會在這個基礎上加1得到即將要插入的自增長id，

面試官：我們知道事務中的鎖是在事務提交后才釋放的，那么在更新自增長id后，當事務沒來及提交，其它的事務獲取自增長id就要等待嗎？這樣的話效率是不是有點低？

我：不用等待的，為了提高插入性能，自增長的鎖不會等到事務提交之后才釋放，而是在相關插入sql陳述句完成后立刻就釋放的，這也是為什么一些事務回滾之后，發現id不連續的原因：

select * from user;
+----+---------+
| id | user_id |
+----+---------+
| .. |      .. |
|  9 |      90 |
| 10 |     100 |
| 12 |     120 |
+----+---------+
# 11那條資料回滾了，但是id也被消耗了，id不會回滾，

面試官：雖然AUTO-INC Locking可以不用等事務提交就釋放，但是在并發的時候，因為AUTO-INC Locking本身會對自增id上鎖的，還是會影響效率，這個該怎么解決？

我：現在InnoDB支持互斥量的方式來實作自增長，通過互斥量可以對記憶體中的計數器進行累加操作，比AUTO-INC Locking要快些，

面試官：那你知道死鎖嗎？

我：知道，

面試官：什么情況下會出現死鎖？

我：死鎖出現的條件就是請求和保持，就是每一方都保持著對方的需要的資源同時請求對方占用的資源，比如：

事務1先鎖id=1這條資料，緊接著事務2鎖住id=2這條資料，然后事務1再次嘗試鎖住id=2這條資料，但是發現被事務2占著在，所以此時事務1會阻塞，最后事務2嘗試獲取id=1的鎖，但是發現被事務1占著在，所以也會阻塞，那么此時就陷入僵局了，這就是死鎖，

面試官：那如何解決死鎖問題呢？

我：

1. InnoDB提供鎖超時的功能，當一個事務獲取鎖超時之后會自動放棄，另一個事務就可以執行，

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

1. 可以讓每次更新都按照約定的順序去更新，這樣也可以避免死鎖，

1. 通過死鎖檢測來提前判斷，InnoDB默認是開啟死鎖檢測的，

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+

InnoDB通過等待圖的方式來進行死鎖檢測的，這要求存盤鎖的資訊鏈表和事務的等待鏈表，然后通過鏈表構造一張等待圖，每次獲取鎖的時候會通過等待圖來判斷是否會造成死鎖，

面試官看了看自己的勞力士：（看來今天鎖不住這個小伙子了，時間也差不多了）那你在這稍等下，我喊下hr，

我：好的好的（終于結束了），

微信搜【假裝懂編程】，一起深入交流