Description:
there is a metadata lock (MDL) acquisition order inconsistency between tablespace-level operations and table-level DDLs. When ALTER TABLESPACE ... RENAME and CREATE INDEX ... ALGORITHM=INPLACE run concurrently, they can trigger a deterministic deadlock.
[Lock Acquisition Order Analysis]
The deadlock is caused by the following "AB-BA" conflict in the MDL subsystem:
Transaction A (ALTER TABLESPACE RENAME):
Step 1: Acquires an Exclusive (X) lock on the TABLESPACE object.
Step 2: Attempts to acquire an Exclusive (X) lock on the TABLE object (to update the Data Dictionary).
Current State: Holding Tablespace(X), waiting for Table(X).
Transaction B (CREATE INDEX ... ALGORITHM=INPLACE):
Step 1: During the open_tables phase, it acquires a Shared Upgradable (SU) lock on the TABLE object.
Step 2: Then, it calls lock_tablespace_names to acquire an Intention Exclusive (IX) lock on the TABLESPACE object.
Current State: Holding Table(SU), waiting for Tablespace(IX).
[Deadlock Cycle]
Transaction A is blocked by Transaction B (X-lock on Table is incompatible with SU-lock).
Transaction B is blocked by Transaction A (IX-lock on Tablespace is incompatible with X-lock).
How to repeat:
create database test;
use test;
CREATE TABLESPACE ts_0 ADD DATAFILE 'ts_0.ibd' ENGINE=InnoDB;
CREATE TABLE t0 (a INT) TABLESPACE ts_0 ENGINE=InnoDB;
# session A
use test;
DELIMITER //
CREATE PROCEDURE loop_rename_ts()
BEGIN
WHILE 1 DO
ALTER TABLESPACE ts_0 RENAME TO ts_new;
ALTER TABLESPACE ts_new RENAME TO ts_0;
END WHILE;
END //
DELIMITER ;
CALL loop_rename_ts();
# session B
use test;
DELIMITER //
CREATE PROCEDURE loop_index_ops()
BEGIN
WHILE 1 DO
CREATE INDEX i_test ON t0(a) ALGORITHM=INPLACE;
DROP INDEX i_test ON t0;
END WHILE;
END //
DELIMITER ;
CALL loop_index_ops();
© 2026, Oracle Corporation and/or its affiliates
Description: there is a metadata lock (MDL) acquisition order inconsistency between tablespace-level operations and table-level DDLs. When ALTER TABLESPACE ... RENAME and CREATE INDEX ... ALGORITHM=INPLACE run concurrently, they can trigger a deterministic deadlock. [Lock Acquisition Order Analysis] The deadlock is caused by the following "AB-BA" conflict in the MDL subsystem: Transaction A (ALTER TABLESPACE RENAME): Step 1: Acquires an Exclusive (X) lock on the TABLESPACE object. Step 2: Attempts to acquire an Exclusive (X) lock on the TABLE object (to update the Data Dictionary). Current State: Holding Tablespace(X), waiting for Table(X). Transaction B (CREATE INDEX ... ALGORITHM=INPLACE): Step 1: During the open_tables phase, it acquires a Shared Upgradable (SU) lock on the TABLE object. Step 2: Then, it calls lock_tablespace_names to acquire an Intention Exclusive (IX) lock on the TABLESPACE object. Current State: Holding Table(SU), waiting for Tablespace(IX). [Deadlock Cycle] Transaction A is blocked by Transaction B (X-lock on Table is incompatible with SU-lock). Transaction B is blocked by Transaction A (IX-lock on Tablespace is incompatible with X-lock). How to repeat: create database test; use test; CREATE TABLESPACE ts_0 ADD DATAFILE 'ts_0.ibd' ENGINE=InnoDB; CREATE TABLE t0 (a INT) TABLESPACE ts_0 ENGINE=InnoDB; # session A use test; DELIMITER // CREATE PROCEDURE loop_rename_ts() BEGIN WHILE 1 DO ALTER TABLESPACE ts_0 RENAME TO ts_new; ALTER TABLESPACE ts_new RENAME TO ts_0; END WHILE; END // DELIMITER ; CALL loop_rename_ts(); # session B use test; DELIMITER // CREATE PROCEDURE loop_index_ops() BEGIN WHILE 1 DO CREATE INDEX i_test ON t0(a) ALGORITHM=INPLACE; DROP INDEX i_test ON t0; END WHILE; END // DELIMITER ; CALL loop_index_ops();