Help Center> Relational Database Service> Best Practices> RDS for MySQL> Resolving Database Operation Failures Caused by Metadata Locks on RDS for MySQL
Updated on 2023-10-09 GMT+08:00
View PDF

Resolving Database Operation Failures Caused by Metadata Locks on RDS for MySQL

RDS for MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locks have been introduced since MySQL 5.5. A metadata lock on a table prevents any data from being read or written, resulting in SQL statements being blocked. You can use Data Admin Service (DAS) to resolve this issue.

Procedure

  1. Log in to the management console.
  2. Click in the upper left corner and select a region and a project.
  3. Click in the upper left corner of the page and choose Databases > Relational Database Service.
  4. On the Instances page, locate the DB instance and click Log In in the Operation column.

    Alternatively, click the instance name on the Instances page. On the displayed Basic Information page, click Log In in the upper right corner.

  5. On the displayed login page, enter the username and password and click Log In.
  6. On the top menu bar, choose SQL Operations > SQL Query.
  7. Run the following SQL statement in the SQL window to view the states of all database threads:

    show full processlist

    Figure 1 Execution result

  8. Check whether a large number of Waiting for table metadata lock are displayed in the State column, which would indicate that SQL statements are being blocked. Locate the sessions in the table operations in the Info column and record the values in the Id column.
  9. Run the following command in the SQL window to unlock the metadata lock:

    kill Id

    Figure 2 Execution result

Parent topic: RDS for MySQL