Mysql SELECT FOR UPDATE - strange issue

Question

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

I have a 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

Answer 1

Because at that time, it's safe to return the (empty) result - there's no lock to set for the record with id=9 as that doesn't exist and thus it cannot be updated - I don't think you can rely on innodb setting a read locks in such a case. It should set a write lock on id=9 though.

If at a later time, one of the transactions do update the table, and touch the same data as another transaction - the update would likely block in one of the transactions and later on fail if the other transaction commited that data. It's perfectly normal for transactions to fail in scenarios like this - leaving you to handle it - which usually is a matter of retrying the transaction.

If there were a record with id=9 you'd probably see the 2. select block until the first transaction is finished, as now there is a record that have to be read locked in case the first transaction decides to update that row.