H2: counting (with table lock)

Question

I need to implement a counter by prefix and get the current value. Therefore I created a table UPLOAD_ID:

 CREATE TABLE UPLOAD_ID
(
    COUNTER INT NOT NULL,
    UPLOAD_PREFIX VARCHAR(60) PRIMARY KEY
);

Using H2 and a Spring nativeQuery:

    @Query(nativeQuery = true, value = MYQUERY)
  override fun nextId(@Param("prefix") prefix: String): Long

with MYQUERY being

SELECT COUNTER FROM FINAL TABLE ( 
              USING (SELECT CAST(:prefix AS VARCHAR) AS UPLOAD_PREFIX FOR UPDATE) S FOR UPDATE
              ON T.UPLOAD_PREFIX = S.UPLOAD_PREFIX
              WHEN MATCHED
                THEN UPDATE
                    SET COUNTER = COUNTER + 1
              WHEN NOT MATCHED
                THEN INSERT (UPLOAD_PREFIX, COUNTER)
                    VALUES (S.UPLOAD_PREFIX, 1) );

I'm unable to lock the table to avoid "Unique index or primary key violation" in my test. In MSSQL I can add WITH (HOLDLOCK) T in MERGE INTO UPLOAD_ID WITH (HOLDLOCK) T to solve this issue.

The gist of my test looks like

     try { uploadIdRepo.deleteById(prefix) } catch (e: EmptyResultDataAccessException) { }

      val startCount = uploadIdRepo.findById(prefix).map { it.counter }.orElseGet { 0L }
      val workerPool = Executors.newFixedThreadPool(35)
      val nextValuesRequested = 100

      val res = (1..nextValuesRequested).toList().parallelStream().map { i ->
        workerPool.run {
          uploadIdRepo.nextId(prefix)
        }
      }.toList()

      res shouldHaveSize nextValuesRequested // result count
      res.toSet() shouldHaveSize nextValuesRequested // result spread
      res.max() shouldBeEqualComparingTo startCount + nextValuesRequested

Can I solve this with H2?

H2: counting (with table lock)

0 Answers0