Bug #58157 InnoDB locks an unmatched row even though using RBR and RC

Submitted: 12 Nov 2010 9:21 Modified: 16 Jul 2012 23:40
Reporter: Yoshinori Matsunobu (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S3 (Non-critical)
Version:5.1.51, 5.0, 5.6.1 OS:Any
Assigned to: Marko Mäkelä CPU Architecture:Any
Tags: innodb

[12 Nov 2010 9:21] Yoshinori Matsunobu
Description:
When scanning and locking rows with < or <=, InnoDB locks the next row even though row based binary logging and read committed is used.

How to repeat:
Set log-bin, binlog-format=row, transaction-isolation=read-committed in my.cnf. 

create table i (id int primary key, value int) engine=innodb;
insert into i values (1,0), (2,0), (3,0), (4,0), (5,0), (6,0), (7,0), (8,0), (9,0), (10,0);

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

mysql> show global status like 'innodb_rows_read';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| Innodb_rows_read | 0       |
+------------------+---------+
1 row in set (0.00 sec)

mysql> update i set value=10 where id <= 4;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> show global status like 'innodb_rows_read';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| Innodb_rows_read | 5       |
+------------------+---------+
1 row in set (0.00 sec)

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

mysql> update i set value=100 where id=5;
...
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

"update i set value=10 where id <= 4;" returns 4 records, but it actually reads (and likely locks) 5 records. So the row of id=5 seems to be exclusively locked. 

Suggested fix:
I think that ... MySQL layer (handler::read_range_next() etc) calls ha_innobase::index_read() 5 times. handler::read_range_next() checks range criteria by calling compare_key(end_range), so InnoDB does not check whether id=5 meets range criteria or not. This is expected because InnoDB does not support condition pushdown, but in this case InnoDB should not lock the row of id=5.
[12 Nov 2010 13:31] Peter Laursen
If I understand this is related to the server 'innodb_autoinc_lock_mode' variable. Refer http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html.  The default setting for 'innodb_autoinc_lock_mode' is not the same in MySQL 5.1.x as in earlier versions.

Peter
(not a MySQL person - but reported almost same earlier!)
[19 Nov 2010 18:42] Sveta Smirnova
Thank you for the reprot.

Verified as described. Can be same as bug #57973
[16 Jul 2012 23:40] John Russell
Added to changelog for 5.1.66, 5.5.28, 5.6.7, 5.7.0: 

When a SELECT ... FOR UPDATE, UPDATE, or other SQL statement scanned
rows in an InnoDB table using a < or <= operator in a WHERE clause,
the next row after the affected range could also be locked. This
issue could cause a lock wait timeout for a row that was not expected
to be locked. The issue occurred under various isolation levels, such
as READ COMMITTED and REPEATABLE READ.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.