Mysql SELECT FOR UPDATE - strange problem

Question

I have a strange problem (at least for me :)) with the ability to lock MySQL.

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 the isolation level of a session transaction serializable;
begin;

Now the most interesting part. The first client executes this request: (makes the intention to insert a row with identifier equal to 9)

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

Then the second client does the same:

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

My question is: why is the second client not blocking? An exclusive blocking of spaces should have been set by the first query, because FOR UPDATEs were used, and the second client should block.

If I'm wrong, can someone tell me how to do it right?

MySql Version Used: 5.1.37-1ubuntu5.1

Answer 1

Because at this time it is safe to return a (empty) result - there is no lock to set the record with id = 9, because it does not exist and therefore it cannot be updated - I don’t think you can rely on innodb to install read lock in this case. It should set write lock on id = 9, though.

If at a later time one of the transactions updates the table and touches the same data as the other transaction, the update will probably be blocked in one of the transactions, and later it will fail if the other transaction makes this data. This is perfectly normal for transactions to crash in scripts like this - which allows you to handle this, which is usually associated with transaction replay.

If there was a record with id = 9, you would probably see block 2. select before the completion of the first transaction, since now there is a record that should be locked if the first transaction decides to update this line.

Answer 2

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html