Timeout error trying to lock table in h2

Question

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

score 50 · Accepted Answer · edited Jan 20 '20 at 12:57

50

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.

edited Jan 20 '20 at 12:57

G. Demecki

10,145
3
58
58

answered Nov 12 '10 at 08:09

Thomas Mueller

48,905
14
116
132

I did set the timeout on the driver URL, but I still get the same error since the above operation takes probably around 30 seconds to complete. I just don't want other users who are using this page to see a crash during that time. Does that mean I will have to set the timeout to something more than 30 secs to avoid this issue? How will I find performance specific problems if the timeout is set to a large number – user339108 Nov 12 '10 at 09:52
Does the transaction really take 30 seconds? That sounds a bit long to me. Is it possible to split the transaction into multiple smaller transactions (for example 0.5 seconds each)? – Thomas Mueller Nov 12 '10 at 11:14
8

Using the parameter "MVCC=true" is the solution, changing the timeout does not fix this issue. – Bryan Hunt Sep 06 '12 at 13:41
1

@BryanHunt yes MVCC=true may also solve the problem, I have updated my answer. However the question was for a lock timeout, and increasing the lock timeout is also a solution in many cases. If it wasn't the solution for your use case that's OK, but the question didn't include a use case at all, so it really depends. – Thomas Mueller Sep 06 '12 at 14:43
@ThomasMueller: If iam using an in-memory database, how should or where should i set "mvcc=true". Adding to that iam not using any url in configuration as am using **** tag in my datasourse. – arun_kk Jun 30 '13 at 05:15
@arunsankarkk I'm not sure where, but I believe the database URL is set _somewhere_, it starts with `jdbc:h2:`. I guess you could do a search (grep) for it. – Thomas Mueller Jun 30 '13 at 14:14
@arunsankarkk You cannot actually specify the URL using the **** tag. You need to specify the datasource as you normally would using ****, etc. – Eric B. Jun 22 '14 at 03:03
@ThomasMueller sorry for digging out this question, but is it "safe" to configure hibernate access to H2 database with MVCC=true ? I need to do so to enable an SQL query within an insert trigger. By safe I mean, from the doc it seems quite experimental feature at this stage – remi Nov 13 '14 at 10:27
@remi I'm sorry, but MVCC in H2 is still experimental – Thomas Mueller Nov 13 '14 at 15:46
5

@ThomasMueller I have the same problem on a H2 DB, i tried the MVCC=true in the connectionurl but it didn't help – Gobliins Jan 15 '15 at 13:47
This really helped. been having this problem and ignored it for months. it currently become an issue and this fix helped – daptordarattler Feb 19 '19 at 11:47
@ThomasMueller Thanks a lot for the solution. I have a question, does setting MVCC=true, can possibly make the transactions inconsistent at times? Because as per my understanding, now multiple transactions can concurrently modify/read tables. – Sen Mar 16 '19 at 21:52
1

@Sen No, as concurrent modifications to the same rows are rejected (only one transaction will succeed) – Thomas Mueller Mar 19 '19 at 13:19

score 39 · Answer 2 · answered Jun 15 '11 at 11:44

39

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

answered Jun 15 '11 at 11:44

Bruno ARLIGUY

516
5
4

1

This is the correct answer, changing the lock timeout is not the solution. – Bryan Hunt Sep 06 '12 at 13:40
4

@BryanHunt it really depends. All you can say is that it is not the solution for your use case. – Thomas Mueller Sep 06 '12 at 14:44
@ThomasMueller Do we have any drawback if we use "MVCC=true"? – Roshan Wijesena Mar 17 '15 at 09:25
1

@RoshanWijesena yes, the MVCC mode is not as well tested in H2 version 1.3.x (with the PageStore). It is the default mode for version 1.4.x however (with the MVStore). – Thomas Mueller Mar 17 '15 at 10:19
6

I am using H2 DB 1.4.x where MVCC=true by default. But I am still getting error – jprism Jun 01 '16 at 20:06

score 7 · Answer 3 · answered Oct 28 '12 at 17:09

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.

score 6 · Answer 4 · answered Nov 07 '11 at 14:25

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

score 3 · Answer 5 · answered Oct 06 '18 at 14:20

3

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

answered Oct 06 '18 at 14:20

Ev.Rei.

251
2
8

2

It's sound poor as "LOCK_MODE=0" in H2 databse URL means "use Read Uncommitted". – Krzysztof Tomaszewski Jan 15 '19 at 13:36

score 1 · Answer 6 · answered May 21 '15 at 19:13

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();

score 1 · Answer 7 · edited Jun 20 '20 at 09:12

1

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

edited Jun 20 '20 at 09:12

Community

1
1

answered May 27 '20 at 02:30

José Braz

575
5
9

score 1 · Answer 8 · answered Sep 18 '11 at 22:30

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

Timeout error trying to lock table in h2

8 Answers8

Linked

Related