java MySQL InnoDB 挂起等待表级锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6185937/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL InnoDB hangs on waiting for table-level locks
提问by Andrey
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 PROCESSLISTshows many simple insert or update queries on different tables but all having status
我有一个大型的生产网络应用程序（Glassfish 3.1 + MySQL 5.5）。所有表都是 InnoDB。每几天应用一次完全挂起。
SHOW FULL PROCESSLIST在不同的表上显示许多简单的插入或更新查询，但都有状态
Waiting for table level lock
等待表级锁
Examples:
例子：
update user<br>
set user.hasnewmessages = NAME_CONST('in_flag',_binary'innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode=2
' 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)
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.
'Time' 最长的查询也在等待表级锁。请帮助弄清楚为什么 MySQL 试图获得级别锁以及什么可以锁定所有这些表。所有关于 InnoDB 锁定的文章都说如果你不强制它这样做，这个引擎不使用表锁定。
My my.cnfhas this:
我的my.cnf有这个：
Binary log is off. I have no "LOCK TABLES" or other explicit locking commands at all. Transactions are READ_UNCOMMITED.
二进制日志已关闭。我根本没有“锁定表”或其他显式锁定命令。交易是READ_UNCOMMITED。
SHOW ENGINE INNODB STATUSoutput:
http://avatar-studio.ru:8080/ph/imonout.txt
SHOW ENGINE INNODB STATUS输出：http: //avatar-studio.ru:
8080/ph/imonout.txt
回答by mogronalol
Are you using MSQLDump to backup your database while it is still being accessed by your application? This could cause that behaviour.
您是否正在使用 MSQLDump 来备份您的应用程序仍在访问的数据库？这可能会导致这种行为。
回答by TMS
I see you havily use NAME_CONST in your code. Just try not to use it. You know, mysql can be sometimes buggy (I also found several bugs), so I recommend don't rely on features which are not so common / well tested. It is related to column names, so maybe it locks something? Well it should't if it affects only the result, but who knows? This is suspicious. Moreover, this is marked as a function for internal use only.
我看到您在代码中随意使用了 NAME_CONST。尽量不要使用它。您知道，mysql 有时会出现错误（我也发现了几个错误），因此我建议不要依赖不那么常见/经过良好测试的功能。它与列名有关，所以它可能会锁定某些东西？好吧，如果它只影响结果，就不应该，但谁知道呢？这很可疑。此外，这被标记为仅供内部使用的功能。
回答by Fabian Barney
I think there are some situations when MySQL does a full table lock (i.e. using auto-inc). I found a link which may help you: http://mysqldatabaseadministration.blogspot.com/2007/06/innodb-table-locks.html
我认为在某些情况下 MySQL 会执行全表锁定（即使用 auto-inc）。我找到了一个可以帮助你的链接：http: //mysqldatabaseadministration.blogspot.com/2007/06/innodb-table-locks.html
Also review java persistence code having all con's commited/rollbacked and closed. (Closing always in finally block.)
还要查看所有已提交/回滚和关闭的 java 持久性代码。（总是在 finally 块中关闭。）
Try setting innodb_table_locks=0in MySQL configuration.
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_table_locks
尝试innodb_table_locks=0在 MySQL 配置中设置。
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_table_locks
Just a few ideas ...
只是一些想法......
回答by WattsInABox
This may seem simple, but you don't have a long-running select statement that is possibly locking out updates and inserts? There's no query that's actually running and not locked?
这可能看起来很简单，但是您没有一个可能会锁定更新和插入的长时间运行的 select 语句？没有实际运行且未锁定的查询？
回答by Joe Goggins
Have you considered using MyISAM instead of InnoDB?
您是否考虑过使用 MyISAM 而不是 InnoDB？
If you are not utilizing any transactional features, MyISAM might make more sense. Its simpler, easier to optimize, and since it doesn't have sophisticated transactional capabilities, easier to configure in your my.cnf.
如果您没有使用任何事务功能，MyISAM 可能更有意义。它更简单、更容易优化，而且由于它没有复杂的事务功能，因此更容易在 my.cnf 中进行配置。
Also, depending on the type of db load your app creates, MyISAM might be more appropriate. I prefer MyISAM for read-heavy applications, again, it's easier to configure and understand.
此外，根据您的应用程序创建的数据库负载类型，MyISAM 可能更合适。对于需要大量读取的应用程序，我更喜欢 MyISAM，同样，它更易于配置和理解。
Other suggestions:
其他建议：
It might be a good idea to find a way to not use NAME_CONST in your SQL. "This function was added in MySQL 5.0.12. It is for internal use only."When the documentation of an open source product says this, its probably a good idea to heed it's advise.
By default, MySQL stores all InnoDB tables & schemas data in 1 enormous file, there could be some kind of OS level lockingon that particular file that propogates to MySQL that prevents all table access. By using the innodb_file_per_tableoption , you may eliminate that potential issue. This also makes MySQL more space efficient.
找到一种在 SQL 中不使用 NAME_CONST 的方法可能是个好主意。 “此功能是在 MySQL 5.0.12 中添加的。仅供内部使用。” 当开源产品的文档说明这一点时，听取它的建议可能是个好主意。
默认情况下，MySQL 将所有 InnoDB 表和模式数据存储在 1 个巨大的文件中，该特定文件可能存在某种操作系统级别的锁定，该锁定会传播到 MySQL，从而阻止所有表访问。通过使用innodb_file_per_table选项，您可以消除该潜在问题。这也使 MySQL 的空间效率更高。
回答by Rahul Mandaliya
in this case you have to create several different database table with same column each other and do not inset more then 3000 row per table, in this case if you want to enter more data into table you have to create another dynamic table(generate table using code) and insert new data into this table and access data from that table. in your condition if more and more table will have to generate then you have to create new database.
在这种情况下，您必须创建多个彼此具有相同列的不同数据库表，并且每个表插入的行数不要超过 3000，在这种情况下，如果您想在表中输入更多数据，则必须创建另一个动态表（使用生成表代码）并将新数据插入该表并访问该表中的数据。在您的情况下，如果必须生成越来越多的表，那么您必须创建新数据库。
i think this tip will help you to design your database more carefully and solve error.
我认为这个技巧将帮助您更仔细地设计数据库并解决错误。
