Timeout error trying to lock table in h2

h2
user339108 picture user339108 · Nov 12, 2010 · Viewed 43.5k times · Source

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

Answer

Thomas Mueller picture Thomas Mueller · Nov 12, 2010

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.

Related questions

Spring Boot default H2 jdbc connection (and H2 console)

I am simply trying to see the H2 database content for an embedded H2 database which spring-boot creates when I don't specify anything in my application.properties and start with mvn spring:run. I can see hibernate JPA creating the …

Read More
H2 in-memory database. Table not found

I've got a H2 database with URL "jdbc:h2:test". I create a table using CREATE TABLE PERSON (ID INT PRIMARY KEY, FIRSTNAME VARCHAR(64), LASTNAME VARCHAR(64));. I then select everything from this (empty) table using SELECT * FROM PERSON. So far, …

Read More
Frontend tool to manage H2 database

How to use H2 database's integrated managment frontend? For operations such as create table, alter table, add column, and so on.

Read More