MySQL Bugs: #2039: SELECT FOR UPDATE does not lock the selected rows

Bug #2039	SELECT FOR UPDATE does not lock the selected rows
Submitted:	7 Dec 2003 17:16	Modified:	18 Dec 2003 15:45
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.0-alpha-max-nt	OS:	Windows (Win2k)
Assigned to:		CPU Architecture:	Any

Description:
SELECT FOR Update does no lock the selected rows:
1) Client A:
Select * from table X where ID=1 for update
Dont Commit!

2) Client B:
Select * from table X where ID=1 for update
The querey returns without waiting for the Client A to commit.

I have tried the same on Oracle 9i, and there, the second client blocks until the transaction of the first is committed. This is the normal behaviour, but mysql seems to fail here, although the doc claims that it supports select for update.

How to repeat:
See above. It can be reproduced on any table and data.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Session A:

mysql> create table x (id int primary key, a int) type=innodb;
Query OK, 0 rows affected (0.06 sec)
 
mysql> insert into x values(1, 1);
Query OK, 1 row affected (0.00 sec)
 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from x where id=1 for update;
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

Session B:
mysql> select * from x where id=1 for update;
ERROR 1205: Lock wait timeout exceeded; Try restarting transaction

Thanks for the fast response!

Your example gave me the idea:
In my tests, i did not include "type=innodb" in the create table statement, since the doc says that this was the default. Omitting it, however, the select for update's do not work. (Regardless of in which DB i put the table.) Now I have retested the whole, and if I specify "type=innodb", the locking works.

The error can be reproduced for example with the following: (Tested with Squirrel, of course autocommit=off)

create table testx (id int unique not null, a int);
0 Rows Updated
Query 1 elapsed time (seconds) - Total: 0,07, SQL query: 0,07, Building output: 0

insert into testx values (1, 1);
1 Rows Updated
Query 1 elapsed time (seconds) - Total: 0,01, SQL query: 0,01, Building output: 0

select * from testx where id=1 for update;
which returns immediately in both sessions.

Regards,
Balazs

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Default table type is runtime parameter and if not specifed default is MyISAM not InnoDB. This is explained in MySQL Manual and it is also explained that SELECT FOR UPDATE applies to InnoDB storage only.

Current default table setting can be verified with:

mysql> show variables like "table_type";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| table_type    | MYISAM |
+---------------+--------+