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

Limits

The PolarDB cluster version must meet one of the following requirements:

• PolarDB for MySQL 8.0 with a revision version of 8.0.1.1.23 or later

• PolarDB for MySQL 5.7 with a revision version of 5.7.1.0.19 or later

• PolarDB for MySQL 5.6 with a revision version of 5.6.1.0.32 or later

The polar_slave_work_on_nonblock_mdl_mode parameter takes effect only when the transaction isolation level on PolarDB read-only nodes is Read Committed or Read Uncommitted.

The polar_slave_work_on_nonblock_mdl_mode parameter takes effect only on read-only nodes of PolarDB.

Background information

Long-running transactions on read-only nodes of PolarDB hold metadata locks (MDLs) on the accessed tables. This prevents DDL operations on the primary node of PolarDB from completing MDL synchronization, which eventually causes a timeout with the error ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize.

When this issue occurs, run the show processlist command on the primary node of PolarDB. If the State field in the query result shows Wait for syncing with replicas, you can run the select * from information_schema.innodb_log_mdl_slot where slot_state = "SLOT_ACQUIRING" command on a read-only node of PolarDB to query the MDL information that is in the waiting state. If this command returns the following result, it indicates that an MDL waiting issue exists on the read-only node of PolarDB.

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

Usage notes

polar_slave_work_on_nonblock_mdl_mode can only resolve DDL blocking caused by long-running transactions. After you enable polar_slave_work_on_nonblock_mdl_mode, you may still encounter MDL synchronization failures caused by large queries (that is, queries that take longer than the MDL synchronization timeout).

Because MDLs acquired by LOCK TABLES and FLUSH TABLES must be explicitly released by running UNLOCK TABLES, polar_slave_work_on_nonblock_mdl_mode cannot resolve MDL blocking caused by LOCK TABLES and FLUSH TABLES.

Procedure

1. Log in to the PolarDB console。

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

3. Find the target cluster and click its ID.

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

5. Find the target parameter loose_polar_slave_work_on_nonblock_mdl_mode and click Modify Parameter.

   In the search box, enter loose_polar_slave_work_on_nonblock_mdl_mode. You can see that the current value of this parameter for the cluster is ON, the global parameter is Yes, and the modification range is [ON|OFF]. Click Modify Parameter.

6. After the parameter is modified, click Submit Changes. In the Save Changes dialog box, click OK.

Examples

After you enable polar_slave_work_on_nonblock_mdl_mode, the same transaction on a read-only node may see different table schemas, as shown in the following example:

1. First, query test.t on a read-only node:

   mysql> begin;
   mysql> select * from test.t;
   +------+
   | a    |
   +------+
   |    1 |
   +------+
   1 row in set (0.00 sec)

2. Then, perform a DDL operation on test.t on the primary node:

   mysql> alter table test.t add column b int;
   Query OK, 0 rows affected (0.32 sec)
   Records: 0  Duplicates: 0  Warnings: 0

3. Finally, query test.t on the read-only node again:

   mysql> select * from test.t;
   +------+------+
   | a    | b    |
   +------+------+
   |    1 | NULL |
   +------+------+
   1 row in set (0.00 sec)

From the preceding example, you can see that when polar_slave_work_on_nonblock_mdl_mode is enabled, a DDL operation is performed on the primary node, so the same transaction on the read-only node sees different numbers of columns. If polar_slave_work_on_nonblock_mdl_mode is disabled, the DDL operation on the primary node keeps waiting for the transaction on the read-only node until the transaction is committed or the wait times out (error: ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize).

Contact us

If you have any questions about DDL operations, Contact us.