MySQL Bugs: #30184: InnoDB: obsolete row visible

Bug #30184	InnoDB: obsolete row visible
Submitted:	1 Aug 2007 17:48	Modified:	17 Aug 2007 17:17
Reporter:	Peter Gulutzan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.21-beta-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
I create an InnoDB table.
I insert one row.
On another connection, I update the row.
Back on the original connection, I update the row and then select.
I see two rows. But I only inserted one row.
One of the rows is the obsolete value before the last update.

The condition is temporary. It happens only if there are
SELECT statements as well as UPDATE statements. It happens
only if the UPDATE statements' SET clauses assign
"column_name + literal" rather than just "literal".

How to repeat:
Start two mysql clients. Call them T1 and T2.

On T1, say:
set @@autocommit=0;
create table t6 (s1 int, primary key(s1)) engine=innodb;
insert into t6 values (1);
commit;
select * from t6;

On T2, say:
set @@autocommit=0;
update t6 set s1 = s1 + 1;
commit;

On T1, say:
update t6 set s1 = s1 + 2;
select * from t6;

The result of the final SELECT looks like this:

mysql> select * from t6;
+----+
| s1 |
+----+
|  1 |
|  4 |
+----+
2 rows in set (0.01 sec)

Thank you for the bug report. Verified as described.

This is a 'consistent read anomaly' that has been reported before. A row not visible in my consistent read can be MADE visible by me if I update that row.

I think it is impossible to resolve this in a consistent way. The root of the problem is that InnoDB shows in my consistent read view all rows that I have inserted or modified. I chose this design, because otherwise, users would be even more confused since they would not see the changes they did themselves!

Marking this as 'Won't fix'.

I think that Mr Tuuri refers to an earlier bug report
Bug#17228 REPEATABLE READ doesn't work correctly in InnoDB tables?
I had forgotten. "Won't fix" is an understandable status.

This is an documented anomaly. Not a bug.