對MySQL鎖定、事務、MVCC的簡單認識

mysql教學欄位介紹對MySQL鎖定、交易、MVCC的簡單認識。

更多相關免費學習推薦：mysql教學(影片)

單一SQL語句執行時，會被當成一個交易提交嗎？

以下內容摘自《高效能MySQL》(第3版)

「

MySQL預設採用自動提交（ AUTOCOMMIT）模式。也就是說，如果不是明確地開始一個事務，則每個查詢都被當作一個事務執行提交操作。在當前連接中，可以透過設定AUTOCOMMIT變數來啟用或停用自動提交模式

」

#MySQL 是如何實作交易的ACID 的？

事務具有 ACID 四大特性，那麼 MySQL 是如何實現事務的這四個屬性的呢？

• 原子性 要嘛全部成功，要嘛全部失敗。 MySQL是透過記錄 undo_log 的方式來實現的原子性。 undo_log 即回溯日誌，在真正的SQL執行之前先將 undo_log 寫入磁碟，然後再對資料庫的資料進行操作。如果發生異常或回滾，就可以依據 undo_log 進行反向操作，恢復資料在交易執行之前的樣子。

• 持久性 一旦事務被正常提交，它對資料庫的影響就應該是永久的。此時即使系統崩潰，修改的資料也不會遺失。 InnoDB 作為 MySQ L的儲存引擎，資料是存放在磁碟中的，但如果每次讀寫資料都需要磁碟IO，效率會很低。為此，InnoDB 提供了快取(Buffer Pool)，作為存取資料庫的緩衝：當從資料庫讀取資料時，會先從Buffer Pool 中讀取，如果Buffer Pool 中沒有，則從磁碟讀取後放入Buffer Pool ；當寫入資料到資料庫時，會先寫入Buffer Pool，Buffer Pool 中修改的資料會定期刷新到磁碟中。

  這樣的設計也帶來了對應的問題：如果資料提交了，這時資料還在緩衝池裡（還沒刷盤），此時MySQL宕機、斷電了怎麼辦？資料會不會遺失？

  答案是不會，MySQL 透過 redo_log 的機制，保證了持久性。 redo_log 即重做日誌，簡單說就是當資料修改時，除了修改Buffer Pool 中的數據，還會在redo_log 記錄這次操作；當交易提交時，會呼叫fsync 介面對redo_log 進行刷盤。如果MySQL宕機，重啟時可以讀取 redo_log 中的數據，對資料庫進行復原。

• 隔離性

  隔離性是ACID 裡面最複雜的一個，這裡面涉及到隔離層級的概念，一共有四個

  • Read uncommitted
  • Read committed
  • Repeatable read
  • #Serializable

  簡單說隔離等級就是規定了：一個事務中資料的修改，哪些事務之間可見，哪些不可見。而隔離性就是要管理多個並發讀寫請求的存取順序。

  MySQL 對於隔離性的具體實作我們後面會展開說。

• 一致性

  透過回滾、復原和在並發環境下的隔離做到一致性。

交易並發可能導致的問題

##透過上個問題我知道單一DDL 執行也會被當成一個事務自動提交，那麼無論是多條SQL並發，還是多個自己手動組織的包含多條SQL的事務並發，都會導致事務並發問題。

具體來說有：

• 髒寫（一個交易提交的資料覆蓋了另一個交易未提交的資料）
• 髒讀（一個交易讀取到另一個交易未提交的資料）
• 不可重複讀取（重點在於update和delete 一個交易內多次讀取的資料不一樣）
• 幻讀（重點在於insert 一個事務內多次讀取的記錄數不一樣）

上面我們提到了事務的隔離級別，MySQL 的所有隔離等級都能保證不產生髒寫，所以就剩下髒讀、不可重複讀和幻讀的問題了。

下面具體看下各隔離等級是如何解決或未解決上面這些問題的：

#Read uncommitted

未提交讀，這個等級在讀的過程中不會加任何鎖，只在寫請求時加鎖，所以寫操作在讀的過程中修改數據，就會造成髒讀。也自然會產生不可重複讀和幻讀。

Read committed

#已提交讀取，與未提交讀取一樣也是讀不加鎖，寫加鎖。不一樣的是利用了 MVCC 機制避免了髒讀的問題，同樣會有不可重複讀和幻讀的問題。關於 MVCC 我們後面會詳細說。

Repeatable read

#MySQL 預設的隔離級別，在這個級別MySQL利用兩種方式解決問題

1. 讀寫鎖 讀讀並行時加讀鎖，讀讀是共用鎖的。 只要有寫請求就加寫鎖，這樣讀寫是串列的。 讀取資料時加鎖，其它事務無法修改這些資料。所以不會產生不可重複讀。 修改刪除數據時也要加鎖，其它事務無法讀取這些數據，所以不會產生髒讀。 第一種方式就是我們常說的 「悲觀鎖」，資料在整個事務處理過程中處於鎖定狀態，比較保守，效能開銷比較大。
2. MVCC （後面講）

此外還利用了Next-Key鎖定 在一定程度上解決了幻讀的問題。關於這個我們後面再說。

Serializable

#在該隔離等級下交易都是序列順序執行的。 如果停用了自動提交，則 InnoDB 會將所有普通的 SELECT 語句隱式轉換為 SELECT ... LOCK IN SHARE MODE。即給讀操作隱式加上一把讀共享鎖，從而避免了髒讀、不可重讀複讀和幻讀問題。

MVCC

#"

Multiversion concurrency control (MCC or MVCC), is a concurrency control method commonly used by database management systems to provide concurrent access to the database and in programming languages to implement transactional memory

”

翻譯過來就是：多版本並發控制（MCC或MVCC）是一種並發控制方法，通常被資料庫管理系統用來提供對資料庫的並發訪問，並以程式語言來實現事務儲存。

簡單來說就是資料庫用來控制並發的一種方法。每個資料庫對於 MVCC 的實作可能不一樣。

以我們常用的 MySQL 來說，MySQL 的 InnoDB 引擎實作了 MVCC 。

MVCC 能解決什麼問題

#從上面的定義我們能看出，MVCC 主要解決交易並發時資料一致性的問題

InnoDB 是如何實作的MVCC

#下面這個圖來自《高效能MySQL》(第3版)

這本書寫的很好，翻譯的也不錯，我對於MySQL 最初的系統性認識也是因為讀了這本書，然而在對於MVCC 是如何實現的敘述上，個人認為是有些問題的。

來看下哪裡有問題

• #首先看下MySQL 的官方文檔，我比較了5.1、5.6、5.7 三個版本的文檔^[1] ，對MVCC 這部分的描述，幾乎是相同的。

  

根據文件很明顯是在每個資料增加三個隱藏列：

• 6位元組的DB_TRX_ID 字段，表示最近一次插入或更新該記錄的事務ID。
• 7位元組的 DB_ROLL_PTR 字段，指向該記錄的 rollback segment 的 undo log 記錄。
• 6位元組的 DB_ROW_ID，當有新資料插入的時候會自動遞增。當表格上沒有使用者主鍵的時候，InnoDB會自動產生聚集索引，包含DB_ROW_ID欄位。

這裡我補充一張包含rollback segment 的MySQL 內部結構圖

版本鏈

##之前我們講過undo_log 的概念，每個undo日誌都有一個roll_pointer 屬性，那麼所有的版本都會被roll_pointer 屬性連接成一個鍊錶，我們把這個鍊錶稱為版本鏈，版本鏈的頭節點就是目前記錄最新的值。

ReadView

透過隱藏列和版本鏈，MySQL 可以將資料還原到指定版本；但是具體要還原到哪個版本，則需要根據 ReadView 來確定。所謂ReadView，是指交易（記做事務A）在某一時刻給整個事務系統（trx_sys）打快照，之後再進行讀取操作時，會將讀取到的資料中的事務id 與trx_sys 快照比較，從而判斷資料對該ReadView 是否可見，即對事務A是否可見。

至此我們發現 MVCC 就是基於隱藏欄位、undo_log 鍊和 ReadView 來實現的。

Read committed 中的MVCC

前面我們講過Read committed 隔離等級中使用MVCC 解決髒讀問題。 這裡我參考了兩篇文章：

• https://cloud.tencent.com/developer/article/1150633
• https:/ /cloud.tencent.com/developer/article/1150630

InnoDB只會尋找版本早於目前交易版本的資料行（也就是，行的版本號碼小於或是等於事務的系統版本號），這樣可以確保資料讀取的行，要麼是在事務開始前已經存在的，要麼是事務本身插入或修改過的。 因此不會產生髒讀。

Read committed 隔離等級下出現不可重複讀取是由於 read view 的產生機製造成的。在 Read committed 層級下，只要目前語句執行前已經提交的資料都是可見的。在每次語句執行的過程中，都關閉 read view, 重新建立目前的一份 read view。這樣就可以根據目前的全域事務鍊錶建立 read view 的事務區間。簡單說就是在 Read committed 隔離等級下，MVCC 在每次 select 時產生一個快照版本，所以每次 select 都會讀到不同的版本數據，所以會產生不可重複讀取。

Repeatable read 中的MVCC

Repeatable read 隔離等級解決了不可重複讀取的問題，一個事務中多次讀取不會有不同的結果，保證了可重複讀。前文我們說 Repeatable read 有兩種實作方式，一種是悲觀鎖的方式，相對的 MVCC 就是樂觀鎖的方式。

Repeatable read 隔離等級能解決不可重複讀取根本原因其實就是 read view 的產生機制和 Read committed 不同。

• Read committed  ：只要是目前語句執行前已經提交的資料都是可見的。
• Repeatable read ：只要是目前交易執行前已經提交的資料都是可見的。

不像 Read committed，在 Repeatable read 的隔離等級下，創建交易的時候，就產生了目前的 global read view,一直維持到交易結束。這樣就能實現可重複讀。

幻讀與Next-Key 鎖定

目前讀取與快照讀取

透過MVCC 機制，雖然讓數據變得可重複讀，但我們讀到的數據可能是歷史數據，是不及時的數據，不是數據庫當前的數據！對於這種讀取歷史資料的方式，我們叫它快照讀 (snapshot read)，而讀取資料庫目前版本資料的方式，叫做目前讀取 (current read) 參考^[3]

• 快照讀：就是select

  • select * from table ….;
• 目前讀取：特殊的讀取操作，插入/更新/刪除操作，屬於當前讀，處理的都是當前的數據，需要加鎖。

  • select * from table where ? lock in share mode;
  • #select * from table where ? for update;
  • #insert;
  • update ;
  • #delete;

##解決幻讀

為了解決

目前讀取中的幻讀問題，MySQL交易使用了next-key lock 。

Repeatable read 透過 next-key lock 機制避免了幻讀現象。

InnoDB儲存引擎有3種行鎖的演算法，分別是：

• Record Lock: 單一記錄上的鎖定
• Gap Lock: 間隙鎖，鎖定一個範圍，但不包括記錄本上
• Next-Key Lock: Gap Lock Record Lock

next-key lock 是行鎖的一種，實現相當於record lock(記錄鎖) gap lock(間隙鎖)；其特點是不僅會鎖住記錄本身( record lock 的功能)，還會鎖定一個範圍( gap lock 的功能)。

當InnoDB掃描索引記錄的時候，會先對索引記錄加上行鎖定（Record Lock），再對索引記錄兩邊的間隙加上間隙鎖定（Gap Lock）。加上間隙鎖之後，其他事務就不能在這個間隙修改或插入記錄。

當查詢的索引含有唯一屬性的時候，Next-Key Lock 會進行最佳化，將其降級為Record Lock，即僅鎖定索引本身，不是範圍。

#

以上是對MySQL鎖定、事務、MVCC的簡單認識的詳細內容。更多資訊請關注PHP中文網其他相關文章！