Running DDL statements in production can disrupt traffic when metadata locks (MDLs) block concurrent transactions. PolarDB-X addresses three MDL-related risks — blocking, deadlock, and exclusion — through built-in optimizations that let DDL statements execute without causing connection pile-ups or traffic drops.
How MDLs cause problems
A metadata lock (MDL) is an internal database lock that ensures consistency of table metadata while transactions run alongside DDL statements. Read/write transactions must acquire a read MDL on the target table; DDL operations must acquire a write MDL.
Running DDL in production exposes three MDL-related risks:
| Risk | Description |
|---|---|
| Blocking | A long-running transaction holds a read MDL. A DDL statement waiting for the write MDL is blocked. Because MDLs in MySQL are fair locks, all new transactions queuing for the same read MDL are also blocked behind the DDL statement, causing a connection pile-up. |
| Deadlock | When multiple DDL statements and transactions run concurrently and request MDLs in different orders, a metadata deadlock can occur. |
| Exclusion | Write MDLs are exclusive. While a DDL statement holds a write MDL, all new transactions are blocked, dropping traffic to zero. Even with Online DDL, MySQL requires short-term write MDL acquisition at certain key steps. |
PolarDB-X addresses all three risks through the optimizations described below.
Preemptible MDL optimization
Supported versions
PolarDB-X V5.4.17-16952556 or later.
How it works
When a DDL statement is waiting to acquire a write MDL and the wait time exceeds the threshold, PolarDB-X automatically closes the connection of the long-running transaction that holds the conflicting read MDL. This unblocks both the DDL statement and any new transactions waiting behind it.
The following table shows a blocking scenario without this optimization.
| Step | Session 1 | Session 2 | Session 3 |
|---|---|---|---|
| 1 | begin; | — | begin; |
| 2 | insert into tb0 values(1); — acquires the read MDL of tb0 | — | — |
| 3 | Transaction kept open to simulate a long-running transaction | — | — |
| 4 | — | alter table tb0 add column col int; — blocked, waiting for the write MDL of tb0 | — |
| 5 | — | — | select id from tb0; — blocked by the DDL statement (fair lock) |
With preemptible MDL optimization enabled, PolarDB-X closes the Session 1 connection when the wait threshold is exceeded. The DDL statement in Session 2 and the new transaction in Session 3 can then proceed.
Distributed metadata deadlock detection
Supported versions
PolarDB-X V5.4.17-16952556 or later.
How it works
PolarDB-X periodically scans lock-wait relationships between transactions and DDL statements. When a deadlock is detected, it automatically terminates a common transaction to let DDL statements and other transactions proceed.
The following table shows a typical MDL deadlock scenario.
| Step | Session 1 | Session 2 | Session 3 | Session 4 |
|---|---|---|---|---|
| 1 | begin; — starts Transaction 1 | begin; — starts Transaction 2 | — | — |
| 2 | insert into t1 values(1); — acquires the read MDL of t1 | insert into t2 values(1); — acquires the read MDL of t2 | — | — |
| 3 | — | — | alter table t1 add column col int; — DDL Statement 1, blocked waiting for the write MDL of t1 | alter table t2 add column col int; — DDL Statement 2, blocked waiting for the write MDL of t2 |
| 4 | insert into t2 values(2); — blocked by DDL Statement 2 (fair lock) | insert into t1 values(2); — blocked by DDL Statement 1 (fair lock) | — | — |
After this deadlock forms, PolarDB-X detects it and closes the Session 2 connection. Transaction 2 is rolled back, allowing Transaction 1, DDL Statement 1, and DDL Statement 2 to complete.
Dual-version MDL optimization
Limitations
This optimization applies to all logically executed DDL statements in PolarDB-X. To check whether a specific DDL statement is logically executed, see Online DDL.
How it works
PolarDB-X implements logical DDL execution through Online Schema Change (OSC). Instead of a single metadata version transition, OSC divides the metadata version into multiple minor versions that evolve in sequence. For example, executing CREATE GLOBAL INDEX passes through these states:
ABSENT (Vn)
DELETE_ONLY (Vn+1)
WRITE_ONLY (Vn+2)
PUBLISH (Vn+3)
PolarDB-X associates each metadata minor version with its own MDL. At any given moment, two metadata versions can coexist — both across different compute nodes (CNs) in a cluster and within a single CN.
Each time the metadata version advances, PolarDB-X acquires the write MDL only for the metadata of the earlier version. New transactions can simultaneously access the new-version metadata using a read MDL on the new version.
This dual-version MDL mechanism ensures no table is locked and traffic does not drop to zero at any point during logically executed DDL statements.