InnoDB lock wait timeout

Databases
1

Hello,

I have a table in my database (actually a few related tables) that get can be manipulated manually from various points through our interface but also automatically from two sources on a continuous basis. The periodic updates can contain huge amounts of data and can result in thousands of inserts/updates. In order to improve performance of the inserts/updates I have used “SET autocommit = 0” around the updates from these automated sources. This has resulted in the desired performance improvement, maybe even more than expected. However the problem now is that if the automated sources overlap or if a manual update is performed very often the database locks up and after a while throws an error:

Lock wait timeout exceeded; try restarting transaction

This may be thrown even in a single statement with autocommit on and no transaction but I guess that is reasonable as well if it conflicts with a transaction. I have read various suggestions, unfortunately there is no ideal solution. I guess my options are:

  1. Try to order updates/inserts on the tables so that locks on all threads are requested in the same order and there is no deadlock. Unfortunately this is no possible, updates need to be applied in the order they are received.

  2. Use LOCK TABLES to serialize transactions. This is theoretically possible but a) Apart from the two automated sources the tables are updated from many points in the system, including triggers, schedules, manually from various interfaces. It would be a nightmare to identify and maintain LOCK tables around all these places and no easy way to know that all have been identified, and b) LOCK TABLES has to lock all tables involved and the updates/inserts though not often but sometimes may need to update many tables as a result of the updates and again need to identify and maintain all the tables that might be updated so that they are included in the LOCK TABLES.

  3. Use a semaphore table before each update in order to achieve the serialization of updates as with LOCK TABLES above but without actually having to use LOCK TABLES. This is an improvement but still has problem a) of LOCK TABLES above.

Any other suggestions? Could the improvement benefits of autocommit = 0 (transactions) be achieved some other way that does not involve locks? Could innodb be configured to actually not lock or lock much less on updates/inserts?

Last resort option may be to move to MyISAM tables. Would this actually achieve performance improvements with heavy inserts/update operations?

Thank you