System objectIds are recycled twice when CTE queries are executed. #3040
Description
The problem
We use version 1.4.199, however it can be reproduced with 1.4.200 as well.
In our web application, data queries with CTE are interleaved with queries from other transactions(DDL), which are creating new tables in the schema.
Often some of DDLs fail with following error:
org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException:
Unique index or primary key violation:
"PRIMARY KEY ON PUBLIC.SYS(ID) [76, 0, 0, STRINGDECODE('CREATE MEMORY TABLE \""PUBLIC\"".\""EXAMPLE_1_9\""(\n \""GENERATED_ID\"" BIGINT DEFAULT (NEXT VALUE FOR \""PUBLIC\"".\""SYSTEM_SEQUENCE_AEB43323_1604_47D9_9BEC_9224FCBFADEB\"") NOT NULL NULL_TO_DEFAULT SEQUENCE \""PUBLIC\"".\""SYSTEM_SEQUENCE_AEB43323_1604_47D9_9BEC_9224FCBFADEB\"",\n \""SIMPLE_VALUE\"" INT\n)')]";
SQL statement: CREATE TABLE public.EXAMPLE_2_9 (GENERATED_ID IDENTITY PRIMARY KEY, SIMPLE_VALUE INT) [23505-199]
I tried to figure out what's going on there, and it seems to me that the culprit is the code here.
I observed that after call to super.stop(); objectIds used during transaction will be recycled.
After that clearCTE(session, prepared); kicks in and once again registers some of objectIds, which were already recycled in a previous step.
When MVStore is enabled, mentioned recycling operation will take place while connection will be being borrowed from the pool to execute next transaction.
By that time other transaction may try to reuse this objectId, as current implementation of id generation tries to make the most of available values.
As a really dirty and ugly fix, I can imagine that clearing session's idsToRelease before returning from this method could help, but I am not sure if it's always the case that all of them will be recycled in super.stop(); call.
Code
One can reproduce the issue by running following piece of code.
Four threads are reading the data with CTE query while the rest creates new tables.
package com.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.stream.IntStream;
import java.util.stream.Stream;
public class Main {
public static final String CONNECTION_URL = "jdbc:h2:mem:test";
private static final String CREATE_TABLE = "CREATE TABLE public.EXAMPLE_%s (GENERATED_ID IDENTITY PRIMARY KEY, SIMPLE_VALUE INT)";
public static void main(String[] args)
throws InterruptedException, ClassNotFoundException, ExecutionException, SQLException {
Class.forName("org.h2.Driver");
Connection initialConnection = DriverManager.getConnection(CONNECTION_URL);
executeStatement(
initialConnection,
String.format(CREATE_TABLE, 0));
ExecutorService executor = Executors.newFixedThreadPool(8);
IntStream.range(1, 4)
.boxed()
.<Runnable>flatMap(number -> Stream.of(
() -> {
try (Connection connection = DriverManager.getConnection(CONNECTION_URL)) {
for (int i = 0; i < 100; i++) {
executeStatement(
connection,
String.format(CREATE_TABLE, number + "_" + i));
}
} catch (Exception e) {
e.printStackTrace();
}
},
() -> {
try (Connection connection = DriverManager.getConnection(CONNECTION_URL)) {
String tableName = String.format("temporaryTable%s", 0);
String query = String.format(
"WITH %s (avgValue) as (SELECT avg(SIMPLE_VALUE) FROM public.EXAMPLE_0) SELECT * FROM %s ",
tableName,
tableName);
for (int i = 0; i < 100; i++) {
executeStatement(connection, query);
}
} catch (Exception e) {
e.printStackTrace();
}
}
)).forEach(executor::submit);
executor.shutdown();
executor.awaitTermination(1, TimeUnit.MINUTES);
}
private static void executeStatement(Connection connection, String statement)
throws SQLException {
PreparedStatement createTable = connection.prepareStatement(statement);
createTable.execute();
connection.commit();
}
}