MySQL在RR隔離等級(jí)下的unique失效和死鎖模擬

2024-07-24 12:32:05

字體：大中小

供稿：網(wǎng)友

今天在測(cè)試MySQL事務(wù)隔離級(jí)別的時(shí)候，發(fā)現(xiàn)了一個(gè)有趣的問題，也參考了楊一之前總結(jié)的一篇。http://blog.itpub.net/22664653/viewspace-1612574/

問題的背景是在MySQL隔離級(jí)別為RR（Repeatable Read)時(shí)，唯一性約束沒有失效，多并發(fā)的場(chǎng)景下能夠復(fù)現(xiàn)出下面的問題。

這樣一個(gè)看起來不可能的事情，能否復(fù)現(xiàn)呢。

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

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

#會(huì)話2

這個(gè)時(shí)候充分利用了MVCC的特性，這是一個(gè)快照讀。

mysql> select *from test3;
+-----+----------+------+
| id1 | id2 | id3 |
+-----+----------+------+
| 1 | 20170831 | 1 |
+-----+----------+------+
1 row in set (0.00 sec)
會(huì)話1插入了一條數(shù)據(jù)，我們?cè)跁?huì)話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)
提交之后，會(huì)話2中就修改完畢了。

#會(huì)話1

這個(gè)時(shí)候根據(jù)MVCC的特點(diǎn)，會(huì)話2中已經(jīng)刪除了id1=1的記錄。所以主鍵列相關(guān)數(shù)據(jù)是插入不了了，那么唯一性索引呢。根據(jù)MVCC的特點(diǎn)，能夠保證重復(fù)讀的特點(diǎn)，讀到的數(shù)據(jù)還是不變。

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

現(xiàn)在的關(guān)鍵就來了，我們插入一條數(shù)據(jù)，主鍵不沖突，唯一性索引沖突，看看是否能夠插入成功。

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)

當(dāng)然到了這里，我們繼續(xù)玩一玩，常規(guī)來說，插入主鍵列沖突數(shù)據(jù)可能是行不通的，比如id1=1,id2=20170831,id3=1,客戶端很快會(huì)反饋失敗。但是在這里做唯一性校驗(yàn)時(shí)，因?yàn)閕d1=1的數(shù)據(jù)已經(jīng)被物理刪除了。

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

--產(chǎn)生死鎖

會(huì)話1:

這個(gè)時(shí)候死鎖有了，事務(wù)也自動(dòng)回滾了。再次插入違反約束的數(shù)據(jù)，就不行了。

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

我們來看看在上面的測(cè)試過程中，關(guān)于死鎖的日志：
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.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)

這里會(huì)充分把x,s鎖，細(xì)粒度鎖的知識(shí)聯(lián)系起來，搞明白又進(jìn)步了一大截。

（編輯：武林網(wǎng)）

上一篇：mysql基本游標(biāo)

下一篇：MySQL入門--MySQL復(fù)制技能之主從從級(jí)聯(lián)復(fù)制