技術文章 » MySQL學習之聊聊鎖及分類

MySQL學習之聊聊鎖及分類

2022-01-28 19:00:40
本篇文章帶大家瞭解MySQL中的鎖，介紹一下鎖的粒度分類和鎖的相容性分類，希望對大家有所幫助。

1. 資料庫並行場景

在高並行場景下，不考慮其他中介軟體的情況下，資料庫會存在以下場景：

  • 讀讀：不存在任何問題，也不需要並行控制。
  • 讀寫：有執行緒安全問題，可能會造成事務隔離性問題，可能遇到髒讀，幻讀，不可重複讀。
  • 寫寫：有執行緒安全問題，可能會存在更新丟失問題，比如第一類更新丟失，第二類更新丟失。

針對以上問題，SQL 標準規定不同隔離級別下可能發生的問題不一樣：

MySQL 四大隔離級別：

隔離級別髒讀不可重複讀幻讀
READ UNCOMMITTED：未提交讀可能發生可能發生可能發生
READ COMMITTED：已提交讀解決可能發生可能發生
REPEATABLE READ：可重複讀解決解決可能發生
SERIALIZABLE：可序列化解決解決解決

可以看到，MySQL 在 REPEATABLE READ 隔離級別實際上就解決了不可重複度問題，基本解決了幻讀問題，但在極端情況下仍然存在幻讀現象。

那麼有什麼方式來解決呢？一般來說有兩種方案：

1️⃣ 讀操作 MVCC ，寫操作加鎖

對於讀，在 RR 級別的 MVCC 下，當一個事務開啟的時候會產生一個 ReadView，然後通過 ReadView 找到符合條件的歷史版本，而這個版本則是由 undo 紀錄檔構建的，而在生成 ReadView 的時候，其實就是生成了一個快照，所以此時的 SELECT 查詢也就是快照讀（或者一致性讀），我們知道在 RR 下，一個事務在執行過程中只有第一次執行 SELECT 操作才會生成一個 ReadView，之後的 SELECT 操作都複用這個 ReadView，這樣就避免了不可重複讀和很大程度上避免了幻讀的問題。

對於寫，由於在快照讀或一致性讀的過程中並不會對錶中的任何記錄做加鎖操作並且 ReadView 的事務是歷史版本，而對於寫操作的最新版本兩者並不會衝突，所以其他事務可以自由的對錶中的記錄做改動。

2️⃣ 讀寫操作都加鎖

如果我們的一些業務場景不允許讀取記錄的舊版本，而是每次都必須去讀取記錄的最新版本，比方在銀行存款的事務中，你需要先把賬戶的餘額讀出來，然後將其加上本次存款的數額，最後再寫到資料庫中。在將賬戶餘額讀取出來後，就不想讓別的事務再存取該餘額，直到本次存款事務執行完成，其他事務才可以存取賬戶的餘額。這樣在讀取記錄的時候也就需要對其進行加鎖操作，這樣也就意味著讀操作和寫操作也像寫-寫操作那樣排隊執行。

對於髒讀，是因為當前事務讀取了另一個未提交事務寫的一條記錄，但如果另一個事務在寫記錄的時候就給這條記錄加鎖，那麼當前事務就無法繼續讀取該記錄了，所以也就不會有髒讀問題的產生了。

對於不可重複讀，是因為當前事務先讀取一條記錄，另外一個事務對該記錄做了改動之後並提交之後，當前事務再次讀取時會獲得不同的值，如果在當前事務讀取記錄時就給該記錄加鎖，那麼另一個事務就無法修改該記錄，自然也不會發生不可重複讀了。

對於幻讀，是因為當前事務讀取了一個範圍的記錄，然後另外的事務向該範圍內插入了新記錄，當前事務再次讀取該範圍的記錄時發現了新插入的新記錄，我們把新插入的那些記錄稱之為幻影記錄。

怎麼理解這個範圍？如下：

  • 假如表 user 中只有一條id=1的資料。

  • 當事務 A 執行一個id = 1的查詢操作，能查詢出來資料，如果是一個範圍查詢，如 id in(1,2)，必然只會查詢出來一條資料。

  • 此時事務 B 執行一個id = 2的新增操作，並且提交。

  • 此時事務 A 再次執行id in(1,2)的查詢，就會讀取出 2 條記錄，因此產生了幻讀。

注：由於 RR 可重複讀的原因，其實是查不出 id = 2的記錄的，所以如果執行一次 update ... where id = 2，再去範圍查詢就能查出來了。

採用加鎖的方式解決幻讀問題就有不太容易了，因為當前事務在第一次讀取記錄時那些幻影記錄並不存在，所以讀取的時候加鎖就有點麻煩，因為並不知道給誰加鎖。

那麼 InnoDB 是如何解決的呢？我們先來看看 InnoDB 儲存引擎有哪些鎖。

2. MySQL中的鎖及分類

在 MySQL 官方檔案 中，InnoDB 儲存引擎介紹了以下幾種鎖：

1.png

同樣，看起來仍然一頭霧水，但我們可以按照學習 JDK 中鎖的方式來進行分類：

2.png

3. 鎖的粒度分類

什麼是鎖的粒度？所謂鎖的粒度就是你要鎖住的範圍是多大。

比如你在家上衛生間，你只要鎖住衛生間就可以了，不需要將整個家都鎖起來不讓家人進門吧，衛生間就是你的加鎖粒度。

怎樣才算合理的加鎖粒度呢？

其實衛生間並不只是用來上廁所的，還可以洗澡，洗手。這裡就涉及到優化加鎖粒度的問題。

你在衛生間裡洗澡，其實別人也可以同時去裡面洗手，只要做到隔離起來就可以，如果馬桶，浴缸，洗漱臺都是隔開相對獨立的（乾溼分離了屬於是），實際上衛生間可以同時給三個人使用，當然三個人做的事兒不能一樣。這樣就細化了加鎖粒度，你在洗澡的時候只要關上浴室的門，別人還是可以進去洗手的。如果當初設計衛生間的時候沒有將不同的功能區域劃分隔離開，就不能實現衛生間資源的最大化使用。

同樣，在 MySQL 中也存在鎖的粒度。通常分為三種，行鎖，表鎖和頁鎖。

3.1 行鎖

在共用鎖和獨佔鎖的介紹中其實都是針對某一行記錄的，所以也可以稱之為行鎖。

對一條記錄加鎖影響的也只是這條記錄而已，所以行鎖的鎖定粒度在 MySQL 中是最細的。InnoDB 儲存引擎預設鎖就是行鎖。

它具有以下特點：

  1. 鎖衝突概率最低，並行性高

    由於行鎖的粒度小，所以發生鎖定資源爭用的概率也最小，從而鎖衝突的概率就低，並行性越高。

  2. 開銷大，加鎖慢

    鎖是非常消耗效能的，試想一下，如果對資料庫的多條資料加鎖，必然會佔用很多資源，而對於加鎖需要等待之前的鎖釋放才能加鎖。

  3. 會產生死鎖

    關於什麼是死鎖，可以往下看。

3.2 表鎖

表級鎖為表級別的鎖定，會鎖定整張表，可以很好的避免死鎖，也是 MySQL 中最大顆粒度的鎖定機制。

MyISAM 儲存引擎的預設鎖就是表鎖。

它具有以下特點：

  1. 開銷小，加鎖快

    由於是對整張表加鎖，速度必然快於單條資料加鎖。

  2. 不會產生死鎖

    都對整張表加鎖了，其他事務根本拿不到鎖，自然也不會產生死鎖。

  3. 鎖粒度大，發生鎖衝突概率大，並行性低

3.3 頁鎖

頁級鎖是 MySQL 中比較獨特的一種鎖定級別，在其他資料庫管理軟體中並不常見。

頁級鎖的顆粒度介於行級鎖與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的並行處理能力同樣也是介於上面二者之間。另外，頁級鎖和行級鎖一樣，會發生死鎖。


行鎖表鎖頁鎖
鎖的粒度小大兩者之間
加鎖效率慢快兩者之間
衝突概率低高-
並行效能高低一般
效能開銷大小兩者之間
是否死鎖是否是

4. 鎖的相容性分類

在 MySQL 中資料的讀取主要分為當前讀和快照讀：

  • 快照讀

    快照讀，讀取的是快照資料，不加鎖的普通 SELECT 都屬於快照讀。

    SELECT * FROM table WHERE ...

  • 當前讀

    當前讀就是讀的是最新資料，而不是歷史的資料，加鎖的 SELECT，或者對資料進行增刪改都會進行當前讀。

    SELECT * FROM table LOCK IN SHARE MODE;
SELECT FROM table FOR UPDATE;
INSERT INTO table values ...
DELETE FROM table WHERE ...
UPDATE table SET ...

而在大多數情況下，我們運算元據庫都是當前讀的情形，而在並行場景下，既要允許讀-讀情況不受影響，又要使寫-寫、讀-寫或寫-讀情況中的操作相互阻塞，就需要用到 MySQL 中的共用鎖和獨佔鎖。

4.1 共用鎖和獨佔鎖

共用鎖（Shared Locks），也可以叫做讀鎖，簡稱 S 鎖。可以並行的讀取資料，但是任何事務都不能對資料進行修改。

獨佔鎖（Exclusive Locks），也可以叫做排他鎖或者寫鎖，簡稱 X 鎖。若某個事物對某一行加上了排他鎖，只能這個事務對其進行讀寫，在此事務結束之前， 其他事務不能對其進行加任何鎖，其他程序可以讀取，不能進行寫操作，需等待其釋放。

來分析一下獲取鎖的情形：假如存在事務 A 和事務 B

  • 事務 A 獲取了一條記錄的 S 鎖，此時事務 B 也想獲取該條記錄的 S 鎖，那麼事務 B 也能獲取到該鎖，也就是說事務 A 和事務 B 同時持有該條記錄的 S 鎖。

  • 如果事務 B 想要獲取該記錄的 X 鎖，則此操作會被阻塞，直到事務 A 提交之後將 S 鎖釋放。

  • 如果事務 A 首先獲取的是 X 鎖，則不管事務 B 想獲取該記錄的 S 鎖還是 X 鎖都會被阻塞，直到事務 A 提交。

因此，我們可以說 S 鎖和 S 鎖是相容的， S 鎖和 X 鎖是不相容的， X 鎖和 X 鎖也是不相容的。

4.2 意向鎖

意向共用鎖（Intention Shared Lock），簡稱 IS 鎖。當事務準備在某條記錄上加 S 鎖時，需要先在表級別加一個 IS 鎖。

意向獨佔鎖（Intention Exclusive Lock），簡稱 IX 鎖。當事務準備在某條記錄上加 X 鎖時，需要先在表級別加一個 IX 鎖。

意向鎖是表級鎖，它們的提出僅僅為了在之後加表級別的 S 鎖和 X 鎖時可以快速判斷表中的記錄是否被上鎖，以避免用遍歷的方式來檢視表中有沒有上鎖的記錄。就是說其實 IS 鎖和 IS 鎖是相容的，IX 鎖和 IX 鎖是相容的。

為什麼需要意向鎖？

InnoDB 的意向鎖主要使用者多粒度的鎖並存的情況。比如事務A要在一個表上加S鎖，如果表中的一行已被事務 B 加了 X 鎖，那麼該鎖的申請也應被阻塞。如果表中的資料很多，逐行檢查鎖標誌的開銷將很大，系統的效能將會受到影響。

舉個例子，如果表中記錄 1 億，事務 A 把其中有幾條記錄上了行鎖了，這時事務 B 需要給這個表加表級鎖，如果沒有意向鎖的話，那就要去表中查詢這一億條記錄是否上鎖了。如果存在意向鎖，那麼假如事務Ａ在更新一條記錄之前，先加意向鎖，再加Ｘ鎖，事務 B 先檢查該表上是否存在意向鎖，存在的意向鎖是否與自己準備加的鎖衝突，如果有衝突，則等待直到事務Ａ釋放，而無須逐條記錄去檢測。事務Ｂ更新表時，其實無須知道到底哪一行被鎖了，它只要知道反正有一行被鎖了就行了。

說白了意向鎖的主要作用是處理行鎖和表鎖之間的矛盾，能夠顯示某個事務正在某一行上持有了鎖，或者準備去持有鎖。

表級別的各種鎖的相容性：


SISXIX
S相容相容不相容不相容
IS相容相容不相容不相容
X不相容不相容不相容不相容
IS相容相容不相容不相容

4.3 讀操作的鎖

對於 MySQL 的讀操作，有兩種方式加鎖。

1️⃣ SELECT * FROM table LOCK IN SHARE MODE

如果當前事務執行了該語句，那麼它會為讀取到的記錄加 S 鎖，這樣允許別的事務繼續獲取這些記錄的 S 鎖（比方說別的事務也使用 SELECT ... LOCK IN SHARE MODE 語句來讀取這些記錄），但是不能獲取這些記錄的 X 鎖（比方說使用 SELECT ... FOR UPDATE 語句來讀取這些記錄，或者直接修改這些記錄）。

如果別的事務想要獲取這些記錄的 X 鎖，那麼它們會阻塞，直到當前事務提交之後將這些記錄上的 S 鎖釋放掉

2️⃣ SELECT FROM table FOR UPDATE

如果當前事務執行了該語句，那麼它會為讀取到的記錄加 X 鎖，這樣既不允許別的事務獲取這些記錄的 S 鎖（比方說別的事務使用 SELECT ... LOCK IN SHARE MODE 語句來讀取這些記錄），也不允許獲取這些記錄的 X 鎖（比如說使用 SELECT ... FOR UPDATE 語句來讀取這些記錄，或者直接修改這些記錄）。

如果別的事務想要獲取這些記錄的 S 鎖或者 X 鎖，那麼它們會阻塞，直到當前事務提交之後將這些記錄上的 X 鎖釋放掉。

4.4 寫操作的鎖

對於 MySQL 的寫操作，常用的就是 DELETE、UPDATE、INSERT。隱式上鎖，自動加鎖，解鎖。

1️⃣ DELETE

對一條記錄做 DELETE 操作的過程其實是先在 B+樹中定位到這條記錄的位置，然後獲取一下這條記錄的 X 鎖，然後再執行 delete mark 操作。我們也可以把這個定位待刪除記錄在 B+樹中位置的過程看成是一個獲取 X 鎖的鎖定讀。

2️⃣ INSERT

一般情況下，新插入一條記錄的操作並不加鎖，InnoDB 通過一種稱之為隱式鎖來保護這條新插入的記錄在本事務提交前不被別的事務存取。

3️⃣ UPDATE

在對一條記錄做 UPDATE 操作時分為三種情況：

① 如果未修改該記錄的鍵值並且被更新的列佔用的儲存空間在修改前後未發生變化，則先在 B+樹中定位到這條記錄的位置，然後再獲取一下記錄的 X 鎖，最後在原記錄的位置進行修改操作。其實我們也可以把這個定位待修改記錄在 B+樹中位置的過程看成是一個獲取 X 鎖的鎖定讀。

② 如果未修改該記錄的鍵值並且至少有一個被更新的列佔用的儲存空間在修改前後發生變化，則先在 B+樹中定位到這條記錄的位置，然後獲取一下記錄的 X 鎖，將該記錄徹底刪除掉（就是把記錄徹底移入垃圾連結串列），最後再插入一條新記錄。這個定位待修改記錄在 B+樹中位置的過程看成是一個獲取 X 鎖的鎖定讀，新插入的記錄由 INSERT 操作提供的隱式鎖進行保護。

③ 如果修改了該記錄的鍵值，則相當於在原記錄上做 DELETE 操作之後再來一次 INSERT 操作，加鎖操作就需要按照 DELETE 和 INSERT 的規則進行了。

PS：為什麼上了寫鎖，別的事務還可以讀操作？

因為InnoDB有 MVCC機制（多版本並行控制），可以使用快照讀，而不會被阻塞。

4. 鎖的粒度分類

什麼是鎖的粒度？所謂鎖的粒度就是你要鎖住的範圍是多大。

比如你在家上衛生間，你只要鎖住衛生間就可以了，不需要將整個家都鎖起來不讓家人進門吧，衛生間就是你的加鎖粒度。

怎樣才算合理的加鎖粒度呢？

其實衛生間並不只是用來上廁所的，還可以洗澡，洗手。這裡就涉及到優化加鎖粒度的問題。

你在衛生間裡洗澡，其實別人也可以同時去裡面洗手，只要做到隔離起來就可以，如果馬桶，浴缸，洗漱臺都是隔開相對獨立的（乾溼分離了屬於是），實際上衛生間可以同時給三個人使用，當然三個人做的事兒不能一樣。這樣就細化了加鎖粒度，你在洗澡的時候只要關上浴室的門，別人還是可以進去洗手的。如果當初設計衛生間的時候沒有將不同的功能區域劃分隔離開，就不能實現衛生間資源的最大化使用。

同樣，在 MySQL 中也存在鎖的粒度。通常分為三種，行鎖，表鎖和頁鎖。

4.1 行鎖

在共用鎖和獨佔鎖的介紹中其實都是針對某一行記錄的，所以也可以稱之為行鎖。

對一條記錄加鎖影響的也只是這條記錄而已，所以行鎖的鎖定粒度在 MySQL 中是最細的。InnoDB 儲存引擎預設鎖就是行鎖。

它具有以下特點：

  • 鎖衝突概率最低，並行性高

    由於行鎖的粒度小，所以發生鎖定資源爭用的概率也最小，從而鎖衝突的概率就低，並行性越高。

  • 開銷大，加鎖慢

    鎖是非常消耗效能的，試想一下，如果對資料庫的多條資料加鎖，必然會佔用很多資源，而對於加鎖需要等待之前的鎖釋放才能加鎖。

  • 會產生死鎖

    關於什麼是死鎖，可以往下看。

4.2 表鎖

表級鎖為表級別的鎖定，會鎖定整張表，可以很好的避免死鎖，也是 MySQL 中最大顆粒度的鎖定機制。

MyISAM 儲存引擎的預設鎖就是表鎖。

它具有以下特點：

  • 開銷小，加鎖快

    由於是對整張表加鎖，速度必然快於單條資料加鎖。

  • 不會產生死鎖

    都對整張表加鎖了，其他事務根本拿不到鎖，自然也不會產生死鎖。

  • 鎖粒度大，發生鎖衝突概率大，並行性低

4.3 頁鎖

頁級鎖是 MySQL 中比較獨特的一種鎖定級別，在其他資料庫管理軟體中並不常見。

頁級鎖的顆粒度介於行級鎖與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的並行處理能力同樣也是介於上面二者之間。另外，頁級鎖和行級鎖一樣，會發生死鎖。


行鎖表鎖頁鎖
鎖的粒度小大兩者之間
加鎖效率慢快兩者之間
衝突概率低高-
並行效能高低一般
效能開銷大小兩者之間
是否死鎖是否是

5. 演演算法實現分類

對於上面的鎖的介紹，我們實際上可以知道，主要區分就是在鎖的粒度上面，而 InnoDB 中用的鎖就是行鎖，也叫記錄鎖，但是要注意，這個記錄指的是通過給索引上的索引項加鎖。

InnoDB 這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖。

不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會使用行鎖來對資料加鎖。

只有執行計劃真正使用了索引，才能使用行鎖：即便在條件中使用了索引欄位，但是否使用索引來檢索資料是由 MySQL 通過判斷不同執行計劃的代價來決 定的，如果 MySQL 認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。

同時當我們用範圍條件而不是相等條件檢索資料，並請求鎖時，InnoDB 會給符合條件的已有資料記錄的索引項加鎖。

不過即使是行鎖，InnoDB 裡也是分成了各種型別的。換句話說即使對同一條記錄加行鎖，如果型別不同，起到的功效也是不同的。通常有以下幾種常用的行鎖型別。

5.1 Record Lock

記錄鎖，單條索引記錄上加鎖。

Record Lock 鎖住的永遠是索引，不包括記錄本身，即使該表上沒有任何索引，那麼innodb會在後臺建立一個隱藏的聚集主鍵索引，那麼鎖住的就是這個隱藏的聚集主鍵索引。

記錄鎖是有 S 鎖和 X 鎖之分的，當一個事務獲取了一條記錄的 S 型記錄鎖後，其他事務也可以繼續獲取該記錄的 S 型記錄鎖，但不可以繼續獲取 X 型記錄鎖；當一個事務獲取了一條記錄的 X 型記錄鎖後，其他事務既不可以繼續獲取該記錄的 S 型記錄鎖，也不可以繼續獲取 X 型記錄鎖。

5.2 Gap Locks

間隙鎖，對索引前後的間隙上鎖，不對索引本身上鎖。

MySQL 在 REPEATABLE READ 隔離級別下是可以解決幻讀問題的，解決方案有兩種，可以使用 MVCC 方案解決，也可以採用加鎖方案解決。但是在使用加鎖方案解決時有問題，就是事務在第一次執行讀取操作時，那些幻影記錄尚 不存在，我們無法給這些幻影記錄加上記錄鎖。所以我們可以使用間隙鎖對其上鎖。

如存在這樣一張表：

CREATE TABLE test (
    id INT (1) NOT NULL AUTO_INCREMENT,
    number INT (1) NOT NULL COMMENT '數位',
    PRIMARY KEY (id),
    KEY number (number) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

# 插入以下資料
INSERT INTO test VALUES (1, 1); 
INSERT INTO test VALUES (5, 3); 
INSERT INTO test VALUES (7, 8); 
INSERT INTO test VALUES (11, 12);

如下：

開啟一個事務 A：

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

此時，會對((1,1),(5,3))和((5,3),(7,8))之間上鎖。

3.png

如果此時在開啟一個事務 B 進行插入資料，如下：

BEGIN;

# 阻塞
INSERT INTO test (id, number) VALUES (2,2);

結果如下：

4.png

為什麼不能插入？因為記錄(2,2)要 插入的話，在索引 number上，剛好落在((1,1),(5,3))和((5,3),(7,8))之間，是有鎖的，所以不允許插入。 如果在範圍外，當然是可以插入的，如：

INSERT INTO test (id, number) VALUES (8,8);

5.3 Next-Key Locks

next-key locks 是索引記錄上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合，包括記錄本身，每個 next-key locks 是前開後閉區間，也就是說間隙鎖只是鎖的間隙，沒有鎖住記錄行，next-key locks 就是間隙鎖基礎上鎖住右邊界行。

預設情況下，InnoDB 以 REPEATABLE READ 隔離級別執行。在這種情況下，InnoDB 使用 Next-Key Locks 鎖進行搜尋和索引掃描，這可以防止幻讀的發生。

6. 樂觀鎖和悲觀鎖

樂觀鎖和悲觀鎖其實不算是具體的鎖，而是一種鎖的思想，不僅僅是在 MySQL 中體現，常見的 Redis 等中介軟體都可以應用這種思想。

6.1 樂觀鎖

所謂樂觀鎖，就是持有樂觀的態度，當我們更新一條記錄時，假設這段時間沒有其他人來操作這條資料。

實現樂觀鎖常見的方式

常見的實現方式就是在表中新增 version欄位，控制版本號，每次修改資料後+1。

在每次更新資料之前，先查詢出該條資料的 version版本號，再執行業務操作，然後在更新資料之前在把查到的版本號和當前資料庫中的版本號作對比，若相同，則說明沒有其他執行緒修改過該資料，否則作相應的例外處理。

6.2 悲觀鎖

所謂悲觀鎖，就是持有悲觀的態度，一開始就假設改資料會被別人修改。

悲觀鎖的實現方式有兩種

共用鎖（讀鎖）和排它鎖（寫鎖），參考上面。

7. 死鎖

是指兩個或兩個以上的程序在執行過程中，由於競爭資源或者由於彼此通訊而造成的一種阻塞的現象，若無外力作用，它們都將無法推進下去。此時稱系統 處於死鎖狀態或系統產生了死鎖。

產生的條件

  • 互斥條件：一個資源每次只能被一個程序使用；
  • 請求與保持條件：一個程序因請求資源而阻塞時，對已獲得的資源保持不放；
  • 不剝奪條件：程序已獲得的資源，在沒有使用完之前，不能強行剝奪；
  • 迴圈等待條件：多個程序之間形成的一種互相迴圈等待的資源的關係。

MySQL 中其實也是一樣的，如下還是這樣一張表：

CREATE TABLE `user` (
  `id` bigint NOT NULL COMMENT '主鍵',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT NULL COMMENT '性別',
  `age` varchar(10) DEFAULT NULL COMMENT '年齡',
  `url` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suf_index_url` (`name`(3)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

# 資料
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1', 'a', '1', '18', 'https://javatv.net');
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('2', 'b', '1', '18', 'https://javatv.net');

按照如下順序執行：


AB
①BEGIN
②
BEGIN
③SELECT * FROM user WHERE name='a' FOR UPDATE
④
SELECT * FROM user WHERE name='b' FOR UPDATE
⑤SELECT * FROM user WHERE name='b' FOR UPDATE
⑥
SELECT * FROM user WHERE name='a' FOR UPDATE

1、開啟 A、B 兩個事務；

2、首先 A 先查詢name='a'的資料，然後 B 也查詢name='b'的資料；

3、在 B 沒釋放鎖的情況下，A 嘗試對 name='b'的資料加鎖，此時會阻塞；

4、若此時，事務 B 在沒釋放鎖的情況下嘗試對 name='a'的資料加鎖，則產生死鎖。

5.png

此時，MySQL 檢測到了死鎖，並結束了 B 中事務的執行，此時，切回事務 A，發現原本阻塞的 SQL 語句執行完成了。可通過show engine innodb status \G檢視死鎖。

如何避免

從上面的案例可以看出，死鎖的關鍵在於：兩個(或以上)的 Session 加鎖的順序不一致，所以我們在執行 SQL 操作的時候要讓加鎖順序一致，儘可能一次性鎖定所需的資料行。

【相關推薦：】

以上就是MySQL學習之聊聊鎖及分類的詳細內容，更多請關注TW511.COM其它相關文章！