尝试锁定 h2 中的表时出现超时错误
我收到以下错误
在特定情况下，当不同的线程通过批量上传操作填充大量用户并且我试图查看不同网页上的所有用户的列表时， 。列表查询抛出以下超时错误。有没有办法设置这个超时，这样我就可以避免这个超时错误。
环境：h2（最新），Hibernate 3.3.x
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "USER"; SQL statement:
[50200-144]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.table.RegularTable.doLock(RegularTable.java:482)
at org.h2.table.RegularTable.lock(RegularTable.java:416)
at org.h2.table.TableFilter.lock(TableFilter.java:139)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:571)
at org.h2.command.dml.Query.query(Query.java:257)
at org.h2.command.dml.Query.query(Query.java:227)
at org.h2.command.CommandContainer.query(CommandContainer.java:78)
at org.h2.command.Command.executeQuery(Command.java:132)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:278)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:137)
at java.lang.Thread.run(Thread.java:619)
at org.h2.engine.SessionRemote.done(SessionRemote.java:543)
at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:152)
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 125 more
I get the following error under a certain scenario
When a different thread is populating a lot of users via the bulk upload operation and I was trying to view the list of all users on a different web page. The list query, throws the following timeout error. Is there a way to set this timeout so that I can avoid this timeout error.
Env: h2 (latest), Hibernate 3.3.x
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "USER"; SQL statement:
[50200-144]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.table.RegularTable.doLock(RegularTable.java:482)
at org.h2.table.RegularTable.lock(RegularTable.java:416)
at org.h2.table.TableFilter.lock(TableFilter.java:139)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:571)
at org.h2.command.dml.Query.query(Query.java:257)
at org.h2.command.dml.Query.query(Query.java:227)
at org.h2.command.CommandContainer.query(CommandContainer.java:78)
at org.h2.command.Command.executeQuery(Command.java:132)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:278)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:137)
at java.lang.Thread.run(Thread.java:619)
at org.h2.engine.SessionRemote.done(SessionRemote.java:543)
at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:152)
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 125 more
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
是的，您可以更改锁定超时。默认值相对较低：1 秒（1000 毫秒）。
在许多情况下，问题是另一个连接锁定了表，使用多版本并发也可以解决问题（将
;MVCC=true附加到数据库 URL）。编辑：不再支持
MVCC=true参数，因为自 h2 1.4 .200 对于 MVStore 引擎来说始终如此，它是默认引擎。Yes, you can change the lock timeout. The default is relatively low: 1 second (1000 ms).
In many cases the problem is that another connection has locked the table, and using multi-version concurrency also solves the problem (append
;MVCC=trueto the database URL).EDIT:
MVCC=trueparam is no longer supported, because since h2 1.4.200 it's always true for a MVStore engine, which is a default engine.我遇到了完全相同的问题并使用参数“MVCC=true”，它解决了它。您可以在 H2 文档中找到有关此参数的更多说明：http://www.h2database.com/html/高级.html#mvcc
I faced quite the same problem and using the parameter "MVCC=true", it solved it. You can find more explanations about this parameter in the H2 documentation here : http://www.h2database.com/html/advanced.html#mvcc
我想建议，如果您收到此错误，那么也许您不应该在批量数据库操作中使用事务。考虑对每个单独的更新执行事务：将整个批量导入视为事务是否有意义？可能不会。如果确实如此，那么 MVCC=true 或更大的锁定超时是一个合理的解决方案。
但是，我认为在大多数情况下，您看到此错误是因为您正在尝试执行非常长的事务 - 换句话说，您不知道自己正在执行非常长的事务。我自己就是这种情况，我只是更加注意如何写入记录（不使用事务或使用较小的事务），并且锁定超时问题得到了解决。
I'd like to suggest that if you are getting this error, then perhaps you should not be using a transaction on your bulk database operation. Consider instead doing a transaction on each individual update: does it make sense to think of an entire bulk import as a transaction? Probably not. If it does, then yes, MVCC=true or a bigger lock timeout is a reasonable solution.
However, I think for most cases, you are seeing this error because you are trying to perform a very long transaction - in other words you are not aware that you are performing a really long transaction. This was certainly the case for myself and I simply took more care on how I was writing records (either using no transactions or using smaller transactions) and the lock timeout issue was resolved.
对于那些在集成测试中遇到此问题的人（即服务器正在访问 h2 数据库，并且集成测试在调用服务器之前访问数据库以准备测试），请在测试之前向执行的脚本添加“提交”以确保在调用服务器之前，数据位于数据库中（没有 MVCC=true - 我发现如果默认情况下未启用它有点“奇怪”）。
For those having this issue with integration tests (i.e. server is accessing the h2 db and an integration test is accessing the db before calling the server, to prepare the test), adding a 'commit' to the script executed before the test makes sure that the data are in the database before calling the server (without MVCC=true - which I find is a bit 'weird' if it is not enabled by default).
我的连接字符串中有 MVCC=true 但仍然出现上面的错误。我添加了
;DEFAULT_LOCK_TIMEOUT=10000;LOCK_MODE=0并解决了问题I had MVCC=true in my connection string but still was getting error above. I had added
;DEFAULT_LOCK_TIMEOUT=10000;LOCK_MODE=0and problem was solved对于 2020 年的用户，请参阅 参考
基本上，该参考说：
From a 2020 user, see reference
Basically, the reference says:
我在 PlayFramework 中遇到了这个问题
它最终成为一种无限循环，因为我有一个
没有 except 会导致函数重复调用自身
I got this issue with the PlayFramework
It ended being an infinite loop of sorts because I had a
without an unless which caused the function to repeatedly call itself
使用 DBUnit、H2 和 Hibernate - 同样的错误，MVCC=true 有所帮助，但在删除数据后的任何测试中我仍然会收到错误。解决这些情况的方法是将实际的删除代码包装在事务中：
Working with DBUnit, H2 and Hibernate - same error, MVCC=true helped, but I would still get the error for any tests following deletion of data. What fixed these cases was wrapping the actual deletion code inside a transaction: