Upsert failing by throwing message Duplicate entry for key 'PRIMARY'

Hibernate ORM
1

I’ve a function which is listening to event and INSERT that event if that is new or UPDATE the event if the event already occurred and Inserted. While this function executes I’m getting 2 Exceptions in random order

Exception - 1

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

Exception - 2

org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2; nested exception is javax.persistence.NonUniqueResultException: query did not return a unique result: 2

By random I mean sometimes I get both Exception, Sometimes One of the Exception and Sometimes no exception only

For Exception - 2 when I checked DB there was only one entry, but still got the exception.

Are both exceptions are related?. Please help me in understanding what might be going wrong and help me in fixing this

Here is how my pseudo code looks like

  public void transform(OrderEvent orderEvent) {

    // extract Orders and save
    ......
    ......
    orderRepository.save(order);       // line where Exception - 1 is happening

    // extract lineItems and save
    ......
    ......
    lineItemRepository.saveAll(lineItems);

    if (orderEvent.containsKey("LINE_ITEM_RETURN") {
     // line where Exception - 2 is happening
      lineItemRepository.findByOrderIdAndLineItemId(orderId, lineItemId).ifPresent(newLineItem -> {       
        newLineItem.setLineItemQuantity(newLineItem.getLineItemQuantity() - quantityRefunded);
        lineItemRepository.save(newLineItem);
      });
    }
  }

Order - Entity

@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties(ignoreUnknown = true)
@ToString(doNotUseGetters = true)
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Builder
@Entity
@Table(name = TableConstants.SHOPIFY_LINE_ITEMS)
@IdClass(value = LineItemsIdClass.class)
public class LineItems {

  @Id
  @Column(name = TableConstants.LINE_ITEM_ID)
  @JsonProperty(value = TableConstants.LINE_ITEM_ID)
  private Long lineItemId;

  @Id
  @Column(name = TableConstants.BOT_REF)
  @JsonProperty(value = TableConstants.BOT_REF)
  private Integer botRef;

  @Column(name = TableConstants.ORDER_ID)
  @JsonProperty(value = TableConstants.ORDER_ID)
  private Long orderId;

  .....
  .....

}

LineItem - Entity

@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties(ignoreUnknown = true)
@ToString(doNotUseGetters = true)
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Builder
@Entity
@Table(name = TableConstants.SHOPIFY_ORDERS)
@IdClass(value = OrderIdClass.class)
public class Order {

  @Id
  @Column(name = TableConstants.ORDER_ID)
  @JsonProperty(value = TableConstants.ORDER_ID)
  private Long orderId;

  @Id
  @Column(name = TableConstants.BOT_REF)
  @JsonProperty(value = TableConstants.BOT_REF)
  private Integer botRef;

  @Column(name = TableConstants.SHOP_DOMAIN)
  @JsonProperty(value = TableConstants.SHOP_DOMAIN)
  private String shopDomain;

  .....
  .....

}

Exception - 1 Detailed Message

Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn=525413) Duplicate entry '4760697667631-75547' for key 'PRIMARY'
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:70)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)
	at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:181)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
	... 46 common frames omitted
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Duplicate entry '4760697667631-75547' for key 'PRIMARY'
	at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)
	at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)
	... 51 common frames omitted
Caused by: java.sql.SQLException: Duplicate entry '4760697667631-75547' for key 'PRIMARY'
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1683)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1545)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1508)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318)
	... 52 common frames omitted

Exception - 2 Detailed message

Caused by: javax.persistence.NonUniqueResultException: query did not return a unique result: 2
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:128)
	at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1648)
	at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:111)
	at sun.reflect.GeneratedMethodAccessor203.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:406)
	at com.sun.proxy.$Proxy218.getSingleResult(Unknown Source)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:196)
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
	... 20 common frames omitted
2

It’s not really possible that you see both exception, unless you catch the first one and continue afterwards. Note that a transaction must be rolled back as soon as one SQL related exception occurs.

As for exception 1, check which constraint is violated on which table. Apparently some constraint named PRIMARY because of a duplicate value '4760697667631-75547', but I don’t see a String field in your model, so I assume that this is about some nested object.

Exception 2 can happen, especially on MariaDB, due to the use of the repeatable read isolation level, which is the default on MariaDB. You probably check the database content with some UI that uses read committed isolation by default, but unless you configured this explicitly in your application, it will apply defaults.

3

4760697667631-75547 is a composite primary key but not a single string value. I expected hibernate to perform upsert but it has failed and throwing duplicate entry exception

4

Yeah I’m using MariaDb only, so how can we solve this issue if there is actually only one row in DB?

5

Please share the logged SQL statements that are executed. Hibernate first tries to look up the entry and only then execute insert or update, depending on whether a row exists or not. I think that this might be related to the repeatable read isolation level issue.

Transaction TX1 starts at T1 and before TX1 tries to upsert, but after T1, another transaction TX2 persist this row. With repeatable read isolation level, TX1 can not see the row, because it was committed after T1. The solution is to defer starting the transaction TX1 until TX2 committed. If that’s not possible, you have to configure the read committed isolation level with hibernate.connection.isolation, also see Hibernate ORM 5.6.12.Final User Guide.

6

Getting SQl logs looks difficult as of now, will try to configure read committed isolation level.

hibernate.connection.isolation = Connection.TRANSACTION_READ_COMMITTED

Also I hope this will help in resolving both exceptions

7

Also instead of setting READ_COMMITED transaction level on whole DB. Should I use @Transactional(isolation = Isolation.READ_COMMITTED on my method transform()?

@Transactional(isolation = Isolation.READ_COMMITTED
  public void transform(OrderEvent orderEvent) {

    // extract Orders and save
    ......
    ......
    orderRepository.save(order);       // line where Exception - 1 is happening

    // extract lineItems and save
    ......
    ......
    lineItemRepository.saveAll(lineItems);

    if (orderEvent.containsKey("LINE_ITEM_RETURN") {
     // line where Exception - 2 is happening
      lineItemRepository.findByOrderIdAndLineItemId(orderId, lineItemId).ifPresent(newLineItem -> {       
        newLineItem.setLineItemQuantity(newLineItem.getLineItemQuantity() - quantityRefunded);
        lineItemRepository.save(newLineItem);
      });
    }
  }
8

I don’t know your business requirements, nor do I know how Spring interacts with Hibernate when setting an isolation level via the @Transactional annotation. You will have to figure out how to do this yourself. All I can tell you, is that the exceptions might occur due to the effects of repeatable read.

Configuring hibernate.connection.isolation = Connection.TRANSACTION_READ_COMMITTED does not configure the isolation level for “the whole DB” but just the connections used by Hibernate. Like I wrote, I don’t know your app or your business requirements. You should read into the semantics of the isolation levels and understand what it is that you need.

1 Like