Hi,
I am witnessing a weird behavior.
1. I set up a test case and tried to create 200,000 rows using a
single thread (main for loop 200,000 times) to H2. All rows got added
in 135,517 ms. This all happened in Transaction mode using standard
@Transactional (propagation=Propagation.REQUIRED).
Main: 200000 assets inserted in batches of 10000 each completed in
135517 ms.
2. Now, I set up another test case and tried to insert 200,000 rows by
creating 4 threads, which will inserts 50,000 each.
First it failed with error as below.
09-07 18:47:56 jdbc[4]: exception
org.h2.jdbc.JdbcSQLException: Timeout trying to lock table
"TRACKED_ASSET100"; SQL statement:
insert into TRACKED_ASSET100 ( ......)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [50200-157]
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:506)
at org.h2.table.RegularTable.lock(RegularTable.java:440)
at org.h2.command.dml.Insert.insertRows(Insert.java:123)
at org.h2.command.dml.Insert.update(Insert.java:84)
at org.h2.command.CommandContainer.update(CommandContainer.java:71)
at org.h2.command.Command.executeUpdate(Command.java:212)
at
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
143)
at
org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:
1092)
I removed the @Transactional annotation just to make it work. That's a
question also if concurrent threads who are trying to write in batch
can cause other connections to timeout. I can increase the timeout if
thats the case.
Scenario:1, 50000 assets inserted in batches of 10000 each completed
in 106657 ms
Scenario:3, 50000 assets inserted in batches of 10000 each completed
in 117173 ms
Scenario:4, 50000 assets inserted in batches of 10000 each completed
in 144907 ms
Scenario:2, 50000 assets inserted in batches of 10000 each completed
in 147001 ms
Almost 4 times the time than the first test case.
In both scenarios, I had the exact same configuration for H2
connection.
Running in Mixed Mode but only using from the embedded node.
Exact same data which I am trying to insert.
I used connection pool in both cases.
H2 is being accessed using Spring JDBC API.
One difference though is that in the concurrent test case, I have no
transactional support but I thought it shall remove any overhead and
not add.
Connection Attrs:
/*SQL */SET LOG 0;
/*SQL */SET WRITE_DELAY 5000;
/*SQL */SET CACHE_SIZE 20000;
/*SQL */SET TRACE_LEVEL_FILE 2;
/*SQL */SET DB_CLOSE_DELAY 5;
Are there any special configuation settings if I need to use H2 with
multiple threads. I saw something like MULTI THREADED mode but that is
mentioned as experimental.
I am kind of clueless totally. Please help.
regards
Navjot Singh
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
