Bug #96237 PFS didn't display metadata lock correctly
Submitted: 17 Jul 2019 15:08 Modified: 2 Mar 2021 14:17
Reporter: Zhao Jianwei Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.16, 5.7.26 OS:Any
Assigned to: CPU Architecture:Any

[17 Jul 2019 15:08] Zhao Jianwei 
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.  

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

mysql> lock table t 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                     |       106102880181920 | INTENTION_EXCLUSIVE  | STATEMENT     | GRANTED     | sql_base.cc:5425  |              58 |             16 |
| SCHEMA            | test               | NULL           | NULL                     |       106102880182048 | INTENTION_EXCLUSIVE  | TRANSACTION   | GRANTED     | sql_base.cc:5412  |              58 |             16 |
| TABLE             | test               | t              | NULL                     |       106102880182176 | SHARED_NO_READ_WRITE | TRANSACTION   | GRANTED     | sql_parse.cc:6024 |              58 |             16 |

Suggested fix:
In source code function "MDL_ticket_store::move_all_to_explicit_duration()"  should call PFS storage engine interface to change the MDL LOCK_DURATION.
[18 Jul 2019 6:49] MySQL Verification Team 
Hello Zhao Jianwei,

Thank you for the report.

regards,
Umesh
[2 Mar 2021 14:17] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.24.

The Performance Schema metadata_locks table could show incorrect
DURATION values, such as when a metadata lock taken for a TRANSACTION
duration was later modified to EXPLICIT duration by a RENAME TABLE
operation.