###一、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 <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::error	err;
		err << "Unlock row could not find a " << lock_mode
			<< " mode lock on the record. Current statement: ";
		err.write(stmt, stmt_len);
	}

return;
```

部分棧如下：
```
(gdb) bt
#0  lock_rec_unlock (trx=trx@entry=0x7fffcd6dd750, block=<optimized out="">, rec=<optimized out="">, 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=<optimized out="">) 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     ;;
```</optimized></optimized></optimized></kailasnath.nagarkar>

MySQL:關於Bug #20939184

相關文章