PolarDB:Prevent long-running transactions on read-only nodes from blocking DDL operations

How it works

In a PolarDB for MySQL cluster, DDL operations on the primary node must synchronize MDLs to all read-only nodes before they can complete. If a long-running transaction on a read-only node holds an MDL on the target table, MDL synchronization blocks until the transaction ends or replica_lock_wait_timeout expires.

When polar_slave_work_on_nonblock_mdl_mode is ON, DDL operations on the primary node proceed immediately without waiting for conflicting MDLs on read-only nodes. The trade-off is that a transaction on a read-only node may observe different table schemas within a single transaction if a DDL runs concurrently on the primary node.

This parameter resolves MDL blocking only when the root cause is a long-running transaction. It does not resolve blocking caused by LOCK TABLES or FLUSH TABLES — those MDLs require an explicit UNLOCK TABLES to release.

Prerequisites

Before you enable this parameter, ensure that your cluster meets all of the following conditions.

Version requirement — your cluster must meet one of the following:

• PolarDB for MySQL 8.0, revision version 8.0.1.1.23 or later

• PolarDB for MySQL 5.7, revision version 5.7.1.0.19 or later

• PolarDB for MySQL 5.6, revision version 5.6.1.0.32 or later

To check your cluster's revision version, see Query the engine version.

Isolation level requirement — the transaction isolation level on read-only nodes must be Read Committed or Read Uncommitted. The parameter has no effect under Repeatable Read or Serializable isolation.

Diagnose MDL blocking

Before enabling the parameter, confirm that long-running transactions (not LOCK TABLES or FLUSH TABLES) are the root cause of the blocking. The parameter cannot unblock MDLs held by explicit table locks.

Step 1: On the primary node — run SHOW PROCESSLIST and check whether any row shows State = Wait for syncing with replicas. If so, a DDL is blocked waiting for MDL synchronization on a read-only node.

SHOW PROCESSLIST;

Step 2: On a read-only node — identify the blocking transaction:

SELECT * FROM information_schema.innodb_log_mdl_slot WHERE slot_state = 'SLOT_ACQUIRING';

If the query returns rows, a long-running transaction holds an MDL:

+---------+----------------+-----------+----------+-----------+
| slot_id | slot_state     | slot_name | slot_lsn | thread_id |
+---------+----------------+-----------+----------+-----------+
|       0 | SLOT_ACQUIRING | test/t    | 35025648 | thread-0  |
+---------+----------------+-----------+----------+-----------+

Next steps based on the output:

Enable the parameter

1. Log on to the PolarDB console.

2. In the upper-left corner, select the region where your cluster is deployed.

3. Find the cluster and click its ID.

4. In the left-side navigation pane, choose Settings and Management > Parameters.

5. Find loose_polar_slave_work_on_nonblock_mdl_mode and click Modify Parameter.

   

6. Click Submit Changes. In the Save Changes dialog box, click OK.

   

Limitations and side effects

Schema consistency behavior when the parameter is ON

When polar_slave_work_on_nonblock_mdl_mode is ON, a DDL on the primary node no longer waits for in-flight transactions on read-only nodes. As a result, a single transaction on a read-only node can observe two different table schemas.

The second SELECT in Session 1 returns the updated schema because the DDL in Session 2 completed while the transaction was still open.

When the parameter is OFF (default), the DDL in Session 2 blocks until Session 1 commits or replica_lock_wait_timeout expires, returning ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize.

Contact us

If you have any questions about DDL operations, please contact technical support.