PolarDB:DDL operation returns 'Failed to obtain MDL lock' error

Problem description

When you perform a DDL operation in a PolarDB for MySQL database, you may receive an error that an MDL lock cannot be acquired. The error messages are as follows:

ERROR HY000: Fail to get MDL on replica during DDL synchronize

ERROR HY000: Fail to get table lock on replica; you can 'set polar_support_mdl_sync_preemption = ON' and try restarting transaction

Cause

Active queries or uncommitted transactions exist on a read-only node.

Solutions

Use one of the following solutions:

• Enable the preemptive DDL feature for the read-only node. This is the recommended solution. For more information, see Preemptive DDL.

• Commit or rollback the uncommitted transaction on the read-only node.

  Enable the polar_slave_work_on_nonblock_mdl_mode parameter on the read-only node to prevent long-running, uncommitted transactions from blocking DDL operations. For more information, see Prevent long-running transactions on read-only nodes from blocking DDL operations.

• Log on to the PolarDB console and navigate to Diagnostics and Optimization > Quick Diagnostics. On the Sessions tab, check for abnormal sessions. If you find an abnormal session, click View details in the abnormal session section to view its information. We recommend ending the sessions that contain Long-running Uncommitted Transactions. For other types of abnormal sessions, Optimize or End them based on your business requirements. For more information, see Session Management.

• Use the Polar Performance Schema feature of your cluster to query the MDL lock status of the target table and terminate the threads for uncommitted transactions on the read-only node. The procedure is as follows:

  1. Check if the Polar Performance Schema feature is enabled in your cluster by using one of the following methods:

     • Log on to the PolarDB console. Go to the Settings and Management > Parameters page to view the value of the loose_polar_performance_schema parameter.

       For compatibility with MySQL configuration files, all cluster parameters in the console have a loose_ prefix.

       

     • Run the following SQL statement:

       SHOW VARIABLES LIKE 'polar_performance_schema';

       +--------------------------+-------+
       | Variable_name            | Value |
       +--------------------------+-------+
       | polar_performance_schema | ON    |
       +--------------------------+-------+

  2. The steps to resolve the issue vary depending on whether the feature is enabled or disabled.

     On

     You can query the performance_schema.metadata_locks table to check the MDL lock status on the target table and identify uncommitted transactions. Then, use the kill command to terminate the threads for these transactions.

     1. Use a hint to specify the read-only node and run the following SQL statement to query the MDL lock status on the target table:

        /*force_node='pi-bp10k7631d6k3****'*/ SELECT t.PROCESSLIST_ID, m.OBJECT_TYPE, m.OBJECT_SCHEMA, m.OBJECT_NAME, m.LOCK_TYPE, m.LOCK_DURATION, m.LOCK_STATUS FROM performance_schema.metadata_locks m LEFT JOIN performance_schema.threads t ON m.owner_thread_id=t.thread_id;

        A sample result is shown below:

        The figure shows that a long-running query or an uncommitted transaction holds a LOCK_TYPE of SHARED_READ on the test01/t1 table. Meanwhile, a lock with a LOCK_TYPE of EXCLUSIVE is PENDING on the test/t1 table.

     2. Use a hint to specify the read-only node and run the kill command to terminate the corresponding session thread.

        Note

        • If an uncommitted transaction on the current read-only node is very important, do not use the kill command to terminate the transaction. Instead, wait for the transaction to complete before you perform a DDL operation.

        • If you cannot terminate the thread by using the kill command, or if you receive an error that the thread ID does not exist, contact us for assistance.

          ERROR 1094 (HY000): Unknown thread id: xxx

        /*force_node='pi-bp10k7631d6k3****'*/ kill 536976473;

     Off

     You can query the information_schema.innodb_trx table to check the MDL lock status on the target table and identify uncommitted transactions. Then, use the kill command to terminate the threads for these transactions.

     1. Use a hint to specify the read-only node and run the following SQL statement to query the MDL lock status from the information_schema.innodb_trx table.

        /*force_node='pi-bp10k7631d6k3****'*/ SELECT * FROM information_schema.innodb_trx\G

     2. Analyze the result to determine if a long-running query or long-running transaction caused the DDL failure:

        • DDL operation fails due to a long-running query: The result is similar to the following:

          The figure indicates that a long-running query exists on the t1 table. This means the current session holds an MDL lock on the t1 table.

        • DDL operation fails due to a long-running transaction: The result is similar to the following:

          The figure shows an uncommitted transaction with the ID 537247177. Because the trx_query field is empty, you cannot confirm that this transaction holds the MDL lock on the current table. In this case, check the trx_started field. A significant time gap between the trx_started timestamp and the current time indicates that transaction 537247177 is likely holding the MDL lock.

     3. Use a hint to specify the read-only node and run the kill command to terminate the thread ID from the trx_mysql_thread_id field.

        /*force_node='pi-bp10k7631d6k3****'*/ kill 537247177

Contact us

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

How it works

The following steps describe how the cloud-native database PolarDB processes a DDL operation:

1. During different phases of a DDL operation, if the table schema needs to be changed, the primary node first acquires an MDL lock and then writes a record to the redo log.

2. When a read-only node parses this redo log record, it attempts to acquire an MDL lock on the same table. One of the following situations occurs:

   • If the lock is acquired, the process proceeds to the next step.

   • If lock acquisition fails, the read-only node reports the failure to the primary node.

3. The primary node waits for all read-only nodes to synchronize to the latest log sequence number (LSN). Within a specific time frame, the primary node checks whether all read-only nodes have parsed the redo log record and successfully acquired the lock. One of the following situations occurs:

   • If all read-only nodes are synchronized to the latest LSN, the DDL operation is successful.

   • If any read-only node fails to acquire the lock within the timeout period, the primary node rolls back the DDL operation and reports an error. Active queries or uncommitted transactions on read-only nodes typically cause these lock acquisition failures.

FAQ

SELECT TRX_ID, TRX_STATE, TRX_STARTED, TRX_QUERY FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = <Session ID>;