導讀
重溫快照讀、目前讀。
本文節選自葉金榮有贊專欄《亂彈MySQL》。
關于InnoDB在事務中何時建立read view,我在
InnoDB MVCC何時建立read view這篇文章裡已經說過,在RC(read committed)隔離級别下是每次SELECT都會構造最新的read view,而在RR(repeatable read)隔離級别下是在事務中發起第一個SELECT時構造read view。接下來我們來看一個幻讀的案例。先看運作環境:
[[email protected]]>\s
...
Server version: 5.6.21-log MySQL Community Server (GPL)
...
[[email protected]]>select * from information_schema.GLOBAL_VARIABLES where
VARIABLE_NAME = 'innodb_locks_unsafe_for_binlog';
+--------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------------+----------------+
| INNODB_LOCKS_UNSAFE_FOR_BINLOG | ON |
+--------------------------------+----------------+
[[email protected]]>select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
也就是在RR隔離級别下,又設定了 INNODB_LOCKS_UNSAFE_FOR_BINLOG=1,其效果相當于禁用GAP LOCK。換言之,也相當于是把隔離級别降到了RC,這時候會産生幻讀。P.S,關于INNODB_LOCKS_UNSAFE_FOR_BINLOG=1有幾點提醒:
- 已經使用RR級别時,最好就不要再将該選項設定為1了,否則有可能會造成主從資料不一緻(這時應該同時設定binlog_format=ROW)
- 該選項設定為1時,唯一鍵和外鍵限制檢測依然會使用next-key lock
- 該選項隻能在啟動時設定,且不能在運作過程中動态修改,而事務隔離級别則可以在運作過程中動态修改。從這方面出發,其實有需要的話,最好是自行調整隔離級别就好,而不是設定本選項
- 該選項從8.0版本開始已經被删除
接來看下這種情況下的幻讀案例場景。關于測試表的背景資訊:
[[email protected]]> show create table t1\G
CREATE TABLE `t1` (
`c1` int(10) unsigned NOT NULL DEFAULT 0,
`c2` int(10) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`c1`),
KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
場景1
session1 | session2 |
begin; | |
select * from t1 where c2 = 5; ... | 5 | 5 | | |
insert into t1 select 7,5; commit; | |
select * from t1 where c2 = 5 for update; | 7 | 5 | | |
#去掉for update後,隻能看到一條記錄 |
場景2
#不會被阻塞 | |
#看到兩條記錄,疑似發生了幻讀? |
場景3
start transaction with consistent snapshot; | |
咦,這個案例看起來和場景1有點像?注意到上面3個案例之間的差別了嗎,我們來羅列一下:
- 場景1,session2的第一個select是在session1執行commit之後發起的,此時建構read view
- 場景2,session2的第一次讀是select ... for update形式的,此時似乎沒有建立read view
- 場景3,session2發起事務時直接加了with consistent snapshot,也就是要求建立一緻性快照讀
- 看起來場景1和場景3,都是在事務送出前建立了read view
- 而場景2裡,session2是在第二次的select才建立快照,而不是在第一次select ... for update時就建立read viw
上面其實已經提到了幾個關鍵資訊:
- 在RR隔離級别下,遇到第一個普通SELECT才會開始建立read view。而如果SELECT ... FOR UPDATE/FOR SHARE這樣的,叫做目前讀或加鎖讀,這種是不會建立read view的
- 即便是 innodb_locks_unsafe_for_binlog=1 的時候,在RR隔離級别下,事務中第一個普通SELECT建立的快照在整個事務過程中依然有效,這個選項的作用隻是禁用了gap lock,并不會影響RR級别的其他行為
- 發起事務時,如果加上with consistent snapshot會立即建立read view,和事務啟動後立刻發起普通SELECT相同效果
- 在RC隔離級别下,還是每次普通SELECT都會重新建立read view
好了,就醬。Enjoy MySQL :)