Announcement Announcement Module
Collapse
No announcement yet.
Long transactions and locks with TableGenerator Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Long transactions and locks with TableGenerator

    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 ?

    We use Hibernate 3.5 with Spring 3.0.

    Here is an example of the way we configure ids in our entities :

    Code:
    @Id
    @TableGenerator(name = "PeriodeIdGenerator", table = "ID_GEN", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "PeriodeIdGenerator")
    @Column(name="ID")
    private Long id;

    This is a urgent problem, so any help is very welcome

    Regards,

    Olivier

  • #2
    Have you use SQL- DIALECT. If not please use that one. If still their is a problem then post it again

    Comment


    • #3
      If you mean SQL Server dialect, yes I do use it.

      Comment


      • #4
        Can you please send me the exact error

        Comment


        • #5
          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


          • #6
            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


            • #7
              Well, I can't see any diffrence with my code. And it doesn't work. Or do you mean I need one table for every id ?

              Comment


              • #8
                Yes for ID generation

                Comment


                • #9
                  Well, I have 100 tables with generated ids, not very nice to have 100 more tables just for ids ...

                  Comment


                  • #10
                    Not 100 table only one table with a sync. lock

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        Hi,

                        I investigated further and it looks like the problem is a SQL Server one. It is due to the way SQL Server optimizer dynamically chooses lock granularity.

                        To understand what happens, I first tried to run the two following transactions in SQL Server Management Studio. Note the two requests make updates on different rows :

                        Code:
                        begin tran
                        update ID_GEN set sequence_next_hi_value=1 where sequence_name='CRENEAU_SEMAINE'
                        Code:
                        begin tran
                        update ID_GEN set sequence_next_hi_value=1 where sequence_name='PERIODE'
                        In this case I have a deadlock on the second request.

                        Then I tried similar request on an other table :

                        Code:
                        begin tran
                        update SITE set LIBELLE='test' where ID=1
                        Code:
                        begin tran
                        update SITE set LIBELLE='test' where ID=10
                        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 :

                        Code:
                        @Id
                        @TableGenerator(name = "CreneauJourIdGenerator", table = "CRENEAU_JOUR_ID", allocationSize = 1)
                        @GeneratedValue(strategy = GenerationType.TABLE, generator = "CreneauJourIdGenerator")
                        @Column(name="ID")
                        private Long id;
                        Code:
                        @Id
                        @TableGenerator(name = "CreneauSemaineIdGenerator", table = "CRENEAU_SEMAINE_ID", allocationSize = 1)
                        @GeneratedValue(strategy = GenerationType.TABLE, generator = "CreneauSemaineIdGenerator")
                        @Column(name="ID")
                        private Long id;
                        I don't find it a great solution, but it works a least !

                        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.

                        Any comment about this ?

                        Comment


                        • #13
                          Excellent Community. Thanks!


                          ________________
                          Los Angeles wedding DJs

                          Comment

                          Working...
                          X