mvcc中的read_view
innodb的mvcc和read view
最近讀High Performance
MySQL,里面提到了innodb事務隔離級別是REPEATABLE-READ時,有這樣一段話
引用
SELECT
InnoDB must examine each row to ensure that it meets two criteria:
a. InnoDB must find a version of the row that is at least as old as the transaction
(i.e., its version must be less than or equal to the transaction’s version). This
ensures that either the row existed before the transaction began, or the trans-
action created or altered the row.
b. The row’s deletion version must be undefined or greater than the transaction’s
version. This ensures that the row wasn’t deleted before the transaction began.
Rows that pass both tests may be returned as the query’s result.
來驗證一下
show create table 20130302t1;
CREATE TABLE `20130302t1` (
`id` int(11) NOT NULL,
`b` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
表中有數據(1,1)
autocommit為false, tx_isolation 是REPEATABLE-READ
考慮以下兩種情況
情況1
|
session A
|
session B
|
|
start transaction;(A)
|
|
|
|
start transaction
|
|
|
update 20130302t1 set b=2 where id=1;
|
|
|
commit;
|
|
select * from 20130302t1;(B)
|
|
B處結果為(1,2),似乎不符合那段話里的a條件,A事務看到了transaction version更大的B事務
情況2
|
session A
|
session B
|
|
|
start transaction
|
|
|
update 20130302t1 set b=2 where id=1;
|
|
start transaction
|
|
|
select * from 20130302t1;
|
|
|
|
commit;
|
|
select * from 20130302t1;(C)
|
|
C處結果為(1,1),也就是說,A事務沒有看到transaction version更小的B事務
是不是那段話有問題呢,后來終于找到了官方的文檔,innodb通過read view來確定一致性讀時的數據庫snapshot,innodb的read view確定一條記錄能否看到,有兩條法則
1 看不到read view創建時刻以后啟動的事務
2 看不到read view創建時活躍的事務
引用
Rule 1: When the read view object is created it notes down the smallest transaction identifier that is not yet used as a transaction identifier (trx_sys_t::max_trx_id). The read view calls it the low limit. So the transaction using the read view must not see any transaction with identifier greater than or equal to this low limit.
Rule 2: The transaction using the read view must not see a transaction that was active when the read view was created.
在情況1中,代碼A處并沒有創建read view,read view是在代碼B處創建的.
如果把A處代碼改為 START TRANSACTION WITH CONSISTENT SNAPSHOT;
才會創建read view,使得代碼B返回(1,1)
在情況2中,B事務在A事務創建read view時處于ACTIVE狀態,所以B事務不會被A事務看到.
這篇文章還提到了mysql5.6 在read only事務的優化,值得一看
