mysql提交bug_MySQL:关于Bug #20939184

一、Bug描述

今天遇到了这个Bug进行了一下查询，这个Bug由印风提交，修复版本为5.7.26，8.0.15如下，这个Bug触发条件还是比较苛刻：

[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

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

值得关注的是这个Bug从提交到修复历史近4年，当然影响也不大。

二、为什么会引起这个Bug

如下：

commit 1b2e8ea269c80cb93cc79d8be9