专业做网站企业/湖南企业seo优化报价
###一、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 1b2e8ea269c80cb93cc79d8be934c40b1c58e947
Author: Kailasnath Nagarkar
Date: Fri Nov 30 16:43:13 2018 +0530
Bug #20939184: INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE
LOCK ON THE RECORD
Issue:
——
Consdier tables t1 and t2 such that t1 has multiple rows
and join condition for t1 left join t2 results in only
single row from t2.
In this case, access to table t2 is const since there
is a single row that qualifies the join condition.
However, while executing the query, attempt is made to
unlock t2’s row multiple times.
The current algorithm to fetch rows approximates to:
1) Retrieve the row for t1.
2) Retrieve the row for t2.
3) Apply the join conditions.
a) If condition evaluates to true:
Project the row to the result.
b) If condition evaluates to false:
i) If t2’s qep_tab->not_null_complement is true,
unlock t2’s row.
ii) Null-complement the row by calling
“evaluate_null_complemented_join_record()”. In
this function qep_tab->not_null_complement is
set to false.
The t2’s only one row, that qualifies join condition,
is unlocked in Step i) when t1’s row is evaluated to
false.
When t1’s next row is also evaluated to false, another
attempt is made to unlock t2’s already unlocked row.
This results in following error being logged in error.log:
“[ERROR] InnoDB: Unlock row could not find a 3 mode lock on
the record. Current statement:
select * from t1 left join t2 ……”
Solution:
———
When a table’s access method is “const”, set record unlock
method for this table to do no operation.
“`
简单的说就是对于const常量表的访问，join类别的DML语句如果不符合条件会提前解锁访问表上的S锁(非修改表)，但是会出现对这个常量数据的重复解锁，对应的代码如下：
“`
for (lock = first_lock; lock != NULL;
lock = lock_rec_get_next(heap_no, lock)) {
if (lock->trx == trx && lock_get_mode(lock) == lock_mode) { //第一次会正常解锁，但是第二次
//就会因为lock_get_mode(lock) == lock_mode条件不能满足而直接进入报错流程。
goto released;
}
}
lock_mutex_exit();
trx_mutex_exit(trx);
stmt = innobase_get_stmt_unsafe(trx->mysql_thd, &stmt_len);
{
ib::errorerr;
err , rec=, lock_mode=LOCK_S) at /opt/mysql/mysql-5.7.22/storage/innobase/lock/lock0lock.cc:4291
#1 0x0000000000fd9967 in row_unlock_for_mysql (prebuilt=0x7fff54422518, has_latches_on_recs=has_latches_on_recs@entry=0) at /opt/mysql/mysql-5.7.22/storage/innobase/row/row0mysql.cc:2733
#2 0x0000000000ef7c09 in ha_innobase::unlock_row (this=0x7fff5400ef70) at /opt/mysql/mysql-5.7.22/storage/innobase/handler/ha_innodb.cc:8415
#3 0x0000000000c64697 in evaluate_join_record (join=join@entry=0x7fff54d35248, qep_tab=qep_tab@entry=0x7fff54d377e0) at /opt/mysql/mysql-5.7.22/sql/sql_executor.cc:1705
#4 0x0000000000c699cc in sub_select (join=0x7fff54d35248, qep_tab=0x7fff54d377e0, end_of_records=) at /opt/mysql/mysql-5.7.22/sql/sql_executor.cc:1297
“`
###三、如何进行的修复
对于const方法的访问直接不进行解锁操作了，定义一个空的方法不做任何操作，如下：
“`
+void
+join_const_unlock_row(QEP_TAB *tab)
+{
+ DBUG_ASSERT(tab->type() == JT_CONST);
+}
case JT_CONST:
read_first_record= join_read_const;
read_record.read_record= join_no_more_records;
+ read_record.unlock_row= join_const_unlock_row;
“`
验证如下：
– 修复前：
“`
4 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 6
MySQL thread id 3, OS thread handle 140735468259072, query id 37 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t1` trx id 1829 lock mode IX
RECORD LOCKS space id 28 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 1829 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000002; asc ;;
2: len 6; hex 000000000723; asc #;;
3: len 7; hex 38000000270110; asc 8 ‘ ;;
4: len 4; hex 80000008; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 4; hex 80000003; asc ;;
2: len 6; hex 000000000725; asc %;;
3: len 7; hex 39000000270110; asc 9 ‘ ;;
4: len 4; hex 80000000; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000004; asc ;;
2: len 6; hex 000000000725; asc %;;
3: len 7; hex 39000000270136; asc 9 ‘ 6;;
4: len 4; hex 80000007; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000006; asc ;;
2: len 6; hex 000000000725; asc %;;
3: len 7; hex 3900000027015c; asc 9 ‘ ;;
4: len 4; hex 80000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 4; hex 80000008; asc ;;
2: len 6; hex 000000000725; asc %;;
3: len 7; hex 39000000270182; asc 9 ‘ ;;
4: len 4; hex 80000007; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 4; hex 80000009; asc ;;
2: len 6; hex 000000000725; asc %;;
3: len 7; hex 390000002701a8; asc 9 ‘ ;;
4: len 4; hex 80000004; asc ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 8000000b; asc ;;
2: len 6; hex 000000000725; asc %;;
3: len 7; hex 390000002701ce; asc 9 ‘ ;;
4: len 4; hex 80000000; asc ;;
“`
– 修复后：
“`
—TRANSACTION 75555, ACTIVE 5 sec
4 lock struct(s), heap size 1160, 8 row lock(s), undo log entries 6
MySQL thread id 5, OS thread handle 140737097840384, query id 44 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`t1` trx id 75555 lock mode IX
RECORD LOCKS space id 99 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 75555 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000002; asc ;;
2: len 6; hex 000000012723; asc ‘#;;
3: len 7; hex 3a0000002c1061; asc : , a;;
4: len 4; hex 80000007; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 4; hex 80000003; asc ;;
2: len 6; hex 000000012723; asc ‘#;;
3: len 7; hex 3a0000002c1087; asc : , ;;
4: len 4; hex 80000003; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000004; asc ;;
2: len 6; hex 000000012723; asc ‘#;;
3: len 7; hex 3a0000002c10ad; asc : , ;;
4: len 4; hex 80000003; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000006; asc ;;
2: len 6; hex 000000012723; asc ‘#;;
3: len 7; hex 3a0000002c10d3; asc : , ;;
4: len 4; hex 80000006; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 4; hex 80000008; asc ;;
2: len 6; hex 000000012723; asc ‘#;;
3: len 7; hex 3a0000002c10f9; asc : , ;;
4: len 4; hex 80000001; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000008; asc ;;
1: len 4; hex 80000009; asc ;;
2: len 6; hex 000000012721; asc ‘!;;
3: len 7; hex 390000002802f3; asc 9 ( ;;
4: len 4; hex 80000008; asc ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 8000000b; asc ;;
2: len 6; hex 000000012723; asc ‘#;;
3: len 7; hex 3a0000002c111f; asc : , ;;
4: len 4; hex 80000004; asc ;;
(这里就是区别，没有解锁，LOCK_S 依旧存在)
TABLE LOCK table `test`.`t2` trx id 75555 lock mode IS
RECORD LOCKS space id 100 page no 3 n bits 80 index PRIMARY of table `test`.`t2` trx id 75555 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000012530; asc %0;;
2: len 7; hex a40000001a0128; asc (;;
3: len 4; hex 80000004; asc ;;
4: len 4; hex 80000005; asc ;;
“`