标识MySQL 5.5上的阻塞查询

Question

对于在Debian下使用MySQL 5.5.60的应用程序，最近我们经历了数据库写入的事务，这些事务在innodb_lock_wait_timeout设置的50多个时间限制内无法成功。

我需要识别持有这些锁的事务/语句，从而解决问题的原因。

我希望能从MySQL获得这方面的信息，但到目前为止，很难识别出这些阻滞剂。

最好的信息来自show engine innodb status报表中的交易部分，例如：

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

虽然我在这里没有看到任何SQL语句。

由于未知的原因，这样的事务不会出现在MySQL慢速查询日志(或进程列表、innotop、mytop等)中。如果它会出现在那里，它将不会列出MySQL线程id或查询id。

线程id似乎列在MySQL全局日志中，由于它需要存储大量的信息，所以我不敢在工作时间激活它。

有没有更好的方法？

到目前为止，我对不可见性的唯一提示是，可能会以不同的方式显示多个语句。

注即使连接处于“睡眠”阶段(如果是多语句事务)，事务也可以是活动的。

(来源)

情况会是这样吗？如何识别/debug这样一个用于锁定的源？

Answer 1

此查询可能有助于搜索锁定和阻塞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

选择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；

Answer 2

• 不要使用autocommit=0，您可能已经忘记做COMMIT了。
• 再试一下SHOW PROCESSLIST;。但这一次寻找的是具有最高“时间”的非系统过程。它可能表示忘记了COMMIT (或其他什么)的“user@host”。
• 唉，慢日志在完成之前不会显示查询。

“睡眠”可以发生在打开的事务中：

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