MySQL如何解決幻讀？
- SERIALIZABLE 串行化
- MVCC + Next-Key Lock
幻讀：
幻讀指事務T1在進行一次查詢之后發現某個記錄不存在，然后會根據這個結果進行下一步操作，此時如果事務T2成功插入了該記錄，那么對于事務T1而言，其進行下一步操作（比如插入該記錄）的時候很可能會報錯。從事務使用的角度來看，在檢查一條記錄不存在之后，其進行插入應該完全沒問題的，但是這里卻拋出主鍵沖突的異常。
簡單來說：事務T1的兩次讀之間有其他事務寫操作，比如事務T1統計年齡 > 30，當T1兩次讀數據之間其他事務新添加了記錄，所以事務T1第二次讀取到的數據突然多了一個，仿佛出現了幻覺一般，這就是一種幻讀
串行化：
- 事務在讀操作時，先加共享鎖，直到事務結束才釋放
- 事務在寫操作時，先加排它鎖，直到事務結束才釋放
隱式鎖機制
FOR UPDATE 或 LOCK IN SHARE MODE。這種隱式鎖機制確保事務按順序執行，并防止并發插入和更新操作。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
多版本并發MVCC：
InnoDB 的 MVCC， 是 通過 在 每 行 記錄 后面 保存 兩個 隱藏 的 列 來 實現 的。 這 兩個 列， 一個 保存 了 行的 創建 時間， 一個 保存 行的 過期 時間（ 或 刪除 時間）。 當然 存儲 的 并不是 實際 的 時間 值， 而是 系統 版 本號（ system version number）。 每 開始 一個 新的 事務， 系統 版本 號 都會 自動 遞增。 事務 開始時 刻 的 系統 版 本號 會 作為 事務 的 版 本號， 用來 和 查詢 到 的 每 行 記錄 的 版本 號 進行 比較。
MVCC 只在 REPEATABLE READ 和 READ COMMITTED 兩個 隔離 級別 下 工作。
MVCC快照讀需滿足條件：
- InnoDB 只 查找 版本 早于 當前 事務 版本 的 數據 行（ 也就是， 行的 系統 版本 號 小于 或 等于 事務 的 系統 版 本號）， 這樣 可以 確保 事務 讀 取的 行， 要么 是在 事務 開始 前 已經 存在 的， 要么 是 事務 自身 插入 或者 修 改過 的。
- 行的 刪除 版本 要么 未定義， 要么 大于 當前 事務 版 本號。 這可 以 確保 事務 讀取 到 的 行， 在 事務 開始 之前 未被 刪除。
MVCC解決了基于快照讀下的幻讀，事務 讀 取的 行， 要么 是在 事務 開始 前 已經 存在 的， 要么 是 事務 自身 插入 或者 修 改過 的。
并不會讀到其他事務的寫操作 ！！！
MVCC無法解決當前讀下的幻讀。
在 REPEATABLE READ 可重復讀的隔離級別下進行當前讀：
1. 事務T1 INSERT id= 10的記錄，事務T2 在事務T1未commit時進行INSERT id=10的記錄會阻塞，
對已COMMIT進行INSERT 會主鍵索引沖突 Duplicate entry '10' for key 'PRIMARY'。
2. 可以顯式的加意向排它鎖、意向共享鎖避免大部分幻讀
select * from tb where ? lock in share mode;
select * from tb where ? for update;
for update：IX鎖(意向排它鎖)，即在符合條件的rows上都加了排它鎖
lock in share mode：是IS鎖(意向共享鎖)，即在符合條件的rows上都加了共享鎖
排它鎖：X鎖、 寫鎖，事務A對一個資源加了X鎖后只有A本身能對該資源進行讀和寫操作，其他事務對該資源的讀和寫操作都將被阻塞，直到A釋放鎖為止
共享鎖：S鎖、 讀鎖， 事務A鎖定的數據其他事務可以共享讀該資源，但不能寫，直到事務A釋放
FOR UPDATE 和 LOCK IN SHARE MODE 在可重復讀隔離級別下會查詢到其他事務已提交的數據。
事務T1
事務T2
start TRANSACTION;
SELECT * FROM orders;
SELECT * FROM orders FOR UPDATE;
事務T1插入id=2的記錄后COMMIT成功，此時事務T2執行 SELECT * FROM orders; 在可重復讀的事務隔離級別下查詢不到id=2的數據記錄。
但執行 SELECT * FROM orders FOR UPDATE; 或 SELECT * FROM orders LOCK IN SHARE MODE; 可以查詢到id=2的記錄
INSERT into orders (id,amount) VALUES (3,50);
耗時：50006.079 ms執行結果：Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction [6aca72a3-5e65-466c-acea-1140658ffa94]
-------------------------------------------------------------------------------------------------------------------------------------------------------
間隙鎖Gap Lock：
事務T1 鎖定 orders 表 amount 大于1的所有記錄
start TRANSACTION;
UPDATE orders SET amount = 200 WHERE amount > 1;
事務T2 執行INSERT 語句報錯 Lock wait timeout exceeded，等待事務T1進行事務COMMIT釋放鎖
start TRANSACTION;
INSERT into orders (id,amount) VALUES (2,100);
耗時：50008.956 ms執行結果：Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction [523d8451-a0ce-4e94-b7b7-3ad0f786ccb4]
Next-Key Lock是Gap Lock（間隙鎖）和Record Lock（行鎖）的結合版，都屬于Innodb的鎖機制，主要應用于可重復讀隔離級別，但也可能出現在更高隔離級別的事務中。
在某些情況下，即使使用 Next-Key Lock，也可能出現幻讀，特別是在非唯一索引上。
select * from tb where id>100 for update;
- 主鍵索引 id 會給 id=100 的記錄加上 record行鎖
- 索引 id 上會加上 gap 鎖，鎖住 id(100,+無窮大）這個范圍
其他事務對 id>100 范圍的記錄讀和寫操作都將被阻塞
插入 id=1000的記錄時候會命中索引上加的鎖會報出事務異常；
Next-Key Lock會確定一段范圍，然后對這個范圍加鎖，保證A在where的條件下讀到的數據是一致的，因為在where這個范圍其他事務根本插不了也刪不了數據，都被Next-Key Lock鎖堵在一邊阻塞掉了。
施瓦茨(Baron Schwartz); 扎伊采夫(Peter Zaitsev); 特卡琴科(Vadim Tkachenko). 高性能MySQL(第3版)
浙公網安備 33010602011771號