edit-icon download-icon

ApsaraDB for RDS MySQL InnoDB lock wait and lock wait timeout

Last Updated: Jul 10, 2018

When a ApsaraDB for RDS MySQL session is waiting for an exclusive Innodb row lock held by another session, Innodb lock wait will occur.

In general, the lock holder will finish the operation in time and release the lock so that the waiting session will get the lock before lock wait timeout for further operations.

However, in some cases, when an instance is not aware that a session is interrupted, the session that holds the lock will keep it for a long time and lead to a lot of lock wait and lock wait timeout.

How to deal with Innodb lock waits:

  1. It controls the timeout of Innodb lock wait (unit: second). Its default value for ApsaraDB for RDS instances is 50 (seconds).

    The session waiting for the exclusive lock will exit the lock wait status after 50 seconds and return ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, as shown below:

    innodb_lock_wait_00.png

    The parameter supports modifications to individual sessions, so you can customize session lock wait timeouts for some special operations of the application, as shown below:

    innodb_lock_wait_01.png

Thank you! We've received your feedback.