Link to home
Start Free TrialLog in
Avatar of David Smithstein
David SmithsteinFlag for United States of America

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)
Avatar of ste5an
ste5an
Flag of Germany image

Maybe executing 111 functions (what ever that means)  in a trigger is simple to much.

I would start in such a case by implementing a stored procedure to insert the necessary data.
Avatar of David Smithstein

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
Avatar of David Smithstein
David Smithstein
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
See last comment