DDL atomicity - PolarDB - Alibaba Cloud Documentation Center

PolarDB-X supports the two-phase DDL feature. You can use this feature to prevent the situation in which the physical execution of user DDL statements is successful on some shards but fails on other shards. This effectively resolves the atomicity issue for physical DDL statements. This topic describes the atomicity for physically executed DDL statements because no atomicity issue exists for logically executed DDL statements.

Background information

In PolarDB-X, a logical table corresponds to multiple physical tables, which are also known as shards. These shards are distributed across multiple data nodes (DNs). If a DDL statement is physically executed on a logical table, related physical DDL statements must be executed on all shards corresponding to the logical table. The physical DDL statements are separately executed on the shards. As a result, the atomicity of physical execution cannot be guaranteed in most cases. If a DDL statement is logically executed, a compute node (CN) creates a temporary table, imports historical data, synchronizes incremental data in double-write mode, and then switches table metadata. In addition, the process of switching table metadata is atomic. Therefore, no atomicity issue exists for logical execution.

If the atomicity of physical DDL statements is not supported, the physical DDL statements are separately executed on multiple shards without a consistent commit point in time. The CN and DNs separately store metadata information. At a point in time, the metadata views at the computing layer and on multiple shards at the storage layer may be different, resulting in query errors.

In addition, for statements that modify the schema constraints of columns, such as ALTER TABLE MODIFY COLUMN, physical DDL statements are likely to fail because the existing data on a shard may violate the constraints. If the execution of physical DDL statements is successful on some shards but fails on other shards, the computing layer cannot implement consistent rollback semantics by delivering physical DDL statements. In this case, manual intervention is required, and metadata views may be inconsistent for a long period of time, which may affect your business.

The two-phase DDL feature supported by PolarDB-X can control the rollback and commit behaviors of physical DDL statements before a commit point by constructing a barrier on DNs for committing physical DDL statements on multiple shards. This ensures that DDL statements are atomically committed on multiple shards, and provides a consistent metadata view on the shards.

Usage notes

Only the PolarDB-X instances whose kernel version is 5.4.18-17108394 or later support the two-phase DDL feature.
All the involved shards must be connected and keep the threads running. A large-scale logical table with more than 500 shards on a single DN is not supported.
For physical DDL statements of the COPY type, the impact of distributed DDL statements changes from locking shards to locking the entire logical table.
A physical DDL statement modifies the data dictionary in the commit phase. This operation is a global and mutually exclusive operation. In the two-phase DDL feature, the operation is performed when the table is locked.
If your business is sensitive to table locking, we recommend that you use the Online Modify Column (OMC) feature. This feature allows you to change column types without the need to lock tables. OMC operations are logically executed and can ensure atomicity. For more information, see Change the column types without locking tables.

Applicable scope

The two-phase DDL feature applies only to the following physical DDL statements: ADD COLUMN, DROP COLUMN, ADD INDEX, DROP INDEX, and MODIFY COLUMN.
This feature does not apply to tables that contain physical partitions, foreign keys, or full-text indexes.

Usage

Check whether an instance supports the two-phase DDL feature and whether this feature is enabled.

show variables like "enable_two_phase_ddl";

If no result is returned, the two-phase DDL feature is not supported. If true is returned, the two-phase DDL feature is enabled. If false is returned, the two-phase DDL feature is disabled.

-- The two-phase DDL feature is enabled by default.

show variables like "enable_two_phase_ddl";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| enable_two_phase_ddl | ON    |
+----------------------+-------+

Enable the two-phase DDL feature.
```
set global enable_two_phase_ddl = true;
```

Disable the two-phase DDL feature.

set global enable_two_phase_ddl = false;

Example

In a PolarDB-X instance, create a logical table named t1. The following sample code shows its schema:

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

Write data to the logical table t1.

insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,999999);
Query OK, 1 row affected (0.01 sec)

In strict mode, execute the ALTER TABLE t1 MODIFY COLUMN b tinyint statement. The t1 table contains a record whose value of the b column is 999999. In strict mode, the physical DDL statement is expected to fail on the shard on which this record resides. The change is expected to fail on all shards because the two-phase DDL feature is enabled by default.

ALTER TABLE t1 MODIFY COLUMN b tinyint;
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] 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`\n\tMODIFY COLUMN b tinyint , Caused by: Data truncation: Out of range value for column 'b' at row 1. .

Execute the SHOW DDL and SHOW DDL RESULT statements to view the DDL tasks in the current instance and the historical execution results.

show ddl;
Empty set (0.01 sec)

show ddl result;
+---------------------+-------------+-------------+--------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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: ERR-CODE: [TDDL-4636][ERR_DDL_JOB_ERROR] 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`\n\tMODIFY COLUMN b tinyint , Caused by: Data truncation: Out of range value for column 'b' at row 1. .  |
+---------------------+-------------+-------------+--------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 rows in set (0.03 sec)

Execute the CHECK TABLE statement to check whether the schemas of the shards of the logical table are the same and whether the metadata is the same as that at the compute layer.

check table t1;
+---------------------------------+-------+----------+----------+
| 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       |
+---------------------------------+-------+----------+----------+
3 rows in set (0.01 sec)

show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)