Shulou(Shulou.com)06/01 Report--

Mvcc and read view of innodb

When I read High Performance MySQL recently, I mentioned that when the transaction isolation level of innodb is REPEATABLE-READ, there is a passage like this.

Quote

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.

To verify it.

Show create table 20130302t1; CREATE TABLE `20130302t1` (`id` int (11) NOT NULL, `b` int (11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB

There is data in the table (1pc1)

Autocommit is false, tx_isolation is REPEATABLE-READ

Consider the following two situations

Case 1

Session Asession Bstart transaction; (A) start transactionupdate 20130302t1 set baked 2 where id=1;commit;select * from 20130302t1; (B)

The result at B is (1), which does not seem to meet the a condition in that paragraph. The A transaction sees the larger B transaction of transaction version.

Case 2

Session Asession Bstart transactionupdate 20130302t1 set bread2 where id=1;start transactionselect * from 20130302t1 * from 20130302t1; (C)

The result at C is (1), that is, A transaction does not see B transaction with smaller transaction version.

Is there something wrong with that passage? later, I finally found the official document. Innodb uses read view to determine the consistency of the database at the time of reading. The read view of snapshot,innodb determines whether a record can be seen. There are two rules.

1 you cannot see the transactions started after the creation time of read view

2 you cannot see the active transactions when read view was created

Quote 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.

In case 1, the read view,read view is not created at code A. it is created at code B.

If you change the code at A to START TRANSACTION WITH CONSISTENT SNAPSHOT

To create a read view so that code B returns (1Jing 1)

In case 2, B transaction is in ACTIVE state when A transaction creates read view, so B transaction will not be seen by A transaction.

This article also mentions the optimization of mysql5.6 in read only transactions, which is worth looking at.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.