Description:
I executed the following statement on MySQL 8.0.35, and the ALTER statement continues to wait for the MDL lock：
create table t1 (c1 char(10));
xa start 'xatest';
insert into t1 values('a');
xa end 'xatest';
xa prepare 'xatest';
alter table t1 modify column c1 varchar(20); -- will wait
I checked the performance_schema.metadata_locks table and found that the threads waiting for the lock and the threads holding the lock are the same：
mysql> select * from 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 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| SCHEMA | test | NULL | NULL | 140313103995408 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | mdl.cc:3702 | 75 | 24 |
| TABLE | test | t1 | NULL | 140313104015552 | SHARED_WRITE | TRANSACTION | GRANTED | mdl.cc:3702 | 75 | 24 |
| GLOBAL | NULL | NULL | NULL | 140313093803408 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5474 | 75 | 25 |
| BACKUP LOCK | NULL | NULL | NULL | 140313104024368 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5481 | 75 | 25 |
| SCHEMA | test | NULL | NULL | 140313094550272 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5461 | 75 | 25 |
| TABLE | test | t1 | NULL | 140313104091232 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:6139 | 75 | 25 |
| TABLESPACE | NULL | test/t1 | NULL | 140313103956384 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:806 | 75 | 25 |
| TABLE | test | #sql-76b0_22 | NULL | 140313104111424 | EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:17097 | 75 | 25 |
| TABLE | test | t1 | NULL | 140313103012112 | SHARED_NO_WRITE | TRANSACTION | PENDING | mdl.cc:3761 | 75 | 25 |
| TABLE | performance_schema | metadata_locks | NULL | 140313240641488 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6139 | 74 | 124 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
However, when I executed the same statement on version 8.0.22, the ALTER statement resulted in an error.
mysql> alter table t1 modify column c1 varchar(20);
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the PREPARED state
How to repeat:
create table t1 (c1 char(10));
xa start 'xatest';
insert into t1 values('a');
xa end 'xatest';
xa prepare 'xatest';
alter table t1 modify column c1 varchar(20);
© 2025, Oracle Corporation and/or its affiliates
Description: I executed the following statement on MySQL 8.0.35, and the ALTER statement continues to wait for the MDL lock： create table t1 (c1 char(10)); xa start 'xatest'; insert into t1 values('a'); xa end 'xatest'; xa prepare 'xatest'; alter table t1 modify column c1 varchar(20); -- will wait I checked the performance_schema.metadata_locks table and found that the threads waiting for the lock and the threads holding the lock are the same： mysql> select * from 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 | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ | SCHEMA | test | NULL | NULL | 140313103995408 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | mdl.cc:3702 | 75 | 24 | | TABLE | test | t1 | NULL | 140313104015552 | SHARED_WRITE | TRANSACTION | GRANTED | mdl.cc:3702 | 75 | 24 | | GLOBAL | NULL | NULL | NULL | 140313093803408 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5474 | 75 | 25 | | BACKUP LOCK | NULL | NULL | NULL | 140313104024368 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5481 | 75 | 25 | | SCHEMA | test | NULL | NULL | 140313094550272 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5461 | 75 | 25 | | TABLE | test | t1 | NULL | 140313104091232 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:6139 | 75 | 25 | | TABLESPACE | NULL | test/t1 | NULL | 140313103956384 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:806 | 75 | 25 | | TABLE | test | #sql-76b0_22 | NULL | 140313104111424 | EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:17097 | 75 | 25 | | TABLE | test | t1 | NULL | 140313103012112 | SHARED_NO_WRITE | TRANSACTION | PENDING | mdl.cc:3761 | 75 | 25 | | TABLE | performance_schema | metadata_locks | NULL | 140313240641488 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6139 | 74 | 124 | +-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ However, when I executed the same statement on version 8.0.22, the ALTER statement resulted in an error. mysql> alter table t1 modify column c1 varchar(20); ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the PREPARED state How to repeat: create table t1 (c1 char(10)); xa start 'xatest'; insert into t1 values('a'); xa end 'xatest'; xa prepare 'xatest'; alter table t1 modify column c1 varchar(20);