一次Deadlock错误

2015-08-05
早上上班时，同事告诉我，客户数据库有个作业出现了死锁。
Oracle死锁时，相关信息会写入告警日志：alert*.log

alert日志中相关信息：

一次六月份的，这次上上线之前，还未影响客户正常使用

Mon Jul 27 09:40:05 2015
ORA-00060: Deadlock detected. More info in file e:\app\administrator\diag\rdbms\hydee\hydee\trace\hydee_ora_9912.trc.
Mon Jul 27 09:42:21 2015

以下是trace文件的内容：

*** 2015-07-27 09:40:04.273
DEADLOCK DETECTED ( ORA-00060 )
 
[Transaction Deadlock]
 
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-0020001f-0000895d       178    4282     X             79    3565           X
TX-00210017-000088ec        79    3565     X            178    4282           X
 
session 4282: DID 0001-00B2-00000614 session 3565: DID 0001-004F-000009BB 
session 3565: DID 0001-004F-000009BB session 4282: DID 0001-00B2-00000614 
 
Rows waited on:
  Session 4282: obj - rowid = 0001447C - AAAUR8AAIAAIOOMAAe
  (dictionary objn - 83068, file - 8, block - 2155404, slot - 30)
  Session 3565: obj - rowid = 0001447C - AAAUR8AAIAAIOOLAAK
  (dictionary objn - 83068, file - 8, block - 2155403, slot - 10)
 
----- Information for the OTHER waiting sessions -----
Session 3565:
  sid: 3565 ser: 44136 audsid: 2081374 user: 95/H2 flags: 0x45
  pid: 79 O/S info: user: SYSTEM, term: WINDOWS-HNAL24I, ospid: 9896
    image: ORACLE.EXE (SHAD)
  client details:
    O/S info: user: webapi, term: IIS46, ospid: 26072:2
    machine: IIS APPPOOL\IIS46 program: w3wp.exe
    application name: w3wp.exe, hash value=2799981571
  current SQL:
  UPDATE TEMP_T_DISTAPPLY_D T SET T.APPLYQTY = T.APPLYQTY - :B2 , T.BAK1 = :B2 WHERE T.WAREID = :B1 
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=2qhua7rnqu3n5) -----
DELETE TEMP_T_DISTAPPLY_D T WHERE T.COMPID = :B2 AND T.BUSNO = :B1 
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000000243F0297B0        18  procedure H2.PROC_AUTO_DISAPPLY
000000244F533BE0        10  function H2.F_AUTO_DISAPPLY

从trace文件来看，引起死锁的SQL语句都是比较简单的.
可以根据：
select dbms_rowid.rowid_object('AAAUR8AAIAAIOOMAAe'),dbms_rowid.rowid_object('AAAUR8AAIAAIOOLAAK')  from dual;

查询死锁对应的object:

这个是个每天都会执行的作业，涉及到的业务机构大概有400多家。


今天早上的,影响了客户使用:

ORA-00060: Deadlock detected. More info in file e:\app\administrator\diag\rdbms\hydee\hydee\trace\hydee_j001_12540.trc.
Errors in file e:\app\administrator\diag\rdbms\hydee\hydee\trace\hydee_j001_12540.trc:
ORA-12012: error on auto execute of job 79949
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "H2.TR_T_STORE_D", line 35
ORA-04088: error during execution of trigger 'H2.TR_T_STORE_D'
ORA-06512: at "H2.TR_T_REACCEPT_D", line 62
ORA-04088: error during execution of trigger 'H2.TR_T_REACCEPT_D'
ORA-06512: at "H2.P_WMS_WRITE_ERP_REJ", line 59
Wed Aug 05 09:01:59 2015



trace文件内容：
*** 2015-08-05 09:06:43.349
DEADLOCK DETECTED ( ORA-00060 )
 
[Transaction Deadlock]
 
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-00130001-0000a12f        75    2626     X             82    4257           X
TX-00380003-000085d2        82    4257     X             75    2626           X
 
session 2626: DID 0001-004B-00001D6D session 4257: DID 0001-0052-0000179C 
session 4257: DID 0001-0052-0000179C session 2626: DID 0001-004B-00001D6D 
 
Rows waited on:
  Session 2626: obj - rowid = 00012E7A - AAAWDdAAIAAABh9AA5
  (dictionary objn - 77434, file - 8, block - 6269, slot - 57)
  Session 4257: obj - rowid = 00012E7A - AAAWDdAAIAAABh7AAW
  (dictionary objn - 77434, file - 8, block - 6267, slot - 22)
 
----- Information for the OTHER waiting sessions -----
Session 4257:
  sid: 4257 ser: 50975 audsid: 2420257 user: 95/H2 flags: 0x45
  pid: 82 O/S info: user: SYSTEM, term: WINDOWS-HNAL24I, ospid: 14224
    image: ORACLE.EXE (SHAD)
  client details:
    O/S info: user: Administrator, term: LENOVO-PC, ospid: 2736:13012
    machine: WORKGROUP\LENOVO-PC program: plsqldev.exe
    application name: PL/SQL Developer, hash value=1190136663
    action name: 测试窗口 - procedure PROC_APPLYT, hash value=4193651987
  current SQL:
  MERGE INTO T_STORE_H STORE_H USING (SELECT :B6 COMPID, :B5 BUSNO, :B4 WAREID, :B3 WAREQTY, :B2 AWAITQTY, :B1 PENDINGQTY FROM DUAL) T ON (STORE_H.COMPID = T.COMPID AND STORE_H.BUSNO = T.BUSNO AND STORE_H.WAREID = T.WAREID) WHEN MATCHED THEN UPDATE SET STORE_H.SUMQTY = STORE_H.SUMQTY - T.WAREQTY, STORE_H.SUMAWAITQTY = STORE_H.SUMAWAITQTY - T.AWAITQTY, STORE_H.SUMPENDINGQTY = STORE_H.SUMPENDINGQTY - T.PENDINGQTY
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=gzabamngz7r5g) -----
MERGE INTO T_STORE_H STORE_H USING (SELECT :B6 COMPID, :B5 BUSNO, :B4 WAREID, :B3 WAREQTY, :B2 AWAITQTY, :B1 PENDINGQTY FROM DUAL) T ON (STORE_H.COMPID = T.COMPID AND STORE_H.BUSNO = T.BUSNO AND STORE_H.WAREID = T.WAREID) WHEN MATCHED THEN UPDATE SET STORE_H.SUMQTY = STORE_H.SUMQTY - T.WAREQTY, STORE_H.SUMAWAITQTY = STORE_H.SUMAWAITQTY - T.AWAITQTY, STORE_H.SUMPENDINGQTY = STORE_H.SUMPENDINGQTY - T.PENDINGQTY
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00000023FFD97CF0        35  H2.TR_T_STORE_D
000000244AEDDE10        62  H2.TR_T_REACCEPT_D
000000241FB705D0        59  procedure H2.P_WMS_WRITE_ERP_REJ

同时， alert日志文件中出现了1200多个业务逻辑校验错误。
ORA-12012: error on auto execute of job 79949
ORA-20001: 商品58745的退货数量应大于0！
ORA-06512: at "H2.TR_T_REACCEPT_D", line 33
ORA-04088: error during execution of trigger 'H2.TR_T_REACCEPT_D'
ORA-06512: at "H2.P_WMS_WRITE_ERP_REJ", line 65

这是因为WMS系统回传退货数量时，数量为0，而后更新对应的退货单时，导致校验错误。
而因为这个错误，这个退货单一直处于未处理状态。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8520577/viewspace-1762372/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8520577/viewspace-1762372/