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事務的最佳化,值得一看

mvcc中的read_view

相關文章