MDEV-27992 DELETE fails to delete record after blocking is released

MDEV-27025 allows to insert records before the record on which DELETE is
locked, as a result the DELETE misses those records, what causes serious ACID
violation.

Revert MDEV-27025, MDEV-27550. The test which shows the scenario of ACID
violation is added.

7 files changed, 56 insertions, 91 deletions

diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def
index a6a31348f8b..84babda2fa0 100644
--- a/mysql-test/suite/galera/disabled.def
+++ b/mysql-test/suite/galera/disabled.def
@@ -12,7 +12,6 @@
 
 MW-328A : MDEV-22666 galera.MW-328A MTR failed: "Semaphore wait has lasted > 600 seconds" and do not release port 16002
 MW-328B : MDEV-22666 galera.MW-328A MTR failed: "Semaphore wait has lasted > 600 seconds" and do not release port 16002
-MW-328D : MDEV-27550 ER_LOCK_DEADLOCK is gone after MDEV-27025
 MW-329 : MDEV-19962 Galera test failure on MW-329
 galera_as_slave_replication_bundle : MDEV-15785 OPTION_GTID_BEGIN is set in Gtid_log_event::do_apply_event()
 galera_concurrent_ctas : MDEV-24842 Galera test failure on galera_concurrent_ctas
diff --git a/mysql-test/suite/innodb/r/lock_delete_updated.result b/mysql-test/suite/innodb/r/lock_delete_updated.result
new file mode 100644
index 00000000000..c2cd47b5dd9
--- /dev/null
+++ b/mysql-test/suite/innodb/r/lock_delete_updated.result
@@ -0,0 +1,20 @@
+CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t VALUES (3);
+BEGIN;
+connection default;
+UPDATE t SET a = 2;
+connect con1,localhost,root;
+DELETE FROM t;
+connection default;
+UPDATE t SET a = 1;
+COMMIT;
+connection con1;
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
+disconnect con1;
+connection default;
+# The above DELETE must delete all the rows in the table, so the
+# following SELECT must show 0 rows.
+SELECT count(*) FROM t;
+count(*)
+1
+DROP TABLE t;
diff --git a/mysql-test/suite/innodb/r/lock_wait_conflict.result b/mysql-test/suite/innodb/r/lock_wait_conflict.result
deleted file mode 100644
index 25d18c03ea1..00000000000
--- a/mysql-test/suite/innodb/r/lock_wait_conflict.result
+++ /dev/null
@@ -1,27 +0,0 @@
-#
-# MDEV-27025 insert-intention lock conflicts with waiting ORDINARY lock
-#
-CREATE TABLE t (a INT PRIMARY KEY, b INT NOT NULL UNIQUE) ENGINE=InnoDB;
-connect prevent_purge,localhost,root,,;
-start transaction with consistent snapshot;
-connection default;
-INSERT INTO t VALUES (20,20);
-DELETE FROM t WHERE b = 20;
-connect con_ins,localhost,root,,;
-SET DEBUG_SYNC = 'row_ins_sec_index_entry_dup_locks_created SIGNAL ins_set_locks WAIT_FOR ins_cont';
-INSERT INTO t VALUES(10, 20);
-connect con_del,localhost,root,,;
-SET DEBUG_SYNC = 'now WAIT_FOR ins_set_locks';
-SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL del_locked';
-DELETE FROM t WHERE b = 20;
-connection default;
-SET DEBUG_SYNC = 'now WAIT_FOR del_locked';
-SET DEBUG_SYNC = 'now SIGNAL ins_cont';
-connection con_ins;
-disconnect con_ins;
-connection con_del;
-disconnect con_del;
-disconnect prevent_purge;
-connection default;
-SET DEBUG_SYNC = 'RESET';
-DROP TABLE t;
diff --git a/mysql-test/suite/innodb/t/lock_delete_updated.test b/mysql-test/suite/innodb/t/lock_delete_updated.test
new file mode 100644
index 00000000000..4621d5fcd2b
--- /dev/null
+++ b/mysql-test/suite/innodb/t/lock_delete_updated.test
@@ -0,0 +1,34 @@
+--source include/have_innodb.inc
+--source include/count_sessions.inc
+
+CREATE TABLE t(a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t VALUES (3);
+
+BEGIN;
+
+connection default;
+UPDATE t SET a = 2;
+
+connect con1,localhost,root;
+send DELETE FROM t;
+
+connection default;
+let $wait_condition=
+    select count(*) = 1 from information_schema.processlist
+    where state = "Updating" and info = "DELETE FROM t";
+--source include/wait_condition.inc
+
+UPDATE t SET a = 1;
+COMMIT;
+
+connection con1;
+error ER_LOCK_DEADLOCK;
+reap;
+disconnect con1;
+
+connection default;
+--echo # The above DELETE must delete all the rows in the table, so the
+--echo # following SELECT must show 0 rows.
+SELECT count(*) FROM t;
+DROP TABLE t;
+--source include/wait_until_count_sessions.inc
diff --git a/mysql-test/suite/innodb/t/lock_wait_conflict.test b/mysql-test/suite/innodb/t/lock_wait_conflict.test
deleted file mode 100644
index 46a29e14b43..00000000000
--- a/mysql-test/suite/innodb/t/lock_wait_conflict.test
+++ /dev/null
@@ -1,60 +0,0 @@
---source include/have_innodb.inc
---source include/count_sessions.inc
---source include/have_debug.inc
---source include/have_debug_sync.inc
-
---echo #
---echo # MDEV-27025 insert-intention lock conflicts with waiting ORDINARY lock
---echo #
-
-# The test checks the ability to acquire exclusive record lock if the acquiring
-# transaction already holds a shared lock on the record and another transaction
-# is waiting for a lock.
-
-CREATE TABLE t (a INT PRIMARY KEY, b INT NOT NULL UNIQUE) ENGINE=InnoDB;
-
---connect(prevent_purge,localhost,root,,)
-start transaction with consistent snapshot;
-
---connection default
-INSERT INTO t VALUES (20,20);
-DELETE FROM t WHERE b = 20;
-
---connect(con_ins,localhost,root,,)
-SET DEBUG_SYNC = 'row_ins_sec_index_entry_dup_locks_created SIGNAL ins_set_locks WAIT_FOR ins_cont';
-send
-INSERT INTO t VALUES(10, 20);
-
---connect(con_del,localhost,root,,)
-SET DEBUG_SYNC = 'now WAIT_FOR ins_set_locks';
-SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL del_locked';
-###############################################################################
-# This DELETE creates waiting ORDINARY X-lock for heap_no 2 as the record is
-# delete-marked, this lock conflicts with ORDINARY S-lock set by the the last
-# INSERT. After the last INSERT creates insert-intention lock on
-# heap_no 2, this lock will conflict with waiting ORDINARY X-lock of this
-# DELETE, what causes DEADLOCK error for this DELETE.
-###############################################################################
-send
-DELETE FROM t WHERE b = 20;
-
---connection default
-SET DEBUG_SYNC = 'now WAIT_FOR del_locked';
-SET DEBUG_SYNC = 'now SIGNAL ins_cont';
-
---connection con_ins
---reap
---disconnect con_ins
-
---connection con_del
-# Without the fix, ER_LOCK_DEADLOCK would be reported here.
---reap
---disconnect con_del
-
---disconnect prevent_purge
-
---connection default
-
-SET DEBUG_SYNC = 'RESET';
-DROP TABLE t;
---source include/wait_until_count_sessions.inc
diff --git a/mysql-test/suite/versioning/r/update.result b/mysql-test/suite/versioning/r/update.result
index db47b0357a0..d123331cc8c 100644
--- a/mysql-test/suite/versioning/r/update.result
+++ b/mysql-test/suite/versioning/r/update.result
@@ -283,6 +283,7 @@ connection default;
 update t1 set b = 'foo';
 connection con1;
 update t1 set a = 'bar';
+ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 disconnect con1;
 connection default;
 drop table t1;
diff --git a/mysql-test/suite/versioning/t/update.test b/mysql-test/suite/versioning/t/update.test
index f496a287697..058d2f4c865 100644
--- a/mysql-test/suite/versioning/t/update.test
+++ b/mysql-test/suite/versioning/t/update.test
@@ -186,9 +186,7 @@ send update t1 set b = 'foo';
 connection con1;
 let $wait_condition= select count(*) from information_schema.innodb_lock_waits;
 source include/wait_condition.inc;
-# There must no be DEADLOCK here as con1 transaction already holds locks, and
-# default's transaction lock is waiting, so the locks of the following "UPDATE"
-# must not conflict with waiting lock.
+error ER_LOCK_DEADLOCK;
 update t1 set a = 'bar';
 disconnect con1;
 connection default;