I am using h2 (1.4.199) as the basis of a "data-preparation"-tool, i.e.
with a focus on creating/editing/dropping tables. I am aware that h2
(mvstore) may not have been developed for such an application.
My problem: I run into an exception on multi-threaded table editing. The
attached 72-line java program starts two threads that repeatedly
create/edit/drop each of them one table. After a few cycles there seems to
be a deadlock that seems to be resolved after 2000 ms, one of the two
threads dies (in my code), the other continues. Here is a typical output on
my computer (core-i5 with 8Gbyte).
Started two threads 'AAA' and 'BBB'
thread AAA required 2021 ms at step: 39
thread BBB stopped with exception at step: 37
msg: Timeout trying to lock table "SYS"; SQL statement:
alter table BBB add column if not exists X BIGINT as -1L [50200-199]
thread: AAA finished OK
Is there anything I could change to prevent this error ?
regards, rafel
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/df0fd8f0-a65a-4903-873f-ac445dc1fd7e%40googlegroups.com.
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import org.h2.tools.Server;
public class Test {
static Connection connection;
static ExecutorService threadPool = Executors.newCachedThreadPool();
public static void main(String[] args) {
try {
// start h2
String dbURL = "jdbc:h2:./testMVCC;MVCC=true;AUTO_SERVER=TRUE;ALIAS_COLUMN_NAME=TRUE";
Server.createTcpServer().start();
// create two threads
for (String x : new String[] {"AAA", "BBB"}) {
final String tName = x;
threadPool.submit(new Runnable() {
Connection connection = DriverManager.getConnection(dbURL);
void execute(String sql) throws SQLException {
connection.createStatement().execute(sql);
}
@Override
public void run() {
int step=0;
try {
// repeat three statements: create/edit/drop a table
for (step=0; step<1000; step++) {
Long start = System.currentTimeMillis();
execute("create table if not exists " + tName + " as select 7");
execute("alter table " + tName + " add column if not exists X BIGINT as -1L");
execute("drop table if exists " + tName );
Long time = System.currentTimeMillis() - start;
if (time > 1000 ) {
System.out.println("\nthread " + tName + " required "
+ (System.currentTimeMillis() - start) + " ms at step: " + step);
}
}
System.out.println("\nthread: " + tName + " finished OK");
} catch (Exception e) {
System.out.println("\nthread " + tName + " stopped with exception at step: " + step
+ "\n\tmsg: " + e.getLocalizedMessage());
}
}
});
}
System.out.println("Started two threads 'AAA' and 'BBB'");
} catch (SQLException e) {
System.out.println(e.getLocalizedMessage());
}
}
}
