RDS for MySQL Metadata Locks

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

Log in to the management console.
Click in the upper left corner and select a region.
Click in the upper left corner of the page and choose Databases > Relational Database Service.
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 Overview page, click Log In in the upper right corner.
On the displayed login page, enter the username and password and click Log In.
On the top menu bar, choose SQL Operations > SQL Query.
Run the following SQL statement in the SQL window to view the states of all database threads:

show full processlist

Figure 1 Execution result
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.
Run the following command in the SQL window to unlock the metadata lock:

kill Id

Figure 2 Execution result