14 Replies Latest reply: Jun 12, 2014 8:49 AM by 933257 RSS

    lock issue

    933257

      Hi,

      I am using version- Release 11.2.0.3.0 of oracle. And its some error in app logic due to which the deadlock is coming repeatedly during a bulk load process and its reproducible too. I am trying to get the RCA for same.  We got below trace for the dead lock issue. I am seeing repeatation of two occurrences of dead lock through out the trace file, one is "two set of deletes on same table but on different row" and another is "Delete and Insert statement". I checked for the unindexed foreign key specific to these tables(tab2 having c2 primary key and tab1 is having c1 as primary key) but all looks good in that perspect.

       

       

       

       

       

      --------------------------------- case 1 -------------------------------
       
       *** 2014-05-29 04:04:55.957
      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
      TM-0003f7b5-00000000       218     169    SX            157    1099    SX   SSX
      TX-00540001-0002d26c       157    1099     X            218     169           S
       
      session 169: DID 0001-00DA-00001E32 session 1099: DID 0001-009D-00002C2E 
      session 1099: DID 0001-009D-00002C2E session 169: DID 0001-00DA-00001E32 
       
      Rows waited on:
        Session 169: obj - rowid = 00049053 - AABJBTABxAACJjHAAA
        (dictionary objn - 299091, file - 113, block - 563399, slot - 0)
        Session 1099: no row
       
      ----- Information for the OTHER waiting sessions -----
      Session 1099:
        sid: 1099 ser: 41963 audsid: 11427087 user: 485/USER1
          flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
          flags2: (0x40009) -/-/INC
        pid: 157 O/S info: user: oracle, term: UNKNOWN, ospid: 14470
          image: oracle@abc123
        client details:
          O/S info: user: snaweblogic, term: unknown, ospid: 1234
          machine: machine1 program: JDBC Thin Client
          application name: JDBC Thin Client, hash value=2546894660
        current SQL:
        delete from tab2 where c2 = '15083723687'
       
      ----- End of information for the OTHER waiting sessions -----
       
      Information for THIS session:
       
      ----- Current SQL Statement for this session (sql_id=c85zarjztzmky) -----
      INSERT INTO tab2 (c2,c3,c4,c5,c6,c7) VALUES ( '15083725339', null, '33101', null, null, null)
      ===================================================
      
      ----------------------------- case 2 --------------------------------
      
      *** 2014-05-29 04:05:45.426
      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
      TM-0003fb74-00000000       218     169    SX   SSX      232    1337    SX   SSX
      TM-0003fb74-00000000       232    1337    SX   SSX      218     169    SX   SSX
       
      session 169: DID 0001-00DA-00001E32 session 1337: DID 0001-00E8-000017FA 
      session 1337: DID 0001-00E8-000017FA session 169: DID 0001-00DA-00001E32 
       
      Rows waited on:
        Session 169: no row
        Session 1337: no row
       
      ----- Information for the OTHER waiting sessions -----
      Session 1337:
        sid: 1337 ser: 38307 audsid: 11427167 user: 485/USER1
          flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
          flags2: (0x40009) -/-/INC
        pid: 232 O/S info: user: oracle, term: UNKNOWN, ospid: 32115
          image: oracle@abc123
        client details:
          O/S info: user: SNAWEBLOGIC, term: unknown, ospid: 1234
          machine: machine2 program: JDBC Thin Client
          application name: JDBC Thin Client, hash value=2546894660
        current SQL:
        delete from tab1 where c1 = '15083713656'
       
      ----- End of information for the OTHER waiting sessions -----
       
      Information for THIS session:
       
      ----- Current SQL Statement for this session (sql_id=8cazb6p5ykhf9) -----
      delete from tab1 where c1 = '15083718484'
      ===================================================
      
      
      
        • 1. Re: Debugging deadlock issue
          sb92075

          does BIT MAPPED INDEX exist on each of these tables?

          • 3. Re: Debugging deadlock issue
            933257

            Thanks. In case-2 as i just noted, it was showing 'No rows' in the trace so i didnt get any object info, although i got the sql query involving table tab1, also checked that, no bitmap index in that.

             

            But in case-1, i got one of the object id-299091 in the trace file for one of the session, and after getting the details from dba_objects, i found its a BITMAP index on one of the column of table tab2. Perhaps because , this columns have only two distinct value and they have got some perfromance benefit compare to b-tree index on this column, thats why they have created the index.

             

            So what should be the approach for fixing this issue in CASE-1 scenario, should we drop the bitmap index and try once, by creating b-tree?


            • 4. Re: Debugging deadlock issue
              sb92075

              Just  DISABLE  the bitmap index & try again

              • 5. Re: Debugging deadlock issue
                933257

                Thanks, i will try that out, and share the result.

                Just thinking, what must be the reason of deadlock in CASE-2?

                • 6. Re: Debugging deadlock issue
                  Jonathan Lewis

                  Case 2 (TM locks in modes 4 (S) and 5 (SSX) is a classic "foreign key locking" issue.  You have some declared referential integrity (foreign key) constraints on the table that appears in the locks.  In fact your case 1 also shows the same problem in the TM lock (but with a different table - as indicated by the hex object_id in the middle of the TM lock id)

                   

                  Regards

                  Jonathan Lewis

                  • 7. Re: Debugging deadlock issue
                    933257

                    Thank you so much, Jonathan.
                    If i am getting it right


                    In case-2, its TM which is row related lock+session dont show any row information+lock type is SX and the cptured sql for both the session are 'delete from tab1' with filter on primary key. So there exist some child table which are referring to column c1 of tab1 but not having index, so during delete activiy its taking full table lock and casuing more prone to deadlock issue.

                     

                    In case-1, its combination of both TX and TM. For TX i.e blocker sesion 1099, giving no row information and the statement captured is a 'INSERT Statement into tab2' and so ,perhaps its notdue to unindexed foreign key issue.
                    For TM its blocker session 169 , giving row information and the object-id shown is 299091(hopefully hex id - 49053 belongs to same object tab2, currently not able to check) belongs to a bitmap index on column c4 of tab2. and the statement captured is 'delete from tab2' with filter on primary key.
                    So in this case its due to missing foreign key index on column c2 or its bitmap index taking block level lock causing deadlock issue or its primary key overlap?

                    • 8. Re: Debugging deadlock issue
                      Jonathan Lewis

                      Case 2 - correct (very nearly).  The child table has object_id 0x3fb74 (260980). The SX locks that are being held are "mode 3", the standard lock mode for simple updates on the table;  the SSX locks being waited show you that you have a foreign key locking problem.  They are SSX (mode 5) rather than S (mode 4) because the sessions were already holding SX (mode 3).  It's not possible to state exactly how you got into this state, but there are two common patterns:

                      a) your application explicitly says - "delete the child rows for a parent", then "delete the parent"

                      b) your foreign key constraint is declared "on delete cascade"

                       

                       

                      Case 1 -  The insert is waiting in TX mode 4 - which means the rowid information may be completely misleading; it may be showing information about an earlier wait which hasn't been cleaned out properly.  But you can see the SX hold and SSX wait for session 1099 which tells you that you you have a foreign key locking problem, and the fact that session 192 is waiting to insert into the same table is a strong indicator that the TX lock is about a bitmap index (though there are 3 or 4 other, more exotic, possibilities). 

                       

                      Regards

                      Jonathan Lewis

                      • 9. Re: Debugging deadlock issue
                        933257

                        In my application log, i got below error during deadlock period, i am trying to get the scenario matched with oracle trace

                         

                        SQLException
                        java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (F_C2) violated - parent key not found
                        ORA-06512: at "USER1.COM_PRC1", line 44
                        ORA-06512: at "USER1.TAB2_TRIG", line 5
                        ORA-04088: error during execution of trigger 'USER1.TBA2_TRIG'
                        ORA-00060: deadlock detected while waiting for resource

                         

                        TAB2_TRIG is a 'after inserts' trigger on tab2, which calls procedure com_prc1 within its code. Its not using autonomous transaction.
                        line-44 in the proc is insert into one of the child table TAB3 referring to tab2. constraint F_C2 is referential constraing on this table TAB3 referring to c2 of TAB2.

                        • 10. Re: Debugging deadlock issue
                          sb92075

                          The Oracle database is the victim; not the culprit.

                          Oracle deadlock is strictly & always due to application coding error and can only be fixed by a coding change to application.

                          • 11. Re: Debugging deadlock issue
                            933257

                            Thanks Jonathan,sb92075.

                             

                            I got the scenario(deadlock graph) reproduced for case-1 with below scenario. Even after creating all foreign key index.

                             

                            Session 1:

                            INSERT INTO tab2 (c2,c3,c4,c5,c6,c7) VALUES ( '15083725339', null, '33101', null, null, null)  

                             

                            1 row created.

                             

                            Session 2:

                            INSERT INTO tab2 (c2,c3,c4,c5,c6,c7) VALUES ( '15083725340', null, '33101', null, null, null)  

                             

                            it just got hung due to bitmap index lock by session 1 (bitmap index present on c4)

                             

                            Session -1

                            delete from tab2 where c2 = '15083723687';

                             

                            0 rows deleted.

                             

                            Session2:

                            Errored out with deadlock!!

                             

                            deadlock graph produced as below

                             

                            Deadlock graph:

                                                   ---------Blocker(s)--------  ---------Waiter(s)---------

                            Resource Name          process session holds waits  process session holds waits

                            TM-000166f1-00000000        39     199    SX             28      11    SX   SSX

                            TX-00090009-000175c4        28      11     X             39     199           S

                            • 12. Re: Debugging deadlock issue
                              sb92075

                              The Oracle database is the victim; not the culprit.

                              Oracle deadlock is strictly & always due to application coding error and can only be fixed by a coding change to application.

                              • 13. Re: Debugging deadlock issue
                                Jonathan Lewis

                                Since your session 1 reports that it deleted zero rows and clearly is not waiting, the deadlock graph you have reported has nothing to do with session 1 - so something else is going on or you've found a bug which reports a deadlock when only one session is waiting.

                                 

                                Create a script that creates and populates tables that matches your definitions, show it to us, then show us the exact steps that lead to the deadlock, and we may be able to help.

                                 

                                Regards

                                Jonathan Lewis

                                • 14. Re: Debugging deadlock issue
                                  933257

                                  Thanks jonathan for the responce.

                                   

                                  Actually i missed to mention, that during replication of the scenario, the table tab2 is having 7 triggers on it, 4 fires on INSERT and 3 during DELETE and i did see these are updating/deleting from child tables of tab2. So i believe this has somehow replicated the above scenario. Thanks.