Announcement Announcement Module
Collapse
No announcement yet.
ORA-08177 - can't serialize access for this transaction Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ORA-08177 - can't serialize access for this transaction

    In last few days I'm trying to switch project on which we are working on, from MySQL to ORACLE database. For some reason in some cases I'm getting this error like in subject. Why? I'm using Hibernate 3.1.

    Any help is appreciated.

    Regards.

  • #2
    Have you checked this out.

    http://forum.springframework.org/sho...ight=ORA-08177

    Comment


    • #3
      Yes I was, it's only post with ORA-08177 issue. My problem is that I have some method which is making insertion into 5-6 related tables (very complex hierarchy and model) in one outer transaction, and in some cases I have to read something from database (make several queries) and do some insert or update.

      Thanks, for your help.

      Comment


      • #4
        Did the advice in that thread sort out your problem then?

        Comment


        • #5
          Originally posted by jandrla View Post
          In last few days I'm trying to switch project on which we are working on, from MySQL to ORACLE database. For some reason in some cases I'm getting this error like in subject. Why? I'm using Hibernate 3.1.

          Any help is appreciated.

          Regards.
          From my experience Oracle serializable transaction isolation level is simply not usable in most real life cases. It should be fairly easy (your mileage may vary though) to switch to read commited level and use explicit pessimistic locking to achieve results that are good enough.

          Comment


          • #6
            Did the advice in that thread sort out your problem then?
            No it didn't.

            Are configuration of transaction manager and interceptor ok?

            Code:
            	<!-- Hibernate - ov TransactionManager -->
            	<bean id="transactionManager"
            		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
            		<property name="dataSource">
            			<ref local="dataSource" />
            		</property>
            		<property name="sessionFactory">
            			<ref local="sessionFactory" />
            		</property>
            	</bean>
            
            	<bean id="TransactionInterceptor"
            		class="org.springframework.transaction.interceptor.TransactionInterceptor">
            		<property name="transactionManager"
            			ref="transactionManager" />
            		<property name="transactionAttributeSource">
            			<ref bean="MatchAllMethods"/>
            		</property>
            	</bean>
            	
            	<bean id="MatchAllMethods" class="org.springframework.transaction.interceptor.MatchAlwaysTransactionAttributeSource">
            		<property name="transactionAttribute">
            			<value>PROPAGATION_REQUIRED,ISOLATION_SERIALIZABLE</value>
            		</property>
            	</bean>
            
            	<bean id="BeanNameAutoProxyCreator"
            		class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
            		<property name="beanNames">
            			<list>
            				<idref bean="documentFacade"/>
            				<idref bean="rpoQueryFacade"/>
            				<idref bean="pregledPoreskihObveznikaFacade"/>
            				<idref bean="loginModuleFacade"/>
            			</list>
            		</property>
            		<property name="interceptorNames">
            			<list>
            				<idref bean="TransactionInterceptor" />
            			</list>
            		</property>
            	</bean>

            Comment


            • #7
              Originally posted by dejanp View Post
              From my experience Oracle serializable transaction isolation level is simply not usable in most real life cases. It should be fairly easy (your mileage may vary though) to switch to read commited level and use explicit pessimistic locking to achieve results that are good enough.

              And in most cases you even need not pessimistic locking - for most real-life sutuations read commited by itself is good enough, I dare to say that in 80% of cases need for serializable isolation level is imaginable (misunderstanding of business requirements or ill-conceived DIY attempt).

              Comment


              • #8
                Setting Isolation level to ISOLATION_READ_COMMITTED solves the problem.

                Thanks for answers.

                Regards.

                Comment


                • #9
                  Originally posted by jandrla View Post
                  Setting Isolation level to ISOLATION_READ_COMMITTED solves the problem.

                  Thanks for answers.

                  Regards.
                  Yes, it should. Once more - in the vaste majority of cases usage of serializable isolation level is a design error that leads to non-scalable applications. Most databases enforce it with very restictive locks that kill concurrency and in case of Oracle and other databases that rely on multiversioning (PosgreSQL, Interbase) you have to be ready to failed ("Can not serialize") transactions. Latter is, usually, tolerable as soon as application is prepared for them (normally, it is enough just to restart transaction). For Oracle quite good discussion of this topic (as almost any other Oracle-related topic) may be found on asktom.oracle.com

                  Regards,
                  Oleksandr

                  Comment


                  • #10
                    Hi

                    We have a production issue that's troubling for the past few days. we have two batch job one that runs for every 5 min and the other one runs daily 1 AM. we are frequently running into the below issue.

                    I have tried the below two options but it did not work out.

                    1. increasing the INITRANS value to 8 for spring batch tables.
                    2. changing theisolationLevelForCreate=ISOLATION_REPEATABLE_RE AD & also ISOLATION_DEFAULT


                    can any one please suggest any solution on how resolve this issue.

                    org.springframework.web.util.NestedServletExceptio n: Request processing failed; nested exception is org.springframework.dao.CannotSerializeTransaction Exception: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ORA-08177: can't serialize access for this transaction
                    ; nested exception is java.sql.SQLException: ORA-08177: can't serialize access for this transaction

                    org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:656)
                    org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:560)
                    javax.servlet.http.HttpServlet.service(HttpServlet .java:637)
                    javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
                    org.springframework.web.filter.HiddenHttpMethodFil ter.doFilterInternal(HiddenHttpMethodFilter.java:7 7)
                    org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
                    org.springframework.web.filter.ShallowEtagHeaderFi lter.doFilterInternal(ShallowEtagHeaderFilter.java :57)
                    org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
                    org.tiaa.batch.common.security.AccessFilter.doFilt er(AccessFilter.java:33)
                    </pre></p><p><b>root cause</b> <pre>org.springframework.dao.CannotSerializeTransa ctionException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ORA-08177: can't serialize access for this transaction
                    ; nested exception is java.sql.SQLException: ORA-08177: can't serialize access for this transaction

                    Comment


                    • #11
                      Hi Sridhar did u get soultion for this...


                      org.springframework.dao.CannotSerializeTransaction Exception: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ORA-08177: can't serialize access for this transaction

                      Comment

                      Working...
                      X