How to Resolve Error 1205 Lock wait timeout exceeded; try restarting transaction

  • by
Got this error when executing some heavy loading jobs initiated by PHP programs.
SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

Let's check current lock wait timeout.
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

50 seconds might be too tight to perform administration jobs well. So I decide to set it as 120 seconds.

At run-time, you can set it for all databases in the same instance.
mysql> SET GLOBAL innodb_lock_wait_timeout=120;
Query OK, 0 rows affected (0.00 sec)

For persisting the setting, you can write it in the configuration file under [mysqld]. (Important!)
[root@test ~]# vi /etc/my.cnf
...
[mysqld]
...
innodb_lock_wait_timeout=120

Then restart the service.
[root@test ~]# service mysqld restart

Leave a Reply

Your email address will not be published. Required fields are marked *