Bug #76803 InnoDB: Unlock row could not find a 2 mode lock on the record

• Submitted: 23 Apr 2015 8:42
• Modified: 25 Jan 2019 1:21
• Reporter: zhai weixiang (OCA)
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.1, 5.5, 5.6, 5.7, 8.0
• OS: Any
• CPU Architecture: Any

[23 Apr 2015 8:42] zhai weixiang

Description:
Error message is reported while executing the following statements

drop table if exists t1,t2;
create  table t1 (c1 int , c2 int, c3 int, primary key (c1,c2) ) engine = innodb;
create  table t2 (c1 int,  c2 int, c3 int,  primary key (c1), key (c2)) engine = innodb;
insert into t1 values (1,2,3),(2,3,4),(3,4,5),(5,6,7),(7,8,9),(8,9,10),(10,11,12);
insert into t2 select * from t1;
update t1 left join t2 on t1.c1 = t2.c2 and t2.c1 = 3 set t1.c3 = rand()*10;

Error message in log file:

2015-04-23T08:41:17.356649Z 4387 [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record. Current statement: update t1 left join t2 on t1.c1 = t2.c2 and t2.c1 = 3 set t1.c3 = rand()*10
2015-04-23T08:41:17.356731Z 4387 [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record. Current statement: update t1 left join t2 on t1.c1 = t2.c2 and t2.c1 = 3 set t1.c3 = rand()*10
2015-04-23T08:41:17.356755Z 4387 [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record. Current statement: update t1 left join t2 on t1.c1 = t2.c2 and t2.c1 = 3 set t1.c3 = rand()*10
2015-04-23T08:41:17.356796Z 4387 [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record. Current statement: update t1 left join t2 on t1.c1 = t2.c2 and t2.c1 = 3 set t1.c3 = rand()*10
2015-04-23T08:41:17.356834Z 4387 [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record. Current statement: update t1 left join t2 on t1.c1 = t2.c2 and t2.c1 = 3 set t1.c3 = rand()*10
2015-04-23T08:41:17.356867Z 4387 [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record. Current statement: update t1 left join t2 on t1.c1 = t2.c2 and t2.c1 = 3 set t1.c3 = rand()*10

How to repeat:
Described above

Suggested fix:
Still work on this issue

[23 Apr 2015 9:05] MySQL Verification Team

Affects 5.1, 5.5, 5.6, 5.7, 5.8
Did not affect 5.0

[23 Apr 2015 9:05] MySQL Verification Team

Thanks for the report!   start server with --transaction-isolation=read-committed to repeat.

[18 Jun 2016 21:26] Omer Barnir

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0

[25 Jan 2019 1:21] Jon Stephens

Documented fix as follows in the MySQL 5.7.26 and 8.0.15 changelogs as follows:

    When the method used to access a joined table was const, InnoDB
    attempted to unlock the matching row multiple times.

Closed.