MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITEbb-10.10-MDEV-28930

ALTER ONLINE TABLE acquires table with TL_READ. Myisam normally acquires
TL_WRITE for DML, which makes it hang until table is freed.

We deadlock once ALTER upgrades its MDL lock.

Solution:
Unlock table earlier. We don't need to hold TL_READ once we finished
copying. Relay log replication requires no data locks on `from` table.

4 files changed, 79 insertions, 1 deletions

diff --git a/mysql-test/main/alter_table_online_debug.result b/mysql-test/main/alter_table_online_debug.result
index 30a0ac919ff..08c473906f3 100644
--- a/mysql-test/main/alter_table_online_debug.result
+++ b/mysql-test/main/alter_table_online_debug.result
@@ -582,3 +582,32 @@ set debug_sync= 'reset';
 drop table t1;
 drop table t2;
 drop table t3;
+#
+# MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE
+#
+create table t1(a int) engine=myisam select 1;
+set debug_sync='alter_table_online_before_lock SIGNAL ready WAIT_FOR go_for_locking';
+alter table t1 force;
+connection con2;
+# Avoid long hang.
+set lock_wait_timeout= 1;
+set debug_sync='now WAIT_FOR ready';
+set debug_sync='thr_multi_lock_before_thr_lock SIGNAL go_for_locking';
+update t1 set a=2;
+set lock_wait_timeout= default;
+connection default;
+set debug_sync='alter_table_online_before_lock SIGNAL ready WAIT_FOR go_for_locking';
+# Avoid long hang.
+set lock_wait_timeout= 1;
+alter table mysql.global_priv force;
+connection con2;
+set debug_sync='now WAIT_FOR ready';
+set debug_sync='thr_multi_lock_before_thr_lock SIGNAL go_for_locking';
+create user user1@localhost;
+connection default;
+#
+# Cleanup
+set lock_wait_timeout= default;
+set debug_sync=reset;
+drop user user1@localhost;
+drop table t1;
diff --git a/mysql-test/main/alter_table_online_debug.test b/mysql-test/main/alter_table_online_debug.test
index 44e5d1ec24a..cacd8cd1e12 100644
--- a/mysql-test/main/alter_table_online_debug.test
+++ b/mysql-test/main/alter_table_online_debug.test
@@ -720,3 +720,47 @@ set debug_sync= 'reset';
 drop table t1;
 drop table t2;
 drop table t3;
+
+
+--echo #
+--echo # MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE
+--echo #
+create table t1(a int) engine=myisam select 1;
+
+set debug_sync='alter_table_online_before_lock SIGNAL ready WAIT_FOR go_for_locking';
+--send
+alter table t1 force;
+
+--connection con2
+--echo # Avoid long hang.
+set lock_wait_timeout= 1;
+set debug_sync='now WAIT_FOR ready';
+set debug_sync='thr_multi_lock_before_thr_lock SIGNAL go_for_locking';
+update t1 set a=2;
+set lock_wait_timeout= default;
+
+--connection default
+--reap
+
+
+set debug_sync='alter_table_online_before_lock SIGNAL ready WAIT_FOR go_for_locking';
+--echo # Avoid long hang.
+set lock_wait_timeout= 1;
+--send
+alter table mysql.global_priv force;
+
+--connection con2
+set debug_sync='now WAIT_FOR ready';
+set debug_sync='thr_multi_lock_before_thr_lock SIGNAL go_for_locking';
+create user user1@localhost;
+
+--connection default
+--reap
+
+--echo #
+--echo # Cleanup
+set lock_wait_timeout= default;
+set debug_sync=reset;
+drop user user1@localhost;
+drop table t1;
+
diff --git a/sql/log_event_server.cc b/sql/log_event_server.cc
index 425de469a7f..acee22b2c6e 100644
--- a/sql/log_event_server.cc
+++ b/sql/log_event_server.cc
@@ -5734,7 +5734,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi)
     used in the transaction and proceed with execution of the actual
     event.
   */
-  if (!thd->lock)
+  if (!thd->open_tables)
   {
     /*
       Lock_tables() reads the contents of thd->lex, so they must be
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index bfde3375d1e..5af136dd72d 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -11852,6 +11852,11 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to,
     // We restore bitmaps, because update event is going to mess up with them.
     to->default_column_bitmaps();
 
+    end_read_record(&info);
+    init_read_record_done= false;
+    mysql_unlock_tables(thd, thd->lock);
+    thd->lock= NULL;
+
     error= online_alter_read_from_binlog(thd, &rgi, binlog);
 
     DEBUG_SYNC(thd, "alter_table_online_before_lock");