polardb/polardbx-sql/polardbx-online-ddl

PolarDB-X Online DDL — Safe DDL Assessment & Lock-Free Execution

Assess whether DDL operations lock the table and execute them safely on PolarDB-X 2.0 Enterprise Edition (AUTO mode). This skill covers the complete DDL safety workflow: assessment → lock-free rewriting → long transaction checks → user confirmation → execution.

Scope: PolarDB-X 2.0 Enterprise Edition + AUTO mode database only.

Version requirement: Instance version >= 5.4.20-20241224 for EXPLAIN ONLINE_DDL and OMC support. For older versions, see reference guide for legacy compatibility methods.

Core Workflow (Follow each time)

Important: DDL is a high-risk operation. NEVER execute DDL directly without presenting assessment results and obtaining explicit user confirmation.

1. Assess table-locking risk with EXPLAIN ONLINE_DDL:

``sql EXPLAIN ONLINE_DDL ALTER TABLE ...; ` Check the DDL TYPE` in the result.

1. Determine execution strategy based on DDL TYPE:

• ONLINE_DDL → Execute the original SQL directly (no table lock).
• LOCK_TABLE → Rewrite with ALGORITHM=OMC and re-EXPLAIN to confirm lock-free:

``sql EXPLAIN ONLINE_DDL ALTER TABLE t1 MODIFY COLUMN b text, ALGORITHM=OMC; -- Confirm: DDL TYPE = ONLINE_DDL, ALGORITHM = OMC ``

1. Check long transactions on the target table:

``sql SELECT TRX_ID, PROCESS_ID, SCHEMA, START_TIME, ROUND(DURATION_TIME/1000/1000, 3) AS 'Duration(s)', SQL AS 'Current SQL' FROM INFORMATION_SCHEMA.POLARDBX_TRX WHERE DURATION_TIME > 15 * 1000 * 1000; `` If long transactions exist, assess risk before proceeding.

1. Present results to user and obtain explicit confirmation before executing DDL.

DDL TYPE / ALGORITHM Quick Reference

Common DDL Operations Assessment

OMC Lock-Free Column Type Change

When EXPLAIN ONLINE_DDL returns LOCK_TABLE, append ALGORITHM=OMC to avoid table locking:

-- Before: locks table
ALTER TABLE t1 MODIFY COLUMN c bigint;
 
-- After: lock-free via OMC
ALTER TABLE t1 MODIFY COLUMN c bigint, ALGORITHM=OMC;

OMC trade-offs: Executes slower and consumes more resources than native DDL. Only use when avoiding table lock is critical.

Long Transaction Decision Matrix

Side effect of MDL preemption: Connections with transactions/queries exceeding 15 seconds will be killed. Avoid DDL during DataWorks/DTS/mysqldump executions.

Monitor DDL Progress

For long-running DDL operations (OMC, INPLACE with data backfill):

SELECT JOB_ID, TABLE_NAME, STATE, PROGRESS, CURRENT_SPEED, DDL_STMT
FROM INFORMATION_SCHEMA.DDL_PROGRESS;

Best Practices

1. Always EXPLAIN before executing: Never run DDL without first checking EXPLAIN ONLINE_DDL.
2. Prefer native Online DDL over OMC: Only use OMC when the operation would lock the table.
3. Check long transactions: Especially for tables with known long-running sync tasks.
4. Schedule DDL during low-traffic windows: Even lock-free DDL consumes IO/CPU resources.
5. Monitor progress for large tables: Use DDL_PROGRESS view for operations involving data backfill.

Reference