MySQL的鎖到底有多少內容 ？再和騰訊大佬的技術面談，我還是小看鎖了！

蓮花童子哪吒發表於2020-09-01
MySql

對酒當歌，人生幾何！ 朝朝暮暮，唯有己脫。

苦苦尋覓找工作之間，殊不知今日之時乃我心之痛，難到是我不配擁有工作嘛。自面試後他所謂的等待都過去一段時日，可惜在下京東上的小金庫都要見低啦。每每想到不由心中一緊。正處為難之間，手機忽然來了個簡訊預約後續面試。 我即刻三下五除二拎包踢門而出。飛奔而去。

此刻面試門外首先映入眼簾的是一個白色似皮球的東西，似圓非圓。好奇冬瓜落地一般。上半段還有一段溼溼的部分，顯得尤為入目。這是什麼情況？

緊接著現身一名中年男子。他身著純白色T桖衫的，一灰色寬鬆的休閒西褲，腰圍至少得三十好幾。外加一雙夏日必備皮製涼鞋。只見，他正低頭看著手上的一張A4紙。透過一頭黑色短髮。滿臉的贅肉橫生。外加上那大腹便便快要把那T桖衫給撐爆的肚子。

看得我好生害怕，不由得嚥了咽口水，生怕自己說錯話。這宛如一顆肉粽呀。不在職場摸滾打拼8、9年，也不會有當前這景象。

什麼是鎖

面試官：: 你是來參加面試的吧？
吒吒輝： 不 不 不，我是來參加複試呢。

面試官：: 看到上次別人點評，MySQL優化還闊以。那你先談談對鎖的理解？

吒吒輝： 嘿嘿，還好！

鎖是計算機在進行多 程式、執行緒執行排程時強行限制資源訪問的同步機制，用於在併發訪問時保證資料的一致性、有效性；

鎖是在執行多執行緒時，用於強行限制資源訪問的同步機制，即用在併發控制中保證對互斥的要求。

一般的鎖是建議鎖（advisory lock），每個執行緒在訪問對應資源前都需獲取鎖的資訊，再根據資訊決定是否可以訪問。若訪問對應資訊，鎖的狀態會改變為鎖定，因此其它執行緒此時不會來訪問該資源，當資源結束後，會恢復鎖的狀態，允許其他執行緒的訪問。

有些系統有強制鎖（mandatory lock），若有未授權的執行緒想要訪問鎖定的資料，在訪問時就會產生異常。

                          ---《維基百科》

鎖的型別和應用原理

面試官：: 那一般資料庫有哪些鎖？ 一般怎麼使用？

此刻，用我那呆若木雞的眼神看向面試官，內心實屬尷尬+害怕，資料庫不就是共享和互斥鎖嗎？
這樣看來，是我太嫩。此處必有坑。殊不知此刻我內心已把你拿捏，定斬不饒。

吒吒輝： 資料庫的鎖根據不同劃分方式有很多種說法，在業務訪問上有以下兩種：

  • 排他鎖

在訪問共享資源之前對其進行加鎖，在訪問完成後進行解鎖操作。 加鎖成功後，任何其它執行緒請求來獲取鎖都會被阻塞，直到當前線自行釋放鎖。

執行緒3狀態:就緒、阻塞、執行

如解鎖時，有一個以上的執行緒阻塞（資源已釋放），那麼所有嘗試獲取該鎖的執行緒都被CPU認為就緒狀態， 如果第一個就緒狀態的執行緒又執行加鎖操作，那麼其他的執行緒又會進入就緒狀態。 在這種方式下，只能有一個執行緒訪問被互斥鎖保護的資源

故此，MySQL的SQL語句加了互斥鎖後，只有接受到請求並獲取鎖的執行緒才能夠訪問和修改資料。 因為互斥鎖是針對執行緒訪問控制而不是請求本身。

  • 共享鎖

被加鎖資源是可被共享的，但僅限於讀請求。它的寫請求只能被獲取到鎖的請求獨佔。 也就是加了共享鎖的資料，只能夠當前執行緒修改，其它執行緒只能讀資料，並不能修改。

吒吒輝： 在 SQL 請求上可分為讀、寫鎖。但本質還是對應對共享鎖和排它鎖。

面試官： 那 SQL 請求上不加鎖怎麼訪問？ 為啥說它們屬於共享鎖和排他鎖? 這之間有何聯絡？

吒吒輝： 除加鎖讀外，還有一種不加鎖讀的情況。這種方式稱為快照讀，讀請求加鎖稱為共享讀。

針對請求加共享、排它鎖的原因在於，讀請求天生是冪等性的，不論你讀多少次資料不會發生變化，所以給讀請求加上鎖就應該為共享鎖。 不然怎麼保證它的特點呢？
而寫請求，本身就需對資料進行修改，所以就需要排它鎖來保證資料修改的一致性。

吒吒輝： 如果按照鎖的顆粒度劃分看，就有表鎖和行鎖

  • 表鎖：

是MySQL中最基本的鎖策略，並且是開銷最小的策略。併發處理較少。表鎖由MySQL服務或儲存引擎管理。多數情況由服務層管理，具體看SQL操作。

例如：伺服器會為諸如 ALTER TABLE 之類的語句使用表鎖
，而忽略儲存引擎的鎖。

加鎖機制:

它會鎖定整張表。一個使用者在對錶進行寫操作（插人、刪除、更新等)前，需要先獲得寫鎖，這會阻塞其他使用者對該表的所有讀寫操作。只有沒有寫鎖時，其他使用者才能獲取到讀鎖。

  • 行鎖:

鎖定當前訪問行的資料，併發處理能力很強。但鎖開銷最大。具體視行資料多少決定。由innoDB儲存引擎支援。

  • 頁級鎖：

頁級鎖是 MySQL 中鎖定粒度介於行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但衝突多，行級衝突少，但速度慢。因此，採取了折衷的頁級鎖，一次鎖定相鄰的一組記錄。由BDB 儲存引擎管理頁級鎖。

面試官： 為啥是表鎖開銷小，而不是行鎖呢？ 畢竟表鎖鎖定是整張表

吒吒輝： 表鎖鎖定的是表沒錯，但它不是把表裡面所有的資料行都上鎖，相當於是封鎖了表的入口，這樣它只是需要判斷每個請求是否可以獲取到表的鎖，沒有就不鎖定。
而行鎖是針對表的每一行資料，資料量一多，鎖定內容就多，故開銷大。 但因它顆粒度小，鎖定行不會影響到別的行。所以併發就高。而如果表鎖在一個入口就卡死了，那整體請求處理肯定就會下降。

面試官： 我記得行鎖裡面有幾種不同的實現方式，你知道嗎？

您可真貼心啊，替我考慮這麼多，大佬都是這麼心比針細？ 我要是說不知道，你老是不是又準備給出穿小鞋啦。強忍內心啃人的衝動

ps:讀懂圖，說明你有故事

吒吒輝： innodb雖支援行鎖，但鎖實現的演算法卻和SQL的查詢形式有關係：

  • Record Lock（記錄鎖）：單個行記錄上的鎖。也就是我們日常認為的行鎖。由

`
where =
`
的形式觸發

  • Gap Lock（間隙鎖）：間隙鎖，鎖定一個範圍，但不包括記錄本身（它鎖住了某個範圍內的多個行，包括根本不存在的資料）。

GAP鎖的目的，是為了防止事務插入而導致幻讀的情況。該鎖只會在隔離級別是RR或者以上的級別記憶體在。間隙鎖的目的是為了讓其他事務無法在間隙中新增資料。 SQL裡面用 where >、>=等範圍條件觸發，但會根據鎖定的範圍內，是否包含了表中真實存在的記錄進行變化，如果存在真實記錄就會進化為 臨建鎖。反之就為間隙所。

  • Next-Key Lock（臨鍵鎖）：它是記錄鎖和間隙鎖的結合，鎖定一個範圍，並且鎖定記錄本身。對於行的查詢，都是採用該方法，主要目的是解決幻讀的問題。next-key 鎖是InnoDB預設的。是一個左開右閉的規則
  • IS鎖：意向共享鎖、Intention Shared Lock。當事務準備在某條記錄上加S（讀）鎖時，需要先在表級別加一個IS鎖。
  • IX鎖：意向排它鎖、Intention Exclusive Lock。當事務準備在某條記錄上加X（寫）鎖時，需要先在表級別加一個IX鎖。

面試官： 那這個東西是怎麼實現的？

t(id PK, name KEY, sex, flag);

表中有四條記錄：

1, zhazhahui, m, A

3, nezha, m, A

5, lisi, m, A

9, wangwu, f, B
  • 記錄鎖

select * from t where id=1 for update;
鎖定 id =1的記錄

  • 間隙鎖

select * from t where id > 3 and id < 9 ;

鎖定(3,5]，（5，9）範圍的值，因為當前訪問3到9的範圍記錄，就需要鎖定表裡面已經存在的資料來解決幻讀和不可重複讀的問題

  • 臨建鎖

select * from t where id >=9 ;

會鎖定 [9,+∞) 。查詢會先選中 9 號記錄，所以鎖定範圍就以9開始到正無窮資料。

面試官： 那意向排它、共享鎖呢？是怎麼個內容

吒吒輝： 意向排它鎖和意向共享鎖，是針對當前SQL請求訪問資料行時，會提前進行申請訪問，如果最終行鎖未命中就會退化為該型別的表鎖。

面試官： 那有這個意向排它鎖有什麼好處呢？

吒吒輝： 可提前做預判，每次嘗試獲取行鎖之前會檢查是否有表鎖，如果存在就不會繼續申請行鎖，從而減少鎖的開銷。從而整個表就退化為表鎖。

面試官： 那你動手給我演示下每個場景

嗯。。。（瞳孔放大2倍）我這不說的很明白嗎？
難道故意和作對，這是幹嘛啊。欺負人嘛不是
只見那面試官忽然翹起來二郎腿，還有節拍的抖動著腿，看向我。一看就是抖音整多了
哎，沒辦法 官大以及壓死人。打碎了牙齒自己咽。你給我看細細看好了，最好眼睛都別眨

吒吒輝： 因為鎖就是解決事務併發的問題，所以記錄鎖就不演示了，直接遊蕩在間隙和臨建鎖裡面。

建立語句：

CREATE TABLE `t1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

表資料：

間隙鎖：

  • 關閉 MySQL 預設的事務自動提交機制。

    • 關閉前：

  • 關閉後：

加鎖：

直接插入 >8 的資料就阻塞，都會上鎖。為的就解決插入新資料而導致幻讀。

【啊！幻讀不知道呀。下篇文章給大家安排上】

面試官： 你這條件不是>=8嗎？ 那等於8呢？ 被吃辣？

吒吒輝： 彆著急嘛，這不還沒說完嗎。為什麼不指定8呢？

因為 >=8 的條件會從間隙鎖升級為臨建鎖，因為你條件裡面包含了 8 這個真實存在的資料。所以會把它鎖起來。如下：

所以，最終的行鎖會和SQL語句的條件觸發有關係，一旦範圍查詢包含了資料庫裡面真實存在資料，就會升級為臨建鎖。不要問我為什麼？ 看前面的定義

面試官獨白：這小夥多少看來還有有點貨，不錯。此刻面試官露出一絲笑容。殊不知他內心又開醞釀起了新的想法。就等我入甕

面試官： 那什麼場景下行鎖不會生效呢？鎖 鎖定的又是什麼？

此刻，我呆了，這都什麼跟什麼啊。不帶這麼玩的吧。天殺的，淨使壞

鎖的觸發機制

吒吒輝：
innodb的行鎖是根據索引觸發，如果沒有相關的索引，那行鎖將會退化成表鎖（即鎖定整個表裡的行）。
而 鎖 鎖定的是索引即索引樹裡面的資料庫欄位的值。

  • id為主鍵索引欄位。

  • 給 age 欄位上鎖

  • age 欄位沒索引，退化成表鎖。直接查詢將失敗。

有索引，用索引欄位查詢可得資料，其餘欄位查詢將失敗。因為獲取不到行鎖，只能等待。而鎖定的是索引，故此其它用其它索引值查詢能拿查詢資料

  • 索引欄位上鎖

  • 索引當前欄位鎖定，用其餘索引欄位可查詢

  • 不是索引欄位都差不到。

面試官： 你前面說到的鎖可以解決事務併發，然而MVCC也是用於解決併發，那幹嘛還用鎖來呢？你給說說

吒吒輝： 通過MVCC可以解決髒讀、不可重複讀、幻讀這些讀一致性問題，但實際上這只是解決了普通select語句的資料讀取問題。
事務利用MVCC進行的讀取操作稱之為快照讀，所有普通的SELECT語句在READ COMMITTED、REPEATABLE READ隔離級別下都算是快照讀。

除了快照讀之外，還有一種是鎖定讀，即在讀取的時候給記錄加鎖，在鎖定讀的情況下依然要解決髒讀、不可重複讀、幻讀的問題。

比如：如果 1 4 7 9 的資料。如果條件為 where > 4 的，那如果不鎖定到 （4，7] (7,9],(9，+∞）。那勢必就會早幻讀，不可重複讀的問題。

ps:不重複讀？髒讀是如何產生的？

死鎖

面試官： 那你說下資料庫的死鎖是個什麼情況？

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

當事務試圖以不同的順序鎖定資源時，就可能產生死鎖。多個事務同時鎖定同一個資源時也可能會產生死鎖。

一般可通過死鎖檢測和死鎖超時機制來解決該問題。
死鎖檢查：
像InnoDB儲存引擎，就能檢測到死鎖的迴圈依賴，並立即返回一個錯誤。否則死鎖會導致出現非常慢的查詢。通過引數 innodb_deadlock_detect 設定為on，來開啟。

超時機制：
就是當查詢的時間達到鎖等待超時的設定後放棄鎖請求。InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務進行回滾（這是相對比較簡單的死鎖回滾演算法)。

可通過配置引數 innodb_lock_wait_timeout 用來設定超時時間。如果有些使用者使用哪種大事務，就設定鎖超時時間大於事務執行時間。
但這種情況下死鎖超時檢查的發現時間是無法接受的。

面試官： 那你說說InnoDB和MyisAM是如何發現死鎖的？

吒吒輝：

  • innodb

資料庫會把事務單元鎖維持的鎖和它所等待的鎖都記錄下來，Innodb提供了wait-for graph演算法來主動進行死鎖檢測，每當加鎖請求無法立即滿足需要進入等待時，wait-for graph演算法都會被觸發。當資料庫檢測到兩個事務不同方向地給同一個資源加鎖（產生循序），它就認為發生了死鎖，觸發wait-for graph演算法。

比如：事務1給A加鎖，事務2給B加鎖，同時事務1給B加鎖（等待），事務2給A加鎖就發生了死鎖。那麼死鎖解決辦法就是終止一邊事務的執行即可，這種效率一般來說是最高的，也是主流資料庫採用的辦法。

Innodb目前處理死鎖的方法就是將持有最少行級排他鎖的事務進行回滾。這是相對比較簡單的死鎖回滾方式。死鎖發生以後，只有部分或者完全回滾其中一個事務，才能打破死鎖。

對於事務型的系統，這是無法避免的，所以應用程式在設計必須考慮如何處理死鎖。大多數情況下只需要重新執行因死鎖回滾的事務即可。

  • MyisAM

MyisAM自身只支援表級鎖，故加鎖後一次性獲取的。所以資源上不會出現多個事務之間互相需要對方釋放鎖之後再來進行處理。故不會有死鎖

面試官： wait-for graph 演算法怎麼理解？

吒吒輝： 如下所示，四輛車就是死鎖

它們相互等待對方的資源，而且形成環路！每輛車可看為一個節點，當節點1需要等待節點2的資源時，就生成一條有向邊指向節點2，最後形成一個有向圖。我們只要檢測這個有向圖是否出現環路即可，出現環路就是死鎖！這就是wait-for graph演算法。

Innodb將各個事務看為一個個節點，資源就是各個事務佔用的鎖，當事務1需要等待事務2的鎖時，就生成一條有向邊從1指向2，最後行成一個有向圖。

面試官： 既然死鎖無法避免，那如何減少發生呢？

吒吒輝：

  • 對應用程式進行調整/修改。某些情況下，你可以通過把大事務分解成多個小事務，使得鎖能夠更快被釋放，從而極大程度地降低死鎖發生的頻率。在其他情況下，死鎖的發生是因為兩個事務採用不同的順序操作了一個或多個表的相同的資料集。需要改成以相同順序讀寫這些資料集，換言之，就是對這些資料集的訪問採用序列化方式。這樣在併發事務時，就讓死鎖變成了鎖等待。
  • 修改表的schema，例如：刪除外來鍵約束來分離兩張表，或者新增索引來減少掃描和鎖定的行。
  • 如果發生了間隙鎖，你可以把會話或者事務的事務隔離級別更改為RC(read committed)級別來避免，可以避免掉很多因為gap鎖造成的死鎖，但此時需要把binlog_format設定成row或者mixed格式。
  • 為表新增合理的索引，不走索引將會為表的每一行記錄新增上鎖（等同表鎖），死鎖的概率大大增大。
  • 為了在單個InnoDB 表上執行多個併發寫入操作時避免死鎖，可以在事務開始時通過為預期要修改的每個元祖（行）使用SELECT ... FOR UPDATE語句來獲取必要的鎖，即使這些行的更改語句是在之後才執行的。
  • 通過SELECT ... LOCK IN SHARE MODE獲取行的讀鎖後，如果當前事務再需要對該記錄進行更新操作，則很有可能造成死鎖。因進行獲鎖讀取在修改

這時，只見對面所坐面試官，捋了捋那沒有毛髮的下巴，故作深思熟慮，像是在端詳這什麼。 難道 難道 是讓我通過了嗎？
此刻內心猶如小鹿亂撞，吶喊到我要幹它二量。真的是不容易。 就在此時，他起身而立，那白色T桖衫包裹著那甩大肚子，猶如波浪上下翻滾。一看就是沒少在酒桌上擼肉。

只見開口到，小夥子不錯啊。

這是肯定我嗎？ 不容易啊，今天不開幾把LOL，難消我心頭之恨

面試官： 其實這資料庫嘛 ，內容還是有很多的，你回去準備下，下一次的面試吧

。。。。什麼個玩意兒，下次？ 那就是這次不行啦， 這還沒考夠啊，下巴本來沒毛，你捋個什麼勁兒，整得個神神忽忽的。 此時內心猶如翻江倒海，猛龍過江。白鶴亮翅的衝動打他，奈何我這小身板子不行

吒吒輝： 那行吧，下次是多久啊，我這好多天都沒整頓好的啦，你給我個準信唄。

我用那水汪汪可憐的小眼神望向他說到。他卻很斯文的笑著，說道

面試官： 快了，小夥子彆著急，我看好你的，加油

我加你那擼啊絲壓榨花生油。 面個試，還嫌我臉上出油出的不多，都是被你擠出來的。只有強忍住內心的衝動。 哎 官大一級壓死人啊
吒吒輝： 行吧，那我走啦
此刻，露出我那灰溜溜的背影，猶如魯迅先生筆下的孔乙己
參考：
《高效能MySQL》
https://zhuanlan.zhihu.com/p/29150809
https://www.cnblogs.com/yulibostu/articles/9978618.html
如有幫助，歡迎點贊關注分享額，微信搜尋【蓮花童子哪吒】 獲取體系化內容，加我納入群聊，一起交流學習進步提升。

相關文章