[h2] "Unique index or primary key violation" at concurrent insers (MULTI_THREADED)

[Anatolii K]

Hi

I've attached test which generates the exception if MULTI_THREADED=1: 

org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: 
"PRIMARY KEY ON PUBLIC.TRAN"; SQL statement:
INSERT INTO tran (id) values(?) [23505-193]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:138)
at org.h2.mvstore.db.MVTable.addRow(MVTable.java:704)
at org.h2.command.dml.Insert.insertRows(Insert.java:156)
at org.h2.command.dml.Insert.update(Insert.java:114)
at org.h2.command.CommandContainer.update(CommandContainer.java:98)
at org.h2.command.Command.executeUpdate(Command.java:258)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:344)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:158)
at java.lang.Thread.run(Unknown Source)

at org.h2.engine.SessionRemote.done(SessionRemote.java:624)
at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:191)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:201)
at Arte.call(Bug_H2_2.java:82)
at Arte.call(Bug_H2_2.java:64)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

In case MULTI_THREADED=0 this test doesn't fail


-- 
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Bug_H2_2 {

    static String dbUrl = "jdbc:h2:tcp://localhost:9002/nioMemFS:db;DB_CLOSE_DELAY=-1;MULTI_THREADED=1";
    static String user = "sa", pwd = "";
    static int threadCount = 100;

    public static void main(String args[]) throws InterruptedException, SQLException {
        Arte[] artes = new Arte[threadCount];
        Connection dbConnect;
        try {
            Class.forName("org.h2.Driver");
            dbConnect = DriverManager.getConnection(dbUrl, user, pwd);
            DbPreparator.prepareScheme(dbConnect);
            System.out.println("DB scheme prepared");

            for (int i = 0; i < threadCount; i++) {
                artes[i] = new Arte(DriverManager.getConnection(dbUrl, user, pwd), i);
            }
            System.out.println("ARTEs created");
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("DB Connection Failed: " + dbUrl);
            e.printStackTrace();
            return;
        }

        ExecutorService threadPool = Executors.newFixedThreadPool(threadCount);
        for (int i = 0; i < threadCount; i++) {
            threadPool.submit(artes[i]);
        }

        while (true) {
            Thread.sleep(100);
        }
    }
}

class DbPreparator {

    public static final int OBJ_CNT = 10000;
    private static final String[] SQLs = new String[]{
        "CREATE TABLE IF NOT EXISTS TRAN ("
        + "     ID NUMBER(18,0) not null PRIMARY KEY\n"
        + ")"
    };

    public static void prepareScheme(Connection db) throws SQLException {
        for (String sql : SQLs) {
            db.createStatement().execute(sql);
            db.commit();
        }
    }

}

class Arte implements Callable<Integer> {

    Connection db;
    PreparedStatement insertTranStmt;
    long tranId;

    public Arte(Connection db_, int number) throws SQLException {
        db = db_;
        db.setAutoCommit(false);
        insertTranStmt = db.prepareStatement("INSERT INTO tran (id) values(?)");
        tranId = number * 1_000_000_000 + System.currentTimeMillis(); //to garantee uniques
    }

    @Override
    public Integer call() {
        try {
            while (true) {
                insertTranStmt.setLong(1, tranId++);
                insertTranStmt.execute();
                db.commit();
            }
        } catch (SQLException e) {
            System.out.println("DB write error: ");
            e.printStackTrace();
            System.exit(0);
        }
        return null;
    }
}