Control DDL lock wait timeouts - ApsaraDB RDS - Alibaba Cloud Documentation Center

ApsaraDB RDS for MySQL supports a feature to control Data Definition Language (DDL) lock wait timeouts. This feature uses hints to control the timeout period for a DDL thread waiting for a metadata lock (MDL). This prevents session blocking and connection buildup caused by long waits for MDLs.

Feature description

Introduction: In MySQL, the lock_wait_timeout parameter controls the MDL timeout. For DDL operations that require precise control over wait behavior, you must explicitly modify this parameter before execution. ApsaraDB RDS for MySQL provides a simpler, more intuitive way to control the MDL lock wait time for DDL statements. You can directly specify the MDL lock wait time in a statement using /*+ WAIT(n) */ and /*+ NO_WAIT() */.

Advantage: Using hints provides better compatibility than introducing new syntax. Hints are written to the binary logging (binlog) file as comments. In downstream instances or subscription services that do not support this feature, the parser automatically ignores the hints. The hints are treated as regular comments and do not cause SQL parsing errors or replication interruptions.

Applicability

You can use the DDL lock wait timeout control feature only when the database major version is MySQL 8.0 and the minor engine version is 20250531 or later. If your instance does not meet the version requirements, you can upgrade the minor engine version or the database major version.
This feature has the following limits:
- It supports only the CREATE, DROP, ALTER, RENAME, TRUNCATE, and OPTIMIZE operations.
- This feature does not take effect on a secondary node or read-only instance during data synchronization from the primary node.
- When you set the MDL wait time with /*+ WAIT(n) */, the value of n (wait time) must be in the range of [0, 31536000]. The unit is seconds.

Usage

When you run CREATE, DROP, ALTER, RENAME, TRUNCATE, and OPTIMIZE operations, add /*+ WAIT(n) */ or /*+ NO_WAIT() */ after the keyword to control the MDL lock wait time. The following examples show how to use the hints:

-- Create table t1 and set the MDL timeout to 10s.
CREATE /*+ WAIT(10) */ TABLE t1(a INT);

-- Add a column to table t1 and set the MDL timeout to no wait.
ALTER /*+ NO_WAIT() */ TABLE t1 ADD COLUMN b INT;

-- Drop table t1 and set the MDL timeout to 1s.
DROP /*+ WAIT(1) */ TABLE t1;

Result

Test method

Start a session. Create a table named t1 and hold an MDL S lock on the table.
```
CREATE TABLE t1(a INT);
LOCK TABLE t1 READ;
```
Start another session. Use /*+ WAIT(n) */ and /*+ NO_WAIT() */ to perform schema changes and delete operations. Then, observe the statement execution.
```
ALTER /*+ NO_WAIT() */ TABLE t1 ADD COLUMN b INT;
DROP /*+ WAIT(1) */ TABLE t1;
```

Test result

After you use /*+ WAIT(n) */ and /*+ NO_WAIT() */ to control the MDL timeout, the schema change and delete operations fail within the expected time.

mysql> ALTER /*+ NO_WAIT() */ TABLE t1 ADD COLUMN b INT;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DROP /*+ WAIT(1) */ TABLE t1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction