MySQL如何解決幻讀和不可重復度？

文章參考：

• https://juejin.im/post/5b8577c26fb9a01a143fe04e
• https://joonwhee.blog.csdn.net/article/details/106893197
• http://blog.51cto.com/14344203/2402076

注意：探討MySQL如何防止不可重復度和幻讀問題之前，默認大家已經理解臟讀、幻讀、不可重復讀的區別，以及資料庫事務的3種隔離級別！

1. MySQL中的3種鎖演算法

首先了解下MySQL中的3種鎖演算法：

• Record lock：記錄鎖（行鎖），單條索引記錄上加鎖，鎖住的永遠是索引，而非記錄本身，

• Gap lock：間隙鎖，在索引記錄之間的間隙中加鎖，或者是在某一條索引記錄之前或者之后加鎖，并不包括該索引記錄本身，

• Next-key lock：臨鍵鎖，Record lock 和 Gap lock 的結合，即除了鎖住記錄本身，也鎖住索引之間的間隙，

下面來詳細介紹下這三種鎖：

1.1 記錄鎖Record Lock

顧名思義，記錄鎖就是為某行記錄加鎖，它封鎖該行的索引記錄：

-- id 列為主鍵列或唯一索引列
SELECT * FROM 表名稱 WHERE id = 1 FOR UPDATE;

這時候 id 為 1 的記錄行會被鎖住，

需要注意的是：

• id 列必須為唯一索引列或主鍵列，否則上述陳述句加的鎖就會變成臨鍵鎖，（行鎖在 InnoDB 中是基于索引實作的，所以一旦某個加鎖操作沒有使用索引，那么該鎖就會退化為表鎖）

• 同時查詢陳述句必須為精準匹配=，不能為>、<、like等，否則也會退化成臨鍵鎖，

在通過 主鍵索引 與 唯一索引 對資料行進行 UPDATE 操作時，也會對該行資料加記錄鎖：

-- id 列為主鍵列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;

1.2 間隙鎖Gap Locks

間隙鎖基于非唯一索引，它鎖定一段范圍內的索引記錄，間隙鎖基于下面將會提到的Next-Key Locking 演算法，請務必牢記：使用間隙鎖鎖住的是一個區間，而不僅僅是這個區間中的每一條資料，

SELECT * FROM 表名稱 WHERE id BETWEN 1 AND 10 FOR UPDATE;

• 即所有在（1，10）區間內的記錄行都會被鎖住，所有id 為 2、3、4、5、6、7、8、9 的資料行的插入會被阻塞，但是 1 和 10 兩條記錄行并不會被鎖住，

• 除了手動加鎖外，在執行完某些 SQL 后，InnoDB 也會自動加間隙鎖，這個我們在下面會提到，

1.3 臨鍵鎖Next-Key Locks

Next-Key 可以理解為一種特殊的間隙鎖，也可以理解為一種特殊的演算法，通過臨建鎖可以解決幻讀的問題， 每個資料行上的非唯一索引列上都會存在一把臨鍵鎖，當某個事務持有該資料行的臨鍵鎖時，會鎖住一段左開右閉區間的資料，需要強調的一點是，InnoDB 中行級鎖是基于索引實作的，臨鍵鎖只與非唯一索引列有關，在唯一索引列（包括主鍵列）上不存在臨鍵鎖，

假設有如下表：

引擎：InnoDB，隔離級別：Repeatable-Read：table(id PK, age KEY, name)

該表中 age 列潛在的臨鍵鎖有：

(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],

在事務 A 中執行如下命令：

-- 根據非唯一索引列 UPDATE 某條記錄
UPDATE table SET name = Vladimir WHERE age = 24;
-- 或根據非唯一索引列 鎖住某條記錄
SELECT * FROM table WHERE age = 24 FOR UPDATE;

不管執行了上述 SQL 中的哪一句，之后如果在事務 B 中執行以下命令，則該命令會被阻塞：

INSERT INTO table VALUES(100, 26, 'Ezreal');

很明顯，事務 A 在對 age 為 24 的列進行 UPDATE 操作的同時，也獲取了 (24, 32] 這個區間內的臨鍵鎖，

不僅如此，在執行以下 SQL 時，也會陷入阻塞等待：

INSERT INTO table VALUES(100, 30, 'Ezreal');

那最終我們就可以得知，在根據非唯一索引對記錄行進行UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作時，InnoDB 會獲取該記錄行的臨鍵鎖 ，并同時獲取該記錄行下一個區間的間隙鎖，

即事務 A在執行了上述的 SQL 后，最終被鎖住的記錄區間為 (10, 32)，

1.4 小結

• InnoDB 中的行鎖的實作依賴于索引，一旦某個加鎖操作沒有使用到索引，那么該鎖就會退化為表鎖，
• 記錄鎖存在于包括主鍵索引在內的唯一索引中，鎖定單條索引記錄，
• 間隙鎖存在于非唯一索引中，鎖定開區間范圍內的一段間隔，它是基于臨鍵鎖實作的，在索引記錄之間的間隙中加鎖，或者是在某一條索引記錄之前或者之后加鎖，并不包括該索引記錄本身，
• 臨鍵鎖存在于非唯一索引中，該型別的每條記錄的索引上都存在這種鎖，它是一種特殊的間隙鎖，鎖定一段左開右閉的索引區間，即，除了鎖住記錄本身，也鎖住索引之間的間隙，

注意：

InnoDB 行鎖是通過索引上的索引項來實作的，意味者：只有通過索引條件檢索資料，InnoDB 才會使用行級鎖，否則，InnoDB將使用表鎖！

• 對于主鍵索引：直接鎖住鎖住主鍵索引即可，
• 對于普通索引：先鎖住普通索引，接著鎖住主鍵索引，這是因為一張表的索引可能存在多個，通過主鍵索引才能確保鎖是唯一的，不然如果同時有2個事務對同1條資料的不同索引分別加鎖，那就可能存在2個事務同時操作一條資料了，

擴展：MySQL 如何實作悲觀鎖和樂觀鎖？

• 樂觀鎖：更新時帶上版本號（cas更新）
• 悲觀鎖：使用共享鎖和排它鎖，select...lock in share mode，select…for update，

2. MySQL如何解決不可重復讀

• MySQL中，默認使用的事務隔離界別是可重復讀，為了解決不可重復讀問題，InnoDB采用了MVCC(多版本并發控制)【基于樂觀鎖】來解決！
• MVCC(多版本并發控制)是利用在每條資料后面加了隱藏的兩列（創建版本號和洗掉版本號），每個事務在開始的時候都會有一個遞增的當前事務版本號！

例如：

-- MVCC新增
begin; -- 假設獲取的 當前事務版本號=1
insert into user (id,name,age) values (1,"張三",10); -- 新增，當前事務版本號是1
insert into user (id,name,age) values (2,"李四",12); -- 新增，當前事務版本號是1
commit; -- 提交事務

-- 上表可以看到，插入的程序中會把當前事務版本號記錄到列 create_version 中去！

-- MVCC洗掉：洗掉操作是直接將行資料的洗掉版本號更新為當前事務的版本號
begin； --假設獲取的 當前事務版本號=3
delete from user where id = 2;
commit; -- 提交事務

-- MVCC更新操作:采用 delete + add 的方式來實作，首先將當前資料標志為洗掉，然后再新增一條新的資料
begin;-- 假設獲取的 當前事務版本號=10
update user set age = 11 where id = 1; -- 更新，當前事務版本號是10
commit; -- 提交事務

-- MVCC查詢操作：
begin;-- 假設拿到的系統事務ID為 12
select * from user where id = 1;
commit; -- 提交事務

查詢操作為了避免查詢到舊資料或已經被其他事務更改過的資料，需要滿足如下條件：

1、查詢時當前事務的版本號需要大于或等于創建版本號create_version

2、查詢時當前事務的版本號需要小于洗掉的版本號delete_version，或者當前洗掉版本號delete_version=NULL

即：(create_version <= current_version < delete_version) || (create_version <= current_version && delete_version-=NULL) ，這樣就可以避免查詢到其他事務修改的資料，同一個事務中，實作了可重復讀！

執行結果應該是：

3. MySQL如何解決幻讀

3.1 MySQL采用的MVCC可以解決幻讀嗎？

幻讀：在一個事務中使用相同的 SQL 兩次讀取，第二次讀取到了其他事務新插入的行，則稱為發生了幻讀，

例如：

• 1）事務1第一次查詢：select * from user where id < 10 時查到了 id = 1 的資料

• 2）事務2插入了id = 2 的資料

• 3）事務1使用同樣的陳述句第二次查詢時，查到了 id = 1、id = 2 的資料，出現了幻讀，

談到幻讀，首先我們要引入“當前讀”和“快照讀”的概念，通過名字就可以理解：

• 快照讀：生成一個事務快照（ReadView），之后都從這個快斬訓取資料，普通 select 陳述句就是快照讀，
• 當前讀：讀取資料的最新版本，常見的 update/insert/delete、還有 select ... for update、select ... lock in share mode 都是當前讀，

對于快照讀，MVCC 因為因為從 ReadView 讀取，所以必然不會看到新插入的行，所以天然就解決了幻讀的問題，

而對于當前讀的幻讀，MVCC 是無法解決的，需要使用 Gap Lock 或 Next-Key Lock（Gap Lock + Record Lock）來解決，

其實原理也很簡單，用上面的例子稍微修改下以觸發當前讀：select * from user where id < 10 for update，當使用了 Gap Lock 時，Gap 鎖會鎖住 id < 10 的整個范圍，因此其他事務無法插入 id < 10 的資料，從而防止了幻讀，

3.2 有人說 RR 解決了幻讀是什么情況？

SQL 標準中規定的 RR 并不能消除幻讀，但是 MySQL 的 RR 可以，靠的就是 Gap 鎖，在 RR 級別下，Gap 鎖是默認開啟的，而在 RC 級別下，Gap 鎖是關閉的，

文章參考：[MySQL 到底是怎么解決幻讀的？](https://blog.csdn.net/weixin_33795833/article/details/93036775]