[h2] Strange Locking errors with simple recursive query

[a***@gmail.com]

Dear All,

we use H2 extensively and think it is a great software. We have a small
database with something around 50 tables, with perhaps 1000 records each.
The server has 16 cores and 16 GB Ram.
Also the same Object Model (Tables, Indexes and Queries) runs on large
Oracle databases with more the 100 Mill. records and we use a lot of
recursive queries, so far without
any problems.

So I was very surprised when this came up:

Caused by: java.sql.SQLException: org.h2.jdbc.JdbcSQLTimeoutException:
Timeout trying to lock table {0}; SQL statement:
SELECT /*+PARALLEL*/
b.ID_INSTRUMENT
, c.ID_COLLATERAL
, a.VALID_DATE
, a.PRIORITY
, a.LIMIT_AMOUNT
, a.ID_CURRENCY
, a.END_DATE
FROM cfe.INSTRUMENT_COLLATERAL_HST a
INNER JOIN cfe.INSTRUMENT_REF b
ON a.id_instrument_ref = b.id_instrument_ref
INNER JOIN cfe.COLLATERAL_REF c
ON a.id_collateral_ref = c.id_collateral_ref
INNER JOIN cfe.instrument d
ON b.id_instrument=d.id_instrument
WHERE valid_date = (SELECT Max(valid_date)
FROM cfe.INSTRUMENT_COLLATERAL_HST
WHERE ID_COLLATERAL_REF = a.ID_COLLATERAL_REF
AND valid_date<=?
)

[50200-197]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:505)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:425)
at org.h2.message.DbException.get(DbException.java:192)
at org.h2.command.Command.filterConcurrentUpdate(Command.java:329)
at org.h2.command.Command.executeQuery(Command.java:205)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:341)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:170)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.h2.message.DbException: Row "11" not found in primary index
"CFE.INSTRUMENT_COLLATERAL_HST_DATA" [90143-197]
at org.h2.message.DbException.get(DbException.java:203)
at org.h2.mvstore.db.MVPrimaryIndex.getRow(MVPrimaryIndex.java:270)
at org.h2.mvstore.db.MVTable.getRow(MVTable.java:477)
at
org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.get(MVSecondaryIndex.java:454)
at org.h2.index.IndexCursor.get(IndexCursor.java:295)
at org.h2.table.TableFilter.getValue(TableFilter.java:1065)
at org.h2.expression.ExpressionColumn.getValue(ExpressionColumn.java:195)
at org.h2.expression.Comparison.getValue(Comparison.java:246)
at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:86)
at org.h2.expression.Expression.getBooleanValue(Expression.java:204)
at org.h2.command.dml.Select.isConditionMet(Select.java:386)
at org.h2.command.dml.Select.gatherGroup(Select.java:447)
at org.h2.command.dml.Select.queryGroup(Select.java:425)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:801)
at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:145)
at org.h2.command.dml.Query.query(Query.java:420)
at org.h2.command.dml.Query.query(Query.java:382)
at org.h2.expression.Subquery.getValue(Subquery.java:36)
at org.h2.expression.Comparison.getValue(Comparison.java:266)
at org.h2.expression.Expression.getBooleanValue(Expression.java:204)
at org.h2.table.TableFilter.isOk(TableFilter.java:588)
at org.h2.table.TableFilter.next(TableFilter.java:533)
at
org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1700)
at org.h2.result.LazyResult.hasNext(LazyResult.java:79)
at org.h2.result.LazyResult.next(LazyResult.java:59)
at org.h2.command.dml.Select.queryFlat(Select.java:642)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:806)
at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:145)
at org.h2.command.dml.Query.query(Query.java:420)
at org.h2.command.dml.Query.query(Query.java:382)
at org.h2.command.CommandContainer.query(CommandContainer.java:115)
at org.h2.command.Command.executeQuery(Command.java:201)
... 3 more

The used connection string is:

jdbc:h2:tcp://localhost//home/are/.manticore/ifrsbox;PAGE_SIZE=8192;MVCC=TRUE;CACHE_SIZE=200000;AUTO_RECONNECT=TRUE;DB_CLOSE_DELAY=15;DB_CLOSE_ON_EXIT=FALSE;DEFRAG_ALWAYS=true;
LOCK_TIMEOUT=10000;LOCK_MODE=1;MULTI_THREADED=TRUE

The error is 100% reproducible. The interesting thing is, that it works
when I unroll the recursive query:

SELECT /*+PARALLEL*/
b.ID_INSTRUMENT
, c.ID_COLLATERAL
, a.VALID_DATE
, a.PRIORITY
, a.LIMIT_AMOUNT
, a.ID_CURRENCY
, a.END_DATE
FROM cfe.INSTRUMENT_COLLATERAL_HST a
INNER JOIN cfe.INSTRUMENT_REF b
ON a.id_instrument_ref = b.id_instrument_ref
INNER JOIN cfe.COLLATERAL_REF c
ON a.id_collateral_ref = c.id_collateral_ref
INNER JOIN cfe.instrument d
ON b.id_instrument=d.id_instrument
*WHERE valid_date = (SELECT Max(valid_date)*
* FROM cfe.INSTRUMENT_COLLATERAL_HST*
* WHERE ID_COLLATERAL_REF = a.ID_COLLATERAL_REF*
* AND valid_date<={d '2018-06-12'})*

--> fails

SELECT /*+PARALLEL*/ b.ID_INSTRUMENT
, c.ID_COLLATERAL
, a.VALID_DATE
, a.PRIORITY
, a.LIMIT_AMOUNT
, a.ID_CURRENCY
, a.END_DATE
FROM cfe.INSTRUMENT_COLLATERAL_HST a
INNER JOIN cfe.INSTRUMENT_REF b
ON a.id_instrument_ref = b.id_instrument_ref
*INNER JOIN (SELECT ID_COLLATERAL_REF*
* , Max(valid_date) valid_date*
* FROM cfe.INSTRUMENT_COLLATERAL_HST*
* WHERE valid_date <= {d '2018-06-12'}*
* GROUP BY ID_COLLATERAL_REF) a1*
* ON a.ID_COLLATERAL_REF = a1.ID_COLLATERAL_REF*
* AND a.valid_date = a1.valid_date*
INNER JOIN cfe.COLLATERAL_REF c
ON a.id_collateral_ref = c.id_collateral_ref
INNER JOIN cfe.instrument d
ON b.id_instrument = d.id_instrument

--> returns in less than 1 second and shows 0 records (correctly).

We use the latest sources from GITHub. Do you have any idea, what is
happening there and how to prevent it w/o unrolling all the recursive
queries manually?
Thank you already and best regards!

[Noel Grandin]

Is it possible you could create a standalone test case for this?

Also using anything other than the default LOCK_MODE is not well tested, so
unless you have a really good reason, I would remove that LOCK_MODE setting
and leave it as default.

[a***@gmail.com]

Post by Noel Grandin
Is it possible you could create a standalone test case for this?
Also using anything other than the default LOCK_MODE is not well tested,
so unless you have a really good reason, I would remove that LOCK_MODE
setting and leave it as default.

Dear Noel,

thank you for your prompt response. Usually we do not set a LOCK_MODE at
all, we only started to tamper with that in order to work around the
problem.
I have tested all the available LOCK_MODES, without success or any
difference.

I could provide the database, shrunk down to the minimal test case, but how
shall I make it accessible to you?

Cheers!

[Noel Grandin]

what I really want is a set of SQL commands that recreates the problem.

i.e. a small script that creates just the necessary tables and runs the
problematic query

[a***@gmail.com]

Post by Noel Grandin
what I really want is a set of SQL commands that recreates the problem.
i.e. a small script that creates just the necessary tables and runs the
problematic query

I have created such a script (the needed tables and indexes and full set of
data) and was not able to trigger that problem. So I have closed and
re-opened the H2 database and also have not been able to trigger that
problem again.

Sorry for wasting your time.

[neoe]

Hi,

I'd like to give some other information if it is on this topic. (sorry for
my English also)

say we make 3 same concurrent update transaction on H2 db, each looks like
"update table1 on row1; sleep 50ms(make the transaction take some time);
update table1 on row1",

then It has big chance to see error: "Timeout trying to lock table"

tweak the timeout config value does not help, and I don't think we need to
set it, because the case is so normal in real life usage.


---
neoe

Post by a***@gmail.com
Dear All,
we use H2 extensively and think it is a great software. We have a small
database with something around 50 tables, with perhaps 1000 records each.
The server has 16 cores and 16 GB Ram.
Also the same Object Model (Tables, Indexes and Queries) runs on large
Oracle databases with more the 100 Mill. records and we use a lot of
recursive queries, so far without
any problems.
SELECT /*+PARALLEL*/