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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.