Online Copy DDL, MODIFY COLUMN without table locking, partitioned table conversion without table locking, Copy DDL optimization-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for MySQL introduces the Online Copy DDL feature, which effectively reduces the business disruptions caused by traditional Copy DDL operations (such as MODIFY COLUMN and conversions between standard tables and partitioned tables) that hold an MDL lock throughout execution.

Background information

During standard Copy DDL execution, MySQL holds an MDL-SNW (Shared No Write) lock, continuously blocking all write operations (INSERT/UPDATE/DELETE) on the table. This can cause connection buildup and, in severe cases, system collapse. Online Copy DDL optimizes the locking mechanism during the data copy phase, allowing concurrent read and write operations while ensuring data consistency. This significantly improves business availability during DDL execution.

Scope

PolarDB for MySQL version 8.0.2 with revision 8.0.2.2.33.1 or later. You can query the version number to confirm the cluster version.

Note

The Online Copy DDL feature is currently in canary release. To use this feature, go to the Quota Center, find the quota name by the quota ID polarm_82_online_copy_ddl, and then click Request in the Operation column to enable the feature. When you submit the request, provide the ID of the cluster that you want to enable (in the format of pc-xxx).

Limits

Supported operations:

Conversions between standard tables and partitioned tables.
MODIFY COLUMN statements. Modifying the column position at the same time is not supported.

Unsupported operations or objects:

Tables that contain JSON columns.
Tables that contain spatial indexes (SPATIAL INDEX).
Tables that contain Global Secondary Indexes (GSIs).
Hybrid partitioned tables.
What is Multi-Master Cluster (Limitless).
Sessions that have executed LOCK TABLES on the table.
DDL statements that explicitly specify ALGORITHM=COPY with LOCK=SHARED or LOCK=EXCLUSIVE.

Note

Before you execute a DDL operation, you can use the EXPLAIN DDL feature to directly determine whether the current DDL operation supports Online Copy DDL.

Usage

After you apply for a quota, the following parameters are automatically enabled for you to use Online Copy DDL. You cannot manually modify these parameters at this time.

Parameter

Level

Description

loose_polar_support_online_copy_ddl_algorithm

Global

The main switch of Online Copy DDL. Valid values:

ON: enables Online Copy DDL.
OFF (default): disables Online Copy DDL.

loose_polar_support_online_copy_ddl_algorithm_for_modify

Global

The Online Copy DDL switch that applies only to MODIFY COLUMN. Valid values:

ON: enables the Online capability for MODIFY COLUMN.
OFF (default): disables the feature.

Method 1: Explicitly specify the algorithm (recommended)

Specify ALGORITHM=COPY, LOCK=NONE to force Online Copy DDL:

CREATE TABLE t(a int);
ALTER TABLE t MODIFY COLUMN a BIGINT DEFAULT NULL, ALGORITHM=COPY, LOCK=NONE;

Note

When you use the preceding statements, if the ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. error is returned, the current column type modification cannot be performed with the Online Copy DDL algorithm. Check whether the loose_polar_support_online_copy_ddl_algorithm parameter is enabled and carefully review the limits.

Method 2: Automatically select the optimal algorithm (default behavior)

If you do not specify an algorithm or use DEFAULT, the system automatically selects the most suitable execution method:

-- Method 1: Explicitly specify DEFAULT
ALTER TABLE t MODIFY COLUMN a BIGINT DEFAULT NULL, ALGORITHM=DEFAULT, LOCK=DEFAULT;

-- Method 2: Omit the clauses entirely (equivalent)
ALTER TABLE t MODIFY COLUMN a BIGINT DEFAULT NULL;

Note

The system first attempts Online Copy DDL. If the conditions are not met, the system automatically falls back to traditional Copy DDL (which locks the table) to ensure that the DDL operation can eventually complete.