agentleFS
polardbpolardb/polardbx-skills/skills/polardbx-online-ddl/SKILL.md

polardbx-online-ddl

Skill8 starsChanged 31 days agoApache-2.0View on GitHub

Online DDL safety assessment and lock-free execution for PolarDB-X 2.0 Enterprise Edition. Determines if DDL locks the table via EXPLAIN ONLINE_DDL, rewrites with ALGORITHM=OMC for lock-free execution, checks long transactions before DDL, and guides safe DDL execution with user confirmation. Use when executing DDL on PolarDB-X and needing to assess table-locking risk, when DDL would lock the table and a lock-free alternative is needed, or when checking long transactions before DDL. Triggers: "Online DDL", "DDL锁表", "lock-free DDL", "OMC", "MDL lock", "DDL安全", "EXPLAIN ONLINE_DDL", "DDL进度", "长事务检查", "无锁变更", "DDL locks table", "lock-free change", "DDL progress", "safe DDL"

The file

We split this skill into the rules it gives an agent

7 rules. About 0 are common, 3 are distinctive, and 4 are very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈3

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 4

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Very specific:

    Check the DDL TYPE in the result.

    line 27
  2. Distinctive:

    If long transactions exist, assess risk before proceeding.

    line 45
  3. Distinctive:

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

    line 47
  4. Very specific:

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

    line 69
  5. Very specific:

    Always EXPLAIN before executing: Never run DDL without first checking EXPLAIN ONLINE_DDL.

    line 104
  6. Distinctive:

    Prefer native Online DDL over OMC: Only use OMC when the operation would lock the table.

    line 105
  7. Very specific:

    Monitor progress for large tables: Use DDL_PROGRESS view for operations involving data backfill.

    line 108

≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

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:
    EXPLAIN ONLINE_DDL ALTER TABLE ...;
    Check the DDL TYPE in the result.
  2. 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:
      EXPLAIN ONLINE_DDL ALTER TABLE t1 MODIFY COLUMN b text, ALGORITHM=OMC;
      -- Confirm: DDL TYPE = ONLINE_DDL, ALGORITHM = OMC
  3. Check long transactions on the target table:
    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.
  4. Present results to user and obtain explicit confirmation before executing DDL.

DDL TYPE / ALGORITHM Quick Reference

DDL TYPEALGORITHMDescriptionBusiness Impact
ONLINE_DDLINSTANT / META_ONLYMetadata-only change, completes in secondsMinimal
ONLINE_DDLINPLACE / OMC / OSCNo table lock, duration depends on data volumeSmall
LOCK_TABLECOPYLocks table, table not writable during executionLarge

Common DDL Operations Assessment

OperationTypical DDL TYPEALGORITHM
Add columnONLINE_DDLINSTANT
Add partitionONLINE_DDLMETA_ONLY
Add indexONLINE_DDLINPLACE
Modify column typeLOCK_TABLECOPY → rewrite with OMC
Drop columnONLINE_DDLINPLACE

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;

Here, "lock-free" means avoiding COPY-style table locking that prevents writes during the data-change phase. Like INPLACE and INSTANT DDL, OMC still acquires metadata locks; metadata-lock and cutover waits can occur.

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

Long Transaction Decision Matrix

SituationRecommended Action
Long transaction is unexpectedInvestigate business logic, resolve before DDL
Long transaction is expected + high priorityPostpone DDL
Long transaction is expected + low priorityCan proceed; DDL will kill the connection (default 15s timeout)

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: Online DDL consumes IO/CPU resources and can encounter metadata-lock waits.
  5. Monitor progress for large tables: Use DDL_PROGRESS view for operations involving data backfill.

Reference

ReferenceDescription
references/online-ddl.mdComplete Online DDL guide: EXPLAIN ONLINE_DDL, OMC, MDL optimizations, long transaction checks, DMS integration, legacy version compatibility

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.