Bug #116625 The LOCK_TYPE in PFS.metadata_locks not change after MDL upgrade or downgrade

Description:
During the DDL progress, the MDL can upgrade or downgrade multiple times, but I found the LOCK_TYPE in table performance_schema.metadata_locks not change after MDL upgrade or downgrade.

How to repeat:
Please see the test case.

Test case

Attachment: pfs_metadata_lock_type_not_change.test (application/octet-stream, text), 1.30 KiB.

Hello George Ma,

Thank you for the report and feedback.

regards,
Umesh

-- 8.0.40

./mtr --nocheck-testcases pfs_metadata_lock_type_not_change --debug-server
Logging: ./mtr  --nocheck-testcases pfs_metadata_lock_type_not_change --debug-server
MySQL Version 8.0.40
Checking supported features
 - Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.40/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
create table t1 (id int key, c1 int);
insert into t1 values (1, 10);
SET DEBUG_SYNC='RESET';
SET DEBUG_SYNC= 'alter_opened_table WAIT_FOR continue1';
SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade WAIT_FOR continue2';
SET DEBUG_SYNC= 'alter_table_before_main_binlog WAIT_FOR continue3';
# Sending:
alter table t1 change column c1 c1 bigint;
select * from performance_schema.metadata_locks where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 't1';
OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COLUMN_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_DURATION	LOCK_STATUS	SOURCE	OWNER_THREAD_ID	OWNER_EVENT_ID
TABLE	test	t1	NULL	139791321176000	SHARED_UPGRADABLE	TRANSACTION	GRANTED	sql_parse.cc:6142	48	21002
SET DEBUG_SYNC= 'now SIGNAL continue1';
select * from performance_schema.metadata_locks where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 't1';
OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COLUMN_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_DURATION	LOCK_STATUS	SOURCE	OWNER_THREAD_ID	OWNER_EVENT_ID
TABLE	test	t1	NULL	139791321176000	SHARED_UPGRADABLE	TRANSACTION	GRANTED	sql_parse.cc:6142	48	21002
SET DEBUG_SYNC= 'now SIGNAL continue2';
select * from performance_schema.metadata_locks where OBJECT_TYPE = 'TABLE' and OBJECT_NAME = 't1';
OBJECT_TYPE	OBJECT_SCHEMA	OBJECT_NAME	COLUMN_NAME	OBJECT_INSTANCE_BEGIN	LOCK_TYPE	LOCK_DURATION	LOCK_STATUS	SOURCE	OWNER_THREAD_ID	OWNER_EVENT_ID
TABLE	test	t1	NULL	139791321176000	SHARED_UPGRADABLE	TRANSACTION	GRANTED	sql_parse.cc:6142	48	21002
SET DEBUG_SYNC= 'now SIGNAL continue3';
# Reaping ALTER TABLE ...
SET DEBUG_SYNC= 'RESET';
drop table t1;
[ 50%] main.pfs_metadata_lock_type_not_change    [ pass ]   3108
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 3.108 of 21 seconds executing testcases

Completed: All 2 tests were successful.

The fix patch.

Attachment: fix_metadata_lock_type_not_change.patch (application/octet-stream, text), 8.37 KiB.

Hello George,

Thank you for the  contribution.
Please ensure to re-upload the suggested patch via contribution tab of this bug page otherwise we may not be able to use it. Thank you.

regards,
Umesh

The fix patch.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix_metadata_lock_type_not_change.patch (application/octet-stream, text), 8.37 KiB.

Thanks for the patch.

Instrumentation was indeed missing in sql/mdl.cc,
to let the performance schema know about the updated lock type.

Posted by developer:
 
Added the following note to the MySQL Server 8.0.41, 8.4.4, and 9.2.0 release notes:
	
 Under certain circumstances a metadata lock can be upgraded or downgraded to a different LOCK_TYPE. 
 This change was not reflected in the METADATA_LOCKS table of the Performance Schema.

Our thanks to George Ma and the Alibaba team for the contribution.