title: Transaction deadlock TX
body: <p>have a batch process and a regular application that updates the same table.My batch have multiple threads that run on multiple sessions. I got the following errors in my batch Tomcat:  </p>
  
  <pre><code>2012-09-10 11:30:17,043 [SyncDataThread567] ERROR org.springframework.batch.core.step.AbstractStep - Encountered an error executing the step
  aaa.bbb.ccc.framework.orm.DAOException:   
  --- The error occurred in abc.xml.  
  --- The error occurred while applying a parameter map.  
  --- Check the ear.updateServiceTimeParamMap.  
  --- Check the statement (update procedure failed).  
  --- Cause: java.sql.SQLException: ORA-20011: FUNC_UPDATESERVICETIME : Error occured  ORA-00060: deadlock detected while waiting for resource
  ORA-06512: at "ER.FUNC_UPDATESERVICETIME", line 154
  ORA-06512: at line 1
  
      at aaa.bbb.ccc.ddd.eee.Sss.updateServiceTimes(ServiceOrderDAOImpl.java:76)
      at sun.reflect.GeneratedMethodAccessor352.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:307)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
      at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
      at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
      at $Proxy6.updateServiceTimes(Unknown Source)
      at aaa.bbb.ccc.ddd.eeee.Inbddd.updateServiceTimes(InbDataWriter.java:144)
      at aaa.bbb.ccc.ddd.eeee.Inbddd.write(InbDataWriter.java:74)
      at sun.reflect.GeneratedMethodAccessor270.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:307)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
      at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
      at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
      at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
      at $Proxy7.write(Unknown Source)
      at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:171)
      at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:150)
      at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:268)
      at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:194)
      at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:74)
      at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:386)
      at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128)
      at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:264)
      at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:76)
      at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:367)
      at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:214)
      at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:143)
      at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:250)
      at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)
      at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:109)
      at org.springframework.batch.core.partition.support.TaskExecutorPartitionHandler$1.call(TaskExecutorPartitionHandler.java:107)
      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      at java.util.concurrent.FutureTask.run(FutureTask.java:138)
      at org.springframework.core.task.SimpleAsyncTaskExecutor$ConcurrencyThrottlingRunnable.run(SimpleAsyncTaskExecutor.java:192)
      at java.lang.Thread.run(Thread.java:619)
  Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   
  --- The error occurred in ael.xml.  
  --- The error occurred while applying a parameter map.  
  --- Check the eraa.updateServiceTimeParamMap.  
  --- Check the statement (update procedure failed).  
  --- Cause: java.sql.SQLException: ORA-20011: FUNC_UPDATESERVICETIME : Error occured  ORA-00060: deadlock detected while waiting for resource
  ORA-06512: at "ER.FUNC_UPDATESERVICETIME", line 154
  ORA-06512: at line 1
  
      at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
      at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:120)
      at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
      at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
      at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
      at com.iit.integration.erl.orm.ServiceOrderDAOImpl.updateServiceTimes(ServiceOrderDAOImpl.java:71)
      ... 44 more
  Caused by: java.sql.SQLException: ORA-20011: FUNC_UPDATESERVICETIME : Error occured  ORA-00060: deadlock detected while waiting for resource
  ORA-06512: at "ER.FUNC_IIT_UPDATESERVICETIME", line 154
  ORA-06512: at line 1
  
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
      at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
      at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
      at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
      at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:278)
      at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:39)
      at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
      ... 49 more
  </code></pre>
  
  <hr>
  
  <p>This is my oracle trace file:</p>
  
  <pre><code>Redo thread mounted by this instance: 1
  
  Oracle process number: 63
  
  Windows thread id: 2464, image: ORACLE.EXE (SHAD)
  
  
  *** 2012-09-10 11:30:12.384
  *** SERVICE NAME:(SYS$USERS) 2012-09-10 11:30:12.244
  *** SESSION ID:(411.3766) 2012-09-10 11:30:12.244
  DEADLOCK DETECTED
  [Transaction Deadlock]
  Current SQL statement for this session:
  UPDATE SP SET SRVC_TM = :B4 , MODIFICATION_DTM=SYSDATE WHERE OPERATION_AREA_CD = :B3 AND ROUTE_TYP = :B2 AND OBJECTID = :B1 
  ----- PL/SQL Call Stack -----
    object      line  object
    handle    number  name
  000000057D9B52E8       134  function ER.FUNC_UPDATESERVICETIME
  000000057C3A5848         1  anonymous block
  The following deadlock is not an ORACLE error. It is a
  deadlock due to user error in the design of an application
  or from issuing incorrect ad-hoc SQL. The following
  information may aid in determining the deadlock:
  Deadlock graph:
                         ---------Blocker(s)--------  ---------Waiter(s)---------
  Resource Name          process session holds waits  process session holds waits
  TX-00040020-0017465b        63     411     X             94     364           X
  TX-00020020-00166804        94     364     X             63     411           X
  session 411: DID 0001-003F-00000033 session 364: DID 0001-005E-00000016
  session 364: DID 0001-005E-00000016 session 411: DID 0001-003F-00000033
  Rows waited on:
  Session 364: obj - rowid = 0000CC64 - AAAMxkAA2AAA1q2AAY
    (dictionary objn - 52324, file - 54, block - 219830, slot - 24)
  Session 411: obj - rowid = 0000CC64 - AAAMxkAA2AAA1q2AAR
    (dictionary objn - 52324, file - 54, block - 219830, slot - 17)
  Information on the OTHER waiting sessions:
  Session 364:
    pid=94 serial=6104 audsid=693767 user: 57/ER
    O/S info: user: , term: , ospid: 1234, machine: abc
              program: 
    Current SQL Statement:
  
  UPDATE SP SET ORIG_NO='751' ,ORIG_SEQ_NO=0,SP_ROUTING_STATUS='A', USER_ID='XXXX', MODIFICATION_DTM=SYSDATE  WHERE OBJECTID IN ('104883389','104883404','104883407','104883440','104883443','104883455','104883467','104883509','104883545','104883764','104883788','104883806','104883812','104883821','104883836','104883854','104883863','104883893','104883899','104883931','104883937','104883964','104884084','104884117','104884120','104884138','104884141','104885439','104883386','104883422','104883560','104883587','104883767','104883785','104883809','104883824','104883845','104883851','104883884','104883890','104883955','104883958','104884012','104884093','104884114','104885412','104885436','104885442','104885445','104883383','104883395','104883413','104883419','104883464','104883494','104883524','104883773','104883842','104883917','104883920','104883943','104883949','104883967','104883997','104884051','104884105','104884108','104885451','104883437','104883461','104883476','104883497','104883500','104883503','104883566','104883584','104883614','104883794','104883800','104883815','104883830','104883857','104883869','104883923','104883952','104884048','104884057','104884063','104884066','104884081','104884087','104884102','104884111','104884135','104885415','104885424','104885427','104886297','104886308','104883398','104883410','104883458','104883473','104883512','104883515','104883527','104883530','104883536','104883554','104883596','104883770','104883782','104883803','104883827','104883833','104883839','104883848','104883866','104883875','104883878','104883896','104883902','104883914','104883970','104883976','104884060','104884069','104884072','104884123','104884132','104885409','104885430','104883425','104883431','104883446','104883449','104883452','104883482','104883506','104883518','104883539','104883548','104883569','104883575','104883578','104883623','104883779','104883797','104883818','104883860','104883925','104883934','104883940','104883946','104883973','104883979','104883982','104884078','104884090','104884096','104885421','104885448','104885454','104883392','104883416','104883428','104883479','104883491','104883521','104883542','104883551','104883557','104883563','104883872','104883911','104883928','104883961','104883994','104884018','104884054','104884099','104884129','104886299','104883401','104883434','104883470','104883485','104883533','104883572','104883581','104883776','104883791','104883881','104883887','104883905','104883908','104884075','104884126','104885418','104885433') 
  End of information on OTHER waiting sessions.
  ===================================================
  PROCESS STATE
  -------------
  Process global information:
       process: 000000057B3343D8, call: 0000000574FCBF78, xact: 0000000576A07F60, curses: 000000057E48D858, usrses: 000000057E48D858
    ----------------------------------------
    SO: 000000057B3343D8, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
    (process) Oracle pid=63, calls cur/top: 0000000574FCBF78/0000000574FD4C48, flag: (0) -
              int error: 0, call error: 0, sess error: 0, txn error 0
    (post info) last post received: 108 0 4
                last post received-location: aaa
                last process to post me: 7e31d890 1 6
                last post sent: 0 0 112
                last post sent-location: bbb
                last process posted by me: 7b334c00 3 0
      (latch info) wait_event=0 bits=10
        holding    (efd=19) 4745310 Parent+children enqueue hash chains level=4 
          Location from where latch is held: cmi: gpl: 
          Context saved from call: 0
          state=busy, wlstate=free
            recovery area:
  Dump of memory from 0x000000057E300810 to 0x000000057E300830
  57E300810 00000000 00000000 00000000 00000000  [............... 
  </code></pre>
  
  <hr>
  
  <p>I have been reaserching this issue from past few days. From what I saw few are saying its a indexing issue, few are saying its INITRANS... I am not sure.. But this deadlock happens very rare. But whenever it happens its a big issue. </p>
  
  <p>So please help me guys.. what to look for.. and how I can solve this issue.. </p>