Bug #47295 Locked a record that shouldn't be such
Submitted: 13 Sep 2009 14:00 Modified: 4 Jan 2010 14:29
Reporter: Saverio Miroddi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1.31-1ubuntu2 OS:Any
Assigned to: CPU Architecture:Any
Tags: locking innodb

[13 Sep 2009 14:00] Saverio Miroddi
Description:
I can't explain a lock that is put on a record in a given configuration.
I read the documentation and asked in the forum, but still can't figure out why if it's happening.

Details given in the "how to repeat" section.

How to repeat:
Create this innodb table:

CREATE TABLE `pizza` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`attachable_id` int(11) DEFAULT NULL,
`priority` int(11) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `attachable_id` (`attachable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8;

Fill it with the following inserts:

INSERT INTO pizza VALUES (1,1,0), (2, NULL, 0), (3, NULL, 0), (5, 2, 0), (6, NULL, 0), (7, NULL, 0);

Now, open this transaction:

BEGIN; UPDATE pizza SET priority = NULL WHERE ID = 1; 

And open this other tx in a separate client:

BEGIN: UPDATE pizza SET priority = priority - 1 WHERE attachable_id IS NULL;

The second tx will now wait for the locks of the first table to be released.
It's not clear why it happens, because the should be a lock only on the record with ID=1.

If I rollback both, and execute:

DELETE FROM pizza WHERE id = 5;

And repeat the two txs, the second one now won't wait for any lock, which is supposed to be the appropriate locking behavior.
[14 Sep 2009 4:01] Valeriy Kravchuk
What about http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html? Doesn't it explain this situation with next-key locks?
[14 Oct 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[4 Jan 2010 14:18] Saverio Miroddi
Carefully read the article - the article generically (but appropriately, given the article subject) says "locks on the index it [mysql] encounters", and it's my problem not to have a deep enough understading of the processso - so I don't consider it a bug.