mysql - 如何对 MySQL `ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction` 进行故障排除

Question

我似乎无法从 MySQL 中提取任何有用的信息来帮助我调试此错误:ERROR 1205 (HY000): Lock wait timeout exceeded;尝试重新启动事务。你能帮我找一些吗？

复制:

一个进程做这样的事情:

start transaction;
update cfgNodes set name="foobar" where ID=29;

只是坐在那里(不 promise ，不回滚)。这显然是罪魁祸首 - 由于长时间运行的事务而占用锁的进程 - 我正试图找到罪犯。

另一个进程尝试:

-- The next line just prevents you from having to wait 50 seconds
set innodb_lock_wait_timeout=1;

update cfgNodes set name="foobar" where ID=29;

第二个进程得到 ERROR 1205 (HY000): Lock wait timeout exceeded;尝试重新启动事务(在 innodb_lock_wait_timeout 之后，默认 50 秒)

我如何找到有关罪魁祸首的任何信息？

Answer 1

标准的推荐来源帮助不大:

INFORMATION_SCHEMA.INNODB_TRX 显示了交易，但没有太多可以帮助我找到它的信息。只有 1 个表被锁定(在这个虚假的小例子中)，并且 trx_mysql_thread_id 是 4093。

mysql> select * from INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 280907
                 trx_state: RUNNING
               trx_started: 2018-11-30 00:35:06
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 4093
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

INFORMATION_SCHEMA.INNODB_LOCKS 为空，考虑到 documentation 是有意义的，因为只有一个事务，目前没有人在等待任何锁。另外 INNODB_LOCKS 无论如何都被弃用了。

SHOW ENGINE INNODB STATUS 没有用:根本没有提到cfgNodes

SHOW FULL PROCESSLIST 是空的，因为罪魁祸首现在实际上并没有运行查询。

但现在还记得之前的 trx_mysql_thread_id 吗？我们可以使用它来查看在该事务中执行的查询:

mysql> SELECT SQL_TEXT
    -> FROM performance_schema.events_statements_history ESH,
    ->      performance_schema.threads T
    -> WHERE ESH.THREAD_ID = T.THREAD_ID
    ->   AND ESH.SQL_TEXT IS NOT NULL
    ->   AND T.PROCESSLIST_ID = 4093
    -> ORDER BY ESH.EVENT_ID LIMIT 10;
+-----------------------------------------------+
| SQL_TEXT                                      |
+-----------------------------------------------+
| select @@version_comment limit 1              |
| start transaction                             |
| update cfgNodes set name="foobar" where ID=29 |
+-----------------------------------------------+
3 rows in set (0.00 sec)

瞧 - 我们现在可以看到每个剩余交易的最近 10 次查询的历史记录，这使我们能够找到罪魁祸首。