David Smithstein
asked on
MySQL lock wait timeout error after trigger table insert statement
I need some help with a MySQL lock wait timeout error. I'm appending a simple three field table, that insert statement then triggers about 111 functions that populate another table with totals for certain categories of information, that are then displayed to the user in the user interface. This process used to work without error, but now certain systems are intermittently throwing this error when the display table gets cleared, my insert statement triggers by 111 functions, and the table is repopulated.
If I close the application and restart it the problem will go away for a while. How can I debug this error?
The actual error message is:
ODBC--insert on a linked table 'triggerdashboard1' failed.
[MySQL][ODBC 5.2(a) Driver][mysqld-5.7.22-log] lock wait timeout exceeded; try restarting transaction (#1205)
If I close the application and restart it the problem will go away for a while. How can I debug this error?
The actual error message is:
ODBC--insert on a linked table 'triggerdashboard1' failed.
[MySQL][ODBC 5.2(a) Driver][mysqld-5.7.22-log]
ASKER
Functions is what Navicat calls procedures/queries... so I have a need to run 111 procedures/queries in order to refresh the data in the table that displays the totals each user sees on their "dashboard" in the user interface.
Well, it still sounds like too much.. Can you post the trigger code?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well, while this is a possibility, you should take a look at those 111 statements and the tables involved. Cause 111 sounds too much, I think this can be optimized. And if there are 78 tables involved I would also look at indices. Optimal indices can reduce locks.
ASKER
See last comment
I would start in such a case by implementing a stored procedure to insert the necessary data.