H2 database – Update from select sets out-of-date data after h2database upgrade to version 1.4.198

We have a simple counter in out project database. Until now we were using version 1.4.197 of H2 database. Executing below example snippet with this version always imply counter to be 5000. Upgrade to version 1.4.198 or above makes below code returning inconsistent results, usually between 1500 and 2000.

public static void main(String[] args) throws SQLException, InterruptedException {
        String url = "jdbc:h2:mem:testdb";

        Connection connection = DriverManager.getConnection(url);
        connection.prepareStatement("CREATE TABLE t1 (id INT PRIMARY KEY, counter INT)").execute();
        connection.prepareStatement("INSERT INTO t1 (id, counter) VALUES (1, 0)").execute();

        int threads = 10;
        int times = 500;

        ExecutorService service = Executors.newFixedThreadPool(threads);
        ExecutorCompletionService<Void> cs = new ExecutorCompletionService<>(service);

        for (int i = 0; i < threads; i++) {
            cs.submit(() -> {
                Connection conn = DriverManager.getConnection(url);
                IntStream.range(0, times).forEach($ -> {
                    try {
                        conn.prepareStatement("UPDATE t1 SET counter = (SELECT counter FROM t1 WHERE id = 1) + 1 WHERE id = 1").executeUpdate();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                });
                return null;
            });
        }

        for (int i = 0; i < threads; i++) {
            cs.take();
        }

        ResultSet resultSet = connection.prepareStatement("SELECT counter FROM t1 WHERE id = 1").executeQuery();
        resultSet.next();
        System.out.println("counter: " + resultSet.getInt("counter"));

    }

My assumption is that the select statement from below SQL is executed before other transaction release lock from the table, and then update is performed with out-of-date data from select statement.

"UPDATE t1 SET counter = (SELECT counter FROM t1 WHERE id = 1) + 1 WHERE id = 1"

Have anyone had similar issue?

Answer

Normal SELECT statement does not lock selected rows by itself and can read committed (old) values from locked rows. You don’t have a barrier around the whole command in your code, that’s why newer versions of H2 with their multi-threaded execution can perform such commands concurrently. Multiple commands may read the same old value and try to update the row. UPDATE locks the row, but subquery was already evaluated and the lock is released immediately after execution of the command due to auto-commit. You need to use SELECT … FOR UPDATE in the subquery.

UPDATE t1 SET counter = (SELECT counter FROM t1 WHERE id = 1 FOR UPDATE) + 1 WHERE id = 1

BTW, H2 1.4.198 is a beta-quality version with many known problems. It’s more safe to use 1.4.199 or 1.4.200 instead of it.