H2是否支持可序列化的隔离级别？ [英] Does H2 support the serializable isolation level?

问题描述

维基百科将Phantom读取现象描述为：

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.

它还声明具有可序列化的隔离级别，幻影读取是不可能的。我试图确保它在H2中是这样，但要么我想错了，要么我做错了什么，或者H2出了什么问题。不过，这里是代码：

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  ???
    }
}

在这里，我启动2个并发连接并配置其中一个它们具有可序列化的事务隔离。之后，我确保两者都看不到任何数据。然后，使用 connection1 ，我插入一个新行。在它之后，我确保这个新行对 connection1 可见，但不对 connection2 可见。然后，我提交更改并期望 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).

但这不会发生：最后一个 selectAll（connection2）返回刚刚插入并行连接的行。 我错了，这种行为是预期的，还是H2出了问题？

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?

以下是辅助方法：

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();
    }
}

完整的测试在这里：https://gist.github.com/loki2302/26f3c052f7e73fd22604

我用H2 1.4.185。

I use H2 1.4.185.

推荐答案

当启用隔离级别可序列化时，在连接1的前两个读取操作中存在悲观锁定2分别应该导致两个共享（写）锁。

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.

后续 insertOne（connection1）需要一个范围锁与来自外来交易的共享锁不兼容2.因此，连接1将进入等待（轮询）状态。不使用 setQueryTimeout（1）您的应用程序将挂起。

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.

关于 https://en.wikipedia.org/wiki/Isolation_（database_systems）#Phantom_reads 您应该更改您的申请（不使用 setQueryTimeout ）允许以下计划，通过手动启动两个JVM实例或使用不同的线程：

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        |               |

如果不支持serializable，你会看到：

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

这篇关于H2是否支持可序列化的隔离级别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！