MySQL Bugs: #35472: row-based InnoDB locks prevent block update queries from other client

Bug #35472	row-based InnoDB locks prevent block update queries from other client
Submitted:	21 Mar 2008 6:00	Modified:	26 Mar 2008 9:44
Reporter:	Mikhail Solovyev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.51a-3	OS:	Linux (Debian Etch)
Assigned to:		CPU Architecture:	Any

Description:
There is some problem with Row-based locks in InnoDB - when I use SELECT .. LIMIT 1 FOR UPDATE in transaction, it should lock only one row (according to MySQL documentation), but I see that it locks something else.

How to repeat:
Create table with simple structure:

mysql> show create table st6431;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                          |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| st6431 | CREATE TABLE `st6431` (
  `id` int(11) NOT NULL,
  `textfield` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Fill it with some data

mysql> select * from st6431;
+----+-----------+
| id | textfield |
+----+-----------+
|  1 | blablabla |
|  2 | blablabla |
|  3 | blablabla |
|  4 | blablabla |
|  5 | blablabla |
|  6 | blablabla |
|  7 | blablabla |
|  8 | blablabla |
|  9 | blablabla |
| 10 | blablabla |
+----+-----------+
10 rows in set (0.00 sec)

Then on first console do:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from st6431 where id=5 FOR UPDATE;
+----+-----------+
| id | textfield |
+----+-----------+
|  5 | blablabla |
+----+-----------+
1 row in set (0.00 sec)

And do not complete this transaction for a while. It will kep one row in locked state.

And then open second console and try several queries:
2-nd client:
mysql> update st6431 set textfield='newblablabla' where id>5;
Query OK, 5 rows affected (0.07 sec)
Rows matched: 5  Changed: 5  Warnings: 0
...it works as expected

mysql> update st6431 set textfield='newblablabla' where id=5;
... it waits till end of transaction as expected

mysql> update st6431 set textfield='newblablabla' where id=3;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0
... it works as expected

mysql> update st6431 set textfield='newblablabla' where id=4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
... it works as expected

mysql> update st6431 set textfield='newblablabla' where id<5;
... It stops and waits until 1-st client's transaction is being completed. WHY???

So I wonder, why "update ... where id>5" works fine, but "update ... where id<5" does not?

Please see my SHOW InnoDB STATUS in attachment.

SHOW InnoDB STATUS

Attachment: show_innodb_status.txt (text/plain), 5.51 KiB.

Thank you for a problem report. This is not a bug though. Your UPDATE:

update st6431 set textfield='newblablabla' where id<5

tries to set next key X lock on id=4 (this next key is a record with id=5), but can not. Read http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html for more details.

Valeriy,

Thanks for giving this links, but I've seen it already, and still nto sure if it is my case.. In this example query has "id > 100", and it's easy to understand that it will lock next row as described in Next-Key locking description.

But in my case there is a bit another thing - why "UPDATE .. WHERE id < 5" tries to set lock to id=5 (which is locked by 1-st transaction)? According to Next-Key locking description it works only in case when "id>xx" statement is used, isn't it so?

Valeriy,

Can you please check my last comment? If it does not seem reasonable - then feel free to close bug, but I believe it would be useful if this behavior is documented in next-key locking description.

Mikhail,

let me try to explain this again for you:

My example is just for imaginations:

You have the following doors:

1, 2, 3, 4, 5, 6, 7, 8, 9

Now somebody will change the door 5 into ? and will lock it.

Result:

1, 2, 3, 4, ? , 6, 7, 8, 9

Of course you forget what was behind the ?.

Now you get the order to open all doors that are smaller then 5.

What will you do with the ?-door?
You don't know, which number it has without looking into it.
It could have 5 but it also could have 0.
You only can solve the riddle correct when you get the chance to inform you if the ?-door is 5 or something that's lower then 5.