MySQL Bugs: #3153: bad result set data with "SELECT...FOR UPDATE"

Bug #3153	bad result set data with "SELECT...FOR UPDATE"
Submitted:	12 Mar 2004 4:58	Modified:	21 Mar 2004 23:09
Reporter:	marcello soffritti	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.16	OS:	HP/UX (HPUX11.11)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Hi,
I have the following table:

CREATE TABLE prova (
eta int,
tipo int, 
PRIMARY KEY(eta)) TYPE=InnoDB;

with the following contents:
+-------------+-------------+
| eta         | tipo        |
+-------------+-------------+
|           1 |           1 |
|           2 |           2 |
|           3 |           3 |
+-------------+-------------+

Note that in /etc/my.cnf I have the following:
[mysqld]
set-variable = innodb_lock_wait_timeout=5

Now I start two mysql different sessions:
session_A :
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
UPDATE prova SET tipo=7774 where tipo=1;

...

session_B:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
SELECT * from prova FOR UPDATE;       (<-- 1)

After about 5-6 seconds (=~ innodb_lock_wait_timeout)
session_B receives:
+-------------+-------------+
| eta         | tipo        |
+-------------+-------------+
+-------------+-------------+
0 rows in set (6.06 sec)

session_B then executes:
SELECT * from prova WHERE eta=1 FOR UPDATE;     (<-- 2)
and prints:
ERROR 1205: Lock wait timeout exceeded; Try restarting transaction

Finally, if session_B then executes:
SELECT * from prova WHERE tipo=1 FOR UPDATE;        (<-- 3)
it receives:
+-------------+-------------+
| eta         | tipo        |
+-------------+-------------+
+-------------+-------------+
0 rows in set (6.06 sec)

Why do query #1 and #3 returns (null) data?
Why do query #1 and #3 not return an error?
Is this the normal behaviour ?

Best regards,
Marcello.

How to repeat:
See Description.

I cannot repeat this against 4.0.19 or 4.1.2.  Session A should and does block all three queries in Session B.  Please try with a current release, and make sure that the test case is accurate.

Clarifying: The lock that Session A holds should (and does in testing) cause each of the three queries that Session B attempts to block and then time out.

Hi!

I was not able to repeat the strange output against 4.0.15 or 4.0.17 on Linux. I tested also with the query cache enabled.

Hmm... could this be some bug in the mysqld HP-UX build?

Thank you,

Heikki

Hi!

A note from Guilhem:

"
Just to say that yesterday I tested on our HP-UX 11 machine
...
with 4.0.15-64bit and 4.1.2-latest-bk and got results like on
Linux (always timeout). So I could not repeat their problem.
"

Thus, we cannot repeat this on 64-bit HP-UX either.

Marcello, please send us very detailed instructions about how to repeat the problem.

Thank you,

Heikki

Hi,
I have noticed that I can't repeat the bug again.
Note that I think that the bug exists: in fact the trace messages + SQL commands added in the bug description are the mysql client real input/output. If I'll encounter it again, I'll add a new comment to the current bug #3153.
Best regards,
Marcello.