Announcement Announcement Module
Collapse
No announcement yet.
Isolation level - READ_COMMITTED_SNAPSHOT Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Isolation level - READ_COMMITTED_SNAPSHOT

    Hi All,

    In my use case, while running the same job in multiple jvm with different job parameters i am getting Deadlocks(If i am running in single jvm it is working fine).

    My batch is long running batch so I have enable the

    ALTER DATABASE BATCH_ESC_DB SET READ_COMMITTED_SNAPSHOT ON

    ALTER DATABASE BATCH_ESC_DB SET ALLOW_SNAPSHOT_ISOLATION ON


    <code>

    <bean id="batchSessionFactory" class="org.springframework.orm.hibernate3.LocalSes sionFactoryBean">
    <property name="mappingLocations">
    <list>
    <value>batch/core/domain/Job.hbm.xml</value>
    </list>
    </property>
    <property name="hibernateProperties">
    <props>
    <prop key="hibernate.dialect">org.hibernate.dialect.SQLS erverDialect
    </prop>
    <prop key="hibernate.show_sql">false</prop>
    <prop key="hibernate.cache.use_second_level_cache">true</prop>
    <prop key="hibernate.cache.generate_statistics">true</prop>
    <prop key="hibernate.connection.isolation">4096</prop>
    </props>
    </property>
    <property name="dataSource">
    <ref bean="dataSource" />
    </property>
    </bean>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDrive r" />
    <property name="url"
    value="jdbc:sqlserver://SERVER;databaseName=BATCH_ESC_DB">
    </property>
    <property name="username" value="XXX" />
    <property name="password" value="YYYY" />
    </bean>

    </code>

    <code>
    Victim Resource Owner:
    2011-06-28 15:48:55.31 spid4s ResType:LockOwner Stype:'OR'Xdes:0x057A66E0 Mode: U SPID:81 BatchID:0 ECID:0 TaskProxy0x057C8378) Value:0x39f76a0 Cost0/35180)
    2011-06-28 15:48:55.31 spid15s deadlock-list
    2011-06-28 15:48:55.31 spid15s deadlock victim=process8da898
    2011-06-28 15:48:55.31 spid15s process-list
    2011-06-28 15:48:55.32 spid15s process id=process8da898 taskpriority=0 logused=35180 waitresource=KEY: 13:1217114567213056 (7a019dd49262) waittime=3937 ownerId=270765 transactionname=implicit_transaction lasttranstarted=2011-06-28T15:48:33.937 XDES=0x57a66e0 lockMode=U schedulerid=1 kpid=2008 status=suspended spid=81 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-06-28T15:48:51.310 lastbatchcompleted=2011-06-28T15:48:51.283 clientapp=Microsoft SQL Server JDBC Driver hostname=tttttttt hostpid=0 loginname=xxx isolationlevel=read committed (2) xactid=270765 currentdb=13 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
    2011-06-28 15:48:55.32 spid15s executionStack

    </code>

    I have configured the isolation level as READ_COMMITTED_SNAPSHOT in mapping file. but while checking deadlock Victim, isolation level is READ_COMMITTED.

    1. Spring Transaction manager will override the READ_COMMITTED_SNAPSHOT to READ_COMMITTED?

    2. How to configure READ_COMMITTED_SNAPSHOT isolation level in spring batch?

    thanks for your reply in advance.

  • #2
    As far as I know READ_COMMITTED_SNAPSHOT is a SQL Server platform feature, so you won't find any settings for it in Spring as such (or maybe it maps to READ_UNCOMMITTED?). You didn't say which query was deadlocking - if it is an INSERT on BATCH_JOB_INSTANCE then the isolation level was set by Spring Batch to prevent you from accidentally launching the same Job twice concurrently. You can change the setting in the JobRepository configuration via namespace or factory bean definition, and in your case DEFAULT would be the right setting. But that deadlock is probably telling you something useful - you shouldn't be launching two identical JobInstances at the same time.

    Comment


    • #3
      Thanks for your reply Dave.

      We tried with the suggestion of changing the isolation level to DEFAULT. But, still the deadlock occurs. I have attached the dead lock graph from the server for your reference.

      My business scenario exists such that a new record is created and the same record is updated in the further flow. At these instant of updations, the dead lock occurs. The business is such that unique items are processed in the individual JVM and thus there is no intersection of the items.But, we could not decipher the reason for the deadlock.

      The same code works fine in a single JVM.

      please enlighten us some way to find the cause and resolve the issue. Let me know in case of any related information.

      Comment


      • #4
        I probably can't really help with a deadlock in user tables. If there is anything you can do in the Spring config then a Java stack trace from the client would be more useful.

        Comment


        • #5
          Thanks a lot Dave.
          Can you give us any idea on the loop holes that we have to look in for the deadlock occuring in user tables.

          I have attached the error log.

          Comment


          • #6
            Deadlocks are notoriously tricky on SQL Server and friends. the best advice would be to switch to Oracle.

            I think there's something wrong with the forum site. Can you just paste the stack trace inside some [code][/code] tags?

            Comment


            • #7
              The Stack trace :

              <code>

              [2011-06-29 13:08:39,847] [1001_main_Child] ERROR org.springframework.batch.core.step.AbstractStep - Encountered an error executing the step
              common.exception.SystemException: CMNDA0001E: A data access system exception has occured. Implementation: Hibernate. Cause: org.hibernate.exception.LockAcquisitionException: could not update: [moneyoperations.domain.PlanContractualPaymentEleme ntDo#component[planNumber,elementNumber]{elementNumber=000057, planNumber=0XXXXX4 }]
              at common.exception.ExceptionUtils.createHibernateSys temException(ExceptionUtils.java:210)
              at common.exception.ExceptionUtils.createHibernateSys temException(ExceptionUtils.java:183)
              at moneyoperations.dao.hibernate.ContractualPaymentEl ementDaoHibernate.recordPlanContractualPaymentElem entList(ContractualPaymentElementDaoHibernate.java :288)
              at moneyoperations.business.PlanCommissionForPaymentE lementHelperBo.recordPlanContractualPaymentElement List(PlanCommissionForPaymentElementHelperBo.java: 3775)
              at moneyoperations.business.PlanCommissionForPaymentE lementBo.recordPlanContractualPaymentElementList(P lanCommissionForPaymentElementBo.java:1074)
              at moneyoperations.business.PlanCommissionForPaymentE lementBo.updatePlanContractualPaymentElementForPre miumIncreaseAmount(PlanCommissionForPaymentElement Bo.java:3029)
              at moneyoperations.business.PlanCommissionForPaymentE lementBo.variationUpdateForPlanContractualPaymentE lement(PlanCommissionForPaymentElementBo.java:378)
              at moneyoperations.business.PlanContractualPaymentEle mentBo.variationUpdateForPlanContractualPaymentEle ment(PlanContractualPaymentElementBo.java:1745)
              at moneyoperations.business.PlanCommissionForPaymentE lementHelperBo.updatePlanContractualPaymentElement Variation(PlanCommissionForPaymentElementHelperBo. java:640)
              at moneyoperations.business.PlanContractualPaymentEle mentBo.updatePlanContractualPaymentElementVariatio n(PlanContractualPaymentElementBo.java:1716)
              at planmanagement.delegate.moneyoperations.java.PlanC ontractualPaymentElementBoDelegateJava.updatePlanC ontractualPaymentElementVariation(PlanContractualP aymentElementBoDelegateJava.java:581)
              at planmanagement.process.PlanGrowthInstructionProces s.processPlanGrowthInstructionSecondStage(PlanGrow thInstructionProcess.java:1485)
              at planmanagement.process.PlanAdministrationManagemen tProcess.processPlanGrowthInstructionSecondStage(P lanAdministrationManagementProcess.java:420)
              at batch.autoescalations.processor.AutomaticEscalatio nStageOneProcessor.process(AutomaticEscalationStag eOneProcessor.java:155)
              at batch.autoescalations.processor.AutomaticEscalatio nStageOneProcessor.process(AutomaticEscalationStag eOneProcessor.java:1)
              at batch.core.processor.BatchRunTaskListProcessorWrap per.process(BatchRunTaskListProcessorWrapper.java: 146)
              at org.springframework.batch.core.step.item.SimpleChu nkProcessor.doProcess(SimpleChunkProcessor.java:11 8)
              at org.springframework.batch.core.step.item.SimpleChu nkProcessor.transform(SimpleChunkProcessor.java:26 0)
              at org.springframework.batch.core.step.item.SimpleChu nkProcessor.process(SimpleChunkProcessor.java:171)
              at org.springframework.batch.core.step.item.ChunkOrie ntedTasklet.execute(ChunkOrientedTasklet.java:74)
              at org.springframework.batch.core.step.tasklet.Taskle tStep$ChunkTransactionCallback.doInTransaction(Tas kletStep.java:347)
              at org.springframework.transaction.support.Transactio nTemplate.execute(TransactionTemplate.java:128)
              at org.springframework.batch.core.step.tasklet.Taskle tStep$2.doInChunkContext(TaskletStep.java:261)
              at org.springframework.batch.core.scope.context.StepC ontextRepeatCallback.doInIteration(StepContextRepe atCallback.java:76)
              at org.springframework.batch.repeat.support.RepeatTem plate.getNextResult(RepeatTemplate.java:367)
              at org.springframework.batch.repeat.support.RepeatTem plate.executeInternal(RepeatTemplate.java:214)
              at org.springframework.batch.repeat.support.RepeatTem plate.iterate(RepeatTemplate.java:143)
              at org.springframework.batch.core.step.tasklet.Taskle tStep.doExecute(TaskletStep.java:247)
              at org.springframework.batch.core.step.AbstractStep.e xecute(AbstractStep.java:196)
              at org.springframework.batch.core.job.SimpleStepHandl er.handleStep(SimpleStepHandler.java:115)
              at org.springframework.batch.core.job.flow.JobFlowExe cutor.executeStep(JobFlowExecutor.java:61)
              at org.springframework.batch.core.job.flow.support.st ate.StepState.handle(StepState.java:60)
              at org.springframework.batch.core.job.flow.support.Si mpleFlow.resume(SimpleFlow.java:144)
              at org.springframework.batch.core.job.flow.support.Si mpleFlow.start(SimpleFlow.java:124)
              at org.springframework.batch.core.job.flow.FlowJob.do Execute(FlowJob.java:99)
              at org.springframework.batch.core.job.AbstractJob.exe cute(AbstractJob.java:281)
              at org.springframework.batch.core.launch.support.Simp leJobLauncher$1.run(SimpleJobLauncher.java:120)
              at org.springframework.core.task.SyncTaskExecutor.exe cute(SyncTaskExecutor.java:49)
              at org.springframework.batch.core.launch.support.Simp leJobLauncher.run(SimpleJobLauncher.java:114)
              at org.springframework.batch.core.launch.support.Simp leJobOperator.start(SimpleJobOperator.java:300)
              at batch.core.launch.StepControllerLauncher$1.run(Ste pControllerLauncher.java:554)
              at java.lang.Thread.run(Unknown Source)
              Caused by: org.hibernate.exception.LockAcquisitionException: could not update: [moneyoperations.domain.PlanContractualPaymentEleme ntDo#component[planNumber,elementNumber]{elementNumber=000057, planNumber=040737119X4 }]
              at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:105)
              at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:66)
              at org.hibernate.persister.entity.AbstractEntityPersi ster.update(AbstractEntityPersister.java:2443)
              at org.hibernate.persister.entity.AbstractEntityPersi ster.updateOrInsert(AbstractEntityPersister.java:2 325)
              at org.hibernate.persister.entity.AbstractEntityPersi ster.update(AbstractEntityPersister.java:2625)
              at org.hibernate.action.EntityUpdateAction.execute(En tityUpdateAction.java:115)
              at org.hibernate.engine.ActionQueue.execute(ActionQue ue.java:279)
              at org.hibernate.engine.ActionQueue.executeActions(Ac tionQueue.java:263)
              at org.hibernate.engine.ActionQueue.executeActions(Ac tionQueue.java:168)
              at org.hibernate.event.def.AbstractFlushingEventListe ner.performExecutions(AbstractFlushingEventListene r.java:321)
              at org.hibernate.event.def.DefaultFlushEventListener. onFlush(DefaultFlushEventListener.java:50)
              at org.hibernate.impl.SessionImpl.flush(SessionImpl.j ava:1028)
              at moneyoperations.dao.hibernate.ContractualPaymentEl ementDaoHibernate.recordPlanContractualPaymentElem entList(ContractualPaymentElementDaoHibernate.java :285)
              ... 39 more
              Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
              at com.microsoft.sqlserver.jdbc.SQLServerException.ma keFromDatabaseError(SQLServerException.java:196)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.ge tNextResult(SQLServerStatement.java:1454)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.doExecutePreparedStatement(SQLServerPrepared Statement.java:388)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement$PrepStmtExecCmd.doExecute(SQLServerPreparedS tatement.java:338)
              at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IO Buffer.java:4026)
              at com.microsoft.sqlserver.jdbc.SQLServerConnection.e xecuteCommand(SQLServerConnection.java:1416)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.ex ecuteCommand(SQLServerStatement.java:185)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.ex ecuteStatement(SQLServerStatement.java:160)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.executeUpdate(SQLServerPreparedStatement.jav a:306)
              at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeUpdate(DelegatingPreparedStatement.java:1 05)
              at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeUpdate(DelegatingPreparedStatement.java:1 05)
              at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(N onBatchingBatcher.java:46)
              at org.hibernate.persister.entity.AbstractEntityPersi ster.update(AbstractEntityPersister.java:2421)
              ... 49 more
              </code>

              Comment


              • #8
                Thanks for your reply Dave.

                I have set the TransactionIsolation as 4096 (ie..READ_COMMITTED_SNAPSHOT), While getting session from the SessionFactoryUtils. Now there is no dead lock happened.

                While setting the same in hibernateProperties, hibernate / Spring didn't pick it up. what may be the reason?

                <code>

                public synchronized Session getCurrentSession() {

                LOGGER.debug("Enter getCurrentSession.");

                Session currentSession = SessionFactoryUtils.getSession(sessionFactory, true);
                try {
                currentSession.connection().setTransactionIsolatio n(4096);
                } catch (HibernateException e) {
                LOGGER.error("HibernateException: " + e);
                } catch (SQLException e) {
                LOGGER.error("SQLException: " + e);
                }
                }

                LOGGER.debug("Exit getCurrentSession.");


                return currentSession;

                }
                </code>

                Comment


                • #9
                  OK, there's nothing funny about that stack trace. You are in a normal chunk transaction started by the TaskletStep. The isolation level for the transaction, if you haven't changed it, should be DEFAULT (meaning Spring won't modify the Connection). Maybe you need to configure your custom default level in the DataSource? Depending on the DataSource and driver you are using there might be a native way of doing that or you could look into the DataSource implementations in Spring (one of them is for setting isolation levels).

                  I know you don't want these locks if you can avoid them, but if you just set that exception to be retryable Spring Batch will retry the transaction for you, and that's often the only way round deadlocks.

                  Comment

                  Working...
                  X