Mysql lock wait timeout exceeded on update query -

in database of production server, procedure runs scheduler daily, in procedure have few delete insert , update statements.

but throwing lock wait timeout exceeded error on 1 update using used tables.

initially innodb_lock_wait_timeout 50 seconds, changed 100, problem solved time, again error occurred changed 120, again solved temporary. have set 150 seconds global , set 200 in session (in procedure). working fine few days.

but procedure important, getting error creates problems has important data.

so there other solution can permanent solution problem?

i newbie please help.

p.s. mysql - 5.6 128 gb ram. using hibernate has persistent connection pool.

do below in my.cnf , restart mysql

[mysqld] innodb_lock_wait_timeout=10000 

or

 set global innodb_lock_wait_timeout = 10000; 

you can make temporary timeout trigger session add below trigger:

set innodb_lock_wait_timeout = 10000;