What do I do if row lock waits occur on an ApsaraDB RDS for MySQL instance and the row lock waits time out? -

Problem description

When a session waits for a row exclusive lock that is held by another session, a row lock wait occurs. If the row lock wait times out, the following error is reported:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Causes

In most cases, the session that holds the row exclusive lock quickly completes relevant operations and releases the lock. Then, the session that waits for the row exclusive lock can obtain the lock before the lock times out and perform relevant operations. Row exclusive locks can be released by using transaction commits or rollback. In some cases, an RDS instance may not receive a notification that an application is disconnected from a database, and a session holds a row exclusive lock for a long period of time. If other sessions want to hold the row exclusive lock, a large number of row lock waits occur, and the row lock waits time out.

Solutions

The methods that are provided in this topic are suitable only when a row lock wait occurs. In ApsaraDB RDS for MySQL, the default timeout period for a row lock wait is 50 seconds. You can set innodb_lock_wait_timeout to a large value to simulate the situation in which row lock waits occur. We recommend that you do not set innodb_lock_wait_timeout to a large value in a production environment.

Use Data Management (DMS) to log on to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.
Perform the following operations to view transactions that are being executed and transactions that are waiting for row locks.
- Execute the following SQL statement to view the transactions that are being executed:
```
SELECT * FROM information_schema.INNODB_TRX;
```
- Execute the following SQL statement to view the transactions that are waiting for row locks:
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
```
- Execute the following SQL statement to view the transactions that are being locked.
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
```
If a session is identified as Blocker, the session holds a row lock that blocks DML operations of other sessions. As a result, row lock waits occur and row lock waits time out. If the transaction for the session can be rolled back, you can roll back the transaction to release the row lock.
If the issue persists, go to the Blocking tab to identify the sessions that cause blocks for a long period of time and view details about the sessions. For more information, see View the blocking statistics of an ApsaraDB RDS for SQL Server instance.

Applicable scope

ApsaraDB RDS for MySQL