H2 in cluster mode with multi-threading results in foreign key violation

[GoogleCodeExporter]

I also posted a thread at stackoverflow with no answers yet: 
http://stackoverflow.com/questions/19134116/h2-in-cluster-mode-with-multi-thread
ing-results-in-foreign-key-violation

When accessing the h2 database (version 1.3.172) on a windows7 machine in 
cluster mode I get foreign key violations. I've attached a simple example with 
two tables (A and B) and B references table A. Then I concurrently insert rows. 
When I have a foreign key constraint from B to A I get 
org.h2.jdbc.JdbcSQLException (Referential integrity constraint violation). 
Without the foreign key constraint all rows are inserted completely.

First here is the test-case:

int port1 = 9193, port2 = 9194;
String serverList = "localhost:" + port1 + ",localhost:" + port2;
String url1 = "jdbc:h2:tcp://localhost:" + port1 + "/test";
String url2 = "jdbc:h2:tcp://localhost:" + port2 + "/test";
String urlCluster = "jdbc:h2:tcp://" + serverList + "/test";

@Test
public void testClusterForeignKey() {

    try {
        String urlExtension = ";MVCC=true;AUTO_RECONNECT=TRUE"; // ;AUTO_RECONNECT=TRUE;MULTI_THREADED=1
        url1 += urlExtension;
        url2 += urlExtension;
        urlCluster += urlExtension;

        //Start the TCP servers:
        Server server1 = org.h2.tools.Server.createTcpServer(
                "-tcpPort", "" + port1, "-baseDir", getBaseDir() + "/node1").start();
        Server server2 = org.h2.tools.Server.createTcpServer(
                "-tcpPort", "" + port2, "-baseDir", getBaseDir() + "/node2").start();
        String user = getUser(), password = getPassword();
        Connection conn = getConnection(urlCluster, user, password);

        //Create table A:
        String sqlStmt = "CREATE CACHED TABLE A (" +
                "id BIGINT NOT NULL, " +
                "value varchar(100) NOT NULL)";
        Statement stat = conn.createStatement();
        stat.execute(sqlStmt);

        //Create table B:
        sqlStmt = "CREATE CACHED TABLE B (" +
                "id BIGINT NOT NULL, " +
                //"aid BIGINT NOT NULL," +
                "aid BIGINT NOT NULL REFERENCES  A(id) ON DELETE CASCADE," +
                "value varchar(100) NOT NULL)";

        stat = conn.createStatement();
        stat.execute(sqlStmt);

        //Create sequences for inserting data:
        sqlStmt = "create sequence A_SEQ";
        stat = conn.createStatement();
        stat.execute(sqlStmt);

        sqlStmt = "create sequence B_SEQ";
        stat = conn.createStatement();
        stat.execute(sqlStmt);

        final FkDml FkDml = new FkDml();
        Callable<String> task = new Callable<String>() {
            @Override
            public String call() {
                return FkDml.doDML();
            }
        };
        List<Callable<String>> tasks = Collections.nCopies(1000, task);
        ExecutorService executorService = Executors.newFixedThreadPool(1000);
        List<Future<String>> futures = null;
        try {
            futures = executorService.invokeAll(tasks);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        sqlStmt = "select count(*) from A";
        String resultId = queryOneValue(sqlStmt, conn);
        long countA = Long.parseLong(resultId);
        System.out.println("Rows in table A: " + countA);

        sqlStmt = "select count(*) from B";
        resultId = queryOneValue(sqlStmt, conn);
        long countB = Long.parseLong(resultId);
        System.out.println("Rows in table B: " + countB);

        conn.close();

        //There have to be 1000 rows in the database:
        assertEquals(countA,1000);
        assertEquals(countB,1000);

    } catch (SQLException e) {
        e.printStackTrace();
        fail();
    }
}

And these are the inserts that are executed concurrently by the executer 
service:

class FkDml {
    private final AtomicLong counter = new AtomicLong();
    public String doDML() {
        String urlCluster = "jdbc:h2:tcp://" + serverList + "/test";
        String urlExtension = ";MVCC=true;AUTO_RECONNECT=TRUE"; // ;AUTO_RECONNECT=TRUE;MULTI_THREADED=1
        urlCluster += urlExtension;
        try {
            long i = counter.incrementAndGet();
            Connection conn = getConnection(urlCluster, "sa", "123");
            conn.setAutoCommit(false);
            String sqlStmt = "insert into A (id,value) values (nextval('A_SEQ'),'testA" + i + "')";
            Statement stat = conn.createStatement();
            stat.execute(sqlStmt);

            sqlStmt = "select id from A where value = 'testA" + i + "'";
            String resultId = queryOneValue(sqlStmt, conn);
            long idFromA = Long.parseLong(resultId);
            System.out.println("ID from table A found: "+idFromA);

            sqlStmt = "insert into B (id,aid,value) values (nextval('B_SEQ')," + idFromA + ",'testB')";
            stat = conn.createStatement();
            stat.execute(sqlStmt);
            conn.setAutoCommit(true);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

What is the expected output? What do you see instead?
There should be no foreign key violations, because the insert into table A is 
alsways executed before insert into B.
But in cluster mode and with multiple concurrent request (only in this 
scenario) i get foreign key violations.

What version of the product are you using? On what operating system, file
system, and virtual machine?
version: 1.3.172
operation system: windows 7
file system: NTFS

Do you know a workaround?
Either do not use cluster mode or do no concurrent access to the cluster.

What is your use case, meaning why do you need this feature?
Because the cluster should run in a multi threaded environment.

How important/urgent is the problem for you?
It is important because otherwise the cluster mode cannot be used wich is 
important for the product.
It is not very urgent.

Kind regards,
Jens Stahl

Original issue reported on code.google.com by jensst...@gmail.com on 8 Oct 2013 at 10:06