Oct 10, 2018

How to deal with Lock wait timeout exceeded in MySQL

Run SHOW ENGINE INNODB STATUS\G after your failed statement, you can find what caused the lock.

pt-deadlock-logger - Log MySQL deadlocks

can do this job for you.

You should consider increasing the lock wait timeout value for InnoDB by setting the innodb_lock_wait_timeout

show variables like 'innodb_lock_wait_timeout';

SET innodb_lock_wait_timeout = 120; (only for current session)

Or

SET GLOBAL innodb_lock_wait_timeout = 120;

or put to configuration file:

[mysqld]
innodb_lock_wait_timeout=120