Bug #108069 PFS didn't display metadata lock correctly
Submitted: 4 Aug 2022 8:50 Modified: 4 Aug 2022 11:42
Reporter: Shun Yi Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Enterprise Monitor Severity:S3 (Non-critical)
Version:8.0.30 OS:Any
Assigned to: CPU Architecture:Any

[4 Aug 2022 8:50] Shun Yi
Description:
Hi, guys

When I  executed LOCK TABLE command, the table of PERFORMANCE_SCHEMA.metadata_locks displayed the MDL lock duration as TRANSACTION,  But actually it should be EXPLICIT.  

The MDL lock duration can only be changed to EXPLICIT when the RENAME TABLE command is executed after LOCK TABLE, which is fixed in Bug #96237.

How to repeat:
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> lock table t1 write;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           | NULL        |       105553176921904 | INTENTION_EXCLUSIVE  | STATEMENT     | GRANTED     | sql_base.cc:5476  |              51 |             14 |
| SCHEMA      | test               | NULL           | NULL        |       105553176920224 | INTENTION_EXCLUSIVE  | TRANSACTION   | GRANTED     | sql_base.cc:5463  |              51 |             14 |
| TABLE       | test               | t1             | NULL        |       105553176920304 | SHARED_NO_READ_WRITE | TRANSACTION   | GRANTED     | sql_parse.cc:6013 |              51 |             14 |
| TABLESPACE  | NULL               | test/t1        | NULL        |       105553176981936 | INTENTION_EXCLUSIVE  | TRANSACTION   | GRANTED     | lock.cc:808       |              51 |             14 |
| TABLE       | performance_schema | metadata_locks | NULL        |       105553176985760 | SHARED_READ          | TRANSACTION   | GRANTED     | sql_parse.cc:6013 |              51 |             17 |
+-------------+--------------------+----------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
5 rows in set (0.00 sec)

mysql> alter table t1 rename t2;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE                 | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           | NULL        |       105553176921904 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5476       |              51 |             14 |
| SCHEMA      | test               | NULL           | NULL        |       105553176920224 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5463       |              51 |             14 |
| TABLE       | test               | t2             | NULL        |       105553176728656 | EXCLUSIVE           | EXPLICIT      | GRANTED     | sql_alter.cc:206       |              51 |             18 |
| TABLESPACE  | NULL               | test/t2        | NULL        |       105553176738976 | EXCLUSIVE           | EXPLICIT      | GRANTED     | dictionary_impl.cc:471 |              51 |             19 |
| TABLE       | performance_schema | metadata_locks | NULL        |       105553176981936 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6013      |              51 |             21 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
5 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

Suggested fix:
Function: MDL_ticket_store::move_all_to_explicit_duration()
[4 Aug 2022 11:42] MySQL Verification Team
Hi Mr. Yi,

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

You have already submitted the same bug under this report:

https://bugs.mysql.com/bug.php?id=108050

Thank you for your interest in MySQL.