Javers duplicate key value violates unique constraint Exception on global_id_pk Key

Question

I've Javers configured in my Java Spring Boot(3.0.4) Application with Postgresql(13.8) since few months and it was working properly.

My config details:

build.gradle dependency:

...
implementation "org.springframework.boot:spring-boot-starter-data-jpa:3.0.4"
implementation "org.javers:javers-spring-boot-starter-sql:7.0.0-RC3"
...

application.properties Javers configuration :

javers.sqlSchema=schema_a
logging.level.org.javers.core.Javers=ERROR

Annotation being used on my Repositories:

@Repository
@JaversSpringDataAuditable
public interface IMyExampleRepository extends IMyBaseRepository<MyExample, UUID> {}


@NoRepositoryBean
public interface IMyBaseRepository<Entity, ID> extends JpaRepository<Entity, ID>, JpaSpecificationExecutor<Entity> {
}

Then I started using the same shared DB in another application module with the same Javers config deployed in a new instance. The config is working fine for non prod environments. But in my prod environment, I recently started getting JaversException about duplicate key constraint violation.

Here's a sample issue:

Exception Cause = JaversException SQL_EXCEPTION: ERROR: duplicate key value violates unique constraint "jv_global_id_pk"
  Detail: Key (global_id_pk)=(571901) already exists.
while executing sql: INSERT INTO schema_a.jv_global_id ( type_name, local_id, global_id_pk ) VALUES  ( ?,?,? )
StackTrace Array begin
JaversException SQL_EXCEPTION: ERROR: duplicate key value violates unique constraint "jv_global_id_pk"
  Detail: Key (global_id_pk)=(571901) already exists.
while executing sql: INSERT INTO schema_a.jv_global_id ( type_name, local_id, global_id_pk ) VALUES  ( ?,?,? )
    at org.javers.repository.sql.session.PreparedStatementExecutor.wrapExceptionAndCall(PreparedStatementExecutor.java:120)
    at org.javers.repository.sql.session.PreparedStatementExecutor.runVoidSql(PreparedStatementExecutor.java:110)
    at org.javers.repository.sql.session.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:39)
    at org.javers.repository.sql.session.Session.execute(Session.java:106)
    at org.javers.repository.sql.session.Session.executeInsertAndGetSequence(Session.java:53)
    at org.javers.repository.sql.session.InsertBuilder.executeAndGetSequence(InsertBuilder.java:54)
    at org.javers.repository.sql.repositories.GlobalIdRepository.insert(GlobalIdRepository.java:118)
    at org.javers.repository.sql.repositories.GlobalIdRepository.getOrInsertId(GlobalIdRepository.java:35)
    at org.javers.repository.sql.repositories.CdoSnapshotRepository.save(CdoSnapshotRepository.java:25)
    at org.javers.repository.sql.JaversSqlRepository.persist(JaversSqlRepository.java:87)
    at org.javers.repository.api.JaversExtendedRepository.persist(JaversExtendedRepository.java:154)
    at org.javers.core.JaversCore.persist(JaversCore.java:109)
    at org.javers.core.JaversCore.commit(JaversCore.java:90)
    at org.javers.spring.transactions.JaversTransactionalDecorator.commit(JaversTransactionalDecorator.java:68)
    at org.javers.spring.jpa.JaversTransactionalJpaDecorator.commit(JaversTransactionalJpaDecorator.java:50)
    at jdk.internal.reflect.GeneratedMethodAccessor133.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:390)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702)
    at org.javers.spring.jpa.JaversTransactionalJpaDecorator$$SpringCGLIB$$0.commit(<generated>)
    at org.javers.spring.auditable.aspect.JaversCommitAdvice.commitObject(JaversCommitAdvice.java:93)
    at java.base/java.util.Arrays$ArrayList.forEach(Arrays.java:4204)
    at java.base/java.util.Collections$UnmodifiableCollection.forEach(Collections.java:1092)

How do I fix this issue? And please suggest if I'm doing anything wrong.

Initially I got such duplicate key constraints violation issues on jv_commit_pk Key:

Caused by: org.javers.common.exception.JaversException: SQL_EXCEPTION: ERROR: duplicate key value violates unique constraint "jv_commit_pk"
  Detail: Key (commit_pk)=(1086610) already exists.

To fix it, I created new Javers tables in another schema schema_b for my new application module to temporarily fix the issue. Then I didn't get issue on jv_commit_pk Key anymore on either of the application modules. But yet I received the issue on "jv_global_id_pk" Key on my first module and I had to create a backup and drop the Javers tables to create new tables to fix this issue permanently.

But I want a better solution since there will be multiple instances of my current multimodule Java Spring Boot application and I don't want my APIs to fail due to Javers DB issues.

Answer 1

To address the question comprehensively, it is essential to consider the configuration of the Javers library. Javers offers several algorithms for generating commit IDs, which can be specified using the CommitIdGenerator class.

• SYNCHRONIZED_SEQUENCE
• RANDOM
• CUSTOM

For more detailed information, please refer to the documentation under org.javers.core.CommitIdGenerator. here

When utilizing the SYNCHRONIZED_SEQUENCE strategy, the Commit_pk in the SQL Repository is generated using a database sequence. This approach ensures safety in multi-instance and multi-threaded applications. However, it's important to note that the strategy involves allocating a block of PK values (e.g., 100 PK values) for a single sequence value. This could potentially pose issues in high-intensity systems. Therefore, in distributed environments, it is highly recommended to consider using the RANDOM strategy for ID generation instead.

There was a similar problem regarding generation of pk-ids in Javers, and the discussion can also be found from here

Another crucial consideration is that using the same tables to store Javers data for different applications may not be an ideal solution. It is advisable to maintain separate Javers-intended tables for each distinct entity that stores its own business data.

Fortunately, Javers provides configuration options to customize table names, allowing you to tailor the solution to your specific requirements.