Does H2 support serializable isolation?

Question

Wikipedia describes the Phantom reading phenomenon as:

A Phantom reading occurs when two identical queries are executed during a transaction, and the set of rows returned by the second query is different from the first.

It also states that with a serialized isolation level, Phantom reading is not possible. I'm trying to make sure that this is the case in H2, but I expect something is wrong, or I'm wrong, or something is wrong with H2. However, here is 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 launch 2 simultaneous connections and configure one of them to serializable transaction isolation. After that, I make sure that both do not see any data. Then, using connection1 , I insert a new line. After that, I make sure that this new line is visible for connection1 , but not for connection2 . Then I commit the change and expect connection2 not be aware of this change. In short, 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 last selectAll(connection2) returns the string that was just inserted into the parallel connection. Am I mistaken, and is this behavior 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(); } } 

Full test here: https://gist.github.com/loki2302/26f3c052f7e73fd22604

I am using H2 1.4.185.

Answer 1

If there is a pessimistic lock when you turn on the serializable isolation level, your first two read operations in connections 1 and 2, respectively, should lead to two common (write) locks.

Subsequently, insertOne(connection1) requires a range lock that is incompatible with the general lock from someone else's transaction 2. Thus, connection 1 enters the standby (polling) state. Without using setQueryTimeout(1) your application will freeze.

Regarding https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads , you should change your application (without using setQueryTimeout ) to allow the following schedule, either manually launch two JVM instances or use 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 | | 

If "serializable" is not 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