目錄
- 臟讀(Dirty reads)
- 不可重復讀(Non-repeatable reads)
- 幻影讀(Phantom reads)
- 可重復讀級別下防止幻讀
- 可串行化級別杜絕幻讀
- 總結
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中的「幻讀」了
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66603.html
標籤：MySQL
上一篇：[MySQL] 查看explain中的key_len判斷究竟使用了哪個索引
下一篇：SQL注入的問題