目錄 "臟讀(Dirty reads)" "不可重覆讀(Non repeatable reads)" "幻影讀(Phantom reads)" "可重覆讀級別下防止幻讀" "可串列化級別杜絕幻讀" "總結" MySQL8中隔離級別的變數跟之前的版本不一樣，之前是tx_isolation，MySQL8 ...
目錄
MySQL8中隔離級別的變數跟之前的版本不一樣，之前是tx_isolation，MySQL8改成了transaction_isolation。查看當前隔離級別的命令是
mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+--------------------------------+-------------------------+
其它參考：MySQL 四種事務隔離級的說明
- 未提交讀(Read Uncommitted)：允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據
- 提交讀(Read Committed)：只能讀取到已經提交的數據。Oracle等多數資料庫預設都是該級別 (不重覆讀)
- 可重覆讀(Repeated Read)：可重覆讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB預設級別。在SQL標準中，該隔離級別消除了不可重覆讀，但是還存在幻象讀
- 串列讀(Serializable)：完全串列化的讀，每次讀都需要獲得表級共用鎖，讀寫相互都會阻塞
以下內容參考了維基百科：事務隔離
創建測試表users並插入測試數據
mysql> CREATE TABLE users (id int(11) NOT NULL, name varchar(20), age int(11), PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO users values (1, 'Joe', 20), (2, 'Jill', 25);
mysql> select * from users;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | Joe | 20 |
| 2 | Jill | 25 |
+----+------+------+
臟讀(Dirty reads)
示例1：隔離級別是未提交讀(READ UNCOMMITTED)，導致臟讀(dirty read)。在我們的例子中，事務2修改了一行，但是沒有提交，事務1讀了這個沒有提交的數據。現在如果事務2回滾了剛纔的修改或者做了另外的修改的話，事務1中查到的數據就是不正確的了。在這個例子中，事務2回滾後就沒有id是1，age是21的數據行了。
-- 設置隔離級別為未提交讀
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Session A Session B
START TRANSACTION; START TRANSACTION;
time
| /* Query 1 */
| SELECT age FROM users WHERE id = 1;
| /* will read 20 */
| /* Query 2 */
v UPDATE users SET age = 21 WHERE id = 1;
/* No commit here */
/* Query 1 */
SELECT age FROM users WHERE id = 1;
/* will read 21 */
ROLLBACK; /* lock-based DIRTY READ */
不可重覆讀(Non-repeatable reads)
示例2：隔離級別是讀已提交(READ COMMITTED)，導致不可重覆讀。在這個例子中，事務2提交成功，因此他對id為1的行的修改就對其他事務可見了。但是事務1在此前已經從這行讀到了另外一個“age”的值。在可串列化（SERIALIZABLE）和可重覆讀的隔離級別，資料庫在第二次SELECT請求的時候應該返回事務2更新之前的值。在提交讀和未提交讀，返回的是更新之後的值，這個現象就是不可重覆讀。
-- 設置隔離級別為提交讀
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Session A Session B
START TRANSACTION; START TRANSACTION;
time
| /* Query 1 */
| SELECT * FROM users WHERE id = 1;
| /* will read age=20 */
| /* Query 2 */
v UPDATE users SET age = 21 WHERE id = 1;
COMMIT; /* in multiversion concurrency
control, or lock-based READ COMMITTED */
/* Query 1 */
SELECT * FROM users WHERE id = 1;
/* will read age=21 */
COMMIT; /* lock-based REPEATABLE READ */
有兩種策略可以避免不可重覆讀。一個是要求事務2延遲到事務1提交或者回滾之後再執行。這種方式實現了T1, T2 的串列化調度。串列化調度可以支持可重覆讀。
另一種策略是多版本併發控制。為了得到更好的併發性能，允許事務2先提交。但因為事務1在事務2之前開始，事務1必須在其開始執行時間點的資料庫的快照上面操作。當事務1最終提交時候，資料庫會檢查其結果是否等價於T1, T2串列調度。如果等價，則允許事務1提交，如果不等價，事務1需要回滾並拋出個串列化失敗的錯誤。
使用基於鎖的併發控制，在可重覆讀的隔離級別中，ID=1的行會被鎖住，在事務1提交或回滾前一直阻塞語句2的執行。在提交讀的級別，語句1第二次執行，age已經被修改了。
在多版本併發控制機制下，可序列化(SERIALIZABLE)級別，兩次SELECT語句讀到的數據都是事務1開始的快照，因此返回同樣的數據。但是，如果事務1試圖UPDATE這行數據，事務1會被要求回滾並拋出一個串列化失敗的錯誤。
在提交讀隔離級別，每個語句讀到的是語句執行前的快照，因此讀到更新前後不同的值。在這種級別不會有串列化的錯誤（因為這種級別不要求串列化），事務1也不要求重試。
幻影讀(Phantom reads)
以下內容參考了：mysql 幻讀的詳解、實例及解決辦法
幻讀錯誤的理解：說幻讀是 事務A 執行兩次 select 操作得到不同的數據集，即 select 1 得到 10 條記錄，select 2 得到 11 條記錄。這其實並不是幻讀，這是不可重覆讀的一種，只會在 R-U R-C 級別下出現，而在 mysql 預設的 RR 隔離級別是不會出現的。
幻讀，並不是說兩次讀取獲取的結果集不同，幻讀側重的方面是某一次的 select 操作得到的結果所表徵的數據狀態無法支撐後續的業務操作。更為具體一些：select 某記錄是否存在，不存在，準備插入此記錄，但執行 insert 時發現此記錄已存在，無法插入，此時就發生了幻讀。
-- 設置隔離級別為可重覆讀
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Session A Session B
START TRANSACTION; START TRANSACTION;
time
| /* Query 1 */
| SELECT * FROM users WHERE id = 3;
| /* Empty set */
| /* Query 2 */
v INSERT INTO users values (3, 'Woody', 28);
COMMIT;
/* Query 3 */
INSERT INTO users values (3, 'Woody', 28);
/* ERROR 1062 (23000): Duplicate entry '3'
for key 'PRIMARY' */
/* Query 4 */
SELECT * FROM users WHERE id = 3;
/* Empty set */
COMMIT;
會話A ：主事務，檢測表中是否有 id 為 3 的記錄，沒有則插入，這是我們期望的正常業務邏輯。
會話B ：干擾事務，目的在於擾亂 會話A 的正常的事務執行。
在 RR 隔離級別下，Query 1、Query 2 是會正常執行的，Query 3 則會報錯主鍵衝突，對於 會話A 的業務來說是執行失敗的，這裡 會話A 就是發生了幻讀，因為 會話A 在 Query 1 中讀取的數據狀態並不能支撐後續的業務操作，會話A：“見鬼了，我剛纔讀到的結果應該可以支持我這樣操作才對啊，為什麼現在不可以”。會話A 不敢相信的又執行了 Query 4，發現和 Query 1 讀取的結果是一樣的（RR下的 MMVC機制）。此時，幻讀無疑已經發生，T1 無論讀取多少次，都查不到 id = 3 的記錄，但它的確無法插入這條他通過讀取來認定不存在的記錄（此數據已被會話B插入），對於 會話A 來說，它幻讀了。
其實 RR 也是可以避免幻讀的，通過對 select 操作手動加 行X鎖（SELECT ... FOR UPDATE 這也正是 SERIALIZABLE 隔離級別下會隱式為你做的事情），同時還需要知道，即便當前記錄不存在，比如 id = 3 是不存在的，當前事務也會獲得一把記錄鎖（因為InnoDB的行鎖鎖定的是索引，故記錄實體存在與否沒關係，存在就加 行X鎖，不存在就加 next-key lock間隙X鎖），其他事務則無法插入此索引的記錄，故杜絕了幻讀。
在 SERIALIZABLE 隔離級別下，step1 執行時是會隱式的添加 行(X)鎖 / gap(X)鎖的，從而 Query2 會被阻塞，Query3 會正常執行，待 T1 提交後，T2 才能繼續執行（主鍵衝突執行失敗），對於 T1 來說業務是正確的，成功的阻塞扼殺了擾亂業務的T2，對於T1來說他前期讀取的結果是可以支撐其後續業務的。
所以 mysql 的幻讀並非什麼讀取兩次返回結果集不同，而是事務在插入事先檢測不存在的記錄時，驚奇的發現這些數據已經存在了，之前的檢測讀獲取到的數據如同鬼影一般。
這裡要靈活的理解讀取的意思，第一次select是讀取，第二次的 insert 其實也屬於隱式的讀取，只不過是在 mysql 的機制中讀取的，插入數據也是要先讀取一下有沒有主鍵衝突才能決定是否執行插入。
不可重覆讀側重表達 讀-讀，幻讀則是說 讀-寫，用寫來證實讀的是鬼影。
可重覆讀級別下防止幻讀
RR級別下只要對 SELECT 操作也手動加行（X）鎖即可類似 SERIALIZABLE 級別（它會對 SELECT 隱式加鎖），即大家熟知的：
# 這裡需要用 X鎖， 用 FOR SHARE 拿到 S鎖 後我們沒辦法做 寫操作
SELECT `id` FROM `users` WHERE `id` = 3 FOR UPDATE;
如果 id = 3 的記錄存在則會被加行（X）鎖，如果不存在，則會加 next-lock key / gap 鎖（範圍行鎖），即記錄存在與否，mysql 都會對記錄應該對應的索引加鎖，其他事務是無法再獲得做操作的。
這裡我們就展示下 id = 3 的記錄不存在的場景，FOR UPDATE 也會對此 “記錄” 加鎖，要明白，InnoDB 的行鎖（gap鎖是範圍行鎖，一樣的）鎖定的是記錄所對應的索引，且聚簇索引同記錄是直接關係在一起的。
-- 設置隔離級別為可重覆讀
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Session A Session B
START TRANSACTION; START TRANSACTION;
time
| /* Query 1 */
| SELECT * FROM users WHERE id = 3 FOR UPDATE;
| /* Empty set */
| /* Query 2 */
v INSERT INTO users values (3, 'Woody', 28);
/* 被阻塞，ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction */
/* Query 3 */
INSERT INTO users values (3, 'Woody', 28);
/* Query OK, 1 row affected */
COMMIT;
/* Query OK, 0 rows affected */
可串列化級別杜絕幻讀
在此級別下，我們便不需要對 SELECT 操作顯式加鎖，InnoDB會自動加鎖，事務安全，但性能很低。
-- 設置隔離級別為可串列化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Session A Session B
START TRANSACTION; START TRANSACTION;
time
| /* Query 1 */
| select * from users where id = 4;
| /* Empty set */
| /* Query 2 */
v INSERT INTO users values (4, 'Bill', 29);
/* 被阻塞，ERROR 1205 (HY000): Lock wait
timeout exceeded; try restarting transaction */
/* Query 3 */
INSERT INTO users values (4, 'Bill', 29);
/* Query OK, 1 row affected */
COMMIT;
/* Query OK, 0 rows affected */
step1: 會話A 查詢 id = 4 的記錄，InnoDB 會隱式的對齊加 X鎖
step2: 會話B 插入 id = 4 的記錄，被阻塞
step3: 會話A 插入 id = 4 的記錄，成功執行（會話B 依然被阻塞中）
step4: 會話A 成功提交（會話B 此時喚醒但主鍵衝突執行錯誤）
會話A事務符合業務需求成功執行，會話B干擾會話A失敗。
總結
RR 級別作為 mysql 事務預設隔離級別，是事務安全與性能的折中，可能也符合二八定律（20%的事務存在幻讀的可能，80%的事務沒有幻讀的風險），我們在正確認識幻讀後，便可以根據場景靈活的防止幻讀的發生。
SERIALIZABLE 級別則是悲觀的認為幻讀時刻都會發生，故會自動的隱式的對事務所需資源加排它鎖，其他事務訪問此資源會被阻塞等待，故事務是安全的，但需要認真考慮性能。
InnoDB的行鎖鎖定的是索引，而不是記錄本身，這一點也需要有清晰的認識，故某索引相同的記錄都會被加鎖，會造成索引競爭，這就需要我們嚴格設計業務sql，儘可能的使用主鍵或唯一索引對記錄加鎖。索引映射的記錄如果存在，加行鎖，如果不存在，則會加 next-key lock / gap 鎖 / 間隙鎖，故InnoDB可以實現事務對某記錄的預先占用，如果記錄存在，它就是本事務的，如果記錄不存在，那它也將是本是無的，只要本是無還在，其他事務就別想占有它。
另外可以參考：以後別再說你不懂MySQL中的「幻讀」了
