mvcc中的read_view
innodb的mvcc和read view
最近讀High Performance
MySQL,里面提到了innodb事務(wù)隔離級(jí)別是REPEATABLE-READ時(shí),有這樣一段話
引用
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.
來(lái)驗(yàn)證一下
show create table 20130302t1;
CREATE TABLE `20130302t1` (
`id` int(11) NOT NULL,
`b` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
表中有數(shù)據(jù)(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處結(jié)果為(1,2),似乎不符合那段話里的a條件,A事務(wù)看到了transaction version更大的B事務(wù)
情況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處結(jié)果為(1,1),也就是說(shuō),A事務(wù)沒(méi)有看到transaction version更小的B事務(wù)
是不是那段話有問(wèn)題呢,后來(lái)終于找到了官方的文檔,innodb通過(guò)read view來(lái)確定一致性讀時(shí)的數(shù)據(jù)庫(kù)snapshot,innodb的read view確定一條記錄能否看到,有兩條法則
1 看不到read view創(chuàng)建時(shí)刻以后啟動(dòng)的事務(wù)
2 看不到read view創(chuàng)建時(shí)活躍的事務(wù)
引用
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處并沒(méi)有創(chuàng)建read view,read view是在代碼B處創(chuàng)建的.
如果把A處代碼改為 START TRANSACTION WITH CONSISTENT SNAPSHOT;
才會(huì)創(chuàng)建read view,使得代碼B返回(1,1)
在情況2中,B事務(wù)在A事務(wù)創(chuàng)建read view時(shí)處于ACTIVE狀態(tài),所以B事務(wù)不會(huì)被A事務(wù)看到.
這篇文章還提到了mysql5.6 在read only事務(wù)的優(yōu)化,值得一看
