Updated on 2024-09-06 GMT+08:00

DDL Fast Timeout

For some specific DDL operations, you can configure their metadata lock (MDL) waiting time, preventing subsequent DML operations from being blocked.

Constraints

  • The kernel version is 2.0.45.230900 or later.
  • Currently, the following DDL operations are supported: ALTER TABLE, CREATE INDEX, and DROP INDEX.

Enabling DDL Fast Timeout

Table 1 Parameter description

Parameter

Level

Description

rds_ddl_lock_wait_timeout

Global, Session

Defines how long that a DDL operation waits for a lock in the current session or global sessions.

  • Value range: 1 to 31536000 (s). Default value: 31536000, indicating that the function is disabled.
  • The actual lock wait timeout for DDL operations is the smaller value between lock_wait_timeout and this parameter value.
  • The actual table lock timeout during DDL execution at the InnoDB layer is the minimum value of innodb_lock_wait_timeout and this parameter value. Row locks are not considered.

Example

  1. Start a client and add a lock for tables.
    Figure 1 Adding a lock
  2. Run the following command to check the status of the DDL fast timeout function

    show variables like "%rds_ddl_lock_wait_timeout%";

    Figure 2 Querying the status of the DDL fast timeout function

    As shown in the preceding figure, the value of rds_ddl_lock_wait_timeout is 31536000 (default value). The function is disabled. The subsequent operations will wait for a long time.

    To enable function, referring to 3.

  3. Run the following command to set rds_ddl_lock_wait_timeout.

    set rds_ddl_lock_wait_timeout=1;

    Figure 3 Configuring parameters
  4. Run the following command to create an index. It is found that the DDL operation times out quickly.
    alter table lzk.t_lzk drop index indexa;
    Figure 4 Creating an index