Timeout error trying to lock table in h2

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.Load开发者_JS百科er.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    at org.hibernate.loader.Loader.doList(Loader.java:2228)
    ... 125 more

---

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=true to the database URL).

EDIT: MVCC=true param is no longer supported, because since h2 1.4.200 it's always true for a MVStore engine, which is a default engine.

---

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

---

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).

---

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.

---

I had MVCC=true in my connection string but still was getting error above. I had added ;DEFAULT_LOCK_TIMEOUT=10000;LOCK_MODE=0 and problem was solved

---

I got this issue with the PlayFramework

JPAQueryException occured : Error while executing query from models.Page where name = ?: Timeout trying to lock table "PAGE"

It ended being an infinite loop of sorts because I had a

@Before

without an unless which caused the function to repeatedly call itself

@Before(unless="getUser")

---

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:

Transaction tx = session.beginTransaction();
...delete stuff
tx.commit(); 

---

From a 2020 user, see reference

Basically, the reference says:

Sets the lock timeout (in milliseconds) for the current session. The default value for this setting is 1000 (one second).

This command does not commit a transaction, and rollback does not affect it. This setting can be appended to the database URL: jdbc:h2:./test;LOCK_TIMEOUT=10000