MySQL在RR隔離級別下的unique失效和死鎖模擬

今天在測試MySQL事務隔離級別的時候，發現了一個有趣的問題，也參考了楊一之前總結的一篇。http://blog.itpub.net/22664653/viewspace-1612574/

    問題的背景是在MySQL隔離級別為RR（Repeatable Read)時，唯一性約束沒有失效，多併發的場景下能夠復現出下面的問題。

  這樣一個看起來不可能的事情，能否復現呢。

  我都這麼問了，潛臺詞就是可以，要不今天的筆記就一個問題就結束了。

  為了模擬這個問題，我們開啟兩個會話視窗，來模擬一下這個問題。

mysql> create table test3(id1 int primary key,id2 int unique,id3 int);
Query OK, 0 rows affected (0.01 sec)

#會話1
set autocommit=0;
mysql> insert into test3 values(1,20170831,1);
Query OK, 1 row affected (0.00 sec)
commit;

#會話2

這個時候充分利用了MVCC的特性，這是一個快照讀。

mysql> select *from test3;
+-----+----------+------+
| id1 | id2      | id3  |
+-----+----------+------+
|   1 | 20170831 |    1 |
+-----+----------+------+
1 row in set (0.00 sec)
會話1插入了一條資料，我們在會話2中刪除。
mysql> delete from test3 where id1=1;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
提交之後，會話2中就修改完畢了。

#會話1

這個時候根據MVCC的特點，會話2中已經刪除了id1=1的記錄。所以主鍵列相關資料是插入不了了，那麼唯一性索引呢。根據MVCC的特點，能夠保證重複讀的特點，讀到的資料還是不變。

mysql> select *from test3;
+-----+----------+------+
| id1 | id2      | id3  |
+-----+----------+------+
|   1 | 20170831 |    1 |
+-----+----------+------+
1 row in set (0.00 sec)

現在的關鍵就來了，我們插入一條資料，主鍵不衝突，唯一性索引衝突，看看是否能夠插入成功。

mysql> insert into test3 values(2,20170831,2);
Query OK, 1 row affected (0.00 sec)

魔性的一幕上演了。

mysql> select *from test3;
+-----+----------+------+
| id1 | id2      | id3  |
+-----+----------+------+
|   1 | 20170831 |    1 |
|   2 | 20170831 |    2 |
+-----+----------+------+
2 rows in set (0.00 sec)

當然到了這裡，我們繼續玩一玩，常規來說，插入主鍵列衝突資料可能是行不通的，比如id1=1,id2=20170831,id3=1,客戶端很快會反饋失敗。但是在這裡做唯一性校驗時，因為id1=1的資料已經被物理刪除了。

mysql>  insert into test3 values(1,20170831,1);
ERROR 1062 (23000): Duplicate entry '20170831' for key 'id2'

我們就來繼續模擬個死鎖吧。

會話2:

這個步驟是做一次資料清理，where條件中是根據主鍵來查詢刪除。

mysql> delete from test3 where id1=1;
Query OK, 0 rows affected (0.00 sec)

會話1:

mysql> insert into test3 values(1,20170831,1);

這個時候會話會被阻塞

會話2:

這個時候在會話2繼續插入這個值，就會報出死鎖問題。

mysql> insert into test3 values(1,20170831,1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

--產生死鎖

會話1:

這個時候死鎖有了，事務也自動回滾了。再次插入違反約束的資料，就不行了。

mysql> insert into test3 values(1,20170831,1);
ERROR 1062 (23000): Duplicate entry '20170831' for key 'id2'

我們來看看在上面的測試過程中，關於死鎖的日誌：
2017-08-28T07:27:48.329631Z 14140 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2017-08-28T07:27:48.329740Z 14140 [Note] InnoDB:
*** (1) TRANSACTION:

TRANSACTION 31790, ACTIVE 315 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 14138, OS thread handle 139809903986432, query id 108686 localhost root update
insert into test3 values(1,20170831,1)
2017-08-28T07:27:48.329801Z 14140 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 36 page no 3 n bits 72 index PRIMARY of table `test`.`test3` trx id 31790 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000007c2f; asc     |/;;
 2: len 7; hex 33000001ac2f63; asc 3    /c;;
 3: len 4; hex 8133c84f; asc  3 O;;
 4: len 4; hex 80000001; asc     ;;

2017-08-28T07:27:48.330040Z 14140 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 31791, ACTIVE 51 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 14140, OS thread handle 139809903720192, query id 108687 localhost root update
insert into test3 values(1,20170831,1)
2017-08-28T07:27:48.330084Z 14140 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 36 page no 3 n bits 72 index PRIMARY of table `test`.`test3` trx id 31791 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000007c2f; asc     |/;;
 2: len 7; hex 33000001ac2f63; asc 3    /c;;
 3: len 4; hex 8133c84f; asc  3 O;;
 4: len 4; hex 80000001; asc     ;;

2017-08-28T07:27:48.330342Z 14140 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 36 page no 4 n bits 72 index id2 of table `test`.`test3` trx id 31791 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8133c84f; asc  3 O;;
 1: len 4; hex 80000002; asc     ;;
2017-08-28T07:27:48.330470Z 14140 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

這裡會充分把x,s鎖，細粒度鎖的知識聯絡起來，搞明白又進步了一大截。

會話1：

最後，我們提交一下事務，再次檢視資料，一切又恢復了平靜。

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test3;
+-----+----------+------+
| id1 | id2      | id3  |
+-----+----------+------+
|   2 | 20170831 |    2 |
+-----+----------+------+
1 row in set (0.00 sec)

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