Lock wait timeout exceeded; try restarting transaction
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
Problem solved.