單條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，即僅鎖住索引本身，不是范圍。