MySQL Bugs: #116598: MDL deadlock occurs due to the order of the view filed

Bug #116598	MDL deadlock occurs due to the order of the view filed
Submitted:	8 Nov 2024 9:44	Modified:	9 Nov 2024 1:12
Reporter:	zongyi chen (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In high-concurrency scenarios, deadlocks may occur in the following statements. However, deadlocks will not occur if views are swapped in sequence.

--session 1
create table t1(id int);
create table t3(id int);
create view v1 as select t3.id as id3, t1.id as id1 from t3, t1;
rename table t1 to t2;

--session 2
rename table t3 to t4;

lead to mdl lock reason is:
1.session 1 will acquire t1 x-lock,and v1 x-lock, at same time session 2 acquire t 3 x-lock;
2.session 1 waiting for t3 s-lock(due to v1 contain t3 field), session 2 waiting for v1 x-lock.so dead lock happend;

but when i change v1 from
create view v1 as select t3.id as id3, t1.id as id1 from t3, t1;
to
create view v1 as select t1.id as id1, t3.id as id2 from t1, t3;

it never be deadlock again.
you can change code to reproduce deadlock

diff --git a/sql/dd_sql_view.cc b/sql/dd_sql_view.cc
index d825dca..368675c 100644
--- a/sql/dd_sql_view.cc
+++ b/sql/dd_sql_view.cc
@@ -475,6 +475,10 @@ static bool open_views_and_update_metadata(
     if (check_schema_readonly(thd, view->db)) return true;
   }

+  LogErr(ERROR_LEVEL, ER_MYSQLBACKUP_MSG, "wait to get table shared lock");
+  DBUG_EXECUTE_IF("view_MDL_X_acquired", {sleep(15);});
+  LogErr(ERROR_LEVEL, ER_MYSQLBACKUP_MSG, "start to get table shared lock");
+
   for (auto view : *views) {
     View_metadata_updater_context vw_metadata_update_context(thd);

Deadlock scenario：
--session 1
SET SESSION DEBUG= "+d,view_MDL_X_acquired";
create table t1(id int);
create table t3(id int);
create view v1 as select t3.id as id3, t1.id as id1 from t3, t1;
rename table t1 to t2;

-- session2
rename table t3 to t4;

no Deadlock scenario:
--session 1
SET SESSION DEBUG= "+d,view_MDL_X_acquired";
create table t1(id int);
create table t3(id int);
create view v1 as select t1.id as id1, t3.id as id2 from t1, t3;
rename table t1 to t2;

-- session2
rename table t3 to t4;

I don't think the order of the view columns should make this difference.

How to repeat:
diff --git a/sql/dd_sql_view.cc b/sql/dd_sql_view.cc
index d825dca..368675c 100644
--- a/sql/dd_sql_view.cc
+++ b/sql/dd_sql_view.cc
@@ -475,6 +475,10 @@ static bool open_views_and_update_metadata(
     if (check_schema_readonly(thd, view->db)) return true;
   }

+  LogErr(ERROR_LEVEL, ER_MYSQLBACKUP_MSG, "wait to get table shared lock");
+  DBUG_EXECUTE_IF("view_MDL_X_acquired", {sleep(15);});
+  LogErr(ERROR_LEVEL, ER_MYSQLBACKUP_MSG, "start to get table shared lock");
+
   for (auto view : *views) {
     View_metadata_updater_context vw_metadata_update_context(thd);

Deadlock scenario：
--session 1
SET SESSION DEBUG= "+d,view_MDL_X_acquired";
create table t1(id int);
create table t3(id int);
create view v1 as select t3.id as id3, t1.id as id1 from t3, t1;
rename table t1 to t2;

-- session2
rename table t3 to t4;

no Deadlock scenario:
--session 1
SET SESSION DEBUG= "+d,view_MDL_X_acquired";
create table t1(id int);
create table t3(id int);
create view v1 as select t1.id as id1, t3.id as id2 from t1, t3;
rename table t1 to t2;

-- session2
rename table t3 to t4;

Hi Mr. chen,

Thank you for your bug report.

We would like to inform you that we have tested both of your test cases on 8.0.40, 8.4.1 and 9.0.1 and in all those cases we never experienced any deadlocks. We try both test cases on all three latest releases and they just worked without any deadlock.

Can' repeat.

you need change the code ,to make it reproduce stably.
If you don't modify the code, it's hard to reproduce.

diff --git a/sql/dd_sql_view.cc b/sql/dd_sql_view.cc
index d825dca..368675c 100644
--- a/sql/dd_sql_view.cc
+++ b/sql/dd_sql_view.cc
@@ -475,6 +475,10 @@ static bool open_views_and_update_metadata(
     if (check_schema_readonly(thd, view->db)) return true;
   }

+  LogErr(ERROR_LEVEL, ER_MYSQLBACKUP_MSG, "wait to get table shared lock");
+  DBUG_EXECUTE_IF("view_MDL_X_acquired", {sleep(15);});
+  LogErr(ERROR_LEVEL, ER_MYSQLBACKUP_MSG, "start to get table shared lock");
+
   for (auto view : *views) {
     View_metadata_updater_context vw_metadata_update_context(thd);

Hi Mr. chen,

Thank you for your feedback.

However, it is our policy that we check reports only on our own, unchanged code. Actually, it is also our policy that we check bug reports on our official binary releases only.

Can't repeat.