PolarDB:DDL atomicity

How two-phase DDL works

When you run a supported DDL statement, two-phase DDL does the following:

1. Constructs a commit barrier on all involved DNs before any physical DDL is applied.

2. Applies the physical DDL to each shard under barrier control.

3. If all shards succeed, commits atomically across all shards.

4. If any shard fails, rolls back the change on all shards before reaching the commit point.

This ensures that at no point can a partial schema change be visible — either all shards reflect the new schema, or none do.

Supported statements

Two-phase DDL applies only to the following physical DDL statements:

Two-phase DDL does not apply to tables that contain physical partitions, foreign keys, or full-text indexes.

Limitations

Version requirement

Two-phase DDL requires kernel version 5.4.18-17108394 or later. To verify support, run:

show variables like "enable_two_phase_ddl";

If no result is returned, the feature is not supported by your instance.

Topology constraints

Locking behavior

• For physical DDL statements of the COPY type, two-phase DDL changes the locking scope from individual shards to the entire logical table.

• Data dictionary modification during the commit phase is a global, mutually exclusive operation and is performed while the table is locked.

If your workload is sensitive to table locking, use the Online Modify Column (OMC) feature instead. OMC changes column types without locking tables, runs as a logically executed operation, and guarantees atomicity. For details, see Change the column types without locking tables.

Check and configure two-phase DDL

Two-phase DDL is enabled by default.

Check whether the feature is supported and its current state:

show variables like "enable_two_phase_ddl";

Expected output when enabled:

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| enable_two_phase_ddl | ON    |
+----------------------+-------+

Enable two-phase DDL:

set global enable_two_phase_ddl = true;

Disable two-phase DDL:

set global enable_two_phase_ddl = false;

Monitor DDL jobs

Use the following statements to inspect DDL job status and verify shard consistency.

SHOW DDL RESULT output includes the following columns:

Example: atomic rollback on constraint violation

This example shows how two-phase DDL prevents partial failure when a column type change is incompatible with existing data.

Step 1. Create a logical table t1 partitioned by key a across 16 shards.

CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 16;

Step 2. Insert rows, including one that will violate a tinyint range constraint.

insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,999999);

Step 3. Attempt to narrow column b from int to tinyint. The value 999999 exceeds the tinyint range, so the physical DDL fails on the shard holding that row. Because two-phase DDL is enabled, the change is rolled back on all shards.

ALTER TABLE t1 MODIFY COLUMN b tinyint;

Expected error:

ERROR 4700 (HY000): ERR-CODE: [TDDL-4700][ERR_SERVER] server error by Failed to execute the DDL task.
Caused by: ERR-CODE: [TDDL-4636][ERR_DDL_JOB_ERROR] failed to execute on group(WUMU_P00000_GROUP):
/* drds_two_phase_ddl(1717761420461916160)*/ALTER TABLE `t1_KdRs_00004` MODIFY COLUMN b tinyint,
Caused by: Data truncation: Out of range value for column 'b' at row 1.

Step 4. Verify that no DDL job is still running, and review the job history.

show ddl;
-- Empty set (0.01 sec)

show ddl result;

Expected output:

+---------------------+-------------+-------------+-------------+--------------+---------------------------------------------------------------------+
| JOB_ID              | SCHEMA_NAME | OBJECT_NAME | DDL_TYPE    | RESULT_TYPE  | RESULT_CONTENT                                                      |
+---------------------+-------------+-------------+-------------+--------------+---------------------------------------------------------------------+
| 1717761420575162368 | wumu        | t1          | ALTER_TABLE | ERROR        | Failed to execute the DDL task. Caused by: ...Out of range value... |
+---------------------+-------------+-------------+-------------+--------------+---------------------------------------------------------------------+

Step 5. Confirm that all shards remain consistent with the original schema.

check table t1;

Expected output:

+---------------------------------+-------+----------+----------+
| TABLE                           | OP    | MSG_TYPE | MSG_TEXT |
+---------------------------------+-------+----------+----------+
| wumu.t1:Topology                | check | status   | OK       |
| wumu.t1:Columns                 | check | status   | OK       |
| t1.auto_shard_key_a:Local Index | check | status   | OK       |
+---------------------------------+-------+----------+----------+

show create table t1;

Expected output — column b remains int(11), unchanged:

+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
         `a` int(11) DEFAULT NULL,
         `b` int(11) DEFAULT NULL,
         KEY `auto_shard_key_a` USING BTREE (`a`)
       ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
       PARTITION BY KEY(`a`)
       PARTITIONS 16                                                                         |
+-------+-------------------------------------------------------------------------------------------+

What's next

• Change the column types without locking tables — use Online Modify Column (OMC) when table locking is not acceptable