Reducing Thread contention on H2

Question

I'm trying to achieve better performance for insert on H2 from multiple threads.

I've tried four different setups, each with 20 concurrent threads doing inserts:

1. Shared connection;
2. Shared connection disabling autocommit;
3. Connection pool;
4. Connection pool disabling autocommit.

Got the best result with option 4:

Table created with:

create table foo (id int not null auto_increment primary key, name varchar(50))

Code for insert (c is a connection from org.h2.jdbcx.JdbcConnectionPool):

private static void insertDataWithConnection(Connection c, String n) throws SQLException {
    c.setAutoCommit(false);
    PreparedStatement ps = c.prepareStatement("insert into foo (name) values (?)");
    ps.setString(1, n);
    ps.executeUpdate();
    ResultSet rs = ps.getGeneratedKeys();
    rs.next();
    rs.getInt(1);
    rs.close();
    c.commit();
    c.setAutoCommit(true);
    ps.close();
}

The connection string:

jdbc:h2:test;AUTOCOMMIT=OFF;WRITE_DELAY=300;MVCC=TRUE;LOCK_MODE=0;FILE_LOCK=SOCKET

Is it possible to reduce the Thread contention somehow?

P.S.:

• Getting the generated key is important.
• Switching H2 to another embedded database may be an option; but, please, the answers should focus on H2.

Answer 1

We've used Multi-version Concurrency control and Multi-threaded options. They do seem to help with some of the contention issues, but they were not a silver bullet.