MySQL Bugs: #43734: SELECT ... ORDER BY ... FOR UPDATE lock may be broken

Bug #43734	SELECT ... ORDER BY ... FOR UPDATE lock may be broken
Submitted:	18 Mar 2009 20:53	Modified:	10 Nov 2009 21:29
Reporter:	Bob Hansen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Windows (XP Pro SP2)
Assigned to:		CPU Architecture:	Any
Tags:	FOR UDPATE, lock, order by, SELECT

Description:
Consider the table StuContactRef with Primary Key (StuRefNum, FamilyNumber).
Also consider the table that I will provide shortly.

Notice that how the following appears to be invalid at first, it is accurate due to a phantom lock:

Connection 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from 0809_schedulegrades where sturefnum='91000447' for update;
24 rows in set (0.00 sec)

Connection 2:
mysql> select * from 0809_schedulegrades where sturefnum='91040418' for update;
16 rows in set (0.00 sec)
mysql> insert into 0809_schedulegrades (sturefnum,recordnum,courses) values ('91
040418','1','1050');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The records for student 91000447 are immediately before the records for student 91040418. Thus locking the former, also will lock the latter.

Now consider the same syntax, adding the "order by" clause to the statements. We get very questionable, and I might argue inaccurate, results.

Connection 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from 0809_schedulegrades where sturefnum='91000447' order by cou
rses for update;
24 rows in set (0.00 sec)

Connection 2:
mysql> select * from 0809_schedulegrades where sturefnum='91040418' order by cou
rses for update;
Empty set (1.86 sec)

I should also note that Lock Wait Timeout variable is set to 1 second (as opposed to 45 by default). I believe it is giving me a Lock Wait Timeout error silently, returning a success, and giving back zero records. Is this correct behavior?

How to repeat:
Restore uploaded table into corresponding database. Execute statements above in two MySQL Command Line Clients to verify. Note: Show Warnings gave me no extra output.

Suggested fix:
Depends on what is supposed to be locked. A timeout SHOULD be returning an error though (-1) and not a success (0).

Thank you for the report.

What do you mean by "Lock Wait Timeout variable"? Is it innodb_lock_wait_timeout or?

Also please explain problem with lock. Does "select * from 0809_schedulegrades where sturefnum='91040418' order by courses for update;" return immediately or?

Correct. Lock Wait Timeout variable is innodb_lock_wait_timeout.

My problem is two-fold.
First, queryA (without the "ORDER BY") does not give me the same results as the queryB (with the "ORDER BY"). Changing the order of results should not change the contents of the results.

Second, queryB should return an empty result set OR timeout, not both. And if it times out then it should fail not succeed.

queryA:
SELECT * FROM 0809_ScheduleGrades where StuRefNum='91040418'
ORDER BY Courses FOR UDPATE;

queryB:
SELECT * FROM 0809_ScheduleGrades where StuRefNum='91040418'
FOR UDPATE;
^This query returns immediately following a 2 second pause.

Thank you for the feedback.

I can not repeat described behavior since version 5.1.31, although bug was repeatable with version 5.1.30. Please upgrade.

Upgraded to latest version 5.1.32

This execution still gives me the same result (in the circumstances prior noted).
mysql> select * from 0809_schedulegrades where sturefnum='91040418' for update;
16 rows in set (0.00 sec)

This execution now works differently.
mysql> select * from 0809_schedulegrades where sturefnum='91040418' order by cou
rses for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

A timeout does indeed return an error instead of silently failing and returning a success. That is a good thing. Unfortunately it now begs the question as to why these two query's are doing different things. They are selecting the same exact criteria, the only difference is the ORDER BY clause. Shouldn't BOTH of them timeout or BOTH of them succeed? Thanks.

I am unable to reproduce the ORDER BY bug using 5.1.35 and 5.1.40, it must be fixed. Although now there is a phantom lock issue. Started new bug report:
http://bugs.mysql.com/bug.php?id=43734