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

Limits

Your PolarDB cluster must meet one of the following requirements:

• A PolarDB for MySQL 8.0 cluster whose revision version is 8.0.1.1.23 or later.

• A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.19 or later.

• A PolarDB for MySQL 5.6 cluster whose revision version is 5.6.1.0.32 or later.

The polar_slave_work_on_nonblock_mdl_mode parameter is effective only if the transaction isolation level on PolarDB read-only nodes is Read Committed or Read Uncommitted.

The polar_slave_work_on_nonblock_mdl_mode parameter applies only to PolarDB read-only nodes.

Background information

Long-running transactions on PolarDB read-only nodes hold the metadata locks (MDLs) on the accessed data tables. In this case, DDL operations on the PolarDB primary node cannot synchronize the MDLs. As a result, timeout errors occur and the "ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize" error message is returned.

If MDL synchronization times out, execute the show processlist statement on the PolarDB primary node. If the value returned for the State parameter is Wait for syncing with replicas, you can execute the select * from information_schema.innodb_log_mdl_slot where slot_state = "SLOT_ACQUIRING" statement on PolarDB read-only nodes to query MDLs that are in the waiting state. If the system returns a table similar to the following table, it indicates that long-running transactions on PolarDB read-only nodes hold MDLs.

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

Precautions

The polar_slave_work_on_nonblock_mdl_mode parameter can be used to resolve only the DDL blocking issues that are caused by long-running transactions. After you set the polar_slave_work_on_nonblock_mdl_mode parameter to ON, MDL synchronization failures may occur because parallel queries consume more time than the timeout period of MDL synchronization.

The MDLs used to lock or flush tables must be obtained by explicitly unlocking tables. Therefore, the MDL blocking issues caused by locking or flushing tables cannot be resolved by setting the polar_slave_work_on_nonblock_mdl_mode parameter to ON.

Procedure

1. Log on to the PolarDB console.
2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
3. Find the cluster and click the cluster ID.

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

5. Find the loose_polar_slave_work_on_nonblock_mdl_mode parameter and click Modify Parameter.

   

6. After you modify the parameters, click Submit Changes. In the Save Changes dialog box, click OK.

   

Examples

After you set the polar_slave_work_on_nonblock_mdl_mode parameter to ON, a transaction on a read-only node may return different table structures.

1. Query the test.t table on a read-only node.

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

2. Perform DDL operations 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. Query the test.t table again on the read-only node.

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

The preceding example indicates that after the polar_slave_work_on_nonblock_mdl_mode parameter is set to ON, a transaction on a read-only node returns different numbers of columns when the transaction is committed before and after DDL operations are performed on the primary node. After you set the polar_slave_work_on_nonblock_mdl_mode parameter to OFF, the DDL operations on the primary node are not allowed until the transactions on read-only nodes are committed or timeout occurs. In this case, the "ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize" error message is returned.