Mysql lock wait timeout exceeded on update query

Question

In the database of production server, a procedure runs by scheduler daily, in that Procedure I have few delete insert and update statements.

but it is throwing lock wait timeout exceeded error sometimes on 1 update that is using some frequently used tables.

Initially innodb_lock_wait_timeout was 50 seconds, then I changed it to 100, so problem was solved for some time, but then again error occurred so I changed it to 120, again it was solved temporary. Now I have set it 150 seconds for global and set 200 in session (in procedure). it is working fine from few days.

But that procedure is very important, getting the error creates problems as it has very important data.

so is there any other solution which can be permanent solution for this problem?

I am newbie please help.

P.S. Mysql - 5.6 128 GB RAM. using hibernate which has persistent connection pool.

Answer 1

Do the below in my.cnf and restart the mysql

[mysqld]
innodb_lock_wait_timeout=10000

or

 SET GLOBAL innodb_lock_wait_timeout = 10000;

You can also make it temporary timeout for you trigger session add below to your trigger:

SET innodb_lock_wait_timeout = 10000;