Bug #108935 Don't show the right type of MDL in performance_schema.metadata_locks table
Submitted: 31 Oct 2022 11:31 Modified: 1 Nov 2022 13:09
Reporter: zhang yinggang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.* OS:Any
Assigned to: CPU Architecture:Any

[31 Oct 2022 11:31] zhang yinggang
Description:
During DDL, thread will acquire SHARED_UPGRADABLE lock first, and it will  upgrade SHARED_UPGRADABLE lock to EXCLUSIVE lock before prepare phase. However, the type of MDL in performance_schema.metadata_locks table is not updated.

How to repeat:
The following test runs in DEBUG mode.

Connect 1:
create database test;
use test;
create table t1(a int, b char(1));
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade WAIT_FOR go';
alter table t1 add index b1(b(3)),algorithm = inplace;

connect 2:
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------------------+
| Id | User            | Host            | db   | Command | Time | State                                                    | Info                                                   |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  |  379 | Waiting on empty queue                                   | NULL                                                   |
|  9 | root            | localhost:41208 | test | Query   |    4 | debug sync point: alter_table_inplace_after_lock_upgrade | alter table t1 add index b11(b(3)),algorithm = inplace |
| 10 | root            | localhost:41355 | test | Query   |    0 | init                                                     | show processlist                                       |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+--------------------------------------------------------+
3 rows in set (0.00 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        |       140418973905024 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5475   |              49 |             23 |
| BACKUP LOCK | NULL               | NULL           | NULL        |       140418973905104 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5482   |              49 |             23 |
| SCHEMA      | test               | NULL           | NULL        |       140418973210096 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5462   |              49 |             23 |
| TABLE       | test               | t1             | NULL        |       140418973904944 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6084  |              49 |             23 |
| TABLESPACE  | NULL               | test/t1        | NULL        |       140418973904864 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | lock.cc:806        |              49 |             23 |
| TABLE       | test               | #sql-c302_9    | NULL        |       140418973224976 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:17023 |              49 |             23 |
| TABLE       | performance_schema | metadata_locks | NULL        |       140418949080400 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6084  |              50 |             15 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
7 rows in set (0.00 sec)

  The type of MDL whose SOURCE is sql_parse.cc:6084 should be EXCLUSIVE instead of SHARED_UPGRADABLE after lock_upgrade.

Suggested fix:
Reason:
At function MDL_context::upgrade_shared_lock():
  new ticket which represents EXCLUSIVE lock is merged into the old ticket which represents SHARED_UPGRADABLE lock. After that behavior, this function forgets to calling psi interface to update MDL's type in performance_schema.metadata_locks table.

How to fix:
  Add an interface such as pfs_set_metadata_lock_type() to update the MDL's type in performance_schema.metadata_locks table. You may need to change the PSI_mdl_service_t from V2 to V3 to ensure the compatibility of different version.
[1 Nov 2022 13:09] MySQL Verification Team
Hi Mr. yinggang,

Thank you for your bug report.

However, it is not a bug.

As you can see in the result set , that DDL is holding both types of lock, which means that upgrade has been performed.

Next, you have not stated which 8.0 release are you using.

Next, your test case is not in MTR format, nor in the standard SQL test case format.

Not a bug.