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.