MySQL基礎隔離性小結

2020 年 2 月 10 日
筆記

Tip:此為MySQL實戰45講閱讀整理筆記。

ACID

名詞	解釋
原子性	事務是一個原子性質的操作單元，在事務中的操作要麼全部執行，要麼都不執行。
一致性	從事務開始到結束，數據必須保持一致狀態，保證數據庫的完整性，亦既數據的正確性，合理性。
隔離性	各個事務相互獨立，在提交完成前，中間的任何數據變化對其他事務都是不可見的。
持久性	一個事務執行完成，其對數據庫的修改是永久的，即使系統故障也不會丟失。

事務啟動方式

主要有以下方式：

顯式啟動事務語句， begin或 start transaction。配套的提交語句是 commit ，回滾語句是 rollback。
set autocommit=0，這個命令會將這個線程的自動提交關掉。意味着如果你只執行一個 select 語句，這個事務就啟動了，而且並不會自動提交。這個事務持續存在直到你主動執行 commit 或 rollback 語句，或者斷開連接。

有些客戶端連接框架會默認連接成功後先執行一個 set autocommit=0 的命令。這就導致接下來的查詢都在事務中，如果是長連接，就導致了意外的長事務。

建議使用方法一，如果擔心多一次交互問題，可以使用commit work and chain語法。在autocommit=1的情況下用begin顯式啟動事務，如果執行commit則提交事務。如果執行commit work and chain則提交事務並自動啟動下一個事務。

可以在 information_schema 庫的 innodb_trx 這個表中查詢長事務，如查詢時長超過60s的事務：

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

事務的啟動時機

上面是啟動方式，但begin/start transaction命令並不是一個事務的起點，在執行到它們之後的第一個操作 InnoDB 表的語句，事務才真正啟動。如果要馬上啟動一個事務，可以使用 start transaction with consistent snapshot 這個命令。兩者區別如下：

第一種啟動方式，一致性視圖是在執行第一個快照讀語句時創建的；
第二種啟動方式，一致性視圖是在執行 start transaction with consistent snapshot時創建的；

至於「一致性視圖」是什麼後面會詳細說到。

隔離性

隔離級別

# 查詢數據庫隔離級別    mysql> show variables like 'transaction_isolation';    +-----------------------+----------------+    | Variable_name | Value |    +-----------------------+----------------+    | transaction_isolation | REPEATABLE-READ |    +-----------------------+----------------+

具體隔離級別如下：

隔離級別	描述
讀未提交	一個事務還未提交，其所做的變更就能被其他事務看到。
讀提交	一個事務提交後，其所做的變更才會被其他事務看到。
可重複讀	一個事務執行過程中看到的數據，總是跟這個事務啟動時看到的數據時一致的。在可重複讀的隔離級別下，未提交變更對其他事務也是不可見的。
串行化	對同一行記錄，寫加「寫鎖」，讀加「讀鎖」。當出現讀寫衝突時，後訪問的事務需等前一個事務執行完才可繼續執行。

事務隔離的實現

MySQL數據庫會創建一個視圖，訪問時以視圖的邏輯結果為準。這個快照（即視圖）是基於整個庫的。

隔離級別	視圖創建時間
可重複讀	在事務啟動時創建的，整個事務存在期間都用這個視圖。
讀提交	在每個SQL語句開始執行時創建的視圖。
讀未提交	直接返回記錄上的最新值，沒有視圖概念。
串行化	直接用加鎖的方式避免並行訪問，沒有視圖概念。

在MySQL中存在兩種視圖：

一個是 view。它是一個用查詢語句定義的虛擬表，在調用的時候執行查詢語句並生成結果。創建視圖的語法是 create view … ，而它的查詢方法與表一樣。
另一個是 InnoDB 在實現 MVCC 時用到的一致性讀視圖，即 consistent read view，用於支持 RC（Read Committed，讀提交）和 RR（Repeatable Read，可重複讀）隔離級別的實現。

這裡說的視圖就是第二種，開頭「事務的啟動時機」中的「一致性視圖」也是這裡所說的第二種視圖，其僅在可重複讀和讀提交這兩種隔離級別下存在。

InnoDB 裏面每個事務有一個唯一的事務ID(transaction id)，在事務開始的時候向 InnoDB 的事務系統申請的，按申請順序嚴格遞增。

數據表中的一行記錄，其實可能有多個版本 (row)，就是數據庫的多版本並發控制（MVCC）,每個版本有自己 row trx_id（即事務將transaction id賦值賦值給這個數據版本的事務 ID）。

在 MySQL 中，實際上每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值。系統會判斷，當沒有事務再需要用到這些回滾日誌(undo log)時（即當系統里沒有比這個回滾日誌更早的 read-view 的時候），回滾日誌會被刪除。

不同時刻啟動的事務會有不同的 read-view。在實現上， InnoDB 為每個事務構造了一個數組，用來保存這個事務啟動瞬間，當前正在「活躍」的所有事務 ID。「活躍」指的就是，啟動了但還沒提交。

數組裏面事務 ID 的最小值記為低水位，當前系統裏面已經創建過的事務 ID 的最大值加 1 記為高水位。

這個視圖數組和高水位，就組成了當前事務的一致性視圖（read-view）。而數據版本的可見性規則，就是基於數據的 row trx_id 和這個一致性視圖的對比結果得到的。

快照讀與當前讀

普通查詢語句是一致性讀（也常說是 「快照讀」 ），一致性讀會根據 row trx_id 和一致性視圖確定數據版本的可見性：

對於可重複讀，查詢只承認在事務啟動前就已經提交完成的數據；
對於讀提交，查詢只承認在語句啟動前就已經提交完成的數據；

實例：

簡單的select操作即可

更新數據都是先讀後寫的，而這個讀，只能讀當前的值，稱為 「當前讀」（current read），包含：

select ... lock in share mode    select ... for update    insert    update    delete

為什麼表結構不支持「可重複讀」？

這是因為表結構沒有對應的行數據，也沒有 row trx_id，因此只能遵循當前讀的邏輯。

隔離現象

事務隔離可能會產生幾種現象：

臟讀一個事務訪問到另一個事務修改但未提交的數據。
不可重複讀一個事務中，兩次查詢同一行數據得到不同的結果。
幻讀 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. 原文簡單來說就是：一個事務中，同一個查詢語句在不同時間查詢出的數據行數不同。

隔離級別	臟讀	不可重複讀	幻讀
讀未提交	Y	Y	Y
讀提交	N	Y	Y
可重複讀	N	N	Y
串行化	N	N	N

以上是ANSI/ISO SQL定義的標準隔離級別可能產生的現象。

對於「可重複讀」，基於鎖機制並發控制的DBMS需要對選定對象的讀鎖（read locks）和寫鎖（write locks）一直保持到事務結束，但不要求「範圍鎖」，因此可能會發生「幻讀」。（參考）

MySQL默認是「可重複讀」（REPEATABLE-READ）級別，但通過gap lock鎖定索引記錄之間的間隙從而消滅幻讀,更準確的說：

在快照讀的情況下，mysql通過mvcc來避免幻讀。
在當前讀的情況下，mysql通過next-key鎖來避免幻讀

參考資料

MySQL實戰45講