revision-id: 35294053b281294bb49a6217da07b93eb6c2595b (mariadb-10.5.11-33-g35294053b28) parent(s): c262ccac027e71f22fc1329cf295a6bf687e4684 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-07-07 18:54:00 +0300 message: MDEV-26106: [ERROR] InnoDB: Unlock row could not find a 2 mode lock on the record Port the following patch from MySQL: commit 1b2e8ea269c80cb93cc79d8be934c40b1c58e947 Author: Kailasnath Nagarkar <kailasnath.nagarkar@oracle.com> 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. --- mysql-test/main/join_outer_innodb.result | 19 +++++++++++++++++++ mysql-test/main/join_outer_innodb.test | 17 +++++++++++++++++ sql/sql_select.cc | 31 +++++++++++++++++++++++++++---- 3 files changed, 63 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index 0b34a399d77..09a37a29702 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -496,3 +496,22 @@ natural right outer join t3; drop table t1,t2,t3; set optimizer_prune_level=@mdev4270_opl; set optimizer_search_depth=@mdev4270_osd; +# +# Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE +# RECORD +# +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); +INSERT INTO t2 SELECT * FROM t1; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +START TRANSACTION; +SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE; +c1 c2 c3 c1 c2 c3 +1 2 3 1 2 3 +2 3 4 NULL NULL NULL +3 4 5 NULL NULL NULL +UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10; +COMMIT; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +DROP TABLE t1,t2; diff --git a/mysql-test/main/join_outer_innodb.test b/mysql-test/main/join_outer_innodb.test index c26cd62fbc7..6b332f3d155 100644 --- a/mysql-test/main/join_outer_innodb.test +++ b/mysql-test/main/join_outer_innodb.test @@ -374,3 +374,20 @@ drop table t1,t2,t3; set optimizer_prune_level=@mdev4270_opl; set optimizer_search_depth=@mdev4270_osd; +--echo # +--echo # Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE +--echo # RECORD +--echo # +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); +INSERT INTO t2 SELECT * FROM t1; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +START TRANSACTION; +#unlocks rows in table t2 where c1 = 1 +SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE; +UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10; +COMMIT; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +DROP TABLE t1,t2; + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 8800fbf5b6d..ab54bd3b94d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -189,6 +189,7 @@ static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); static void join_read_key_unlock_row(st_join_table *tab); +static void join_const_unlock_row(JOIN_TAB *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); static int join_no_more_records(READ_RECORD *info); @@ -10968,8 +10969,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, else j->type=JT_EQ_REF; - j->read_record.unlock_row= (j->type == JT_EQ_REF)? - join_read_key_unlock_row : rr_unlock_row; + if (j->type == JT_EQ_REF) + j->read_record.unlock_row= join_read_key_unlock_row; + else if (j->type == JT_CONST) + j->read_record.unlock_row= join_const_unlock_row; + else + j->read_record.unlock_row= rr_unlock_row; DBUG_RETURN(0); } @@ -13232,6 +13237,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) /* Only happens with outer joins */ tab->read_first_record= tab->type == JT_SYSTEM ? join_read_system : join_read_const; + tab->read_record.unlock_row= join_const_unlock_row; if (!(table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) && (!jcl || jcl > 4) && !tab->ref.is_access_triggered()) push_index_cond(tab, tab->ref.key); @@ -21532,6 +21538,19 @@ join_read_key_unlock_row(st_join_table *tab) tab->ref.use_count--; } +/** + Rows from const tables are read once but potentially used + multiple times during execution of a query. + Ensure such rows are never unlocked during query execution. +*/ + +void +join_const_unlock_row(JOIN_TAB *tab) +{ + DBUG_ASSERT(tab->type == JT_CONST); +} + + /* ref access method implementation: "read_first" function @@ -23938,8 +23957,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, else if (select && select->quick) select->quick->need_sorted_output(); - tab->read_record.unlock_row= (tab->type == JT_EQ_REF) ? - join_read_key_unlock_row : rr_unlock_row; + if (tab->type == JT_EQ_REF) + tab->read_record.unlock_row= join_read_key_unlock_row; + else if (tab->type == JT_CONST) + tab->read_record.unlock_row= join_const_unlock_row; + else + tab->read_record.unlock_row= rr_unlock_row; } // QEP has been modified