For an application using MySQL 5.5.60 under Debian, lately we experience transactions with database writes, which do not succeed within the 50s time limit of the
I need to identify the transactions / statements which hold those locks, thus the cause, to start resolving the issue.
My hope was to get this information from the MySQL side, but so far it seems embarrassingly difficult to identify the blockers.
Best information was in the TRANSACTIONS section from the report given by a
show engine innodb status statement, e.g.:
---TRANSACTION 6FEE8DD5, ACTIVE 13021 sec 1314 lock struct(s), heap size 129464, 1599 row lock(s), undo log entries 1212 MySQL thread id 16165411, OS thread handle 0x7f1491f53700, query id 1056480972 host.domain.tld 22.214.171.124 username Trx read view will not see trx with id >= 6FEE8DD6, sees < 6FEE0FE9
Though I do not see any SQL statement here.
For unknown reason such a transaction does not show up in the MySQL slow query log (or processlist, innotop, mytop etc.). And if it would show up there, it will not list the MySQL thread id or the query id.
The thread id seems to be listed in the MySQL global log, which I dared not to activate during business hours, due to the mass of information it has to store.
Is there a better approach?
My only hint for the invisibility so far is that multiple statements might be displayed differently.
Note transaction can be active even if the connection is in “Sleep” stage – if it is multiple-statement transaction.
Can this be the case? And how would one identify /debug such a source for locking?