Patch that changes approach to how we acquire metadata

locks for DML statements and changes the way MDL locks
are acquired/granted in contended case.

Instead of backing-off when a lock conflict is encountered
and waiting for it to go away before restarting open_tables()
process we now wait for lock to be released without releasing
any previously acquired locks. If conflicting lock goes away
we resume opening tables. If waiting leads to a deadlock we
try to resolve it by backing-off and restarting open_tables()
immediately.

As result both waiting for possibility to acquire and
acquiring of a metadata lock now always happen within the
same MDL API call. This has allowed to make release of a lock
and granting it to the most appropriate pending request an
atomic operation.
Thanks to this it became possible to wake up during release
of lock only those waiters which requests can be satisfied
at the moment as well as wake up only one waiter in case
when granting its request would prevent all other requests
from being satisfied. This solves thundering herd problem
which occured in cases when we were releasing some lock and
woke up many waiters for SNRW or X locks (this was the issue
in bug#52289 "performance regression for MyISAM in sysbench
OLTP_RW test".
This also allowed to implement more fair (FIFO) scheduling
among waiters with the same priority.
It also opens the door for introducing new types of requests
for metadata locks such as low-prio SNRW lock which is
necessary in order to support LOCK TABLES LOW_PRIORITY WRITE.

Notice that after this sometimes can report ER_LOCK_DEADLOCK
error in cases in which it has not happened before.
Particularly we will always report this error if waiting for
conflicting lock has happened in the middle of transaction
and resulted in a deadlock. Before this patch the error was
not reported if deadlock could have been resolved by backing
off all metadata locks acquired by the current statement.

7 files changed, 280 insertions, 45 deletions

diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result
index f8b8d3f9d56..67d778211dd 100644
--- a/mysql-test/r/mdl_sync.result
+++ b/mysql-test/r/mdl_sync.result
@@ -1765,6 +1765,7 @@ drop tables t1, t2;
 # locking subsystem.
 #
 drop tables if exists t0, t1, t2, t3, t4, t5;
+set debug_sync= 'RESET';
 create table t1 (i int);
 create table t2 (j int);
 create table t3 (k int);
@@ -1943,6 +1944,98 @@ commit;
 # Reap ALTER TABLE ... RENAME.
 drop table t2;
 #
+# Test that in situation when MDL subsystem detects a deadlock
+# but it turns out that it can be resolved by backing-off locks
+# acquired by one of participating transactions (which is
+# possible when one of transactions consists only of currently
+# executed statement, e.g. in autocommit mode) no error is
+# reported.
+#
+create table t1 (i int);
+create table t2 (j int);
+# Ensure that the below SELECT stops once it has acquired metadata
+# lock on table 't2'.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+# Sending:
+select * from t2, t1;
+#
+# Switching to connection 'deadlock_con1'.
+# Wait till SELECT acquires MDL on 't2' and starts waiting for signal.
+set debug_sync= 'now WAIT_FOR locked';
+# Sending:
+lock tables t1 write, t2 write;
+#
+# Switching to connection 'deadlock_con2'.
+# Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked
+# while trying to acquire SNRW lock on 't1'.
+# Resume SELECT execution, this should eventually unblock LOCK TABLES.
+set debug_sync= 'now SIGNAL finish';
+#
+# Switching to connection 'deadlock_con1'.
+# Reaping LOCK TABLES.
+unlock tables;
+#
+# Switching to connection 'default'.
+# Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error.
+j	i
+drop tables t1, t2;
+#
+# Test coverage for situation in which a race has happened
+# during deadlock detection process which led to unwarranted
+# ER_LOCK_DEADLOCK error.
+#
+create table t1 (i int);
+# Ensure that ALTER waits once it has acquired SNW lock.
+set debug_sync='after_open_table_mdl_shared SIGNAL parked1 WAIT_FOR go1';
+# Sending:
+alter table t1 add column j int;
+#
+# Switching to connection 'deadlock_con1'.
+# Wait till ALTER acquires SNW lock and stops.
+set debug_sync='now WAIT_FOR parked1';
+# Ensure that INSERT is paused once it detects that there is
+# a conflicting metadata lock so it has to wait, but before
+# deadlock detection is run.
+set debug_sync='mdl_acquire_lock_wait SIGNAL parked2 WAIT_FOR go2';
+# Sending:
+insert into t1 values ();
+#
+# Switching to connection 'deadlock_con2'.
+# Wait till INSERT is paused.
+set debug_sync='now WAIT_FOR parked2';
+# Resume ALTER execution. Eventually it will release its
+# metadata lock and INSERT's request for SW lock will be
+# satisified.
+set debug_sync='now SIGNAL go1';
+#
+# Switching to connection 'default'.
+# Reaping ALTER TABLE.
+# Add a new request for SNW lock to waiting graph.
+# Sending:
+alter table t1 drop column j;
+#
+# Switching to connection 'deadlock_con2'.
+# Wait until ALTER is blocked.
+# Resume INSERT so it can start deadlock detection.
+#
+# At this point there is a discrepancy between the fact that INSERT's
+# SW lock is already satisfied, but INSERT's connection is still
+# marked as waiting for it. Looking for a loop in waiters graph
+# without additional checks has detected a deadlock (INSERT waits
+# for SW lock; which is not granted because of pending SNW lock from
+# ALTER; which waits for active SW lock from INSERT). Since requests
+# for SW and SNW locks have same weight ALTER was selected as a victim
+# and ended with ER_LOCK_DEADLOCK error.
+set debug_sync='now SIGNAL go2';
+#
+# Switching to connection 'deadlock_con1'.
+# Reaping INSERT.
+#
+# Switching to connection 'default'.
+# Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK.
+drop table t1;
+set debug_sync= 'RESET';
+#
 # Test for bug #46748 "Assertion in MDL_context::wait_for_locks()
 # on INSERT + CREATE TRIGGER".
 #
@@ -2175,7 +2268,7 @@ alter table t1 add column e int, rename to t2;;
 #
 # Switching to connection 'default'.
 set debug_sync='now WAIT_FOR alter_table_locked';
-set debug_sync='before_open_table_wait_refresh SIGNAL alter_go';
+set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go';
 # The below statement should get ER_LOCK_DEADLOCK error
 # (i.e. it should not allow ALTER to proceed, and then
 # fail due to 't1' changing its name to 't2').
diff --git a/mysql-test/r/sp_sync.result b/mysql-test/r/sp_sync.result
index a16babaef67..174b3c60745 100644
--- a/mysql-test/r/sp_sync.result
+++ b/mysql-test/r/sp_sync.result
@@ -59,30 +59,31 @@ SET DEBUG_SYNC= 'RESET';
 #
 # Bug #48246 assert in close_thread_table
 #
+CREATE TABLE t0 (b INTEGER);
 CREATE TABLE t1 (a INTEGER);
 CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1;
-CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1;
+CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1, t0;
+INSERT INTO t0 VALUES(1);
 INSERT INTO t1 VALUES(1), (2);
 # Connection 2
 CALL p1();
 COUNT(f1(a))
 2
-# Connection default
-SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR called';
-# Sending:
-CREATE TABLE t1 (a INTEGER);
-# Connection 2
-SET DEBUG_SYNC= 'now WAIT_FOR locked';
-SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL called WAIT_FOR created';
-# This call used to cause an assertion. MDL locking conflict will
-# cause back-off and retry. A variable indicating if a prelocking list
-# exists, used to be not reset properly causing an eventual assert.
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked_t1 WAIT_FOR go_for_t0';
+# This call used to cause an assertion. MDL deadlock with upcoming
+# LOCK TABLES statement will cause back-off and retry.
+# A variable indicating if a prelocking list exists, used to be not
+# reset properly causing an eventual assert.
 # Sending:
 CALL p1();
 # Connection default
-# Reaping: CREATE TABLE t1 (a INTEGER)
-ERROR 42S01: Table 't1' already exists
-SET DEBUG_SYNC= 'now SIGNAL created';
+SET DEBUG_SYNC= 'now WAIT_FOR locked_t1';
+# Issue LOCK TABLES statement which will enter in MDL deadlock
+# with CALL statement and as result will cause it to perform
+# back-off and retry.
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL go_for_t0';
+LOCK TABLES t0 WRITE, t1 WRITE;
+UNLOCK TABLES;
 # Connection 2
 # Reaping: CALL p1()
 COUNT(f1(a))
@@ -90,5 +91,5 @@ COUNT(f1(a))
 # Connection default
 DROP PROCEDURE p1;
 DROP FUNCTION f1;
-DROP TABLE t1;
+DROP TABLES t0, t1;
 SET DEBUG_SYNC= 'RESET';
diff --git a/mysql-test/suite/perfschema/r/dml_setup_instruments.result b/mysql-test/suite/perfschema/r/dml_setup_instruments.result
index 73dc1178a54..55256894743 100644
--- a/mysql-test/suite/perfschema/r/dml_setup_instruments.result
+++ b/mysql-test/suite/perfschema/r/dml_setup_instruments.result
@@ -25,7 +25,7 @@ wait/synch/rwlock/sql/LOCK_system_variables_hash	YES	YES
 wait/synch/rwlock/sql/LOCK_sys_init_connect	YES	YES
 wait/synch/rwlock/sql/LOCK_sys_init_slave	YES	YES
 wait/synch/rwlock/sql/LOGGER::LOCK_logger	YES	YES
-wait/synch/rwlock/sql/MDL_context::waiting_for_lock	YES	YES
+wait/synch/rwlock/sql/MDL_context::LOCK_waiting_for	YES	YES
 wait/synch/rwlock/sql/MDL_lock::rwlock	YES	YES
 wait/synch/rwlock/sql/Query_cache_query::lock	YES	YES
 wait/synch/rwlock/sql/THR_LOCK_servers	YES	YES
diff --git a/mysql-test/suite/rpl/r/rpl_sp.result b/mysql-test/suite/rpl/r/rpl_sp.result
index 0af76e93ab4..42f7a9c7ed8 100644
--- a/mysql-test/suite/rpl/r/rpl_sp.result
+++ b/mysql-test/suite/rpl/r/rpl_sp.result
@@ -1215,18 +1215,23 @@ lock table t2 write;
 # Sending 'insert into t1 (a) values (f1())'...
 insert into t1 (a) values (f1());
 # Waitng for 'insert into t1 ...' to get blocked on table lock...
-# Sending 'drop function f1'. It will abort the table lock wait.
-drop function f1;
+# Sending 'drop function f1'. It will wait till insert finishes.
+drop function f1;;
 # --> connection default
+# Check that 'drop function f1' gets blocked.
 # Now let's let 'insert' go through...
 unlock tables;
-# --> connection con1
+# --> connection master
 # Reaping 'insert into t1 (a) values (f1())'...
-ERROR 42000: FUNCTION test.f1 does not exist
+# --> connection master1
+# Reaping 'drop function f1'
+# --> connection master
 select * from t1;
 a
+1
 select * from  t1;
 a
+1
 drop table t1, t2;
 drop function f1;
 ERROR 42000: FUNCTION test.f1 does not exist
diff --git a/mysql-test/suite/rpl/t/rpl_sp.test b/mysql-test/suite/rpl/t/rpl_sp.test
index db21ffb42fd..8bf4d70e277 100644
--- a/mysql-test/suite/rpl/t/rpl_sp.test
+++ b/mysql-test/suite/rpl/t/rpl_sp.test
@@ -657,17 +657,25 @@ connection master1;
 let $wait_condition=select count(*)=1 from information_schema.processlist
 where state='Waiting for table' and info='insert into t1 (a) values (f1())';
 --source include/wait_condition.inc
---echo # Sending 'drop function f1'. It will abort the table lock wait.
-drop function f1;
+--echo # Sending 'drop function f1'. It will wait till insert finishes.
+--send drop function f1;
 --echo # --> connection default
 connection default;
+--echo # Check that 'drop function f1' gets blocked.
+let $wait_condition=select count(*)=1 from information_schema.processlist
+where state='Waiting for table' and info='drop function f1';
+--source include/wait_condition.inc
 --echo # Now let's let 'insert' go through...
 unlock tables;
---echo # --> connection con1
+--echo # --> connection master
 connection master;
 --echo # Reaping 'insert into t1 (a) values (f1())'...
---error ER_SP_DOES_NOT_EXIST
 --reap
+--echo # --> connection master1
+connection master1;
+--echo # Reaping 'drop function f1'
+--reap
+--echo # --> connection master
 connection master;
 select * from t1;
 sync_slave_with_master;
diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test
index 6b517bf1e03..19f9b396087 100644
--- a/mysql-test/t/mdl_sync.test
+++ b/mysql-test/t/mdl_sync.test
@@ -2411,6 +2411,7 @@ drop tables t1, t2;
 --disable_warnings
 drop tables if exists t0, t1, t2, t3, t4, t5;
 --enable_warnings
+set debug_sync= 'RESET';
 
 connect(deadlock_con1,localhost,root,,);
 connect(deadlock_con2,localhost,root,,);
@@ -2700,6 +2701,136 @@ connection default;
 
 drop table t2;
 
+--echo #
+--echo # Test that in situation when MDL subsystem detects a deadlock
+--echo # but it turns out that it can be resolved by backing-off locks
+--echo # acquired by one of participating transactions (which is
+--echo # possible when one of transactions consists only of currently
+--echo # executed statement, e.g. in autocommit mode) no error is
+--echo # reported.
+--echo #
+create table t1 (i int);
+create table t2 (j int);
+--echo # Ensure that the below SELECT stops once it has acquired metadata
+--echo # lock on table 't2'.
+set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
+--echo # Sending:
+--send select * from t2, t1
+
+--echo #
+--echo # Switching to connection 'deadlock_con1'.
+connection deadlock_con1;
+--echo # Wait till SELECT acquires MDL on 't2' and starts waiting for signal.
+set debug_sync= 'now WAIT_FOR locked';
+--echo # Sending:
+--send lock tables t1 write, t2 write
+
+--echo #
+--echo # Switching to connection 'deadlock_con2'.
+connection deadlock_con2;
+--echo # Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked
+--echo # while trying to acquire SNRW lock on 't1'.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "lock tables t1 write, t2 write";
+--source include/wait_condition.inc
+--echo # Resume SELECT execution, this should eventually unblock LOCK TABLES.
+set debug_sync= 'now SIGNAL finish';
+
+--echo #
+--echo # Switching to connection 'deadlock_con1'.
+connection deadlock_con1;
+--echo # Reaping LOCK TABLES.
+--reap
+unlock tables;
+
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error.
+--reap
+
+drop tables t1, t2;
+
+--echo #
+--echo # Test coverage for situation in which a race has happened
+--echo # during deadlock detection process which led to unwarranted
+--echo # ER_LOCK_DEADLOCK error.
+--echo #
+create table t1 (i int);
+
+--echo # Ensure that ALTER waits once it has acquired SNW lock.
+set debug_sync='after_open_table_mdl_shared SIGNAL parked1 WAIT_FOR go1';
+--echo # Sending:
+--send alter table t1 add column j int
+
+--echo #
+--echo # Switching to connection 'deadlock_con1'.
+connection deadlock_con1;
+--echo # Wait till ALTER acquires SNW lock and stops.
+set debug_sync='now WAIT_FOR parked1';
+--echo # Ensure that INSERT is paused once it detects that there is
+--echo # a conflicting metadata lock so it has to wait, but before
+--echo # deadlock detection is run.
+set debug_sync='mdl_acquire_lock_wait SIGNAL parked2 WAIT_FOR go2';
+--echo # Sending:
+--send insert into t1 values ()
+
+--echo #
+--echo # Switching to connection 'deadlock_con2'.
+connection deadlock_con2;
+--echo # Wait till INSERT is paused.
+set debug_sync='now WAIT_FOR parked2';
+--echo # Resume ALTER execution. Eventually it will release its
+--echo # metadata lock and INSERT's request for SW lock will be
+--echo # satisified.
+set debug_sync='now SIGNAL go1';
+
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping ALTER TABLE.
+--reap
+--echo # Add a new request for SNW lock to waiting graph.
+--echo # Sending:
+--send alter table t1 drop column j
+
+--echo #
+--echo # Switching to connection 'deadlock_con2'.
+connection deadlock_con2;
+--echo # Wait until ALTER is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table" and info = "alter table t1 drop column j";
+--source include/wait_condition.inc
+--echo # Resume INSERT so it can start deadlock detection.
+--echo #
+--echo # At this point there is a discrepancy between the fact that INSERT's
+--echo # SW lock is already satisfied, but INSERT's connection is still
+--echo # marked as waiting for it. Looking for a loop in waiters graph
+--echo # without additional checks has detected a deadlock (INSERT waits
+--echo # for SW lock; which is not granted because of pending SNW lock from
+--echo # ALTER; which waits for active SW lock from INSERT). Since requests
+--echo # for SW and SNW locks have same weight ALTER was selected as a victim
+--echo # and ended with ER_LOCK_DEADLOCK error.
+set debug_sync='now SIGNAL go2';
+
+--echo #
+--echo # Switching to connection 'deadlock_con1'.
+connection deadlock_con1;
+--echo # Reaping INSERT.
+--reap
+
+--echo #
+--echo # Switching to connection 'default'.
+connection default;
+--echo # Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK.
+--reap
+
+drop table t1;
+
+set debug_sync= 'RESET';
+
 disconnect deadlock_con1;
 disconnect deadlock_con2;
 disconnect deadlock_con3;
@@ -3097,7 +3228,7 @@ set debug_sync='after_lock_tables_takes_lock SIGNAL alter_table_locked WAIT_FOR
 --echo # Switching to connection 'default'.
 connection default;
 set debug_sync='now WAIT_FOR alter_table_locked';
-set debug_sync='before_open_table_wait_refresh SIGNAL alter_go';
+set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go';
 --echo # The below statement should get ER_LOCK_DEADLOCK error
 --echo # (i.e. it should not allow ALTER to proceed, and then
 --echo # fail due to 't1' changing its name to 't2').
diff --git a/mysql-test/t/sp_sync.test b/mysql-test/t/sp_sync.test
index 391298b604a..431db463b67 100644
--- a/mysql-test/t/sp_sync.test
+++ b/mysql-test/t/sp_sync.test
@@ -108,38 +108,35 @@ disconnect con3;
 --echo # Bug #48246 assert in close_thread_table
 --echo #
 
+CREATE TABLE t0 (b INTEGER);
 CREATE TABLE t1 (a INTEGER);
 CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1;
-CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1;
+CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1, t0;
 
+INSERT INTO t0 VALUES(1);
 INSERT INTO t1 VALUES(1), (2);
 
 --echo # Connection 2
 connect (con2, localhost, root);
 CALL p1();
 
---echo # Connection default
-connection default;
-SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR called';
---echo # Sending:
---send CREATE TABLE t1 (a INTEGER)
-
---echo # Connection 2
-connection con2;
-SET DEBUG_SYNC= 'now WAIT_FOR locked';
-SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL called WAIT_FOR created';
---echo # This call used to cause an assertion. MDL locking conflict will
---echo # cause back-off and retry. A variable indicating if a prelocking list
---echo # exists, used to be not reset properly causing an eventual assert.
+SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked_t1 WAIT_FOR go_for_t0';
+--echo # This call used to cause an assertion. MDL deadlock with upcoming
+--echo # LOCK TABLES statement will cause back-off and retry.
+--echo # A variable indicating if a prelocking list exists, used to be not
+--echo # reset properly causing an eventual assert.
 --echo # Sending:
 --send CALL p1()
 
 --echo # Connection default
 connection default;
---echo # Reaping: CREATE TABLE t1 (a INTEGER)
---error ER_TABLE_EXISTS_ERROR
---reap
-SET DEBUG_SYNC= 'now SIGNAL created';
+SET DEBUG_SYNC= 'now WAIT_FOR locked_t1';
+--echo # Issue LOCK TABLES statement which will enter in MDL deadlock
+--echo # with CALL statement and as result will cause it to perform
+--echo # back-off and retry.
+SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL go_for_t0';
+LOCK TABLES t0 WRITE, t1 WRITE;
+UNLOCK TABLES;
 
 --echo # Connection 2
 connection con2;
@@ -151,7 +148,7 @@ connection default;
 disconnect con2;
 DROP PROCEDURE p1;
 DROP FUNCTION f1;
-DROP TABLE t1;
+DROP TABLES t0, t1;
 SET DEBUG_SYNC= 'RESET';