Innodb Lock bug with select for update ?

[Christophe Fondacci]

Hello all,

I have a big trouble with innodb locks and select for update.
The problem is that mysql is locking a table on a select for update statement which does not select anything (because the where clause does not match any row).

Since a simple example is much better than long explanation, here is the test case.

Using 2 mysql clients.

Client 1 :
mysql> create table lck (id int) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)


Client 2 :
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from lck where id=100 for update;
Empty set (0.00 sec)

mysql>


Client 1 :
mysql> insert into lck (id) values (1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction



Could someone explain to me what is happening ? It is *as if* mysql is locking the lck table on a "select...for update" statement which returns 0 row. I would have expected mysql to lock a row only if the selected row had been found.

Is it a bug ?

Thank you.
Christophe.