Tag Archives: session level

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

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

Error
Transaction is aborted and we need to restart the transaction. Some other transaction is blocking the transaction.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Solution
We have two solutions for this:
1. Increase the database parameter value “innodb_lock_wait_timeout”.
2. Check the transaction which caused blocking and kill that session.

Increase the database parameter value
1. Check the database parameter value.

mysql> show variables like 'innodb_lock_wait_timeout';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.02 sec)

2. Increase the value of parameter.

-- Set at database level, effect seen during making new connection.
SET GLOBAL innodb_lock_wait_timeout = 100;

-- Set at session level
SET innodb_lock_wait_timeout = 100;

Check the transaction which caused blocking and kill that session

Follow the following link: Handle Locking and kill the session