Spring Data JPA and MySQL: How to avoid "duplicate entry" error in multithreaded environment?

Question

My web application's database infrastructure is layered like this:

1. Spring Data JPA
2. JPA
3. Hibernate
4. C3P0 pool
5. MySQL

Some transactions take quite some time, sometimes one minute for one big transaction (the purpose is caching data into a database): an HTTP request from a user to my web server may begin this transaction. My web server then may query yet another remote third-party server for missing data. When all data is collected, the transaction finishes and all collected data is written to the database.

It may happen that a user reloads my website during this long-running transaction. This causes to begin yet another transaction for the same data in a separate thread. As the purpose of this transaction is caching and therefore this operation is idempotent in nature, I don't mind having the same calculations going on twice. But in these situations, my web application sporadically runs into the following error.

20:12:46.392 container [Thread-24] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 1062, SQLState: 23000
20:12:46.392 container [Thread-24] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Duplicate entry '146845042025054' for key 'PRIMARY'
20:12:46.602 container [Thread-24] INFO  o.h.e.j.b.internal.AbstractBatchImpl - HHH000010: On release of batch it still contained JDBC statements
Exception in thread "Thread-24" org.springframework.dao.DataIntegrityViolationException: Duplicate entry '146845042025054' for key 'PRIMARY'; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: Duplicate entry '146845042025054' for key 'PRIMARY'
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:104)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:516)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:394)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at com.sun.proxy.$Proxy45.save(Unknown Source)
    at com.mycompany.MyJpaEntityDomainClass.setId(MyJpaEntityDomainClass.java:83)
    at com.mycompany.MyCustomRepository.findMyJpaEntityDomainClassInstance(MyCustomRepository.java:72)
    at sun.reflect.GeneratedMethodAccessor33.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at com.sun.proxy.$Proxy46.findMyJpaEntityDomainClassInstance(Unknown Source)
    at com.mycompany.AnotherDomainEntityClass.setAssociatedJpaEntityDomainInstances(AnotherDomainEntityClass.java:343)
    at com.mycompany.MyThreadInvokingService$SearcherThread.run(MyThreadInvokingService.java:106)
    at java.lang.Thread.run(Thread.java:722)
Caused by: org.hibernate.exception.ConstraintViolationException: Duplicate entry '146845042025054' for key 'PRIMARY'
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at com.sun.proxy.$Proxy76.executeUpdate(Unknown Source)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:56)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2962)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3403)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:362)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:354)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:275)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:326)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1210)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:399)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175)
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:75)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:512)
    ... 31 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '146845042025054' for key 'PRIMARY'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2355)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    ... 48 more

That call to com.sun.proxy.$Proxy45.save is actually a call to the save method of MyJpaEntityDomainClassRepository extends org.springframework.data.jpa.repository.JpaRepository<MyJpaEntityDomainClass, String>.

As you can deduce from this stack trace, MyJpaEntityDomainClass instances take care of creating and updating themselves in the repository.

I have the feeling that the duplicate entry error happens in situations of two concurrent threads trying to write the same entity to the database. I don't understand why this error happens at all, as the org.springframework.data.jpa.repository.JpaRepositor.save( uniqueId ) in the slightly later thread should just overwrite the uniqueId-related entity/record put into the repository by the slightly earlier thread.

How can I avoid such transaction-related duplicate entry race condition errors?

Answer 1

duplicate entry error would occur when two records of the same entity are attempted to be inserted . things to check would be :

• is the same JPA/Hibernate session shared across the threads doing the updating at time of the error .
• how is the unique record id generated ? ( myqsl auto-increment ; id generator code . etc ) and if there's an id column does it differ from the actual Primary Key ( the constraint ) of the table .

Answer 2

From your post I can tell you are using transactions so you have to synchronize the threads somehow. The easiest but not the most portable or efficient solution is to lock tables you might be using the @Transactional annotation you should add the isolation of your transaction to be Isolation.SERIALIZABLE

@Transactional(isolation = Isolation.SERIALIZABLE)

If your application is running on a single JVM another easy way to make a method only accesible by one thread is to use the synchronized keyword.

 public synchronized  void method(){}

You can make a shared data structure and keep it in memory and either synchronize it like the method or use a framework like hazelcast or terracotta this answer goes deeper in this approach.

The last method I can think of is the most reliable, efficent and difficult. And that is to synchronize your application via messages JMS or something.

The problem might also be that the entry already exist so you could query for the latest id with something like:

Entity findTop1ByOrderByIdDesc();

Then recover the id and add one this can leave holes if you delete your entites and not the most efficient either but will get the job done.

Also look at the second suggestion diarmuid mentions. The startegy on the primary key in the JPA definition of your entity.

@Id
@GeneratedValue(strategy = GenerationType.AUTO) //This one
@Column(name = "id", nullable = false)
public int getId() {
    return id;
}

And double check thet the id has auto increment in the table definition. As a last recommendation you can also check if your transaction access several entities one of them might have the GeneratedValue missing and this might be the culprit for the message sent.