MDEV-26206 gap lock is not set if implicit lock exists

If lock type is LOCK_GAP or LOCK_ORDINARY, and the transaction holds
implicit lock for the record, then explicit gap-lock will not be set for
the record, as lock_rec_convert_impl_to_expl() returns true and
lock_rec_convert_impl_to_expl() bypasses lock_rec_lock() call.

The fix converts explicit lock to implicit one if requested lock type is
not LOCK_REC_NOT_GAP.

The fix also influence MDEV-14479. Before the fix
lock_rec_convert_impl_to_expl() did not create explicit lock if caller's
transaction owns found implicit lock. After the fix
lock_rec_convert_impl_to_expl() can create explicit lock under the above
conditions if the requested lock mode is not LOCK_REC_NOT_GAP. And that
is why we need to check if the table is X-locked in
lock_rec_convert_impl_to_expl().

innodb_information_schema test result is also changed as after the fix
the following statements execution:

SET autocommit=0;
INSERT INTO t1 VALUES (5,10);
SELECT * FROM t1 FOR UPDATE;

leads to additional gap locks requests.

4 files changed, 51 insertions, 8 deletions

diff --git a/mysql-test/suite/innodb/r/implicit_gap_lock_convertion.result b/mysql-test/suite/innodb/r/implicit_gap_lock_convertion.result
new file mode 100644
index 00000000000..fd197324c3e
--- /dev/null
+++ b/mysql-test/suite/innodb/r/implicit_gap_lock_convertion.result
@@ -0,0 +1,17 @@
+CREATE TABLE t(a INT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t VALUES (10), (30);
+connect  con1,localhost,root,,;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN;
+INSERT INTO t VALUES (20);
+SELECT * FROM t WHERE a BETWEEN 10 AND 30;
+a
+10
+20
+30
+connection default;
+SET session innodb_lock_wait_timeout=1;
+INSERT INTO t VALUES (15);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+disconnect con1;
+DROP TABLE t;
diff --git a/mysql-test/suite/innodb/r/innodb_information_schema.result b/mysql-test/suite/innodb/r/innodb_information_schema.result
index 70458758437..6325917c236 100644
--- a/mysql-test/suite/innodb/r/innodb_information_schema.result
+++ b/mysql-test/suite/innodb/r/innodb_information_schema.result
@@ -45,7 +45,7 @@ trx_last_foreign_key_error	varchar(256)	YES		NULL
 trx_is_read_only	int(1)	NO		0	
 trx_autocommit_non_locking	int(1)	NO		0	
 trx_state	trx_weight	trx_tables_in_use	trx_tables_locked	trx_rows_locked	trx_rows_modified	trx_concurrency_tickets	trx_isolation_level	trx_unique_checks	trx_foreign_key_checks
-RUNNING	3	0	1	5	1	0	REPEATABLE READ	1	1
+RUNNING	3	0	1	6	1	0	REPEATABLE READ	1	1
 trx_isolation_level	trx_unique_checks	trx_foreign_key_checks
 SERIALIZABLE	0	0
 trx_state	trx_isolation_level	trx_last_foreign_key_error
diff --git a/mysql-test/suite/innodb/t/implicit_gap_lock_convertion.test b/mysql-test/suite/innodb/t/implicit_gap_lock_convertion.test
new file mode 100644
index 00000000000..bf2d09ffb2e
--- /dev/null
+++ b/mysql-test/suite/innodb/t/implicit_gap_lock_convertion.test
@@ -0,0 +1,21 @@
+--source include/have_innodb.inc
+--source include/count_sessions.inc
+
+CREATE TABLE t(a INT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;
+
+INSERT INTO t VALUES (10), (30);
+
+--connect (con1,localhost,root,,)
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN;
+INSERT INTO t VALUES (20);
+SELECT * FROM t WHERE a BETWEEN 10 AND 30;
+
+--connection default
+SET session innodb_lock_wait_timeout=1;
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t VALUES (15);
+
+--disconnect con1
+DROP TABLE t;
+--source include/wait_until_count_sessions.inc
diff --git a/storage/innobase/lock/lock0lock.cc b/storage/innobase/lock/lock0lock.cc
index bcdf430e476..9d527e95795 100644
--- a/storage/innobase/lock/lock0lock.cc
+++ b/storage/innobase/lock/lock0lock.cc
@@ -5205,16 +5205,18 @@ lock_sec_rec_read_check_and_lock(
 	if the max trx id for the page >= min trx id for the trx list or a
 	database recovery is running. */
 
-	if (!page_rec_is_supremum(rec)
+	trx_t *trx = thr_get_trx(thr);
+	if (!lock_table_has(trx, index->table, LOCK_X)
+	    && !page_rec_is_supremum(rec)
 	    && page_get_max_trx_id(block->frame) >= trx_sys.get_min_trx_id()
-	    && lock_rec_convert_impl_to_expl(thr_get_trx(thr), id, rec,
-					     index, offsets)) {
+	    && lock_rec_convert_impl_to_expl(trx, id, rec,
+					     index, offsets)
+	    && gap_mode == LOCK_REC_NOT_GAP) {
 		/* We already hold an implicit exclusive lock. */
 		return DB_SUCCESS;
 	}
 
 #ifdef WITH_WSREP
-	trx_t *trx= thr_get_trx(thr);
 	/* If transaction scanning an unique secondary key is wsrep
 	high priority thread (brute force) this scanning may involve
 	GAP-locking in the index. As this locking happens also when
@@ -5290,9 +5292,12 @@ lock_clust_rec_read_check_and_lock(
 
 	heap_no = page_rec_get_heap_no(rec);
 
-	if (heap_no != PAGE_HEAP_NO_SUPREMUM
-	    && lock_rec_convert_impl_to_expl(thr_get_trx(thr), id, rec,
-					     index, offsets)) {
+	trx_t *trx = thr_get_trx(thr);
+	if (!lock_table_has(trx, index->table, LOCK_X)
+	    && heap_no != PAGE_HEAP_NO_SUPREMUM
+	    && lock_rec_convert_impl_to_expl(trx, id, rec,
+					     index, offsets)
+	    && gap_mode == LOCK_REC_NOT_GAP ) {
 		/* We already hold an implicit exclusive lock. */
 		return DB_SUCCESS;
 	}