DDL lock wait timeout control

Overview

Introduction: In MySQL, the lock_wait_timeout parameter controls the MDL lock timeout. To precisely control the wait time for a specific DDL operation, you must modify this parameter before execution. RDS for MySQL offers a simpler way to control the MDL lock wait time for individual DDL statements: you can specify the timeout directly in the statement by using the /*+ WAIT(n) */ and /*+ NO_WAIT() */ hints.

Benefits: Using a hint offers better compatibility than introducing new syntax. The hint is written to the binlog as a comment. Downstream instances or subscription services that do not support this feature will automatically ignore the hint, treating it as a regular comment. This prevents SQL parsing errors and replication interruptions.

Applicability

This feature is available only for instances that meet the following version requirements. If your instance does not meet these requirements, you can update the minor engine version or upgrade the major database version.

• MySQL 8.4

• MySQL 8.0 with a minor engine version of 20250531 or later

The following limitations apply when you use this feature:

• Only CREATE, DROP, ALTER, RENAME, TRUNCATE, and OPTIMIZE operations are supported.

• The hint does not take effect on secondary nodes or read-only instances during replication from the primary node.

• In the /*+ WAIT(n) */ hint, n specifies the wait time in seconds and must be in the range [0, 31536000].

Syntax

For CREATE, DROP, ALTER, RENAME, TRUNCATE, and OPTIMIZE operations, add the /*+ WAIT(n) */ or /*+ NO_WAIT() */ hint immediately after the keyword to control the MDL lock wait time. Examples:

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

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

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

Example

Test procedure

1. Open a session, create the table t1, and acquire an MDL S lock on the table.

   CREATE TABLE t1(a INT);
   LOCK TABLE t1 READ;

2. In a second session, attempt to alter and drop the table by using the /*+ WAIT(n) */ and /*+ NO_WAIT() */ hints, and observe the results.

   ALTER /*+ NO_WAIT() */ TABLE t1 ADD COLUMN b INT;
   DROP /*+ WAIT(1) */ TABLE t1;

Test results

With the /*+ WAIT(n) */ and /*+ NO_WAIT() */ hints, DDL operations fail quickly and predictably if a lock is not acquired within the specified timeout.

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