ttdevelopers
diff --git a/‎mysql-test/r/mdl_sync.result‎
Lines changed: 94 additions & 1 deletion b/‎mysql-test/r/mdl_sync.result‎
Lines changed: 94 additions & 1 deletion
diff --git a/‎mysql-test/r/sp_sync.result‎
Lines changed: 16 additions & 15 deletions b/‎mysql-test/r/sp_sync.result‎
Lines changed: 16 additions & 15 deletions
diff --git a/‎mysql-test/suite/perfschema/r/dml_setup_instruments.result‎
Lines changed: 1 addition & 1 deletion b/‎mysql-test/suite/perfschema/r/dml_setup_instruments.result‎
Lines changed: 1 addition & 1 deletion
diff --git a/‎mysql-test/suite/rpl/r/rpl_sp.result‎
Lines changed: 9 additions & 4 deletions b/‎mysql-test/suite/rpl/r/rpl_sp.result‎
Lines changed: 9 additions & 4 deletions
diff --git a/‎mysql-test/suite/rpl/t/rpl_sp.test‎
Lines changed: 12 additions & 4 deletions b/‎mysql-test/suite/rpl/t/rpl_sp.test‎
Lines changed: 12 additions & 4 deletions
diff --git a/‎mysql-test/t/mdl_sync.test‎
Lines changed: 132 additions & 1 deletion b/‎mysql-test/t/mdl_sync.test‎
Lines changed: 132 additions & 1 deletion
@@ -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').
 
@@ -59,36 +59,37 @@ 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))
 2
 # Connection default
 DROP PROCEDURE p1;
 DROP FUNCTION f1;
-DROP TABLE t1;
+DROP TABLES t0, t1;
 SET DEBUG_SYNC= 'RESET';
@@ -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
 
@@ -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
 
@@ -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;
 
@@ -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').