MySQL事務學習筆記(三） 甚歡篇

北冥有隻魚發表於2022-04-10
MySql筆記

在我剛開始工作的時候，就聽前輩說過這個概念，但是還是似懂非懂。這個概念也是躺在我的學習計劃中，擱置到今天才準備去徹底去學些這個概念。

我會列一個學習大綱，上面有我感興趣的概念，每當開啟Typora ， 就會想寫這個，又想寫這個。想起高中時期比較喜歡的一位武俠小說作者滄月，按她的說法是她的電腦裡面也存了一些未完成的小說，然後每次開啟電腦創作，這些小說就好像對她說： 寫我，寫我。本文主要講述的是InnoDB中鎖的相關概念，主要內容也是參考自掘金小冊《MySQL 是怎樣執行的：從根兒上理解 MySQL》，我主要是選取了自己想知道的內容，又組合了一下。

建議閱讀本系列的文章的時候可以先看:

  • MySQL優化學習手札(一)
  • MySQL優化學習筆記手札(二)
  • MySQL優化學習手札(三)
  • MySQL事務學習筆記(一) 初遇篇
  • MySQL事務學習筆記(二) 相識篇

會出問題的情況

只是讀的話，是不會出現併發問題的，那我們首先將這種情況忽略，那麼問題出現的重災區就是:

  • 寫 - 寫

即併發事務相繼對一條記錄進行改動，在這種情況就可能發生髒寫。在任何隔離級別下MySQL都不允許髒寫發生。所以在多個併發事務對相同記錄進行改動的時候，MySQL會讓他們排隊執行，這個排隊執行事實上是通過鎖來實現的，類似於現在的面試(不考慮群面這種情況)，一般面試官面試完這個人之後，才會叫HR讓下個人進來。在MySQL中當一個事務要對某條事務進行改動的時候，首先會看記憶體中有沒有與這條記錄關聯的鎖結構，當沒有的時候就會在記憶體中生成一個鎖結構與之相關聯。

  • 讀 - 寫 或 寫 - 讀

在這種情況下，可能發生髒讀、不可重複讀、幻讀的問題。這裡注意一下幻讀問題的產生是因為某個事務讀取了一個範圍的記錄，之後其他事務又在該範圍內插入新記錄，該事務再次讀取該範圍的記錄的時候，可以讀到新插入的記錄。所以幻讀並不是因為讀取和寫入一條相同的記錄而產生。

在前面我們已經嘮叨過在不同的隔離級別下可能會發生的問題了，這裡再簡單的提一下，不再過多的進行贅述，不同資料庫廠商對SQL標準的支援可能都不一樣，MySQL在 REPEATABLE READ隔離級別實際上就已經解決了幻讀問題。

解決方案在MySQL也就是兩個:

  • MVCC: 讀利用多版本併發控制(MVCC), 寫操作進行加鎖。
  • 讀、寫都採取加鎖方式

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

為什麼呢，我們來分析一下，我們討論的還是在可重複讀這個級別上進行討論，假設我們以轉賬業務為例，如果允許讀舊的賬戶餘額會發生什麼樣的問題。

如果是像下面的語句來執行:

 UPDATE Student Set money = money + 50 where id = '1';

兩次提交併不會產生問題，因為兩個事務排隊執行。 我原先的想法是在執行UPDATE的時候MySQL也讀了，這樣讀到事務提交之前的記錄，最終就會是轉了兩次賬，只加了五十塊錢。那如果是如果是做運算呢，比如說我們在程式碼裡面做運算，然後最後的更新語句像下面這樣就會有問題:

 UPDATE Student Set money = 60 where id = '1';

後提交的事務就會把先提交的事務覆蓋掉，這並不算髒寫，只是兩次獨立的更新操作。

採用MVCC方式的話，讀-寫操作並不衝突，效能更好，採用加鎖方式的話，需要排隊執行，影響效能。一般情況下我們更願意採取MVCC來解決事務併發執行帶來的問題，但是業務在某些情況下，不能接受MVCC，在MySQL裡面似乎也只剩下了加鎖這一個選項。

事務利用MVCC進行的讀取操作稱之為一致性讀，或者一致性無鎖讀、快照讀。所有的SELECT語句在READ COMMITTED、REPEATABLE READ隔離級別都算是一致性讀。

鎖概念淺析

行共享鎖和獨佔鎖

在MySQL中鎖可以大致分為兩類:

  • 共享鎖 英文名 Shared Locks，簡稱為S鎖。
如果一個事務某個記錄上有共享鎖, 如果另一個事務也想獲取這把鎖，也是可以的，這就意味著兩個事務在該記錄上可以同時持有該鎖。
  • 獨佔鎖 也常稱 排他鎖，英文名: Exclusive Locks , 簡稱為S鎖，
如果事務T1已經獲取了該記錄的S鎖，事務T2想要再獲取一條記錄的X鎖，那麼此操作就會被阻塞，直到T1釋放掉S鎖。

MySQL提供了兩種語法來讓我們在讀取記錄的時候就可以獲取該記錄的X鎖、S鎖:

SELECT ... LOCK IN SHARE MODE; 
在讀取記錄的時候獲取該記錄的共享鎖
SELECT ... FOR UPDATE;
在該事務中獲取該記錄的X鎖

如果當前事務執行了獲取共享鎖的語句(SELECT ... LOCK IN SHARE MODE),那麼它會為讀取到的記錄加S鎖，這樣允許別的事務繼續獲取這些記錄的S鎖(如果其他事務也使用 SELECT ... LOCK IN SHARE MODE;來讀取這些記錄)，但是不能獲取這些記錄的X鎖(比方說使用SELECT ... FOR UPDATE來讀取這些記錄，或者修改這些記錄 ) 如果別的事務想要獲取這些記錄的X鎖，那麼它們會阻塞，直到當前事務提交之後將這些記錄尚的S鎖釋放掉。

如果事務中執行了SELECT ... FOR UPDATE， 那麼它會為讀取到的記錄加X鎖，別的事務即無法獲取這個記錄的S鎖和X鎖，如果事務想要獲取這些記錄的S鎖和X鎖，那麼它們會阻塞，直到當前事務提交之後將這些記錄上的X鎖釋放掉。

表共享鎖和獨佔鎖

上面我們提到的可以認為是行鎖，也就是針對若干行進行加鎖，這個鎖的粒度是比較細的。其實一個事務也可以在表級別進行加鎖，自然就被稱之為表級鎖或者表鎖，對錶加鎖影響整個表中的記錄，給表加的鎖也可以被分為共享鎖(S鎖)和獨佔鎖(X鎖):

如果一個事務給表加了S鎖:

  • 其他事務可以繼續獲取得該表的S鎖
  • 其他事務可以繼續獲取該表中記錄的S鎖
  • 別的事務無法獲取該表的X鎖
  • 如果該行記錄上面有S鎖，那麼其他事務無法獲得該行記錄的X鎖

如果一個事務給表加了X鎖(獨佔這個表)：

  • 其他事務無法獲得該表的S鎖
  • 如果該表中的記錄上有X鎖，那麼其他事務無法獲得該記錄的S鎖。
  • 如果該表中的記錄上有S鎖，那麼其他事務無法獲得該記錄的X鎖
  • 其他事務不可以繼續獲得該表中的某些記錄的X鎖。

但這事實上有兩個問題，如果MySQl想對錶整體上S鎖，首先需要確保表中的記錄不能有X鎖，遍歷表的記錄？ MySQL的開發人員設計了意向鎖:

  • 意向共享鎖 Intention Shared Lock, 簡稱為IS鎖。當事務準備在某條記錄尚加S鎖時，需要先在表加上IS鎖。
  • 意向獨佔鎖, Intention Exclusive Lock，簡稱為IX鎖。當事務準備在某條記錄上加X鎖，需要先在表級別加一個IX鎖。

這樣就可以避免全表掃表。

表鎖概述

在對某個表執行一些ALTER TABLE、DROP TABLE這裡的DDL語句時，其他事務對這個表併發執行增刪改查會發生阻塞，同理，某個事務對某個表執行SELECT、INSERT、DELETE、UPDATE語句時，其他會話中執行DDL語句也會發生阻塞。這個過程其實是通過Server層使用一種後設資料鎖(Metadata Locks ,簡稱為MDL)來實現的，一般情況下也不會使用InnoDB儲存引擎提供的表級別S鎖、X鎖。所以這個表級的S、X鎖頗有種雞肋的感覺。我們重點關注行鎖。上面我們嘮叨的意向鎖也屬於表鎖。再有就是MySQL的自增屬性，在MySQL有兩種方式實現自增:

  • 採用AUTO-INC鎖，也就是在執行插入語句的時候就在表上加上一個AUTO-INC鎖，然後為每條自增屬性的賦值。一個事務持有AUTO—INC鎖，其他插入事務會被阻塞。插入語句執行之後就被釋放。

​ 不確定插入的記錄數量可採取AUTO-INC鎖。

  • 輕量級鎖，在為插入語句生成自增屬性列的值之後，就把該輕量級鎖釋放掉，並不需要等到整個插入語句執行完成才釋放鎖。

    如果語句執行前就可以確定插入記錄數量，一般採用輕量級鎖的方式對自增列賦值。

那麼該如何選擇自增方式呢，InnoDB中有一個innodb_autoinc_lock_mode的系統變數來控制哪兩種方式來為自增列賦值，為0時，一律採用AUTO-INC鎖，為2時，一律採用輕量級鎖(可能會造成不同事務中的插入語句的自增列是交叉的，在有主從複製的場景是不安全的)。當為1時，插入記錄數量確定時，採取輕量級鎖，不確定時採取AUTO-INC鎖。

總結

MySQL鎖的概念比我原想的要複雜，知也無涯，生也有涯哉，本來打算這一節將MySQL中的鎖大致過一遍，但是到現在還是沒有梳理出一條主線將這些內容連在一起。到最後就是隻介紹了行鎖、表鎖的基本概念。

參考資料

  • 《MySQL 是怎樣執行的：從根兒上理解 MySQL》 掘金小冊

相關文章