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!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
