5 Replies Latest reply: Mar 4, 2009 6:15 AM by Charles Hooper RSS

    Deadlocks - conflicting sqls

    610916
      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
          Most likely locks while inserting would be either inserting same unique/primary key values or bitmap index keys.
          • 2. Re: Deadlocks - conflicting sqls
            Charles Hooper
            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
              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
                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
                  Charles Hooper
                  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.