> 公眾號請關注ElseF，ElseF(Else Figure)，是一個專註於「分享」國內外高質量的科技類文章和新聞的自媒體。維護者主要來自國內外大型互聯網公司和創業公司，主要專註於與互聯網相關的精彩內容。

MySQL解決幻讀

啥是幻讀

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a 「phantom」 row.

上面這句話摘自MySQL的官方手冊。它只說明瞭讀會讀到上一次沒有返回的記錄，看起來是幻影一般。如果你理解到這裡，那麼恭喜你，你會遇到各種困惑。 其實幻讀的現象遠不止於此，更不僅僅只是兩次「讀」，第二次「讀」來發現有幻覺。

MySQL的隔離級別

MySQL的InnoDb存儲引擎默認的隔離級別是REPEATABLE-READ，即可重複讀。 那什麼是「可重複讀」呢，簡單來說就是一個事務裏的兩個相同條件的查詢查到的結果應該是一致的，即結果是「可以重複讀到的」，所以就解決了「幻讀」。

OK，聽起來很簡單，一個隔離級別就可以搞定了，但是內部的機制和原理並不簡單，並且有些概念的作用可能大家並不知道具體解決了什麼問題。

如何解決

MVCC

MVCC的原理

MVCC(Multi-Version Concurrency Control多版本並發控制)：

  • MVCC每次更新操作都會複製一條新的記錄，新紀錄的創建時間為當前事務id
  • 優勢為讀不加鎖，讀寫不衝突
  • InnoDb存儲引擎中，每行數據包含了一些隱藏欄位 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT
  • DATA_TRX_ID 欄位記錄了數據的創建和刪除時間，這個時間指的是對數據進行操作的事務的id
  • DATA_ROLL_PTR 指向當前數據的undo log記錄，回滾數據就是通過這個指針
  • DELETE BIT位用於標識該記錄是否被刪除，這裡的不是真正的刪除數據，而是標誌出來的刪除。真正意義的刪除是在mysql進行數據的GC，清理歷史版本數據的時候。

具體的DML：

  • INSERT：創建一條新數據，DB_TRX_ID中的創建時間為當前事務id，DB_ROLL_PT為NULL
  • UPDATE：將當前行的DB_TRX_ID中的刪除時間設置為當前事務id，DELETE BIT設置為1
  • DELETE：複製了一行，新行的DB_TRX_ID中的創建時間為當前事務id，刪除時間為空，DB_ROLL_PT指向了上一個版本的記錄，事務提交後DB_ROLL_PT置為NULL

可知，為了提高並發度，InnoDb提供了這個「非鎖定讀」，即不需要等待訪問行上的鎖釋放，讀取行的一個快照即可。 既然是多版本讀，那麼肯定讀不到隔壁事務的新插入數據了，所以解決了幻讀。

MVCC與隔離級別

  • Read Uncommitted每次都讀取記錄的最新版本，會出現臟讀，未實現MVCC
  • Serializable對所有讀操作都加鎖，讀寫發生衝突，不會使用MVCC
  • SELECT
    • (RR級別)InnoDb檢查每行數據，確保它們符合兩個標準：
    • 只查找創建時間早於當前事務id的記錄，這確保當前事務讀取的行都是事務之前已經存在的，或者是由當前事務創建或修改的行
    • 行的DELETE BIT為1時，查找刪除時間晚於當前事務id的記錄，確定了當前事務開始之前，行沒有被刪除
    • (RC級別)每次重新計算read view，read view的範圍為InnoDb中最大的事務id，為避免臟讀讀取的是DB_ROLL_PT指向的記錄

就這麼簡單嗎？ 其實幻讀有很多種出現形式，簡單的SELECT不加條件的查詢在RR下肯定是讀不到隔壁事務提交的數據的。但是仍然可能在執行INSERT/UPDATE時遇到幻讀現象。因為SELECT 不加鎖的快照讀行為是無法限制其他事務對新增重合範圍的數據的插入的。

所以還要引入第二個機制。

Next-Key Lock

其實更多的幻讀現象是通過寫操作來發現的，如SELECT了3條數據，UPDATE的時候可能返回了4個成功結果，或者INSERT某條不在的數據時忽然報錯說唯一索引衝突等。

首先來瞭解一下InnoDb的鎖機制，InnoDB有三種行鎖：

  • Record Lock：單個行記錄上的鎖
  • Gap Lock：間隙鎖，鎖定一個範圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況
  • Next-Key Lock：前兩個鎖的加和，鎖定一個範圍，並且鎖定記錄本身。對於行的查詢，都是採用該方法，主要目的是解決幻讀的問題

如果是帶排他鎖操作（除了INSERT/UPDATE/DELETE這種，還包括SELECT FOR UPDATE/LOCK IN SHARE MODE等），它們默認都在操作的記錄上加了Next-Key Lock。只有使用了這裡的操作後才會在相應的記錄周圍和記錄本身加鎖，即Record Lock + Gap Lock，所以會導致有衝突操作的事務阻塞進而超時失敗。

性能

隔離級別越高並發度越差，性能越差，雖然MySQL默認的是RR，但是如果業務不需要嚴格的沒有幻讀現象，是可以降低為RC的或修改配置innodb_locks_unsafe_for_binlog為1 來避免Gap Lock的。 注意有的時候MySQL會自動對Next-Key Lock進行優化，退化為只加Record Lock，不加Gap Lock，如相關條件欄位為主鍵時直接加Record Lock。

REPEATABLE-READ的誤解

誤解零

凡是在REPEATABLE-READ中執行的語句均不會遇到幻讀現象。

這個顯然是錯誤的。REPEATABLE-READ只是有機制可以用來防止幻讀的發生，但如果你沒有「使用」或「激活」它相關機制，你仍然會遇到幻讀現象。

誤解一

REPEATABLE-READ肯定不會讀到隔壁事務已經提交的數據，即使某個數據已經由隔壁事務提交，當前事務插入不會報錯，否則就是發生了幻讀。

簡單來說前半句話是對的，後半句有什麼問題呢？可REPEATABLE-READ中如何「讀」是我們自己來寫SELECT 的，如果不加鎖則屬於快照讀，當前事務讀不到的數據並不一定是不存在的，如果已經存在對應的數據，那麼當前事務嘗試插入的時候是可能會失敗的。 而插入失敗的原因可能是因為主鍵衝突導致資料庫報異常，跟隔離級別無直接關係。任何隔離級別下插入已經存在的數據都會報錯。

一句話，看不到並不代表沒有，並不代表可以自以為然的插入無憂。

誤解二

REPEATABLE-READ的事務裏查不到的數據一定是不存在的，所以我可以放心插入，100%成功。

這個觀點也是錯的，查不到只能說明當前事務裏讀不到，並不代表此時其他事務沒有插入這樣的數據。 如何保證判斷某個數據不存在以後其他事務也不會插入成功？答案是上Next-Key Lock。不上鎖是無法阻止其他事務插入的。

SELECT * FROM table1 WHERE id >100

上面這個語句在事務裏判斷後如果不存在數據是無法保證其他事務插入符合條件的數據的，需要加鎖

SELECT * FROM table1 WHERE id >100 FOR UPDATE;

此時如果有隔壁事務嘗試插入大於100的id的數據則會等待當前事務釋放鎖，直到超時後中斷當前事務。

(waiting for lock … then timeout) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

但是如果當前事務使用的加鎖的條件僅僅是某一個行鎖的話最多會在前後加Next-Key Lock，影響範圍較小，但仍然可能阻塞其他事務的插入，如恰好新數據的位置被GAP Lock鎖住了，那隻能等待當前事務釋放鎖了。

說了這麼多，有一點要注意，就是這個Next-Key Lock一定是在REPEATABLE-READ下才有，READ-COMMITTED是不存在的。

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to 「lock」 the nonexistence of something in your table.

即InnoDb在REPEATABLE-READ下提供Next-Key Lock機制，但是需要業務自己去加鎖，如果不加鎖，只是簡單的SELECT查詢，是無法限制並行事務的插入的。

誤解三

凡是REPEATABLE-READ中的讀都無法讀取最新的數據。

這個觀點也是錯誤的，雖然我們讀取的記錄都是可重複讀取的，但是如果你想讀取最新的記錄可以用加鎖的方式讀。

If you want to see the 「freshest」 state of the database, you should use either the READ COMMITTED isolation level or a locking read:

以下任意一種均：

  • SELECT * FROM table1 LOCK IN SHARE MODE;
  • SELECT * FROM table1 FOR UPDATE;

但這裡要說明的是這樣做跟SERIALIZABLE沒有什麼區別，即讀也加了鎖，性能大打折扣。

誤解四

如果使用了當前讀加了鎖，但是鎖的行並不存在則不會阻止隔壁事務插入符合條件的數據。

其實記錄存在與否和事務加鎖成功與否無關，如SELECT * FROM user WHERE id = 5 FOR UPDATE，此時id=5的記錄不存在，隔壁事務仍然無法插入記錄（假設當前自增的主鍵id已經是4了）。因為鎖定的是索引，故記錄實體存在與否沒關係。

誤解五

MySQL中的幻讀只有在讀的時候才會發生，讀這裡特指SELECT操作。

其實INSERT也是隱式的讀取，只不過是在MySQL的機制中讀取的，插入數據也是要先讀取一下有沒有主鍵衝突才能決定是否執行插入的。 不可重複讀測試「讀-讀」，而幻讀側重「讀-寫」，用寫來證實讀的是幻影。為啥幻讀不是側重「讀-讀」呢？因為MVCC保證了一個事務是不可能讀到另外一個事務的新插入數據的，所以這種場景下不會發生幻讀。

參考

  • dev.mysql.com/doc/refma
  • dev.mysql.com/doc/refma

本文首次發佈於 ElseF』s Blog, 作者 @stuartlau ,轉載請保留原文鏈接.

    推薦閱讀：

    相關文章