Help Center> Relational Database Service> Best Practices> MySQL> Resolving Database Operation Failures Caused by Metadata Locks on RDS for MySQL
View PDF

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

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Since MySQL 5.5, metadata locks were introduced. Metadata locking applies to tables. Data cannot be read or written, resulting in SQL statements 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 Service List. Under Database, click Relational Database Service to go to the RDS console. The RDS console is displayed.
  4. On the Instance Management page, locate the target DB instance and click Log In in the Operation column.

    Alternatively, click the target DB instance on the Instance Management page. On the displayed Basic Information page, click Log In in the upper right corner of the page.

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

    show full processlist

  8. Check whether a large number of Waiting for table metadata lock are displayed in the State column, which indicates that SQL blocking occurs. 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

Parent topic: MySQL