Oracle Database Discussions

Hi: I am on 10.2.0.3.

Can someone help me with identifying conflicting sqls in deadlock? Is the first (update) sql waiting while the insert one is locking? If so, why they refer to different tables?


Dump file /db/dbdump/XXPRD/udump/fsprd_ora_27440.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/app/oracle/product/10.2.0
System name: HP-UX
Node name: indxxx17
Release: B.11.23
Version: U
Machine: ia64
Instance name: XXPRD
Redo thread mounted by this instance: 1
Oracle process number: 123
Unix process pid: 27440, image: oracleXXPRD@indxxx17

*** SERVICE NAME:(FSPRD) 2009-02-26 10:28:32.262
*** SESSION ID:(446.2263) 2009-02-26 10:28:32.262
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-000a0000-0002a5d5 123 446 X 122 404 X
TX-000b002a-0001b1ea 122 404 X 123 446 S
session 446: DID 0001-007B-0000034A session 404: DID 0001-007A-00000278
session 404: DID 0001-007A-00000278 session 446: DID 0001-007B-0000034A
Rows waited on:
Session 404: obj - rowid = 0000D536 - AAANU2AAXAAAAMeAAA
(dictionary objn - 54582, file - 23, block - 798, slot - 0)
Session 446: obj - rowid = 00000000 - D/////ADAAAAB0eAAA
(dictionary objn - 0, file - 192, block - 7454, slot - 0)
Information on the OTHER waiting sessions:
Session 404:
pid=122 serial=59321 audsid=1173413 user: 59/SYSADM
O/S info: user: SYSTEM, term: INDXXX80, ospid: 2424:4888, machine: ENT\INDXXX80
program: sqrw.exe
client info: BGILBERT,2424
application name: sqrw.exe, hash value=0
Current SQL Statement:
UPDATE PS_BI_LOADGL_PROC SET BI_LOAD_GL_STAT = :1 where OPRID = :2 and RUN_CNTL_ID = :3 and SEQ_NUM = :4
End of information on OTHER waiting sessions.
Current SQL statement for this session:
INSERT INTO PS_BI_ACCT_ENTRY (BUSINESS_UNIT,INVOICE,LINE_SEQ_NUM,ACCOUNTING_DT,ACCT_ENTRY_TYPE,DISC_SUR_LVL,DISC_SUR_ID,LINE_DST_SEQ_NUM,TAX_AUTHORITY_CD,D
ISC_SUR_INDICATOR,BUSINESS_UNIT_GL,LEDGER_GROUP,LEDGER,ACCOUNTING_PERIOD,FISCAL_YEAR,ACCOUNT,ALTACCT,DEPTID,OPERATING_UNIT,PRODUCT,FUND_CODE,CLASS_FLD,PROGRA
M_CODE,BUDGET_REF,AFFILIATE,AFFILIATE_INTRA1,AFFILIATE_INTRA2,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,PROJECT_ID,STATISTICS_CODE,MONETARY_AMOUNT,STATISTIC_AMOUNT
,OPEN_ITEM_KEY,JRNL_LN_REF,LINE_DESCR,USER1,USER2,USER3,USER4,USER5,JOURNAL_ID,JOURNAL_LINE,BUDGET_HDR_STATUS,BUDGET_LINE_STATUS,KK_TRAN_OVER_FLAG,KK_TRAN_OV
ER_OPRID,GL_DISTRIB_STATUS,PROCESS_INSTANCE,APPL_JRNL_ID,CURRENCY_CD,FOREIGN_CURRENCY,FOREIGN_AMOUNT,RT_TYPE,RATE_MULT,RATE_DIV,DOC_TYPE,DOC_SEQ_NBR,DOC_SEQ_
DATE,FROM_ACCRUAL,BUSINESS_UNIT_PC,ACTIVITY_ID,RESOURCE_TYPE,RESOURCE_CATEGORY,RESOURCE_SUB_CAT,ANALYSIS_TYPE,ENTRY_EVENT) select B2.BUSINESS_UNIT, B2.INVOIC
E, B2.LINE_SEQ_NUM, B2.ACCOUNTING_DT, B2.ACCT_ENTRY_TYPE, B2.DISC_SUR_LVL, B2.DISC_SUR_ID, B2.LINE_DST_SEQ_NUM, B2.TAX_AUTHORITY_CD, B2.DISC_SUR_INDICATOR, B
2.BUSINESS_UNIT_GL, B2.LEDGER_GROUP, B2.LEDGER, B2.ACCOUNTING_PERIOD, B2.FISCAL_YEAR, B2.ACCOUNT,B2.ALTACCT,B2.DEPTID,B2.OPERATING_UNIT,B2.PRODUCT,B2.FUND_CO
DE,B2.CLASS_FLD,B2.PROGRAM_CODE,B2.BUDGET_REF,B2.AFFILIATE,B2.AFFILIATE_INTRA1,B2.AFFILIATE_INTRA2,B2.CHARTFIELD1,B2.CHARTFIELD2,B2.CHARTFIELD3,B2.PROJECT_ID
,B2.STATISTICS_CODE, B2.MONETARY_AMOUNT, B2.STATISTIC_AMOUNT, B2.OPEN_ITEM_KEY, B2.JRNL_LN_REF, B2.LINE_DESCR, B2.USER1, B2.USER2, B2.USER3, B2.USER4,
B2.USER5, B2.JOURNAL_ID, B2.JOURNAL_LINE, 'V', 'V', ' ', ' ', 'N', 319204, B2.APPL_JRNL_ID, B2.CURRENCY_CD, B2.FOREIGN_CURRENCY, B2.FOREIGN_AMOUN
T, B2.RT_TYPE, B2.RATE_MULT, B2.RATE_DIV, B2.DOC_TYPE, B2.DOC_SEQ_NBR, B2.DOC_SEQ_DATE, B2.FROM_ACCRUAL, B2.BUSINESS_UNIT_PC, B2.ACTIVITY_ID, B2.RESOU
RCE_TYPE, B2.RESOURCE_CATEGORY, B2.RESOURCE_SUB_CAT, B2.ANALYSIS_TYPE, B2.ENTRY_EVENT from PS_BI_ACCT_LN_STG B2, PS_BI_LDGL_TMP D2 where B2.BUSINESS_UN
IT = D2.BUSINESS_UNIT and B2.INVOICE = D2.INVOICE and B2.BUSINESS_UNIT = :1 and B2.ACCT_ENTRY_TYPE = 'AR' and
D2.PROCESS_INSTANCE = :2
===================================================
PROCESS STATE
-------------
Process global information:
process: c00000012b3c9698, call: c0000000ad6da080, xact: c000000129b02ab0, curses: c00000012e53f6f8, usrses: c00000012e53f6f8
----------------------------------------
SO: c00000012b3c9698, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=123, calls cur/top: c0000000ad6da080/c0000000ad6da080, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 115 0 4
last post received-location: kslpsr
last process to post me: c00000012e3a3c20 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: c00000012e3a3c20 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: c00000012b428278
O/S info: user: oracle, term: UNKNOWN, ospid: 27440
OSD pid info: Unix process pid: 27440