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

            for InnoDB tables"

Don't use thr_lock.c locks for InnoDB tables. Below is list of changes that
were needed to implement this:
- HANDLER OPEN acquireis MDL_SHARED_READ instead of MDL_SHARED
- HANDLER READ calls external_lock() even if SE is not going to be locked by
  THR_LOCK
- InnoDB lock wait timeouts are now honored which are much shorter by default
  than server lock wait timeouts (1 year vs 50 seconds)
- with @@autocommit= 1 LOCK TABLES disables autocommit implicitely, though
  user still sees @@autocommt= 1
- the above starts implicit transaction
- transactions started by LOCK TABLES are now rolled back on disconnect
  (previously everything was committed due to autocommit)
- transactions started by LOCK TABLES are now rolled back by ROLLBACK
  (previously everything was committed due to autocommit)
- it is now impossible to change BINLOG_FORMAT under LOCK TABLES (at least
  to statement) due to running transaction
- LOCK TABLES WRITE is additionally handled by MDL
- ...in contrast LOCK TABLES READ protection against DML is pure InnoDB
- combining transactional and non-transactional tables under LOCK TABLES
  may cause rolled back changes in transactional table and "committed"
  changes in non-transactional table
- user may disable innodb_table_locks, which will cause LOCK TABLES to be
  noop basically

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.

1 files changed, 0 insertions, 221 deletions

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.