MySQL Bugs: #115540: Showing details about metadata deadlock

Bug #115540	Showing details about metadata deadlock
Submitted:	8 Jul 2024 9:52	Modified:	9 Jul 2024 13:16
Reporter:	Yunus UYANIK	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	8.0.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	metadata deadlock

Description:
The problem is when we have metadata deadlock, we can’t catch any information about it. Even the message that we received is the same like InnoDB deadlock. So if there is any way to figure out which deadlock happened and which queries causing the metadata deadlock that would be good.

How to repeat:
session1:
mysql [localhost:8037] {msandbox} ((none)) > begin;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8037] {msandbox} ((none)) > select count(1) from test.t3;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+
1 row in set (0.01 sec)

session2:
mysql [localhost:8037] {msandbox} ((none)) > alter table test.t3 add column d varchar(100), ALGORITHM=INPLACE;

session1:
mysql [localhost:8037] {msandbox} ((none)) > update test.t3 set tcol01=10 where tcol2=136;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

session2:
mysql [localhost:8037] {msandbox} ((none)) > alter table test.t3 add column d varchar(100), ALGORITHM=INPLACE;
Query OK, 0 rows affected (13.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

Here is the output SHOW ENGINE INNODB STATUS;

mysql [localhost:8037] {msandbox} ((none)) > SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2024-07-04 12:47:20 139963925579520 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 136 srv_active, 0 srv_shutdown, 3599 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 135
OS WAIT ARRAY INFO: signal count 127
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1572
Purge done for trx's n:o < 1571 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421439378787544, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421439378788352, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421439378786736, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421439378785928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

Hi Mr. UYANIK,

Thank you for your bug report.

However, this feature exists for a very long time.

Take a look at the  Performance Schema, with it's  metadata_locks table.

Not a bug.

Thanks for your response, and I agree it's not a bug, but metadata_locks table only showing the time being happening, so it's not useful for dig into like we do have deadlocks log for InnoDB. 
Do you mean that there is other report like (duplicate) this one?

Hi Mr. Matonin,

Actually that table has all of the info that you require.

MDL's do have deadlocks ...... but ...... MDL do not have and can not have all history of what ever happened .....  They are there only for the protection of DDL's against the undesirable changes. They simply have to take as little time as possible, leaving the rest to the transactional engine(s).

They also have to be very, very fast, hence ..... if you want to check whether you can run more DDL's or DML's on the same table that is undergoing DDL, you have to query that table more often.

Not a bug.