My SQL 5.0.15, InnoDB
We have found cases where more than the selected row is being locked. A simple example is given below. When an order by is used in the first select statement it (apparently) places exclusive locks on rows in addition to the one selected. These additional row locks prevent the second statement from executing.
This behaviour is not consistent with row-level locking and I have not found any comprehensive explanation in the documentation (including Innodb-locks-set
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html). I would appreciate any help you can offer.
// Table to use in our test
CREATE TABLE `test`.`block` (
`id` int(10) unsigned NOT NULL auto_increment,
`shape` char(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `BY_SHAPE` (`shape`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into block values (null, 'Triangle');
insert into block values (null, 'Square');
insert into block values (null, 'Circle');
// In one session perform the following.
begin;
select * from block where shape = 'square' order by id for update;
// In another session preform the following.
begin;
select * from block where shape = 'circle' order by id for update;