Identifying blocking queries on MySQL 5.5

Question

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 innodb_lock_wait_timeout setting.

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 217.69.64.139 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.

(Source)

Can this be the case? And how would one identify /debug such a source for locking?

Answer 1

This query may assist in your search for locking and blocking ids.

# IS-tx-wait-block-qrys.sql to help research BUSY systems Last Updated 20191227 wh
# if this helps you, please email SHAREABLE comments to info@mysqlservertuning.com
# From article at 
# https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-server-activity/
# 20191227 tested on MariaDB 10.3.10

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Answer 2

• Don't use autocommit=0, you might have forgotten to do COMMIT.
• Try SHOW PROCESSLIST; again. But this time look for the non-system process with the highest "Time". It may indicate the 'user@host' that forgot to COMMIT (or whatever).
• Alas, the slowlog does not show a query until it finishes.

"Sleep" can occur for an open transaction:

BEGIN;
SELECT ... FOR UPDATE; -- "SELECT" shows in processlist
do some client work    -- "Sleep" shows now
INSERT ...             -- "INSERT" shows
COMMIT                 -- "COMMIT" shows