MySQL Bugs: #115441: deadlock occurs when multiple tables are renamed concurrently.

Bug #115441	deadlock occurs when multiple tables are renamed concurrently.
Submitted:	27 Jun 2024 3:39	Modified:	3 Jul 2024 8:39
Reporter:	jack kuang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
what I did：
1、Create two new tables, associate the views of the two tables, and two temporary tables：
-create table t1(id int);
-create table t2(id int);
-create view v1 as select t1.id as id1, t2.id as id2 from t1, t2;
-create table t1_temp like t1;
-create table t2_temp like t2;

2、Concurrently execute rename operations in two sessions session1 and session2
#session1
rename table t1 to t1_exec, t1_temp to t1, t1_exec to t1_temp;
#session2
rename table t2 to t2_exec, t2_temp to t2, t2_exec to t2_temp;

what I expected to happen：
rename table success！

what happened：
Deadlock found when trying to get lock; try restarting transaction

How to repeat:
1、create table and view
-create table t1(id int);
-create table t2(id int);
-create view v1 as select t1.id as id1, t2.id as id2 from t1, t2;
-create table t1_temp like t1;
-create table t2_temp like t2;

2、concurrency rename t1 and t2
delimiter  //
CREATE PROCEDURE rename1()
BEGIN
  DECLARE i INT;
  set i=0;
  WHILE i<=1000 DO
     rename table t1 to t1_exec, t1_temp to t1, t1_exec to t1_temp;
     SET i = i+1;
  END WHILE;
END//

delimiter  //
CREATE PROCEDURE rename2()
BEGIN
  DECLARE i INT;
  set i=0;
  WHILE i<=1000 DO
     rename table t2 to t2_exec, t2_temp to t2, t2_exec to t2_temp;
     SET i = i+1;
  END WHILE;
END;//

call rename1()
call rename2()

Suggested fix:
We guess the possible cause of the bug is：
The rename table statement will sequentially add MDL-X locks to the table and the views associated with the table, and add MDL_SHARED_READ locks to all tables associated with the view.
In a scenario where two tables are associated with the same view, executing rename statements on both tables at the same time may cause a deadlock.

HI Mr. Kuano,

Thank you for your bug report.

However, it is not a bug.

That is how metadata locks are designed to work.

They are performing a functionality that are ensuring that DDL operations are safe to perform. Some of the DDL operations, like renaming, must be fully protected until they finish. Hence, that is why exclusive locks are taken.

Hence, the metadata locks are by design. Whenever exclusive locks are introduced, there is a possibility that deadlock can occur.

Deadlocks are a proof that locks are performing their jobs properly.

You can always query Information Schema for the metadata locks that are taken.

This is also fully described in our Reference Manual.

Not a bug.

thank you for your reply!
now,we have two suggestions that we hope you can consider:

first, Is it possible to lock all tables and views at the same time? 
For example:
1、Create two new tables, associate the views of the two tables, and two temporary tables：
-create table t1(id int);
-create table t2(id int);
-create view v1 as select t1.id as id1, t2.id as id2 from t1, t2;
-create table t1_temp like t1;
-create table t2_temp like t2;

2、Concurrently execute rename operations in two sessions session1 and session2
#session1
rename table t1 to t1_exec, t1_temp to t1, t1_exec to t1_temp;
#session2
rename table t2 to t2_exec, t2_temp to t2, t2_exec to t2_temp;

The current locking sequence is:
In session1, t1 is locked first, then v1 is locked, and finally t2 is locked. 
In session2, t2 is locked first, then v1 is locked, and finally t1 is locked.
Can it be changed to:
In session1,Lock t1, t2, v1 at the same time,
and Blocking and waiting because the lock cannot be obtained in session2,
to avoid deadlock.

second,Do not add MDL_SHARED_READ locks to all tables associated with the view in the sql/sql_base.cc->open_tables()

Sorry Mr. kuang,

That is not possible ......

MySQL has to abide by several standards, including SQL, ACID, MVCC, which are all designed with security of the data.

Also, we cannot introduce unsafe changes that would  not all break standards, but would jeopardize  security.

Not a bug.