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 Asession Bstart transaction;(A)start transactionupdate 20130302t1 set b=2 where id=1;commit;select * from 20130302t1;(B)
B处结果为(1,2),似乎不符合那段话里的a条件,A事务看到了transaction version更大的B事务
情况2
session A session Bstart transactionupdate 20130302t1 set b=2 where id=1;start transactionselect * 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事务的优化,值得一看
