Does H2 support the serializable isolation level?

Question

Wikipedia describes the Phantom read phenomenon as:

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

It also states that with serializable isolation level, Phantom reads are not possible. I'm trying to make sure it is so in H2, but either I expect the wrong thing, or I do a wrong thing, or something is wrong with H2. Nevertheless, here's the code:

try(Connection connection1 = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    connection1.setAutoCommit(false);

    try(Connection connection2 = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
        connection2.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        connection2.setAutoCommit(false);

        assertEquals(0, selectAll(connection1));
        assertEquals(0, selectAll(connection2)); // A: select

        insertOne(connection1);                  // B: insert

        assertEquals(1, selectAll(connection1));
        assertEquals(0, selectAll(connection2)); // A: select

        connection1.commit();                    // B: commit for insert

        assertEquals(1, selectAll(connection1));
        assertEquals(0, selectAll(connection2)); // A: select  ???
    }
}

Here, I start 2 concurrent connections and configure one of them to have serializable transaction isolation. After it, I make sure that both don't see any data. Then, using connection1, I insert a new row. After it, I make sure that this new row is visible to connection1, but not to connection2. Then, I commit the change and expect the connection2 to keep being unaware of this change. Briefly, I expect all my A: select queries to return the same set of rows (an empty set in my case).

But this does not happen: the very last selectAll(connection2) returns the row that has just been inserted in a parallel connection. Am I wrong and this behavior is expected, or is it something wrong with H2?

Here are the helper methods:

public void setUpDatabase() throws SQLException {
    try(Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
        try (PreparedStatement s = connection.prepareStatement("create table Notes(text varchar(256) not null)")) {
            s.executeUpdate();
        }
    }
}

private static int selectAll(Connection connection) throws SQLException {
    int count = 0;
    try (PreparedStatement s = connection.prepareStatement("select * from Notes")) {
        s.setQueryTimeout(1);
        try (ResultSet resultSet = s.executeQuery()) {
            while (resultSet.next()) {
                ++count;
            }
        }
    }

    return count;
}

private static void insertOne(Connection connection) throws SQLException {
    try (PreparedStatement s = connection.prepareStatement("insert into Notes(text) values(?)")) {
        s.setString(1, "hello");
        s.setQueryTimeout(1);
        s.executeUpdate();
    }
}

The complete test is here: https://gist.github.com/loki2302/26f3c052f7e73fd22604

I use H2 1.4.185.

Answer 1

In presence of pessimistic locking when enabling isolation level "serializable" your first two read operations on connection 1 and 2 respectively should result in two shared (write) locks.

The subsequent insertOne(connection1) needs a range lock being incompatible with a shared lock from an alien transaction 2. Thus connection 1 will go into "wait" (polling) state. Without using setQueryTimeout(1) your application would hang.

With respect to https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads you should alter your application (not using setQueryTimeout) to allow for the following schedule, either by manually starting two JVM instances or by using different threads:

Transaction 1 | Transaction 2 | Comment
--------------+---------------+--------
    -         | selectAll     | Acquiring shared lock in T2
insert        |     -         | Unable to acquire range lock
  wait        |     -         | T1 polling
  wait        | selectAll     | T2 gets identical row set
  wait        |     -         |
  wait        | commit        | T2 releasing shared lock
              |               | T1 resuming insert
commit        |               |

In case "serializable" is not being supported you will see:

Transaction 1 | Transaction 2 | Comment
--------------+---------------+--------
    -         | selectAll     | Acquiring shared lock in T2
insert        |     -         | No need for range lock due to missing support
commit        |               | T1 releasing all locks
              | selectAll     | T2 gets different row set