How do I resolve "Error 1205: Lock wait timeout exceeded. Try restarting transaction" in RDS for MySQL?

Short description

When you run DML or DDL queries, you might receive an error similar to the following one:

"Error Code 1205: Lock wait timeout exceeded. Try restarting connection."

It's common to receive the 1205 error in the following scenarios:

• DML queries with XA transactions that are in the PREPARED status.
• DDL and DML queries that are stuck in the Waiting for the table metadata lock status.
• DML queries that fail when you run an INSERT, UPDATE, or DELETE command. This might occur when you run these commands against a record in a table that's locked by other transactions.

Resolution

Note: The following steps are verified with Amazon RDS for MySQL version 8.0.32.

DML queries with XA transactions that are stuck in the PREPARED status

1.    To identify the XA transactions that are in the PREPARED status, run the SHOW ENGINE INNODB STATUS command.

Example output:

---TRANSACTION [2043536230](tel:2043536230), ACTIVE (PREPARED) 302510 sec recovered trx  
9 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1  
---TRANSACTION [2043368758](tel:2043368758), ACTIVE (PREPARED) 303628 sec recovered trx  
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2

Note: XA transactions don't appear in SHOW FULL PROCESSLIST.

2.    Run the XA RECOVER command.

Example output:

mysql> xa recover;  
  formatID  gtrid_length  bqual_length  data                                   
         1            35             1  3d36dccd-61d0-4ae1-9b39-f9ccc2400d44:44       
         1            43             2  11740f2a-8a85-4b33-b924-982c1539d197:2222

Note: Each XA transaction begins with a XA keyword followed by the XA transaction identifier (XID). An XID has three parts:

• The gtrid is a global transaction identifier.
• The bqual is a branch qualifier. This value must be different for each XA transaction within a global transaction.
• The formatID is a number that identifies the format that the gtrid and bqual values use.

For more information on gtrid, bqual, and formatID, see XA transaction SQL statements on the MySQL website.

3.    Run the XA RECOVER CONVERT XID command. This command gives you the XID in hexadecimal values.

Example output:

mysql> xa recover convert xid;  
  formatID  gtrid_length  bqual_length  data                                       
         1             8             2  0x1A644692573273379927     
         1             9             4  0x1703627130792935360A01911845  
3 rows in set (0.00 sec)

Note: For the next step, you must separate the values in the preceding data column (XID). The first part of the XID is the gtrid length in bytes. The second part of the XID is the bqual length in bytes. The last part of the XID is the formatID.

Break down the XID into its three parts:

• The preceding example XID is 0x1A644692573273379927.
• The first part is gtrid length. This value is 8 B and reads as 1A 64 46 92 57 32 73 37.
• The second part is bqual length. This value is 2 B and reads as 99 27.
• The last part is formatID. This value is 1.

4.    Run the XA ROLLBACK command to roll back the XA transactions. Use the preceding method to break down every XA transaction that requires a rollback.

Example output:

mysql> xa rollback x'1A64469257327337',x'9927',1;

Query OK, 0 rows affected (0.04 sec)

(Optional) Run the COMMIT command:

mysql> xa commit x'1A64469257327337',X'9927',1

Query OK, 0 rows affected (0.02 sec)

5.    After all XA transactions are rolled back or committed, run the DML queries that contain the error. For more information, see XA transactions on the MySQL website.

DDL and DML queries that are stuck in the Waiting for the table metadata lock status

Note: Run the following commands in a separate DDL or DML query session.

1.    To identify the open transaction with the Waiting for table metadata lock status, run the SHOW FULL PROCESSLIST command.

Note: Make sure that you acknowledge the process ID of the transaction with the table metadata lock.

2.      To identify the sessions that are blocked and what's causing the block, run SCHEMA_TABLE_LOCK_WAITS commands:

MySQL [(none)]> USE sys;

MySQL [sys]> SELECT * FROM schema_table_lock_waits\G

For more information, see The schema_table_lock_waits and x$schema_table_lock_waits views on the MySQL website.

3.    To determine what caused the block, identify the blocking lock type.

4.    To stop the session that's running the blocking statement, run the CALL mysql.rds_kill(example-thread-id) command.

Note: If the blocking session is in progress with a DML query, then it's a best practice to stop the session during non-business hours. When you stop or roll back a long-running operation, it might be time consuming and I/O intensive.

5.    Run the DDL or DML query again.

To avoid delays because of metadata locks, take the following actions:

• Perform stops on blocking sessions during non-business hours.
• Don't keep any transactions open. In an OLTP database, it's difficult to find open or idle transactions that cause metadata locks.
• When you establish an Amazon RDS connection, set the session autocommit variable to 1 or on.
• Before you run ALTER on the table, it's a best practice to use the metadata_locks table in performance_schema to check for existing metadata locks. Run the following command:

SELECT * from performance_schema.metadata_locks;

Note: Use the lock_wait_timeout parameter to specify the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default value is 31536000. This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions. Also included are lock tables, flush tables with read lock, and handler statements. For more information, see lock_wait_timeout, LOCK TABLES and UNLOCK TABLES statements, FLUSH TABLES WITH READ LOCK, and HANDLER statement on the MySQL website.

DML queries that fail when you run an INSERT, UPDATE, or DELETE command

If you try to run an INSERT, UPDATE, or DELETE command against a record in a table that shows error 1205, then your DML query fails. This also occurs against a record in a table that's locked by other transactions, such as a metadata lock or an exclusive lock. For more information, see Why was a query to my Amazon RDS MySQL DB instance blocked when there's no other active session?

Note: By default, The DB parameter innodb_lock_wait_timeout is set to 50 seconds. If a transaction tries to access a resource that's locked by another transaction, then the maximum wait time is 50 seconds before the error occurs. When the lock wait timeout error occurs, the current statement is rolled back. To set your transaction to wait longer, increase the innodb_lock_wait_timeout parameter value based on your MySQL workload. The innodb_lock_wait_timeout parameter is dynamic. Changes that you make are immediately applied. You don't need to reboot the affected instance. For more information, see innodb_lock_wait_timeout on the MySQL website.