MDEV-14479: Do not acquire InnoDB record locks when covering table locksbb-10.5-MDEV-14479-table-locks

exist

lock_rec_lock() does not set record lock if table lock is stronger or
equal to the acquired record lock.

3 files changed, 214 insertions, 5 deletions

diff --git a/mysql-test/suite/innodb/r/monitor.result b/mysql-test/suite/innodb/r/monitor.result
index 16aa8630012..4cfe85090b6 100644
--- a/mysql-test/suite/innodb/r/monitor.result
+++ b/mysql-test/suite/innodb/r/monitor.result
@@ -685,6 +685,28 @@ LIKE 'buffer_page_written_index_leaf';
 NAME	COUNT > 0
 buffer_page_written_index_leaf	1
 DROP TABLE t1;
+CREATE TABLE fl0 (
+id INT NOT NULL PRIMARY KEY
+) ENGINE = InnoDB;
+CREATE TABLE fl1 (
+id INT NOT NULL PRIMARY KEY,
+fl0_id INT,
+CONSTRAINT `fkl0`
+    FOREIGN KEY (fl0_id) REFERENCES fl0 (id)
+ON DELETE CASCADE
+ON UPDATE RESTRICT
+) ENGINE = InnoDB;
+CREATE TABLE fl2 (
+id INT NOT NULL PRIMARY KEY,
+fl1_id INT,
+CONSTRAINT `fkl1`
+    FOREIGN KEY (fl1_id) REFERENCES fl1 (id)
+ON DELETE CASCADE
+ON UPDATE SET NULL
+) ENGINE = InnoDB;
+INSERT INTO fl0 VALUES (1000);
+INSERT INTO fl1 VALUES (500, 1000), (1500, 1000);
+INSERT INTO fl2 VALUES (200, 500), (800, 500), (1200, 1500), (1800, 1500);
 CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b))
 ENGINE=InnoDB;
 SET @start = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
@@ -698,7 +720,96 @@ SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
 SELECT @end - @start;
 @end - @start
 0
+# Records must not be S/X-locked if a table is X-locked.
+SET @start = @end;
+SET autocommit = 0;
+BEGIN;
+LOCK TABLE t1 WRITE;
+SELECT * FROM t1;
+id	a	b
+1	1	a
+4	4	d
+3	10000	c
+SELECT * FROM t1 WHERE a>=10000;
+id	a	b
+3	10000	c
+SELECT * FROM t1 FOR UPDATE;
+id	a	b
+1	1	a
+4	4	d
+3	10000	c
+SELECT * FROM t1 WHERE a>=10000 FOR UPDATE;
+id	a	b
+3	10000	c
+UPDATE t1 SET b = 'b' WHERE id = 4;
+UPDATE t1 SET b = 'b' WHERE a = 10000;
+REPLACE INTO t1 VALUES (4,3,'a');
+INSERT INTO t1 VALUES (3,1,'e') ON DUPLICATE KEY UPDATE b = 'b';
+INSERT INTO t1 VALUES (5,5,'e');
+DELETE FROM t1 WHERE a = 1 AND b='a';
+DELETE FROM t1;
+COMMIT;
+UNLOCK TABLES;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
+SELECT @end - @start;
+@end - @start
+0
+# Records must not be S-locked if a table is S-locked.
+SET @start = @end;
+BEGIN;
+LOCK TABLE t1 WRITE;
+INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
+DELETE FROM t1 WHERE a = 9999 AND b='b';
+COMMIT;
+UNLOCK TABLES;
+BEGIN;
+LOCK TABLE t1 READ;
+SELECT * FROM t1 LOCK IN SHARE MODE;
+id	a	b
+1	1	a
+4	4	d
+3	10000	c
+SELECT * FROM t1 WHERE a>=10000 LOCK IN SHARE MODE;
+id	a	b
+3	10000	c
+COMMIT;
+UNLOCK TABLES;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
+SELECT @end - @start;
+@end - @start
+0
+# Records must not be S-locked for foreign keys enforcement
+SET @start = @end;
+BEGIN;
+LOCK TABLE fl0 READ, fl1 READ, fl2 WRITE;
+INSERT INTO fl2 VALUES (300, 500), (700, 500), (1300, 1500), (1700, 1500);
+SELECT * FROM fl1 LOCK IN SHARE MODE;
+id	fl0_id
+500	1000
+1500	1000
+COMMIT;
+UNLOCK TABLES;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
+SELECT @end - @start;
+@end - @start
+0
+# Records must not be X-locked for foreign keys cascade
+SET @start = @end;
+BEGIN;
+LOCK TABLE fl0 READ, fl1 WRITE, fl2 WRITE;
+DELETE FROM fl1 WHERE id = 1500;
+UPDATE fl1 SET id = 2500 WHERE id = 500;
+COMMIT;
+UNLOCK TABLES;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
+SELECT @end - @start;
+@end - @start
+0
+SET autocommit = default;
 DROP TABLE t1;
+DROP TABLE fl2;
+DROP TABLE fl1;
+DROP TABLE fl0;
 SET GLOBAL innodb_monitor_enable=default;
 SET GLOBAL innodb_monitor_disable=default;
 SET GLOBAL innodb_monitor_reset_all=default;
diff --git a/mysql-test/suite/innodb/t/monitor.test b/mysql-test/suite/innodb/t/monitor.test
index 10a0219767d..7fa0c64f81b 100644
--- a/mysql-test/suite/innodb/t/monitor.test
+++ b/mysql-test/suite/innodb/t/monitor.test
@@ -449,6 +449,32 @@ LIKE 'buffer_page_written_index_leaf';
 
 DROP TABLE t1;
 
+CREATE TABLE fl0 (
+  id INT NOT NULL PRIMARY KEY
+) ENGINE = InnoDB;
+
+CREATE TABLE fl1 (
+  id INT NOT NULL PRIMARY KEY,
+  fl0_id INT,
+  CONSTRAINT `fkl0`
+    FOREIGN KEY (fl0_id) REFERENCES fl0 (id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
+) ENGINE = InnoDB;
+
+CREATE TABLE fl2 (
+  id INT NOT NULL PRIMARY KEY,
+  fl1_id INT,
+  CONSTRAINT `fkl1`
+    FOREIGN KEY (fl1_id) REFERENCES fl1 (id)
+    ON DELETE CASCADE
+    ON UPDATE SET NULL
+) ENGINE = InnoDB;
+
+INSERT INTO fl0 VALUES (1000);
+INSERT INTO fl1 VALUES (500, 1000), (1500, 1000);
+INSERT INTO fl2 VALUES (200, 500), (800, 500), (1200, 1500), (1800, 1500);
+
 CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b))
 ENGINE=InnoDB;
 
@@ -464,7 +490,73 @@ SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
 = 'lock_rec_lock_created');
 SELECT @end - @start;
 
+--echo # Records must not be S/X-locked if a table is X-locked.
+SET @start = @end;
+# Tables will not be locked if autocommit is not 0.
+# See OPTION_NOT_AUTOCOMMIT in ha_innobase::external_lock().
+SET autocommit = 0;
+BEGIN;
+LOCK TABLE t1 WRITE;
+SELECT * FROM t1;
+SELECT * FROM t1 WHERE a>=10000;
+SELECT * FROM t1 FOR UPDATE;
+SELECT * FROM t1 WHERE a>=10000 FOR UPDATE;
+UPDATE t1 SET b = 'b' WHERE id = 4;
+UPDATE t1 SET b = 'b' WHERE a = 10000;
+REPLACE INTO t1 VALUES (4,3,'a');
+INSERT INTO t1 VALUES (3,1,'e') ON DUPLICATE KEY UPDATE b = 'b';
+INSERT INTO t1 VALUES (5,5,'e');
+DELETE FROM t1 WHERE a = 1 AND b='a';
+DELETE FROM t1;
+COMMIT;
+UNLOCK TABLES;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
+SELECT @end - @start;
+
+--echo # Records must not be S-locked if a table is S-locked.
+SET @start = @end;
+BEGIN;
+LOCK TABLE t1 WRITE;
+INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
+DELETE FROM t1 WHERE a = 9999 AND b='b';
+COMMIT;
+UNLOCK TABLES;
+BEGIN;
+LOCK TABLE t1 READ;
+SELECT * FROM t1 LOCK IN SHARE MODE;
+SELECT * FROM t1 WHERE a>=10000 LOCK IN SHARE MODE;
+COMMIT;
+UNLOCK TABLES;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
+SELECT @end - @start;
+
+--echo # Records must not be S-locked for foreign keys enforcement
+SET @start = @end;
+BEGIN;
+LOCK TABLE fl0 READ, fl1 READ, fl2 WRITE;
+INSERT INTO fl2 VALUES (300, 500), (700, 500), (1300, 1500), (1700, 1500);
+SELECT * FROM fl1 LOCK IN SHARE MODE;
+COMMIT;
+UNLOCK TABLES;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
+SELECT @end - @start;
+
+--echo # Records must not be X-locked for foreign keys cascade
+SET @start = @end;
+BEGIN;
+LOCK TABLE fl0 READ, fl1 WRITE, fl2 WRITE;
+DELETE FROM fl1 WHERE id = 1500;
+UPDATE fl1 SET id = 2500 WHERE id = 500;
+COMMIT;
+UNLOCK TABLES;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
+SELECT @end - @start;
+
+SET autocommit = default;
 DROP TABLE t1;
+DROP TABLE fl2;
+DROP TABLE fl1;
+DROP TABLE fl0;
 
 --disable_warnings
 SET GLOBAL innodb_monitor_enable=default;
diff --git a/storage/innobase/lock/lock0lock.cc b/storage/innobase/lock/lock0lock.cc
index a2de83c0739..6c7a7c1556a 100644
--- a/storage/innobase/lock/lock0lock.cc
+++ b/storage/innobase/lock/lock0lock.cc
@@ -1929,6 +1929,10 @@ lock_rec_lock(
   ut_ad((LOCK_MODE_MASK & mode) != LOCK_X ||
          lock_table_has(trx, index->table, LOCK_IX));
 
+  if (lock_table_has(trx, index->table,
+		     static_cast<lock_mode>(LOCK_MODE_MASK & mode)))
+	  goto exit;
+
   if (lock_t *lock= lock_rec_get_first_on_page(lock_sys.rec_hash, block))
   {
     trx_mutex_enter(trx);
@@ -1995,9 +1999,10 @@ lock_rec_lock(
 
     err= DB_SUCCESS_LOCKED_REC;
   }
-  lock_mutex_exit();
-  MONITOR_ATOMIC_INC(MONITOR_NUM_RECLOCK_REQ);
-  return err;
+exit:
+	lock_mutex_exit();
+	MONITOR_ATOMIC_INC(MONITOR_NUM_RECLOCK_REQ);
+	return err;
 }
 
 /*********************************************************************//**
@@ -6358,8 +6363,9 @@ lock_trx_has_expl_x_lock(
 
 	lock_mutex_enter();
 	ut_ad(lock_table_has(trx, table, LOCK_IX));
-	ut_ad(lock_rec_has_expl(LOCK_X | LOCK_REC_NOT_GAP, block, heap_no,
-				trx));
+	ut_ad(lock_table_has(trx, table, LOCK_X)
+	      || lock_rec_has_expl(LOCK_X | LOCK_REC_NOT_GAP, block, heap_no,
+				   trx));
 	lock_mutex_exit();
 	return(true);
 }