【MySQL】可重複讀模式下 unique key失效案例

G8bao7發表於2015-05-08
MySql模式
一 【背景】
   今天上午文能提筆安天下,武能上馬定乾坤的給團隊出了一道題目，誰先復現問題，獎勵星巴克一杯。激起了一群忙碌的屌絲DBA的極大熱情。問題是這樣滴，如下圖

登博提示了幾個細節：
   1. code上的uk並未失效。
   2. rr隔離級別。
   3. 有併發執行緒的操作。
二 【原理分析】
1 事務隔離級別的基礎知識:
  •  未提交讀(Read Uncommitted)：允許髒讀，也就是可能讀取到其他會話中未提交事務修改的資料。
  •  提交讀(Read Committed)：只能讀取到已經提交的資料。Oracle等多數資料庫預設都是該級別 (不重複讀)。
  •  可重複讀(Repeated Read)：可重複讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB預設級別。在SQL標準中，該隔離級別消除了不可重複讀，但是還存在幻象讀。
  •  序列讀(Serializable)：完全序列化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞。
 這裡重點說一下RR 模式:可重複讀 是指在一個事務內，多次讀同一資料。在這個事務還沒有結束時，另外一個事務也訪問該同一資料。那麼，在第一個事務中的兩次讀資料之間，即使第二個事務對資料進行修改，第一個事務兩次讀到的的資料是一樣的。這樣就發生了在一個事務內兩次讀到的資料是一樣的，因此稱為是可重複讀。
2 MVCC 的讀操作
   在MVCC併發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當前讀 (current read)。快照讀，讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖。
當前讀，讀取的是記錄的最新版本，並且，當前讀返回的記錄，都會加上鎖，保證其他事務不會再併發修改這條記錄。
快照讀：簡單的select操作，屬於快照讀，不加鎖。
select * from table where ?;
當前讀：特殊的讀操作，插入/更新/刪除操作，屬於當前讀，需要加鎖。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的語句，都屬於當前讀，讀取記錄的最新版本。並且，讀取之後，還需要保證其他併發事務不能修改當前記錄，對讀取記錄加鎖。其中，除了第一條語句，對讀取記錄加S鎖 (共享鎖)外，其他的操作，都加的是X鎖 (排它鎖)。
注意:insert操作可能會觸發Unique Key的衝突檢查，也會進行一個當前讀。
 
三【解決.復現】
測試版本: 5.5.18 5.6.16 均可復現。
現在我們根據上述理論資訊進行復現問題，具體的實現步驟如下:
注意 資料庫的隔離級別為RR 
session 1 session 2
root@test 08:47:41>set global tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:16>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:22>insert into yy values(1,20,13);
Query OK, 1 row affected (0.00 sec)
root@test 08:53:31>commit;
Query OK, 0 rows affected (0.00 sec)
root@test 08:53:39>select * from yy;
+----+------+------+
| id | code | val  |
+----+------+------+
|  1 |   20 |   13 |
+----+------+------+
1 row in set (0.00 sec)
root@test 08:53:46>select * from yy;
+----+------+------+
| id | code | val  |
+----+------+------+
|  1 |   20 |   13 |
+----+------+------+
1 row in set (0.00 sec)
root@test 08:53:53>delete from yy where id=1;
Query OK, 1 row affected (0.00 sec)
root@test 08:53:59>commit;
root@test 08:54:10>insert into yy values(2,20,13);
Query OK, 1 row affected (5.59 sec)
root@test 08:54:23>select * from yy;
+----+------+------+
| id | code | val  |
+----+------+------+
|  1 |   20 |   13 |
|  2 |   20 |   13 |
+----+------+------+
2 rows in set (0.00 sec)

當session 2中將id=1 的刪除之後，session1 進行insert操作時，觸發unique key衝突檢查，此時因為id=1 code=20的資料已經被物理刪除了，MySQL 檢查無衝突，進行insert insert into yy values(2,20,13); 便成功了。
四【結果展示】


五【參考資料】
 1 《》
 2 《》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26250550/viewspace-1630174/，如需轉載，請註明出處，否則將追究法律責任。

相關文章