Could not synchronize database state with session org.hibernate.exception.ConstraintViolationException because of Duplicate GUID

Question

I use hibernate to insert data in a table using autogenerated GUID, but insertion fails sometimes with duplicate GUID exception.

For Example:

From Logs , insertion fails for the first 2 attempts by printing the duplicate GUID '0500edac-0074-4324-3436-31444231342d'. The time taken are as follows

     1st attempt :08-27-2018 04:27:00.012,

     2nd attempt :08-27-2018 04:27:01.024,

     3rd attempt was not logged ,as it was successful

but in the database I see a row with GUID '0500edac-0074-4324-3436-31444231342d' created at '08-27-2018 04:27:01.054'

So I am not sure why I am getting the exceptions for the first 2 attempts and then successfully it inserts the 3rd time.

SQL Table Properties: I have a SQL Server table named "DataHistory" with a column named

"DataHistoryGuid" with the following properties uniqueidentifier,ROWGUIDCOL,Primary Key column,newsequentialid .

Hibernate Properties: I am using hibernate to store the data in that table, for the GUID column, I am using the

<id name="dataHistoryGuid" type="java.util.UUID" >
        <column name="DataHistoryGuid"/>
        <generator class="guid"/>
    </id>

The following is the exception trace:

[event.def.AbstractFlushingEventListener:performExecutions:324]
Could not synchronize database state with session 
org.hibernate.exception.ConstraintViolationException: could not 
 insert: [com.testProj.dataprocessor.model.sql.SqlDataHistory] 
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94) 
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) 
  at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2295) 
  at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2688) 
  at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79) 
  at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279) 
  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) 
  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167) 
  at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) 
  at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50) 
  at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027) 
  at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390) 
  at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:420) 
  at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) 
  at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:748) 
  at com.testProj.dataprocessor.common.sql.hibernate.HibernateSession.upsertDataHistory(HibernateSession.java:505) 
  at com.testProj.dataprocessor.common.sql.SqlStore.upsertDataHistory(SqlStore.java:92) 
  at com.testProj.dataprocessor.common.sql.SqlStore$$FastClassByCGLIB$$18d897d8.invoke(<generated>) 
  at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) 
  at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700) 
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) 
  at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:77) 
  at com.testProj.dataprocessor.model.performance.Profiler.profile(Profiler.java:15) 
  at sun.reflect.GeneratedMethodAccessor160.invoke(Unknown Source) 
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
  at java.lang.reflect.Method.invoke(Method.java:498) 
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:627) 
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616) 
  at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:64) 
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) 
  at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:77) 
  at com.testProj.dataprocessor.common.sql.SqlRetryPolicy.retry(SqlRetryPolicy.java:20) 
  at sun.reflect.GeneratedMethodAccessor161.invoke(Unknown Source) 
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
  at java.lang.reflect.Method.invoke(Method.java:498) 
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:627) 
  at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616) 
  at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:64) 
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) 
  at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) 
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) 
  at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635) 
  at com.testProj.dataprocessor.common.sql.SqlStore$$EnhancerByCGLIB$$f3a323cc.upsertDataHistory(<generated>) 
  at com.testProj.dataprocessor.dao.DataDAO.updateDataHistory(DataDAO.java:88) 
  at com.testProj.dataprocessor.eventhandler.DataHistoryEventHandler.doWork(DataHistoryEventHandler.java:34) 
  at com.testProj.dataprocessor.eventhandler.DataHistoryEventHandler.updateDiagnosticsHistory(DataHistoryEventHandler.java:28) 
  at com.testProj.dataprocessor.DataProcessorService.doWork(DataProcessorService.java:37) 
  at com.testProj.dataprocessor.DataProcessorService.process(DataProcessorService.java:24) 
  at com.testProj.dataprocessor.DataProcessorService.process(DataProcessorService.java:80) 
  at com.testProj.dataprocessor.DataProcessorService.postDataEventSync(DefaultDataProcessorService.java:41) 
  at com.testProj.dataprocessor.DataProcessorService.postDataEvent(DefaultDataProcessorService.java:36) at sun.reflect.GeneratedMethodAccessor272.invoke(Unknown Source) 
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
  at java.lang.reflect.Method.invoke(Method.java:498) 
  at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:173) 
  at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:89) 
  at org.apache.cxf.jaxws.JAXWSMethodInvoker.invoke(JAXWSMethodInvoker.java:60) 
  at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:75) 
  at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58) 
  at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) 
  at java.util.concurrent.FutureTask.run(FutureTask.java:266) 
  at org.apache.cxf.workqueue.SynchronousExecutor.execute(SynchronousExecutor.java:37) 
  at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:106) 
  at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:236) 
  at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:109) 
  at Caused by: java.sql.SQLException: Violation of PRIMARY KEY constraint 'PK_DataHistory_on_DataHistoryGuid'. Cannot insert duplicate key in object 'dbo.DataHistory'. The duplicate key value is (0500edac-0074-4324-3436-31444231342d). 
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) 
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) 
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) 
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671) 
  at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613) 
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572) 
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:727) 
  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) 
  at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) 
  at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:46) 
  at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2275) ... 68 more

Assumptions:

1.As Sql Server websites has mentioned that 1 in a billion Guid's created will be a duplicate, so i don't expect duplicates to be present in my tables.

check this below link: https://stackoverflow.com/questions/4474472/exception-could-not-synchronize-database-state-with-session — Nidhi257, Aug 30 '19 at 04:49
This line indicates the key to the problem: Caused by: java.sql.SQLException: Violation of the PRIMARY KEY constraint 'PK_DataHistory_on_DataHistoryGuid'. Cannot insert duplicate key in object 'dbo.DataHistory'. The duplicate key value is (0500edac-0074-4324-3436-31444231342d). A duplicate value is being inserted into a primary key. According to what it explains, it could be that the first two insertion attempts are in the same transaction and therefore fail and the third insertion is correct because it is in another transaction? — borchvm, Sep 02 '19 at 09:32
@borchvm i am not using any Transaction in my application, but i use a retry mechanism using Spring AOP to retry if there is a failure and also my assumption is same thread will be used for retrying. — Vijay Manohar, Sep 04 '19 at 17:46
@Nidhi257 i am only inserting data into that table and not updating ,so update=false is not necessary for me, also i can't use session.flush() because i am worried it might cause some side effects in my cache as i receive more than 2K events in a second.Also i am using session.saveOrUpdate() to store my data in database and not commiting data — Vijay Manohar, Sep 04 '19 at 18:18

bruceskyaus · Answer 1 · 2019-09-05T23:01:50.267

Although the question is focussed on Hibernate, the following line in the trace indicates that the database is rejecting the insert.

Caused by: java.sql.SQLException: Violation of PRIMARY KEY constraint 'PK_DataHistory_on_DataHistoryGuid'. Cannot insert duplicate key in object 'dbo.DataHistory'. The duplicate key value is (0500edac-0074-4324-3436-31444231342d).

This is more of a design issue than a problem with SQL Server or Hibernate. If an auto generated UUID as a PRIMARY KEY in the DataHistory table is your goal, then you are much better off using the database to achieve this, rather than using Hibernate (or worse, rolling your own solution). This is because the database is designed to avoid a collision of values, if a UNIQUE INDEX or PRIMARY KEY is specified.

See SQL UNIQUE Constraint

The task of creating the auto generated UUID is available for you, with PRIMARY KEY DEFAULT (NEWID()). If you need to use the UUID later, then use a non primary key value to select the value after it has been created.

Example table:

CREATE TABLE dbo.DataHistory
(
    DatahistoryGuid uniqueidentifier PRIMARY KEY DEFAULT (NEWID()),
    history_row integer NOT NULL,
    data_stuff  nvarchar(4000) NOT NULL,
    created_date datetime2 DEFAULT (GETDATE())
);

Try setting the insertable property to false in Hibernate. Do this for all columns defaulted by the database. For example:

@Column(name = "DatahistoryGuid", insertable=false)
@Column(name = "created_date", insertable=false)

This results in SQL similar to the following:

INSERT INTO dbo.DataHistory (history_row, data_stuff)
VALUES (1234, 'Joe Bloggs');

See Related Question

To get the DatahistoryGuid value, use a SELECT statement:

SELECT DatahistoryGuid from dbo.DataHistory where history_row = 1234;

SQL Fiddle Example

In Database,For DataHistoryGuid column,I am using 'newsequentialid()' to be set as Default value ,Along with that in hibernate mapping file, i am using for DataHistoryGuid column.If i remove DataHistoryGuid column in hibernate mapping file, it throws the error as "The content of the element type "class" must match"(meta*,subselect?,cache?,synchronize*,comment?,tuplizer*,(id|composite-id?,natural-id?,(version|timestamp)?,(property|many-to-one|one-to-one" — Vijay Manohar, Sep 05 '19 at 17:05
Answer updated. I think I found the answer to the mapping issue. — bruceskyaus, Sep 05 '19 at 23:02
I am not able to make the id column as "insert=false" .Also i have used which means the value will be fetched from Database and hibernate doesn't generate value. — Vijay Manohar, Sep 06 '19 at 18:48
I have already implemented in a similar design to your proposal .My design as follows AutoGenerated Guid is generated by Sql Server and not by Hibernate( generator class="guid") which does that and in sqlserver i use newSequentialId() which has better performance than newId().Also i tried to mark insertable=false,but i couldn't add it to because its a primary key column — Vijay Manohar, Sep 11 '19 at 03:57

Could not synchronize database state with session org.hibernate.exception.ConstraintViolationException because of Duplicate GUID

1 Answers1