This discussion is archived
5 Replies Latest reply: Mar 4, 2009 4:15 AM by CharlesHooper RSS

Deadlocks - conflicting sqls

610916 Newbie
Currently Being Moderated
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
  • 1. Re: Deadlocks - conflicting sqls
    Ramesh Eega Explorer
    Currently Being Moderated
    Most likely locks while inserting would be either inserting same unique/primary key values or bitmap index keys.
  • 2. Re: Deadlocks - conflicting sqls
    CharlesHooper Expert
    Currently Being Moderated
    Taking a somewhat wild guess:
    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)
    If I were to take a guess from the above, session 123 inserted into the child table (or modified a row) and is attemping to insert into the parent table, but was blocked by session 122. Session 122 modified another row in the parent table, but the child table has a missing foreign key index, so the modification of the parent table tried to generate a mode 4 share lock on the child table, but is blocked due to the mode 6 exclusive row lock on the child table acquired by session 123. The mode 4 share lock may be caused by other problems including potential duplicate values in a primary/unique column (should the other session commit), a problem with initrans, bitmap indexes, a session trying to modify a row already modified by another session, or a handful of other problems.

    If you search the forum, I have previously posted a couple different deadlock graphs and the SQL code that I used to generate those deadlock graphs. You might find an exact match for the above pattern.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating Inc.

    Edited by: Charles Hooper on Mar 3, 2009 7:46 PM
    See (/* Test 4 - Transaction table that does not use a sequence contributes to the problem */):
    Re:  Deadlock ( holds: S, waits: SSX )
  • 3. Re: Deadlocks - conflicting sqls
    155651 Newbie
    Currently Being Moderated
    Find the session ID's causing the deadlocks. Get sql_text from v$sqlarea

    select sql_text from v$sqlarea where (address,hash_value) IN(select sql_address, sql_hash_value from v$session where sid=<sid>);

    See this link to get SID's of sessions causing deadlocks
    http://www.myoracleguide.com/s/managelocks.htm
  • 4. Re: Deadlocks - conflicting sqls
    mbobak Oracle ACE
    Currently Being Moderated
    Well, I have to disagree w/ Charles. If the deadlock was due to unindexed foreign keys, that would be a TM enqueue. This deadlock graph indicates two TX enqueues.

    The current session, 446, is executing the insert on PS_BI_ACCT_ENTRY. The second line of the deadlock graph indicates that the session is waiting on a TX in 'S' mode.

    The other session, 404, is executing the update on PS_BI_LOADGL_PROC. The first line of the deadlock graph indicates that the session is waiting on a TX in 'X' mode.

    The insert waiting on TX in 'S' mode is almost certainly a case of a session (446) trying to insert a value for a primary or unique key, where another session (404) has already inserted that value and no yet committed. This session will wait for the other session to either commit or rollback.

    The update waiting on TX in 'X' mode is almost certainly a case of a session (404) trying to update (lock) an existing row that another session (446) has already selected for update or updated or deleted that same row and not yet committed.

    Hope that helps,

    -Mark
  • 5. Re: Deadlocks - conflicting sqls
    CharlesHooper Expert
    Currently Being Moderated
    Hi Mark,

    No problem with you disagreeing with me, and I agree that an unindexed foreign key would result in a TM lock on the child table. The setup that I was trying to describe was one in which the TM lock could not be obtained due to another session already holding a TX lock on the child, something like this:
    CREATE TABLE T1(C1 NUMBER(10) PRIMARY KEY);
    
    INSERT INTO T1 VALUES(1);
    INSERT INTO T1 VALUES(2);
    INSERT INTO T1 VALUES(3);
    INSERT INTO T1 VALUES(4);
    
    COMMIT;
    
    CREATE TABLE T2(
      C1 NUMBER(10) PRIMARY KEY,
      C2 NUMBER(10),
      CONSTRAINT FK_T1_C1 FOREIGN KEY(C2) REFERENCES T1(C1) ENABLE);
    
    INSERT INTO T2 VALUES (1,1);
    INSERT INTO T2 VALUES (2,2);
    INSERT INTO T2 VALUES (3,3);
    INSERT INTO T2 VALUES (4,4);
    
    COMMIT;
    
    In session 1:
    SELECT
      *
    FROM
      T2
    WHERE
      C2=1
    FOR UPDATE;
    
    In session 2:
    INSERT INTO T1 VALUES(15);
    
    UPDATE
      T1
    SET
      C1=15
    WHERE
      C1=15;
    
    (Session 2 hangs trying to acquire a TM lock on the child table)
    
    In session 1:
    INSERT INTO T1 VALUES(15);
    
    (Session 1 and 2 deadlock)
    
    Deadlock graph:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name          process session holds waits  process session holds waits
    TX-000a0025-000038bf        17     213     X             18     210           S
    TM-0000d79a-00000000        18     210    SX             17     213    SS     S
    session 213: DID 0001-0011-000003C2     session 210: DID 0001-0012-000002F1
    session 210: DID 0001-0012-000002F1     session 213: DID 0001-0011-000003C2
    Rows waited on:
    Session 210: no row
    Session 213: no row
    I was initially expecting the above deadlock graph to show two TX locks, but that did not happen as you stated. Thanks for the correction.

    Your suggested cause of the problem appears to be correct. I wonder if there is a third table involved which maintains the "next" primary key value?

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points