Use nonblocking DDL statements to improve DDL execution stability - PolarDB

ALTER TABLE on a large table can lock out all new transactions for minutes or hours. When a blocking DDL statement holds the MDL-X lock request queue open, every transaction touching that table stacks up behind it — and if the queue grows long enough, connection exhaustion can bring down your entire application. Nonblocking DDL eliminates this risk: the DDL statement retries for the lock in short bursts, releasing its queue position between attempts so new transactions continue to run.

How it works

MySQL's metadata locking (MDL) system protects tables during schema changes. A blocking DDL statement requests an exclusive MDL-X lock and keeps the request queue blocked until it acquires the lock. Because MDL-X has the highest priority, every new transaction that touches the table stacks up behind the DDL statement — even if the DDL itself is still waiting for an older transaction to finish.

Nonblocking DDL changes this behavior:

The DDL statement attempts to acquire the MDL-X lock within a short timeout (loose_polar_nonblock_ddl_lock_wait_timeout, default: 1 second).
If the lock is unavailable, the DDL statement releases its queue position entirely. Because the queue is no longer blocked, pending and incoming transactions can acquire their own locks and proceed normally.
After a configurable interval (loose_polar_nonblock_ddl_retry_interval, default: 6 seconds), the DDL statement retries.
Steps 2–3 repeat up to loose_polar_nonblock_ddl_retry_times attempts.

During retry intervals, the table remains fully accessible. Transactions per second (TPS) may dip briefly when the DDL acquires the lock, but never drops to zero.

Note

Nonblocking DDL has lower priority than blocking DDL and a higher chance of failing to acquire the MDL-X lock. Use it when business continuity matters more than the speed of the schema change.

Supported versions

Nonblocking DDL requires one of the following:

PolarDB for MySQL 8.0.1 at revision version 8.0.1.1.29 or later
PolarDB for MySQL 8.0.2 at revision version 8.0.2.2.12 or later

To check your cluster's revision version, see Engine versions 5.6, 5.7, and 8.0.

Supported DDL statements

The statements that support nonblocking DDL depend on your revision version.

Revision version	Supported statements	Notes
8.0.1.1.29 or later, or 8.0.2.2.12	`ALTER TABLE` only	To defragment an InnoDB table, use `ALTER TABLE table_name ENGINE=InnoDB` instead of `OPTIMIZE TABLE table_name`
8.0.2.2.13 or later	`ALTER TABLE`, `OPTIMIZE TABLE`, `TRUNCATE TABLE`	—

Enable nonblocking DDL

All four parameters are session-level. Set them in the same session before running the DDL statement.

Step 1: Enable nonblocking DDL mode

SET SESSION loose_polar_nonblock_ddl_mode = ON;

loose_polar_nonblock_ddl_mode is OFF by default.

Step 2: Configure retry behavior

SET SESSION loose_polar_nonblock_ddl_retry_times = 4194304;
SET SESSION loose_polar_nonblock_ddl_retry_interval = 6;
SET SESSION loose_polar_nonblock_ddl_lock_wait_timeout = 1;

Parameter	Default	Valid values	Unit	When to change
`loose_polar_nonblock_ddl_retry_times`	0 (derived from `lock_wait_timeout`)	0–31536000	—	Set to `4194304` for long-running schema changes on large tables
`loose_polar_nonblock_ddl_retry_interval`	6	1–31536000	Seconds	Increase to reduce lock contention; decrease to finish the DDL faster
`loose_polar_nonblock_ddl_lock_wait_timeout`	1	1–31536000	Seconds	Increase if the lock is not acquired often enough under heavy write load

For more information on applying these parameters, see Configure cluster and node parameters.

Step 3: Run the DDL statement

ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3);

The statement runs with nonblocking behavior for the current session. Other sessions are unaffected.

Performance test

The following tests compare blocking DDL, nonblocking DDL, and the gh-ost online schema change tool using SysBench. For more information about SysBench, see OLTP performance test.

Test environment: PolarDB for MySQL 8.0, Cluster Edition, 8 CPU cores, 64 GB memory.

Setup

Create a table with 1 million rows:

./oltp_read_write.lua --mysql-host="Cluster endpoint" --mysql-port="Port number" \
  --mysql-user="Username" --mysql-password="Password" \
  --mysql-db="sbtest" --tables=1 --table-size=1000000 \
  --report-interval=1 --percentile=99 --threads=8 --time=6000 prepare

Simulate a read/write workload:

./oltp_read_write.lua --mysql-host="Cluster endpoint" --mysql-port="Port number" \
  --mysql-user="Username" --mysql-password="Password" \
  --mysql-db="sbtest" --tables=1 --table-size=1000000 \
  --report-interval=1 --percentile=99 --threads=8 --time=6000 run

Hold an MDL lock by starting a transaction and leaving it open:
```
/* Session 1 */
BEGIN;
SELECT * FROM sbtest1;
```

In a second session, add a column to the table:

/* Session 2 */
ALTER TABLE sbtest1 ADD COLUMN d INT;

Repeat the schema change using gh-ost (requires binary logging — see Enable binary logging):

./gh-ost --assume-rbr --user="Username" --password="Password" \
  --host="Cluster endpoint" --port="Port number" \
  --database="sbtest" --table="sbtest1" --alter="ADD COLUMN d INT" \
  --allow-on-master --aliyun-rds \
  --initially-drop-old-table --initially-drop-ghost-table --execute

TPS impact

Blocking DDL (nonblocking DDL disabled) — TPS drops to zero and stays there until the DDL completes.
Nonblocking DDL — TPS dips periodically as the DDL retries the lock but never reaches zero. Business impact is minimal.
gh-ost — TPS drops to zero in periodic spikes. gh-ost works by creating a ghost table, applying changes there, then atomically renaming the ghost table into place (the cut-over step). This cut-over requires a brief table lock, which causes the periodic TPS drops.