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
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[31 Jul 2007 21:46] Peter Gulutzan
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.
[31 Jul 2007 22:02] Miguel Solorzano
Thank you for the bug report. Verified as described.
[1 Aug 2007 15:24] Paul Dubois
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.)
[1 Aug 2007 15:50] Peter Gulutzan
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.
[1 Aug 2007 15:58] Peter Gulutzan
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."