Hi,

I have a strange issue (at least for me :)) with the MySQL's locking facility.

I have a table:

Create Table: CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

With this data:

+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 10 |
| 11 |
| 12 |
+----+

Now I have 2 clients with these commands executed at the beginning:

set autocommit=0;
set session transaction isolation level serializable;
begin;

Now the most interesting part. The first client executes this query: (makes an intent to insert a row with id equal to 9)

SELECT * from test where id = 9 FOR UPDATE;
Empty set (0.00 sec)

Then the second client does the same:

SELECT * from test where id = 9 FOR UPDATE;
Empty set (0.00 sec)

My question is: Why the second client does not block ? An exclusive gap lock should have been set by the first query because FOR UPDATE have been used and the second client should block.

If I am wrong, could somebody tell me how to do it correctly ?

The MySql version I use is: 5.1.37-1ubuntu5.1

Thanks, Michal