oracle - 交易死锁TX

Question

有一个批处理和一个更新同一个表的常规应用程序。我的批处理有多个线程在多个 session 上运行。我的批处理 Tomcat 中出现以下错误:

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

---

这是我的 oracle 跟踪文件:

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  [............... 

---

过去几天我一直在研究这个问题。据我所知，很少有人说它是一个索引问题，很少有人说它是 INITRANS ……我不确定……但这种死锁很少发生。但每当它发生时，它就是一个大问题。

所以请大家帮帮我..要寻找什么..以及我如何解决这个问题..

Answer 1

查看您的两个 UPDATE 语句并尝试理解为什么它们会请求同一行，但顺序不同。几乎都是这样发生死锁。

有几种可能的方法可以避免此错误:

1) 以相同的顺序更新行。您可以通过提示强制执行全表扫描或索引来执行此操作。 (我不是 100% 确定使用相同的访问方法总能避免这个问题，但在实践中它似乎确实解决了这个问题。请参阅我的旧 question 关于访问方法相同时的死锁的痛苦讨论。 )

2) 不要同时运行两个进程。

3) 处理异常。例如，像这样:

declare
    deadlock exception;
    pragma exception_init(deadlock, -00060);
begin
    <code>
    exception when deadlock then
        <do something about it here, such as re-try>
end;
/

您需要将异常处理添加到两个代码块。并且死锁仍然会生成一个跟踪文件，这可能会放慢速度并占用很多没人期望的空间。