MySQL Bugs: #58157: InnoDB locks an unmatched row even though using RBR and RC

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

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.

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!)

Thank you for the reprot.

Verified as described. Can be same as bug #57973

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.