Mysql SELECT FOR UPDATE - strange issue
- by Michal Fronczyk
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
