MDEV-10962 Deadlock with 3 concurrent DELETEs by unique keybb-10.6-midenok-MDEV-10962

Deadlock on concurrent acquisition of multiple types is fixed by
prioritization over waiting locks if transaction already has
non-waiting lock on same (block, heap_no).

Deadlock happens by this scheme:

1. TXA acquires L1 (index "a", X|NOT_GAP);
2. TXB waits L2 (index "a", X|NOT_GAP) for L1;
3. TXA waits L3 (index "a", X) for L2.

TXB waits TXA, TXA waits TXB: DeadlockChecker wants a victim.

Since L3 is weaker than L1 we can not apply optimization from
MDEV-18706. But the problem of this loop is silly queuing. TXA already
acquired non-gap lock L1 and wants to expand it into next-key. TXB from
parallel thread does the same. Deadlock detection processes the
requests in FIFO order so the above order fails. But if L3 is acquired
before L2 there is no deadlock.

We can make queue smarter: if there are already lock seized on same
resource (block, heap_no), no matter what type it is, we priritize all
further locks on that transaction. There is no treat to fair
scheduling as this is basically lock promotion in 2PL scheme.

3 files changed, 67 insertions, 6 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-lock.result b/mysql-test/suite/innodb/r/innodb-lock.result
index 8d069991f90..75fb41f1cb4 100644
--- a/mysql-test/suite/innodb/r/innodb-lock.result
+++ b/mysql-test/suite/innodb/r/innodb-lock.result
@@ -334,3 +334,25 @@ ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 connection default;
 disconnect con1;
 drop table t1;
+#
+# MDEV-10962 Deadlock with 3 concurrent DELETEs by unique key
+#
+create table t1 (a int unique) engine innodb;
+insert into t1 values (1);
+connect  con1, localhost, root,, test;
+connect  con2, localhost, root,, test;
+connect  con3, localhost, root,, test;
+connection con1;
+delete from t1 where a = 1;
+connection con2;
+delete from t1 where a = 1;
+connection con3;
+delete from t1 where a = 1;
+connection con1;
+connection con2;
+connection con3;
+connection default;
+disconnect con1;
+disconnect con2;
+disconnect con3;
+drop table t1;
diff --git a/mysql-test/suite/innodb/t/innodb-lock.test b/mysql-test/suite/innodb/t/innodb-lock.test
index f73bf0d5a99..d17d73991aa 100644
--- a/mysql-test/suite/innodb/t/innodb-lock.test
+++ b/mysql-test/suite/innodb/t/innodb-lock.test
@@ -401,4 +401,28 @@ drop table t1;
 
 # TODO (MDEV-10962): H) Insert into an already passed locked gap (15)
 
-
+--echo #
+--echo # MDEV-10962 Deadlock with 3 concurrent DELETEs by unique key
+--echo #
+create table t1 (a int unique) engine innodb;
+insert into t1 values (1);
+connect (con1, localhost, root,, test);
+connect (con2, localhost, root,, test);
+connect (con3, localhost, root,, test);
+connection con1;
+send delete from t1 where a = 1;
+connection con2;
+send delete from t1 where a = 1;
+connection con3;
+send delete from t1 where a = 1;
+connection con1;
+reap;
+connection con2;
+reap;
+connection con3;
+reap;
+connection default;
+disconnect con1;
+disconnect con2;
+disconnect con3;
+drop table t1;
diff --git a/storage/innobase/lock/lock0lock.cc b/storage/innobase/lock/lock0lock.cc
index 771fc406fbd..68bfea0456b 100644
--- a/storage/innobase/lock/lock0lock.cc
+++ b/storage/innobase/lock/lock0lock.cc
@@ -985,25 +985,40 @@ lock_rec_other_has_conflicting(
 	ulint			heap_no,/*!< in: heap number of the record */
 	const trx_t*		trx)	/*!< in: our transaction */
 {
-	lock_t*		res = NULL;
+	lock_t*		conflict = NULL;
+	bool		skip_waiting = false;
 
 	bool	is_supremum = (heap_no == PAGE_HEAP_NO_SUPREMUM);
 
 	for (lock_t* lock = lock_sys_t::get_first(cell, id, heap_no);
 	     lock; lock = lock_rec_get_next(heap_no, lock)) {
+
+		if (skip_waiting && lock->is_waiting()) {
+			continue;
+		}
+
 		/* If current trx already acquired a lock not weaker covering
 		same types then we don't have to wait for any locks. */
 		if (lock->is_stronger(mode, heap_no, trx)) {
 
 			return(NULL);
-		}
+		} else if (lock->trx == trx && !lock->is_waiting()) {
+			if (conflict && conflict->is_waiting()) {
+				conflict = NULL;
+			}
+			skip_waiting = true;
+		} else if (lock_rec_has_to_wait(true, trx, mode, lock,
+						is_supremum)) {
 
-		if (!res && lock_rec_has_to_wait(true, trx, mode, lock, is_supremum)) {
-			res = lock;
+			if (!conflict || (conflict->is_waiting()
+					  && !lock->is_waiting())) {
+
+				conflict = lock;
+			}
 		}
 	}
 
-	return(res);
+	return(conflict);
 }
 
 /*********************************************************************//**