MySQL Bugs: #47295: Locked a record that shouldn't be such

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

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.

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?

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".

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.