This article discusses about the approach taken by InnoDB Storage Engine of MySQL to provide the
repeatable read isolation level. First, an example is
presented to demonstrate the two different designs that are
possible. Then the design used in InnoDB is presented
followed by a short discussion about the advantages and
disadvantages of this design choice. As part of this
discussion, we also present a performance optimization done in
MySQL 5.6.
An Example Scenario
I used MySQL 5.5 for this purpose. Let us create the
following tables t1 and t2 in the test database that is available
by default. Even though the default storage engine in MySQL 5.5
is InnoDB, I explicitly specify it for clarity.
mysql> use test;
mysql> create table t1 (f1 int) engine=innodb;
mysql> create …