MDEV-20605 With optimistic and aggressive modes of parallel replication, some replicated statements have no effectbb-10.6-MDEV-20605-cur-pos-fix

Switch off the fix for non-locking read.

Squash the commit after testing.

3 files changed, 91 insertions, 23 deletions

diff --git a/mysql-test/suite/innodb/r/cursor-restore-non-locking-read.result b/mysql-test/suite/innodb/r/cursor-restore-non-locking-read.result
new file mode 100644
index 00000000000..d8644847166
--- /dev/null
+++ b/mysql-test/suite/innodb/r/cursor-restore-non-locking-read.result
@@ -0,0 +1,26 @@
+SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
+CREATE TABLE t1 (pk int PRIMARY KEY, c int UNIQUE) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10,10),(20,20),(30,30);
+connect prevent_purge,localhost,root,,;
+start transaction with consistent snapshot;
+UPDATE t1 SET c=300 WHERE pk = 30;
+connection default;
+DELETE FROM t1 WHERE pk = 10;
+INSERT INTO t1 VALUES(5,10);
+SET DEBUG_SYNC = "row_search_clust_unlatched SIGNAL unlatched WAIT_FOR cont";
+SELECT pk FROM t1 FORCE INDEX (c);
+connect con1,localhost,root,,;
+SET DEBUG_SYNC = "now WAIT_FOR unlatched";
+disconnect prevent_purge;
+InnoDB		1 transactions not purged
+SET DEBUG_SYNC = 'now SIGNAL cont';
+disconnect con1;
+connection default;
+pk
+5
+20
+30
+SET DEBUG_SYNC = 'RESET';
+DROP TABLE t1;
+SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
diff --git a/mysql-test/suite/innodb/t/cursor-restore-non-locking-read.test b/mysql-test/suite/innodb/t/cursor-restore-non-locking-read.test
new file mode 100644
index 00000000000..c9ae258791c
--- /dev/null
+++ b/mysql-test/suite/innodb/t/cursor-restore-non-locking-read.test
@@ -0,0 +1,44 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/count_sessions.inc
+
+SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+SET GLOBAL innodb_purge_rseg_truncate_frequency=1;
+
+CREATE TABLE t1 (pk int PRIMARY KEY, c int UNIQUE) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (10,10),(20,20),(30,30);
+
+--connect(prevent_purge,localhost,root,,)
+start transaction with consistent snapshot;
+# We need this to update page's transaction id for secondary index.
+UPDATE t1 SET c=300 WHERE pk = 30;
+
+--connection default
+DELETE FROM t1 WHERE pk = 10;
+INSERT INTO t1 VALUES(5,10);
+SET DEBUG_SYNC = "row_search_clust_unlatched SIGNAL unlatched WAIT_FOR cont";
+# With the above sync point row_search_mvcc() will be blocked on delete-marked
+# record (10,10) in secondary index just after all page latches are released.
+# After this record is purged, row_searc_mvcc() will be unblocked, and cursor
+# will be restored to the secondary index record (10,5). As the unique field is
+# the same as in the cursor's stored record, and the bug is not fixed, there
+# value 5 will be doubled in the result set.
+--send SELECT pk FROM t1 FORCE INDEX (c)
+
+--connect(con1,localhost,root,,)
+SET DEBUG_SYNC = "now WAIT_FOR unlatched";
+--disconnect prevent_purge
+let $wait_all_purged= 1;
+--source include/wait_all_purged.inc
+SET DEBUG_SYNC = 'now SIGNAL cont';
+--disconnect con1
+
+--connection default
+--reap
+
+SET DEBUG_SYNC = 'RESET';
+DROP TABLE t1;
+SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
+--source include/wait_until_count_sessions.inc
diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
index b57b41b1a62..43e7afa25f2 100644
--- a/storage/innobase/row/row0sel.cc
+++ b/storage/innobase/row/row0sel.cc
@@ -3588,28 +3588,22 @@ err_exit:
 	return(err);
 }
 
-/********************************************************************//**
-Restores cursor position after it has been stored. We have to take into
+/** Restores cursor position after it has been stored. We have to take into
 account that the record cursor was positioned on may have been deleted.
 Then we may have to move the cursor one step up or down.
+@param[out] same_user_rec TRUE if we were able to restore the cursor on a user
+record with the same ordering prefix in in the B-tree index
+@param[in] latch_mode latch mode wished in restoration
+@param[in] pcur cursor whose position has been stored
+@param[in] moves_up TRUE if the cursor moves up in the index
+@param[in] mtr mtr; CAUTION: may commit mtr temporarily!
+@param[in] locking_read true if the function is called for locking read
 @return true if we may need to process the record the cursor is now
 positioned on (i.e. we should not go to the next record yet) */
-static
-bool
-sel_restore_position_for_mysql(
-/*===========================*/
-	ibool*		same_user_rec,	/*!< out: TRUE if we were able to restore
-					the cursor on a user record with the
-					same ordering prefix in in the
-					B-tree index */
-	ulint		latch_mode,	/*!< in: latch mode wished in
-					restoration */
-	btr_pcur_t*	pcur,		/*!< in: cursor whose position
-					has been stored */
-	ibool		moves_up,	/*!< in: TRUE if the cursor moves up
-					in the index */
-	mtr_t*		mtr)		/*!< in: mtr; CAUTION: may commit
-					mtr temporarily! */
+static bool sel_restore_position_for_mysql(ibool *same_user_rec,
+                                           ulint latch_mode, btr_pcur_t *pcur,
+                                           ibool moves_up, mtr_t *mtr,
+                                           bool locking_read)
 {
 	auto status = pcur->restore_position(latch_mode, mtr);
 
@@ -3632,7 +3626,7 @@ sel_restore_position_for_mysql(
 	switch (pcur->rel_pos) {
 	case BTR_PCUR_ON:
 		if (!*same_user_rec && moves_up) {
-			if (status == btr_pcur_t::SAME_UNIQ)
+			if (status == btr_pcur_t::SAME_UNIQ && locking_read)
 			  return true;
 next:
 			if (btr_pcur_move_to_next(pcur, mtr)
@@ -4697,7 +4691,8 @@ wait_table_again:
 
 		bool	need_to_process = sel_restore_position_for_mysql(
 			&same_user_rec, BTR_SEARCH_LEAF,
-			pcur, moves_up, &mtr);
+			pcur, moves_up, &mtr,
+			prebuilt->select_lock_type != LOCK_NONE);
 
 		if (UNIV_UNLIKELY(need_to_process)) {
 			if (UNIV_UNLIKELY(prebuilt->row_read_type
@@ -5754,12 +5749,14 @@ next_rec:
 		btr_pcur_store_position(pcur, &mtr);
 		mtr.commit();
 		mtr_has_extra_clust_latch = FALSE;
-
+		DEBUG_SYNC_C("row_search_clust_unlatched");
 		mtr.start();
 
 		if (sel_restore_position_for_mysql(&same_user_rec,
 						   BTR_SEARCH_LEAF,
-						   pcur, moves_up, &mtr)) {
+						   pcur, moves_up, &mtr,
+						   prebuilt->select_lock_type
+						      != LOCK_NONE)) {
 			goto rec_loop;
 		}
 	}
@@ -5843,7 +5840,8 @@ lock_table_wait:
 		if (!dict_index_is_spatial(index)) {
 			sel_restore_position_for_mysql(
 				&same_user_rec, BTR_SEARCH_LEAF, pcur,
-				moves_up, &mtr);
+				moves_up, &mtr,
+				prebuilt->select_lock_type != LOCK_NONE);
 		}
 
 		if (trx->isolation_level <= TRX_ISO_READ_COMMITTED