A merge statement produces wrong results when running in parallel to an insert statement with the same PK

Hi,
We are having issues when running MERGE statements in parallel.
They either produce wrong results or one of the two transactions locks (until timing out), despite the other transaction having committed.
I have managed to reproduce the first scenario (wrong results), which I have also checked that it was working in version 1.4.197.

static final String JDBC_DRIVER = "org.h2.Driver";
static final String DB_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DEFAULT_LOCK_TIMEOUT=5000;LOB_TIMEOUT=2000;MV_STORE=TRUE;";
static final String USER = "sa";
static final String PASS = "";

@Test
public void test() throws SQLException, InterruptedException, ExecutionException, ClassNotFoundException {

  Class.forName(JDBC_DRIVER);

  try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
      Connection conn1 = DriverManager.getConnection(DB_URL, USER, PASS);
      Connection conn2 = DriverManager.getConnection(DB_URL, USER, PASS);

      Statement stmt = conn.createStatement();) {

    conn1.setAutoCommit(false);
    conn2.setAutoCommit(false);

    stmt.execute("DROP TABLE IF EXISTS TABLE1");
    stmt.execute(
      "CREATE TABLE TABLE1(COL1 VARCHAR(10), COL2 VARCHAR(10), COL3 VARCHAR(10), COL4 INT, PRIMARY KEY (COL1, COL2, COL3))");

    stmt.executeUpdate("INSERT INTO TABLE1 VALUES('A', 'B', 'C', 100)");
    stmt.executeUpdate("COMMIT");

    String sql = "MERGE INTO TABLE1 (COL1, COL2, COL3, COL4) KEY (COL1, COL2, COL3) SELECT COL1, COL2, COL3, SUM(COL4) + ? FROM TABLE1 WHERE COL1 = 'A' AND COL2 = 'B' AND COL3 = 'C'";

    Runnable task1 = () -> {
      try (PreparedStatement ps = conn1.prepareStatement(sql)) {
        ps.setInt(1, 200);
        ps.executeUpdate();
        conn1.commit();
      } catch (SQLException e) {
        fail(e.toString());
      }
    };

    Runnable task2 = () -> {
      try (PreparedStatement ps = conn2.prepareStatement(sql)) {
        ps.setInt(1, 300);
        ps.executeUpdate();
        conn2.commit();
      } catch (SQLException e) {
        fail(e.toString());
      }
    };

    // Run merge tasks sequentially
    ExecutorService executor = Executors.newSingleThreadExecutor();
    Future<?> f1 = executor.submit(task1);
    f1.get();
    Future<?> f2 = executor.submit(task2);
    f2.get();

    ResultSet rs = stmt.executeQuery("SELECT SUM(COL4) FROM TABLE1 WHERE COL1 = 'A' AND COL2 = 'B' AND COL3 = 'C'");
    rs.next();
    assertEquals("Column value", 600, rs.getInt(1)); // This is OK

    // Reset the values
    stmt.executeUpdate("TRUNCATE TABLE TABLE1");
    stmt.executeUpdate("INSERT INTO TABLE1 VALUES('A', 'B', 'C', 100)");
    stmt.executeUpdate("COMMIT");

    // Now run the merge tasks in parallel
    ExecutorService parallel = Executors.newFixedThreadPool(2);
    List<Future<?>> futures = new ArrayList<>();
    futures.add(parallel.submit(task1));
    futures.add(parallel.submit(task2));

    for (Future<?> future : futures) {
      try {
        future.get();
      } catch (ExecutionException e) {
        fail(e.toString());
      }
    }

    rs = stmt.executeQuery("SELECT SUM(COL4) FROM TABLE1 WHERE COL1 = 'A' AND COL2 = 'B' AND COL3 = 'C'");
    rs.next();
    assertEquals("Column value", 600, rs.getInt(1)); // Fails
  }
}

Thanks,
Marcello

Start by running the supplied JDBC/JUnit reproduction for parallel MERGE statements against the H2 database. Trace the MERGE transaction and locking path after confirming that sequential execution returns 600 while parallel execution does not. Done means both concurrent transactions commit without timing out and the final SUM(COL4) is 600.