Non-blocking DDL

When a user executes a DDL statement on a table with uncommitted long transactions or large queries, the DDL statement keeps waiting for an MDL-X lock. GaussDB(for MySQL) gives MDL-X locks the highest priority. When a DDL statement is waiting for an MDL-X lock, all new transactions on the table are blocked. As a result, connections are congested, which may even cause the entire service system to break down. Non-blocking DDL allows new transactions to enter the table even if the MDL-X lock cannot be acquired, ensuring the stability of the entire service system.

Prerequisites

The kernel version is 2.0.54.240600 or later.

Constraints

Enabling non-blocking DDL lowers the priority of DDL statements, and increases the chance of DDL statement execution failure if an MDL-X lock cannot be acquired.

Non-blocking DDL is only supported for ALTER TABLE, RENAME TABLE, CREATE INDEX, DROP INDEX, and OPTIMIZE TABLE statements.

Parameters

You can set rds_nonblock_ddl_enable to enable non-blocking DDL, and then set rds_nonblock_ddl_retry_times, rds_nonblock_ddl_retry_interval, and rds_nonblock_ddl_lock_wait_timeout to specify the maximum number, interval, and timeout period of retries for acquiring an MDL-X lock, respectively.

**Table 1** Parameter description
Parameter	Level	Description
rds_nonblock_ddl_enable	Global, Session	Enables or disables non-blocking DDL. Value range: ON: Non-blocking DDL is enabled. OFF: Non-blocking DDL is disabled. Default value: OFF
rds_nonblock_ddl_lock_wait_timeout	Global, Session	Controls how long a statement waits to acquire the MDL-X lock before giving up. Value range: 1 to 31536000, in seconds Default value: 1
rds_nonblock_ddl_retry_interval	Global, Session	Controls the amount of time between retry attempts for acquiring the MDL-X lock. Value range: 1 to 31536000, in seconds Default value: 6
rds_nonblock_ddl_retry_times	Global, Session	Controls the maximum number of times to retry for acquiring the MDL-X lock. Value range: 0 to 31536000 Default value: 0 If this parameter is set to 0, the value is calculated based on the smaller value of the lock_wait_timeout and rds_ddl_lock_wait_timeout parameters. For statements that do not support the rds_ddl_lock_wait_timeout parameter, the value is calculated based on the lock_wait_timeout parameter.

Example

Use sysbench to create a test table sbtest1 and insert one million rows of data into the table.

./oltp_read_write.lua --mysql-host="cluster_address" --mysql-port="port" --mysql-user="username" --mysql-password="password" --mysql-db="sbtest" --tables=1 --table-size=1000000 --report-interval=1 --percentile=99 --threads=8 --time=6000 prepare

Use oltp_read_write.lua in sysbench to simulate user services.

./oltp_read_write.lua --mysql-host="cluster_address" --mysql-port="port" --mysql-user="username" --mysql-password="password" --mysql-db="sbtest" --tables=1 --table-size=1000000 --report-interval=1 --percentile=99 --threads=8 --time=6000 run

Start a new transaction on table sbtest1 but do not commit the transaction. The transaction holds the MDL lock of table sbtest1.
```
begin;
select * from sbtest1;
```
Start a new session, add columns to table sbtest1 when non-blocking DDL is enabled and disabled, and observe the TPS changes.
```
alter table sbtest1 add column d int;
```
Check the test results.
- When non-blocking DDL is disabled, the TPS keeps decreasing to zero. The default timeout period is 31,536,000 seconds, which severely affects user services.
- When non-blocking DDL is enabled, the TPS periodically decreases but does not decrease to zero, which has little impact on user services.