What Should I Do If Locks on Long Transactions Block the Execution of Subsequent Transactions?

Scenario

Error code 1205 was reported:

"MySQL error code MY-001205 (ER_LOCK_WAIT_TIMEOUT): Lock wait timeout exceeded; try restarting transaction"

Possible Causes

1. Check the value of the monitoring metric Row Lock Time. In this example, the value of this metric was high, so we know there were lock conflicts in the system.

   For details about monitoring metrics, see Viewing Instance Monitoring Metrics.

   

2. Log in to the DB instance and run the following SQL statement to check the long transactions in the system and the row locks held by the transactions:

   select trx_mysql_thread_id, trx_id, trx_state, trx_started, trx_tables_locked, trx_rows_locked, trx_isolation_level, trx_query, trx_operation_state from information_schema.innodb_trx order by trx_started;

   

   • information_schema.innodb_trx: information about transactions that are being executed in the InnoDB.
   • trx_started: start time of a transaction, which is used to determine whether the current transaction is a long transaction. The execution time of a transaction is the current time minus the start time.
   • trx_state: Status of the current transaction. The values are as follows:
     • RUNNING
     • LOCK WAIT
       

       If the status of a transaction is LOCK WAIT, the transaction holds a row lock.

     • ROLLING BACK
     • COMMITTING

Solution

Kill the long transactions.