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.
SERIALIZABLEincludesREPEATABLE_READ, so it's fair enough if H2 handlesREPEATABLE_READasSERIALIZABLE. Setting both the connections toSERIALIZABLEdidn't help. This indeed should not be a global setting: the app may be happy enough with the defaultREAD_COMMITTEDfor some operations, but for others it might wantSERIALIZABLE. – RheologyPlease note MVCC is enabled in version 1.4.x by default, when using the MVStore. In this case, table level locking is not used. Instead, rows are locked for update, and *read committed* is used in all cases (changing the isolation level has no effect). Looks like read commited is used by default in MVCC. – Outlander