MySQL Bugs: #30159: Different results with SELECT FOR UPDATE versus plain SELECT

Bug #30159	Different results with SELECT FOR UPDATE versus plain SELECT
Submitted:	31 Jul 2007 21:46	Modified:	1 Aug 2007 16:09
Reporter:	Peter Gulutzan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.21-beta-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Under some circumstances, "select * from t1 where s1 = 8 for update"
and "select * from t1 where s1 = 8" can return different results.

Actually I think this behaviour is 'as intended'.
But I couldn't find it in the manual.
So I'll claim it's a documentation flaw.

How to repeat:
Start with two mysql client connections. Call them T1 and T2.

On T1, say:
create table t1 (s1 int)
engine=innodb
partition by list (s1) (partition p8 values in (8), partition p9 values in (9));
set @@autocommit=0;
insert into t1 values (8);
insert into t1 values (9);
commit;
select * from t1 where s1 = 8 for update;

On T2, say:
set @@autocommit=0;
select * from t1 where s1 = 9 for update;

On T1, say:
select * from t1 where s1 = 9 for update; /* hangs */

On T2, say:
select * from t1 where s1 = 8 for update;
select * from t1 where s1 = 8;

The final SELECT on T2 will look like:
"
mysql> select * from t1 where s1 = 8 for update;
Empty set (0.01 sec)

mysql> select * from t1 where s1 = 8;
+------+
| s1   |
+------+
|    8 |
+------+
1 row in set (0.00 sec)
"
So you can see that the results are different if
there is no FOR UPDATE clause.

Thank you for the bug report. Verified as described.

Hi Peter,

"Under some circumstances": What is the nature of these circumstances? (That is, what is the basis for having an expectation that the queries will return different results.)

I've changed the category to 'server bug' because

(a) Paul DuBois is unaware of any documentation about this behaviour

(b) making it a 'server bug' might help us find who worked on FOR UPDATE.

I still think it can be okay if SELECT and SELECT FOR UPDATE return
different results "under some circumstances", and so does Mr Schumacher:
http://dev.mysql.com/tech-resources/articles/falcon-transactional-engine-part2.html
"The result of a simple SELECT and a SELECT … FOR UPDATE may differ within
the same repeatable read transaction."
But if so the responsible person should list what the circumstances are.

See also Bug#29320.

http://dev.mysql.com/tech-resources/articles/falcon-transactional-engine-part2.html
"The result of a simple SELECT and a SELECT … FOR UPDATE may differ within the same repeatable read transaction."