MySQL的REPEATABLE-READ隔離等級下讀取到的“重複資料”

MySQL的REPEATABLE-READ隔離等級下讀取到的“重複資料”

在MySQL中，使用MVCC來實現REPEATABLE-READ隔離等級，由於SELECT操作不會對資料加鎖，其他回話可以修改當前回話所讀取過的資料而不會被阻塞，因此讀寫不衝突。

在MVCC並發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當前讀 (current read)。快照讀，讀取的是記錄的可見版本 (有可能是曆史版本)，不用加鎖。當前讀，讀取的是記錄的最新版本，並且，當前讀返回的記錄，都會加上鎖，保證其他事務不會再並發修改這條記錄。

當事務中進行查詢時，MySQL會把快照讀和當前讀的結果進行合并再返回給用戶端，而這個合并可能導致一些奇特的結果。

產生測試資料：

drop table tb002;create table tb002(id int primary key,c2 int,unique index uni_c2(c2));begin;insert into tb002(id,c2) select 1,1;insert into tb002(id,c2) select 2,2;insert into tb002(id,c2) select 4,4;commit;

假設有回話A和回話B,均使用REPEATABLE-READ隔離等級

##========================================================##

首先回話A執行SQL：

begin;select * from tb002;

返回結果如下：

##========================================================##

然後回話B執行SQL：

begin;delete from tb002 where id=2;commit;

由於回話A沒有加鎖，所以回話B能順利完成刪除並提交事務，當前資料庫中無C2=2的記錄，且會話B提交事務釋放鎖。

##========================================================##

回到回話A執行SQL：

insert into tb002(id,c2) select 3,2;

由於當前資料庫中無C2=2的記錄，且其他回話沒有在此C2=2的範圍上加鎖，因此回話A可以完成C2=2的資料插入。

在回話A上再次進行查詢：

select * from tb002;

返回結果如：

C2上有唯一索引，但為什麼查詢結果中仍包含兩條C2=2的記錄呢？ID=2的記錄屬於快照讀的資料，ID=3的記錄資料當前讀的資料，MySQL將當前讀和快照讀的資料進行簡單的合并後返回給用戶端，並不檢查“結果資料是否滿足唯一索引”的要求。

##========================================================##

上面的測試針對唯一索引進行，那如果針對主鍵會有啥區別呢？

將插入SQL修改為：

insert into tb002(id,c2) select 2,3;

即回話B刪除的ID值和回話A新插入的ID值相同情況下，最後的查詢結果並不會包含兩條相同ID的記錄，對於“快照讀”和“當前讀”兩個結果集存在"主鍵衝突“的情況，最終返回用戶端的結果會”丟棄“快照讀中的”老版本“記錄，保留最新版本的記錄。

可見對於主鍵不存在上述問題。

##========================================================##

總結：

在基於MVCC實現的REPEATABLE-READ隔離等級下，由於快照讀和當前讀的影響，會導致返回資料結果集超過”期望結果集“的情況，甚至返回結果集中包含重複的”唯一索引鍵“，但返回結果集中不會包含重複的“主鍵”（PS：單表查詢的前提下）。

如果在事務中包含先插入後查詢的情況，應該考慮上述問題對業務的影響。