This forum is now a read-only archive. All commenting, posting, registration services have been turned off. Those needing community support and/or wanting to ask questions should refer to the Tag/Forum map, and to http://spring.io/questions for a curated list of stackoverflow tags that Pivotal engineers, and the community, monitor.

Long transactions and locks with TableGenerator

May 9th, 2011, 04:13 AM

Hi,

We use @TableGenerator JPA annotation in entities to generate ids a DBMS independent way. It works well most of time, but we have problems with SQL Server 2008 because of long transactions that put locks on the id generation table. From what I understood TableGenerator uses the Hibernate session connection and so the current transaction. Is there a way to tell Hibernate to use its own transaction while generating ids ? Or is there something wrong the way we use TableGenerator strategy ?

Comment

Comment

Here is an extract the stack trace we sometimes get (sorry some messages are in french) :

Code:

10 mai 2011 09:35:50 org.hibernate.id.MultipleHiLoPerTableGenerator doWorkInCurrentTransaction
GRAVE: could not update hi value in: ID_GEN
com.microsoft.sqlserver.jdbc.SQLServerException: La transaction (ID de processus 60) a été bloquée sur les ressources verrou par un autre processus et a été choisie comme victime. Réexécutez la transaction.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at org.hibernate.id.MultipleHiLoPerTableGenerator.doWorkInCurrentTransaction(MultipleHiLoPerTableGenerator.java:187)
at org.hibernate.engine.TransactionHelper$1Work.doWork(TransactionHelper.java:61)
at org.hibernate.engine.transaction.Isolater$JdbcDelegate.delegateWork(Isolater.java:249)
at org.hibernate.engine.transaction.Isolater.doIsolatedWork(Isolater.java:70)
at org.hibernate.engine.TransactionHelper.doWorkInNewTransaction(TransactionHelper.java:74)
at org.hibernate.id.MultipleHiLoPerTableGenerator.generate(MultipleHiLoPerTableGenerator.java:208)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:122)
at org.hibernate.event.def.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:415)
at org.hibernate.event.def.DefaultMergeEventListener.mergeTransientEntity(DefaultMergeEventListener.java:341)
at org.hibernate.event.def.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:303)
at org.springframework.orm.hibernate3.support.IdTransferringMergeEventListener.entityIsTransient(IdTransferringMergeEventListener.java:59)
at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:258)
at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:84)
at org.hibernate.impl.SessionImpl.fireMerge(SessionImpl.java:859)
at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:843)
at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:847)
at xxx.socle.utils.orm.AbstractHibernateDAO.save(AbstractHibernateDAO.java:83)
at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
[...]
2011-05-10 09:35:50,546 - impossible d'enregistrer le projet
xxx.strategique.service.simulation.SimulationServiceException: org.hibernate.exception.LockAcquisitionException: could not get or update next value
at xxx.strategique.service.simulation.SimulationServiceImpl.saveSimulation(SimulationServiceImpl.java:95)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy149.saveSimulation(Unknown Source)
at xxx.strategique.web.controller.projets.CreationProjetController.save(CreationProjetController.java:209)
[...]
Caused by: org.hibernate.exception.LockAcquisitionException: could not get or update next value
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:107)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.engine.TransactionHelper$1Work.doWork(TransactionHelper.java:64)
at org.hibernate.engine.transaction.Isolater$JdbcDelegate.delegateWork(Isolater.java:249)
at org.hibernate.engine.transaction.Isolater.doIsolatedWork(Isolater.java:70)
at org.hibernate.engine.TransactionHelper.doWorkInNewTransaction(TransactionHelper.java:74)
at org.hibernate.id.MultipleHiLoPerTableGenerator.generate(MultipleHiLoPerTableGenerator.java:208)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:122)
at org.hibernate.event.def.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:415)
at org.hibernate.event.def.DefaultMergeEventListener.mergeTransientEntity(DefaultMergeEventListener.java:341)
at org.hibernate.event.def.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:303)
at org.springframework.orm.hibernate3.support.IdTransferringMergeEventListener.entityIsTransient(IdTransferringMergeEventListener.java:59)
at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:258)
at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:84)
at org.hibernate.impl.SessionImpl.fireMerge(SessionImpl.java:859)
at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:843)
at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:847)
at xxx.socle.utils.orm.AbstractHibernateDAO.save(AbstractHibernateDAO.java:83)
at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
[...]
... 41 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: La transaction (ID de processus 60) a été bloquée sur les ressources verrou par un autre processus et a été choisie comme victime. Réexécutez la transaction.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at org.hibernate.id.MultipleHiLoPerTableGenerator.doWorkInCurrentTransaction(MultipleHiLoPerTableGenerator.java:187)
at org.hibernate.engine.TransactionHelper$1Work.doWork(TransactionHelper.java:61)
... 67 more
10 mai 2011 09:35:50 org.hibernate.util.JDBCExceptionReporter logExceptions
ATTENTION: SQL Error: 1205, SQLState: 40001
10 mai 2011 09:35:50 org.hibernate.util.JDBCExceptionReporter logExceptions
GRAVE: La transaction (ID de processus 60) a été bloquée sur les ressources verrou par un autre processus et a été choisie comme victime. Réexécutez la transaction.

It looks like generator runs within the current transaction opened by Spring. And SQL Server puts locks on ID_GEN table, which causes problems on long transactions. One solution I see would be to run generator within its own short transaction that would be committed each time we get a new id, but I see no solution for that.

Thanks for your help

Comment

Value MUST be fetched in a seperate transaction to the Session transaction so the generator must be able to obtain a new connection and commit it. Hence this implementation may not be used when the user is supplying connections.

Try this one also (I think this will work):-
@Id
@GeneratedValue(generator="ID_GEN", strategy=GenerationType.TABLE)
@TableGenerator(name="ID_GEN", table="[$SSMA_seq_SEQ_EXAMPLE_ID]")

Its in french language but as per my understanding above code should work

Comment

OK. So this is the way we already do it. But we have lock problems with SQL Server and this is why I asked for help.

I investigated further with SQL Server profiler and it looks like each generator call is made within its own transaction (which is commited at each "generate" call). So it looks like problem doesn't deal with transactions. I really don't understand where this problems comes from

In this case there's no deadlock while the requests are very similar !

So it looks like SQL Server doesn't choose the same lock mode (either row, table or block) in the two cases. Maybe the algorithm takes into account the number of records in the table, the size of each record, etc ...

I could reduce number of deadlocks by creating an index on sequence_name column in ID_GEN table, but it doesn't fully solve the problem since I still have deadlocks sometimes (no more in SQL Server studio anyway, but still from Hibernate). From what I read the more reliable solution would be to add hints in SQL requests (something like "with (readpast)") but I am not sure about the consequences of this. And most of all I can't see any solution to tell Hibernate to add this in requests for id generator !

The only reliable workaround I found is to have one table for each id generator. I mean something like that :

From all this, my conclusion is that if you want to use table generator on SQL Server, and if you have requests with a lot of insert requests (an so a lot of update requests on ids table), I would recommend to have one table for each id generator.