Increased SQL Server parallelism causes "Violation of PRIMARY KEY constraint" when using Sequences

Hibernate ORM
1

Hello

We are experiencing a weird behavior and would like to ask if someone has encountered a similar case before.

Given that:

  1. We are using Hibernate 5.2.15.Final + SQL Server 2014 Standard Edition
  2. We are parsing text files and inserting them into the staging tables for further processing
  3. Each file is parsed and inserted in a single thread, using batches of 2k rows
  4. There can be multiple files being parsed at the same time - throttled by JMS listener (1 msg per file)
  5. We are using SQL Server Sequences with allocation/increase of 1000 configured on both Entity and Sequence
  6. We are not using Hibernate’s batching
    7, The application is the only source of data for the stating tables so there is no possibility of some other process breaking the PKs

When:

  1. We increase the parallelism level on SQL Server from 1 to 4

Then:

  1. We are getting lots of “Violation of PRIMARY KEY constraint” errors during the inserting to the staging tables

When:

  1. We decrease the parallelism level back to 1

Then:

  1. Errors are gone and the data is inserted correctly.

Conclusion:
Somehow the increased parallelism is breaking the sequence management either in SQL Server or in Hibernate. Does someone know what can be the cause or what to check?

2

To me, it sounds like an SQL Server bug, probably this one.

As long as the DB can provide a SEQUENCE object which can be called concurrently and always provide monotonically increasing values, the Hibernate SEQUENCE generator should work just fine.

3

Thx Vlad. Suspected as much, needed a confirmation that there is no tricks involved in Sequence configuration in relation to the parallelism level. Will pass this on to our DBAs.

4

The DB sequence is supposed to work concurrently and non-transactionally (to reduce locking time as much as possible). Since Hibernate is meant to operate with multiple Sessions in parallel, it makes sense to expect that all sequences work as such by default. Hence, there is no configuration for this since this is the norm.