MDEV-7660 - MySQL WL#6671 "Improve scalability by not using thr_lock.c locksbb-10.2-mdev7660

            for InnoDB tables"

Don't use thr_lock.c locks for InnoDB tables.

Let HANDLER READ call external_lock() even if SE is not going to be locked by
THR_LOCK. This fixes at least main.implicit_commit failure.

Removed tests for BUG#45143 and BUG#55930 which cover InnoDB + THR_LOCK. To
operate properly these tests require code flow to go through THR_LOCK debug
sync points, which is not the case after this patch. These tests are removed
by WL#6671 as well. An alternative is to port them to different storage engine.

5 files changed, 12 insertions, 504 deletions

diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test
index cb57c092e40..85ba41860df 100644
--- a/mysql-test/t/innodb_mysql_lock.test
+++ b/mysql-test/t/innodb_mysql_lock.test
@@ -150,14 +150,16 @@ let $wait_condition=
 --source include/wait_condition.inc
 LOCK TABLES t1 READ;
 SELECT release_lock('bug42147_lock');
+let $wait_condition=
+  SELECT COUNT(*) > 0 FROM information_schema.processlist
+  WHERE state = 'executing'
+  AND info = 'INSERT INTO t1 SELECT get_lock(\'bug42147_lock\', 60)';
+--source include/wait_condition.inc
+UNLOCK TABLES;
 
 connection default;
 --reap
 
-connection con2;
-UNLOCK TABLES;
-
-connection default;
 disconnect con2;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test
index c090e3a1d93..07c16acc72a 100644
--- a/mysql-test/t/lock_sync.test
+++ b/mysql-test/t/lock_sync.test
@@ -406,6 +406,12 @@ let $restore_table= t2;
 --echo # 2.8 REPLACE with a subquery.
 --echo # 
 --echo # Same is true for this statement as well.
+
+--echo # Suppress warnings for REPLACE ... SELECT
+--disable_query_log
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+--enable_query_log
+
 let $statement= replace into t2 select i+5 from t1;
 let $restore_table= t2;
 --source include/check_no_concurrent_insert.inc
@@ -873,116 +879,6 @@ set @@global.concurrent_insert= @old_concurrent_insert;
 
 
 --echo #
---echo # Test for bug #45143 "All connections hang on concurrent ALTER TABLE".
---echo #
---echo # Concurrent execution of statements which required weak write lock
---echo # (TL_WRITE_ALLOW_WRITE) on several instances of the same table and
---echo # statements which tried to acquire stronger write lock (TL_WRITE,
---echo # TL_WRITE_ALLOW_READ) on this table might have led to deadlock.
-#
-# Suppress warnings for INSERTs that use get_lock().
-#
-disable_query_log;
-call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
-enable_query_log;
-
---disable_warnings
-drop table if exists t1;
-drop view if exists v1;
---enable_warnings
---echo # Create auxiliary connections used through the test.
-connect (con_bug45143_1,localhost,root,,test,,);
-connect (con_bug45143_3,localhost,root,,test,,);
-connect (con_bug45143_2,localhost,root,,test,,);
-connection default;
---echo # Reset DEBUG_SYNC facility before using it.
-set debug_sync= 'RESET';
---echo # Turn off logging so calls to locking subsystem performed
---echo # for general_log table won't interfere with our test.
-set @old_general_log = @@global.general_log;
-set @@global.general_log= OFF;
-
-create table t1 (i int) engine=InnoDB;
---echo # We have to use view in order to make LOCK TABLES avoid
---echo # acquiring SNRW metadata lock on table.
-create view v1 as select * from t1;
-insert into t1 values (1);
---echo # Prepare user lock which will be used for resuming execution of
---echo # the first statement after it acquires TL_WRITE_ALLOW_WRITE lock.
-select get_lock("lock_bug45143_wait", 0);
-
-connection con_bug45143_1;
---echo # Sending:
---send insert into t1 values (get_lock("lock_bug45143_wait", 100));
-
-connection con_bug45143_2;
---echo # Wait until the above INSERT takes TL_WRITE_ALLOW_WRITE lock on 't1'
---echo # and then gets blocked on user lock 'lock_bug45143_wait'.
-let $wait_condition= select count(*)= 1 from information_schema.processlist
-                       where state= 'User lock' and
-                             info='insert into t1 values (get_lock("lock_bug45143_wait", 100))';
---source include/wait_condition.inc
---echo # Ensure that upcoming SELECT waits after acquiring TL_WRITE_ALLOW_WRITE
---echo # lock for the first instance of 't1'.
-set debug_sync='thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go';
---echo # Sending:
---send select count(*) > 0 from t1 as a, t1 as b for update;
-
-connection con_bug45143_3;
---echo # Wait until the above SELECT ... FOR UPDATE is blocked after
---echo # acquiring lock for the the first instance of 't1'.
-set debug_sync= 'now WAIT_FOR parked';
---echo # Send LOCK TABLE statement which will try to get TL_WRITE lock on 't1':
---send lock table v1 write;
-
-connection default;
---echo # Wait until this LOCK TABLES statement starts waiting for table lock.
-let $wait_condition= select count(*)= 1 from information_schema.processlist
-                       where state= 'Waiting for table level lock' and
-                             info='lock table v1 write';
---source include/wait_condition.inc
---echo # Allow SELECT ... FOR UPDATE to resume.
---echo # Since it already has TL_WRITE_ALLOW_WRITE lock on the first instance
---echo # of 't1' it should be able to get lock on the second instance without
---echo # waiting, even although there is another thread which has such lock
---echo # on this table and also there is a thread waiting for a TL_WRITE on it.
-set debug_sync= 'now SIGNAL go';
-
-connection con_bug45143_2;
---echo # Reap SELECT ... FOR UPDATE
---reap
-
-connection default;
---echo # Resume execution of the INSERT statement.
-select release_lock("lock_bug45143_wait");
-
-connection con_bug45143_1;
---echo # Reap INSERT statement.
---echo # In Statement and Mixed replication mode we get here "Unsafe 
---echo # for binlog" warnings. In row mode there are no warnings.
---echo # Hide the discrepancy.
---disable_warnings
---reap
---enable_warnings
-
-
-connection con_bug45143_3;
---echo # Reap LOCK TABLES statement.
---reap
-unlock tables;
-
-connection default;
---echo # Do clean-up.
-disconnect con_bug45143_1;
-disconnect con_bug45143_2;
-disconnect con_bug45143_3;
-set debug_sync= 'RESET';
-set @@global.general_log= @old_general_log;
-drop view v1;
-drop table t1;
-
-
---echo #
 --echo # Bug#50821 Deadlock between LOCK TABLES and ALTER TABLE
 --echo #
 
@@ -1051,55 +947,6 @@ SET DEBUG_SYNC="RESET";
 
 
 --echo #
---echo # Bug#55930 Assertion `thd->transaction.stmt.is_empty() ||
---echo #           thd->in_sub_stmt || (thd->state..
---echo #
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-
-CREATE TABLE t1(a INT) engine=InnoDB;
-INSERT INTO t1 VALUES (1), (2);
-
-connect (con1, localhost, root);
-connect (con2, localhost, root);
-
-connection con1;
-SET SESSION lock_wait_timeout= 1;
-SET DEBUG_SYNC= 'ha_admin_open_ltable SIGNAL opti_recreate WAIT_FOR opti_analyze';
---echo # Sending:
---send OPTIMIZE TABLE t1
-
-connection con2;
-SET DEBUG_SYNC= 'now WAIT_FOR opti_recreate';
-SET DEBUG_SYNC= 'after_lock_tables_takes_lock SIGNAL thrlock WAIT_FOR release_thrlock';
---echo # Sending:
---send INSERT INTO t1 VALUES (3)
-
-connection default;
-SET DEBUG_SYNC= 'now WAIT_FOR thrlock';
-SET DEBUG_SYNC= 'now SIGNAL opti_analyze';
-
-connection con1;
---echo # Reaping: OPTIMIZE TABLE t1
---reap
-SET DEBUG_SYNC= 'now SIGNAL release_thrlock';
-disconnect con1;
---source include/wait_until_disconnected.inc
-
-connection con2;
---echo # Reaping: INSERT INTO t1 VALUES (3)
---reap
-disconnect con2;
---source include/wait_until_disconnected.inc
-
-connection default;
-DROP TABLE t1;
-SET DEBUG_SYNC= 'RESET';
-
-
---echo #
 --echo # Bug#57130 crash in Item_field::print during SHOW CREATE TABLE or VIEW
 --echo #
 
diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test
index 0b6d6f58013..4aa191d3dfc 100644
--- a/mysql-test/t/mdl_sync.test
+++ b/mysql-test/t/mdl_sync.test
@@ -119,10 +119,6 @@ alter table t1 add index (not_exist);
 --echo # lock.
 --error ER_DUP_ENTRY
 alter table t1 add primary key (c1);
---echo # Check that SNRW lock is compatible with S lock.
-lock table t1 write;
-insert into t1 values (1);
-unlock tables;
 --echo # Check that X lock is incompatible with S lock.
 --echo # Sending:
 --send rename table t1 to t2;
@@ -172,35 +168,6 @@ connection mdl_con1;
 alter table t1 drop column c2;
 --echo # 
 connection default;
-handler t1 open;
---echo # 
-connection mdl_con1;
---echo # Check that upgrade from SNRW to X is blocked by presence of S lock.
-lock table t1 write;
---echo # Sending:
---send alter table t1 add column c2 int;
---echo # 
-connection mdl_con2;
---echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked
---echo # because of S lock.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table metadata lock" and
-        info = "alter table t1 add column c2 int";
---source include/wait_condition.inc
---echo # 
-connection default;
---echo # Unblock ALTER TABLE.
-handler t1 close;
---echo # 
-connection mdl_con1;
---echo # Reaping ALTER TABLE.
---reap
---echo # Restore the original state of the things.
-alter table t1 drop column c2;
-unlock tables;
---echo # 
-connection default;
 --echo #
 --echo # 2) Acquire SH (shared high-priority) lock on the table.
 --echo #    We have to involve DEBUG_SYNC facility for this as usually
@@ -797,8 +764,6 @@ lock table t1 write;
 --echo # 
 connection mdl_con1;
 --echo # Check that S and SH locks are compatible with it.
-handler t1 open;
-handler t1 close;
 select column_name from information_schema.columns where
   table_schema='test' and table_name='t1';
 --echo # Check that SR lock is incompatible with SNRW lock.
@@ -1293,8 +1258,6 @@ where state = "Waiting for table metadata lock" and
       info = "lock table t1 write";
 --source include/wait_condition.inc
 --echo # Check that S and SH locks are compatible with pending SNRW
-handler t1 open t;
-handler t close;
 select column_name from information_schema.columns where
   table_schema='test' and table_name='t1';
 --echo # Check that SR is incompatible with pending SNRW
@@ -2162,190 +2125,6 @@ disconnect mdl_con3;
 set debug_sync= 'RESET';
 drop table t1, t2;
 
-
---echo #
---echo # Additional coverage for some scenarios in which not quite
---echo # correct use of S metadata locks by HANDLER statement might
---echo # have caused deadlocks.
---echo #
---disable_warnings
-drop table if exists t1, t2;
---enable_warnings
-connect(handler_con1,localhost,root,,);
-connect(handler_con2,localhost,root,,);
-connection default;
-create table t1 (i int);
-create table t2 (j int);
-insert into t1 values (1);
-
---echo #
---echo # First, check scenario in which we upgrade SNRW lock to X lock
---echo # on a table while having HANDLER READ trying to acquire TL_READ
---echo # on the same table.
---echo #
-handler t1 open;
---echo # 
-connection handler_con1;
-lock table t1 write;
---echo # Upgrade SNRW to X lock.
---echo # Sending:
---send alter table t1 add column j int;
---echo # 
-connection handler_con2;
---echo # Wait until ALTER is blocked during upgrade.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table metadata lock" and
-        info = "alter table t1 add column j int";
---source include/wait_condition.inc
---echo # 
-connection default;
---echo # The below statement should not cause deadlock.
---send handler t1 read first;
---echo # 
-connection handler_con1;
---echo # Reap ALTER TABLE.
---reap
-unlock tables;
---echo # 
-connection default;
---echo # Reap HANDLER READ.
---reap
-handler t1 close;
-
---echo #
---echo # Now, check scenario in which upgrade of SNRW lock to X lock
---echo # can be blocked by HANDLER which is open in connection currently
---echo # waiting to get table-lock owned by connection doing upgrade.
---echo #
-handler t1 open;
---echo # 
-connection handler_con1;
-lock table t1 write, t2 read;
---echo # 
-connection default;
---echo # Execute statement which will be blocked on table-level lock
---echo # owned by connection 'handler_con1'.
---echo # Sending:
---send insert into t2 values (1);
---echo # 
-connection handler_con1;
---echo # Wait until INSERT is blocked on table-level lock.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table level lock" and
-        info = "insert into t2 values (1)";
---source include/wait_condition.inc
---echo # Sending 'alter table t1 drop column j'. It should not cause
---echo # deadlock.
-send alter table t1 drop column j;
-connection handler_con2;
---echo # Wait until ALTER is blocked during upgrade.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table metadata lock" and
-        info = "alter table t1 drop column j";
---source include/wait_condition.inc
---echo # 
-connection default;
---echo # Reap INSERT.
---error ER_LOCK_ABORTED
---reap
-handler t1 close;
---echo #
-connection handler_con1;
---echo # Reaping 'alter table t1 drop column j'
---reap
-unlock tables;
-connection default;
-
---echo # Then, check the scenario in which upgrade of SNRW lock to X
---echo # lock is blocked by HANDLER which is open in connection currently
---echo # waiting to get SW lock on the same table.
---echo #
-handler t1 open;
---echo # 
-connection handler_con1;
-lock table t1 write;
---echo # 
-connection default;
---echo # The below insert should be blocked because active SNRW lock on 't1'.
---echo # Sending:
---send insert into t1 values (1);
---echo # 
-connection handler_con1;
---echo # Wait until INSERT is blocked because of SNRW lock.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table metadata lock" and
-        info = "insert into t1 values (1)";
---source include/wait_condition.inc
---echo # The below ALTER TABLE will be blocked because of presence of HANDLER.
---echo # Sending:
---send alter table t1 add column j int;
---echo # 
-connection default;
---echo # INSERT should be chosen as victim for resolving deadlock.
---echo # Reaping INSERT.
---error ER_LOCK_DEADLOCK
---reap
---echo # Close HANDLER to unblock ALTER TABLE.
-handler t1 close;
---echo # 
-connection handler_con1;
---echo # Reaping ALTER TABLE.
---reap
-unlock tables;
---echo # 
-connection default;
-
---echo #
---echo # Finally, test in which upgrade of SNRW lock to X lock is blocked
---echo # by HANDLER which is open in connection currently waiting to get
---echo # SR lock on the table on which lock is upgraded.
---echo #
-handler t1 open;
---echo # 
-connection handler_con1;
-lock table t1 write, t2 write;
---echo # 
-connection default;
---echo # The below insert should be blocked because active SNRW lock on 't1'.
---echo # Sending:
---send insert into t2 values (1);
---echo # 
-connection handler_con1;
---echo # Wait until INSERT is blocked because of SNRW lock.
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table metadata lock" and
-        info = "insert into t2 values (1)";
---source include/wait_condition.inc
---echo # The below ALTER TABLE will be blocked because of presence of HANDLER.
---echo # Sending:
---send alter table t1 drop column j;
---echo # 
-connection default;
---echo # INSERT should be chosen as victim for resolving deadlock.
---echo # Reaping INSERT.
---error ER_LOCK_DEADLOCK
---reap
---echo # Close HANDLER to unblock ALTER TABLE.
-handler t1 close;
---echo # 
-connection handler_con1;
---echo # Reaping ALTER TABLE.
---reap
-unlock tables;
---echo # 
-connection default;
-
---echo # Clean-up.
-disconnect handler_con1;
-disconnect handler_con2;
-drop tables t1, t2;
-
-
 --echo #
 --echo # Test coverage for basic deadlock detection in metadata
 --echo # locking subsystem.
diff --git a/mysql-test/t/partition_debug_sync.test b/mysql-test/t/partition_debug_sync.test
index 11af9b06cdd..f085ea3fb83 100644
--- a/mysql-test/t/partition_debug_sync.test
+++ b/mysql-test/t/partition_debug_sync.test
@@ -82,43 +82,3 @@ connection default;
 SET DEBUG_SYNC= 'RESET';
 
 --echo End of 5.1 tests
-
---echo #
---echo # Coverage test for non pruned ha_partition::store_lock()
---echo #
-CREATE TABLE t1 (a int) ENGINE = InnoDB;
-CREATE TABLE t2 (a int PRIMARY KEY)
-ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 3;
-
-HANDLER t1 OPEN;
-
-connect (con1, localhost, root,,);
-
-LOCK TABLES t1 WRITE, t2 READ;
-
-connection default;
-
-SET DEBUG_SYNC="wait_for_lock SIGNAL locking";
-send INSERT INTO t2 VALUES (1), (2), (3);
-
-connection con1;
-SET DEBUG_SYNC="now WAIT_FOR locking";
-
-send ALTER TABLE t1 ADD COLUMN b int;
-
-connection default;
---error ER_LOCK_ABORTED
---reap
-
-SELECT 1;
-
-connection con1;
---reap
-
-UNLOCK TABLES;
---disconnect con1
-
-connection default;
-SET DEBUG_SYNC = 'RESET';
-
-DROP TABLE t1, t2;
diff --git a/mysql-test/t/truncate_coverage.test b/mysql-test/t/truncate_coverage.test
index 0834f7a3eca..3351ce84232 100644
--- a/mysql-test/t/truncate_coverage.test
+++ b/mysql-test/t/truncate_coverage.test
@@ -17,86 +17,6 @@ DROP TABLE IF EXISTS t1;
 --echo # Bug#20667 - Truncate table fails for a write locked table
 --echo #
 ########
-# Attack wait_while_table_is_used(). Kill query while trying to
-# upgrade MDL.
-#
-CREATE TABLE t1 (c1 INT);
-INSERT INTO t1 VALUES (1);
-#
-# Acquire a shared metadata lock on table by opening HANDLER for it and wait.
-# TRUNCATE shall block on this metadata lock.
-# We can't use normal DML as such statements would also block LOCK TABLES.
-#
---connect (con1, localhost, root,,)
-HANDLER t1 OPEN;
-#
-# Get connection id of default connection.
-# Lock the table and start TRUNCATE, which will block on MDL upgrade.
-#
---connection default
-let $ID= `SELECT @id := CONNECTION_ID()`;
-LOCK TABLE t1 WRITE;
-SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting';
-send TRUNCATE TABLE t1;
-#
-# Get the default connection ID into a variable in an invisible statement.
-# Kill the TRUNCATE query. This shall result in an error return
-# from wait_while_table_is_used().
-#
---connect (con2, localhost, root,,)
-SET DEBUG_SYNC='now WAIT_FOR waiting';
-let $invisible_assignment_in_select = `SELECT @id := $ID`;
-KILL QUERY @id;
---disconnect con2
---connection default
---error ER_QUERY_INTERRUPTED
-reap;
-UNLOCK TABLES;
---connection con1
---echo # Release shared metadata lock by closing HANDLER.
-HANDLER t1 CLOSE;
---disconnect con1
---connection default
-DROP TABLE t1;
-SET DEBUG_SYNC='RESET';
-########
-# Attack reopen_tables(). Remove form file.
-#
-CREATE TABLE t1 (c1 INT);
-INSERT INTO t1 VALUES (1);
-#
-# Acquire a shared metadata lock on table by opening HANDLER for it and wait.
-# TRUNCATE shall block on this metadata lock.
-# We can't use normal DML as such statements would also block LOCK TABLES.
-#
---connect (con1, localhost, root,,)
-HANDLER t1 OPEN;
-#
-# Lock the table and start TRUNCATE, which will block on MDL upgrade.
-#
---connection default
-LOCK TABLE t1 WRITE;
-SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting';
-send TRUNCATE TABLE t1;
-#
-# Remove datafile.
-# Commit to let TRUNCATE continue.
-#
---connect (con2, localhost, root,,)
-SET DEBUG_SYNC='now WAIT_FOR waiting';
---remove_file $MYSQLD_DATADIR/test/t1.frm
---disconnect con2
---connection con1
-HANDLER t1 CLOSE;
---disconnect con1
---connection default
---error ER_NO_SUCH_TABLE
-reap;
-UNLOCK TABLES;
---error ER_BAD_TABLE_ERROR
-DROP TABLE t1;
-SET DEBUG_SYNC='RESET';
-########
 # Attack acquire_exclusive_locks(). Hold a global read lock.
 # Non-LOCK TABLE case.
 #