MySQL Bugs: #5839: Different versions of the same rows returned at the same time in a transaction

Bug #5839	Different versions of the same rows returned at the same time in a transaction
Submitted:	1 Oct 2004 7:38	Modified:	12 Oct 2004 13:18
Reporter:	Shuichi Tamagawa	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.5-gamma	OS:	Linux (SuSE Linux 9.0 / Win XP SP2)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
If the column has PRIMARY KEY or UNIQUE restriction, two different version of the same table can be returned at the same time in one single transaction.

To see an example,  please see the 'How to Repeat' section. 

How to repeat:
Create table and insert some records as follows.

CREATE TABLE t1(a INT PRIMARY KEY) ENGINE = INNODB;
INSERT INTO t1 VALUES (1),(2),(3);

And execute transaction from two connections by the following order.
(Transaction Isolation level is "REPEATABLE-READ") 

Connection-1> SET AUTOCOMMIT=0;
Connection-2> SET AUTOCOMMIT=0;
Connection-1> SELECT * FROM t1;
Connection-2> SELECT * FROM t1;
Connection-1> UPDATE t1 SET a=a*10;
Connection-1> SELECT * FROM t1; # Point 1
Connection-2> SELECT * FROM t1; # Point 2
Connection-2> UPDATE t1 SET a=a+1;
Connection-1> COMMIT;
Connection-2> SELECT * FROM t1; # Point 3
Connection-2> COMMTT;

#Result set at the Point 1 (This is OK)
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+

#Result set at the Point 2 (??? It is supposed to be the same as Point 3)
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
| 11 |
| 21 |
| 31 |
+----+

# Result set at the point 3 (This is OK)
+----+
| a  |
+----+
| 11 |
| 21 |
| 31 |
+----+

If the the column a doesn't have the PRIMARY KEY or UNIQUE restriction, the result at the point 2 and 3 is the same, which is:

+----+
| a  |
+----+
| 11 |
| 21 |
| 31 |
+----+

Suggested fix:
Shouldn't the result at the point 2 be 
+----+
| a  |
+----+
| 11 |
| 21 |
| 31 |
+----+
???

Correction in transaction order:

Connection-1> SET AUTOCOMMIT=0;
Connection-2> SET AUTOCOMMIT=0;
Connection-1> SELECT * FROM t1;
Connection-2> SELECT * FROM t1;
Connection-1> UPDATE t1 SET a=a*10;
Connection-1> SELECT * FROM t1;
Connection-2> SELECT * FROM t1; # Point 1
Connection-2> UPDATE t1 SET a=a+1;  # Wait untill Connection-1 COMMIT
Connection-1> COMMIT;
Connection-2> SELECT * FROM t1; # Point 2
Connection-2> COMMTT;
Connection-2> SELECT * FROM t1; # Point 3

Verified using with the latest versions of 4.0/4.1/5.0 on Linux and Windows.

Hi!

The reason for this behavior is the following:

In the first consistent read SELECT in a transaction, InnoDB takes a snapshot of the database, and uses that in subsequent queries in the same transaction. But InnoDB also makes subsequent queries to see the 'modifications made by the transaction itself'. Unfortunately, if the transaction itself has made changes, there is no way to define a 'consistent view' to the database. The current implementation is an approximation which tries to handle the most common cases right. But it does not handle this particular case right.

In SELECT #2, transaction 2 sees the old versions (1), (2), and (3) of the rows, since they were in the original snapshot. But it ALSO sees (11), (22), and (33) because those rows came from an UPDATE run by transaction 2. Thus, the read view is badly inconsistent, since it sees 6 rows instead of 3!

If you want your consistent read snapshot to be certainly consistent, you should run it as a read-only transaction.

Regards,

Heikki