Managing Locks & Transactions

Introduction

Metadata Locks

• Metadata locks are used for tables to prevent conflicting DDL and DML operations from being executed concurrently on these tables. Executing DDL statements on a table generates metadata write locks. If there is a metadata lock, all subsequent SELECT, DML, and DDL operations on the table will be blocked, causing a connection backlog.
• Metadata locks are displayed in real time. You can quickly identify problems and terminate the sessions with metadata locks to restore blocked operations.
• DML locks are not included. You can view and analyze them on the InnoDB Locks page.
• This function is available only in RDS for MySQL 5.6 and 5.7.
• A maximum of 1,000 records can be displayed.

InnoDB Locks

• InnoDB lock waits generated before DML operations are displayed in real time. You can quickly locate the session waits and any blocks that happened when multiple sessions update the same piece of data at the same time, and can terminate the source session that holds locks to restore blocked operations.
• DDL locks, also called metadata locks, are not included. You can view and analyze them on the Metadata Locks page.
• To view lock information of RDS for MySQL 8.0 instances, set performance_schema to ON. You can run the SHOW GLOBAL VARIABLES LIKE "performance_schema" command or refer to Modifying Parameters of an RDS for MySQL Instance to check the performance_schema settings.

Procedure

1. Log in to the management console.
2. Click in the upper left corner of the page and choose Databases > Relational Database Service.
3. On the Instances page, click the DB instance name.
4. In the navigation pane, choose DBA Assistant > Real-Time Diagnosis.
5. Click the Locks & Transactions tab and enter the administrator password to log in to the database.
6. On the Metadata Locks page, filter metadata locks by session ID, lock status, lock type, and database name.
7. Check whether there are any sessions with metadata locks. If yes, select the sessions and click Kill Session.
8. On the InnoDB Locks page, check whether there are any lock waits.