High values of innodb_lock_wait_timeout on Mysql

Question

we're trying to get some statistics over our large log tables on MySQL. Some select queries are taking too long to complete and causing exceptions as;
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
This is causing our whole application to stop serving with the same error. After some research we decided to change 'innodb_lock_wait_timeout' variable of our MySQL server configuration.

But, What are the drawbacks of this configuration change?

Answer 1

I am not sure this applies to your issue, but your question is something I have dealt with a while ago. I found out that on my system the locks were not needed and were related to queries like CREATE TABLE AS SELECT * FROM table_x... which apparently lock all records in table_x even in InooDB.

The solution was to set the global parameter innodb_locks_unsafe_for_binlog to true (in my.cnf add the line innodb_locks_unsafe_for_binlog=1). Which changes the way InnoDB locks records.

Here is some documentation about it. It really saved my application from those unexpected locks.