MySQL InnoDB挂起等待表级锁 [英] MySQL InnoDB hangs on waiting for table-level locks

问题描述

我有一个大型的生产网络应用程序（Glassfish 3.1 + MySQL 5.5）。所有表都是InnoDB。每隔几天应用程序完全挂起。
SHOW FULL PROCESSLIST 在不同的表上显示许多简单的插入或更新查询，但都具有状态

I have a big production web-application (Glassfish 3.1 + MySQL 5.5). All tables are InnoDB. Once per several days application totally hangs. SHOW FULL PROCESSLIST shows many simple insert or update queries on different tables but all having status

等待表级锁定

Waiting for table level lock

示例：

update user<br>
set user.hasnewmessages = NAME_CONST('in_flag',_binary'\0' COLLATE 'binary')
where user.id = NAME_CONST('in_uid',66381)

insert into exchanges_itempacks
set packid = NAME_CONST('in_packId',332149), type = NAME_CONST('in_type',1), itemid = NAME_CONST('in_itemId',23710872)

具有最长时间的查询也在等待表级锁定。
请帮助弄清楚为什么MySQL试图获得级别锁定以及可以锁定所有这些表的内容。关于InnoDB锁定的所有文章都说这个引擎在没有强制它的情况下不使用表锁定。

Queries with the longest 'Time' are waiting for the table-level lock too. Please help to figure out why MySQL tries to get level lock and what can be locking all these tables. All articles about the InnoDB locking say this engine uses no table locking if you don't force it to do so.

我的 my.cnf 有这个：

innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode=2

二进制日志已关闭。我根本没有LOCK TABLES或其他显式锁定命令。交易是 READ_UNCOMMITED 。

Binary log is off. I have no "LOCK TABLES" or other explicit locking commands at all. Transactions are READ_UNCOMMITED.

SHOW ENGINE INNODB STATUS 输出：
http://avatar-studio.ru:8080/ph/imonout .txt

推荐答案

您是否正在使用MSQLDump备份数据库，而应用程序仍在访问它？这可能会导致这种行为。

Are you using MSQLDump to backup your database while it is still being accessed by your application? This could cause that behaviour.

这篇关于MySQL InnoDB挂起等待表级锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！