MySQL InnoDB在等待表级锁时出现挂起的情况

Question

我有一个大型的生产级Web应用程序（Glassfish 3.1 + MySQL 5.5）。 所有的表都是InnoDB引擎。但每隔几天应用程序就会完全挂起。

SHOW FULL PROCESSLIST

显示许多简单的插入或更新查询，涉及不同的表，但所有这些查询的状态都是：

等待表级锁定

例如：

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)

具有最长“Time”的查询也在等待表级锁。请帮忙找出MySQL为什么尝试获取级别锁以及可能正在锁定所有这些表的内容。关于InnoDB锁定的所有文章都说，如果您不强制它这样做，则此引擎不使用表锁。

我的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。

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

- Andrey

---

为什么你要将隔离级别设置为“READ_UNCOMMITED”呢，这是一种脏模式，我绝不建议在生产服务器上设置它。 - ovais.tariq

这里可能有一些有用的信息：InnoDB锁模式，特别是意向锁和死锁可能的原因，如文档后面所述。还请注意文末的用户评论。 - Mike

Answer 1

你是否在应用程序访问数据库时使用MSQLDump来备份你的数据库？这可能导致这种行为。

- mogronalol

---

这是我生产环境的问题。我刚刚读到可以通过使用选项--single-transaction --quick来解决innodb数据库的问题，所以我会尝试一下。 - runholen

Answer 2

我看到你在代码中大量使用NAME_CONST。尽量不要使用它。你知道，MySQL有时可能会出现错误（我也发现了一些错误），所以我建议不要依赖那些不太常见/经过充分测试的功能。这与列名有关，所以可能会锁定某些内容？好吧，如果只影响结果，那就不应该，但谁知道呢？这很可疑。此外，这被标记为仅供内部使用的函数。

- Tomas

Answer 3

我认为在某些情况下MySQL会进行全表锁定（例如使用自增）。

我找到了一个链接，可能会对你有所帮助：http://mysqldatabaseadministration.blogspot.com/2007/06/innodb-table-locks.html

同时，请检查Java持久化代码是否已经提交/回滚和关闭（关闭总是在finally块中执行）。

尝试在MySQL配置中设置innodb_table_locks=0。 http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_table_locks

这只是一些想法...

- Fabian Barney

---

Andrey已经设置了innodb_autoinc_lock_mode=2，这将确保没有插入类语句会获取任何自增锁。 - ovais.tariq

Answer 4

在这种情况下，您需要创建几个具有相同列的不同数据库表，并且每个表不要插入超过3000行。如果您想向表中输入更多数据，则必须创建另一个动态表（使用代码生成表），并将新数据插入该表并从该表访问数据。如果需要生成越来越多的表，则必须创建新数据库。

我认为这个提示将帮助您更仔细地设计数据库并解决错误。

- Rahul Mandaliya

Answer 5

这可能看起来很简单，但是你没有一个长时间运行的 select 语句，可能会锁定更新和插入吗？没有正在运行且未被锁定的查询吗？

- WattsInABox

Answer 6

你有没有考虑使用MyISAM而不是InnoDB？

如果你没有使用任何事务特性，那么MyISAM可能更合适。它更简单、更易于优化，并且由于它没有复杂的事务能力，在my.cnf中更容易配置。

此外，根据你的应用程序创建的数据库负载类型，MyISAM可能更合适。我更喜欢在读取密集型应用程序中使用MyISAM，因为它更容易配置和理解。

其他建议：

• 最好找到一种方法不使用SQL中的NAME_CONST。 "此函数是在MySQL 5.0.12中添加的，仅供内部使用。" 当开源产品的文档这样说时，遵循它的建议可能是一个好主意。

• 默认情况下，MySQL将所有InnoDB表和模式数据存储在一个巨大的文件中，可能存在某种OS级别的锁定，该锁定会传播到MySQL，从而阻止所有表访问。通过使用innodb_file_per_table选项，您可以消除潜在问题。这也使MySQL更节省空间。

- Joe Goggins