6 Replies Latest reply: Jan 6, 2012 11:28 AM by DanyC RSS

    Deadlock caused by missing index on FK ?

    DanyC
      Hi all,

      I have an interesting case where a deadlock was raised and looking at the trace file i see
      Deadlock graph:
                             ---------Blocker(s)--------  ---------Waiter(s)---------
      Resource Name          process session holds waits  process session holds waits
      TM-0000b228-00000000        35    1057    SX             32    1068    SX   SSX
      "TX-00090012-00008545        32    1068     X             21    1065           X"
      TX-000a0009-0000bf57        21    1065     X             35    1057           X
       
      session 1057: DID 0001-0023-00000400     session 1068: DID 0001-0020-00000131 
      session 1068: DID 0001-0020-00000131     session 1065: DID 0001-0015-0000000A 
      session 1065: DID 0001-0015-0000000A     session 1057: DID 0001-0023-00000400 
       
      Rows waited on:
        Session 1057: obj - rowid = 0000B277 - AAALJ3AALAAAAL8AAA
        (dictionary objn - 45687, file - 11, block - 764, slot - 0)
        "Session 1068: no row"
        Session 1065: obj - rowid = 0000B228 - AAALIoAALAAAAY5AAP
        (dictionary objn - 45608, file - 11, block - 1593, slot - 15)
       
      ----- Information for the OTHER waiting sessions -----
      Session 1068:
        sid: 1068 ser: 113 audsid: 282002 user: 129/LSM flags: 0x41
        pid: 32 O/S info: user: oracle, term: UNKNOWN, ospid: 23787
          image: oracle@oradev01
        client details:
          O/S info: user: apps1, term: unknown, ospid: 1234
          machine: oradev01 program: JDBC Thin Client
          application name: JDBC Thin Client, hash value=2546894660
        current SQL:
        
      delete from cont_ctrl_prms where id=:1
      
      Session 1065:
        sid: 1065 ser: 740 audsid: 281949 user: 129/LSM flags: 0x41
        pid: 21 O/S info: user: oracle, term: UNKNOWN, ospid: 28912
          image: oracle@oradev01
        client details:
          O/S info: user: apps1, term: unknown, ospid: 1234
          machine: oradev01 program: JDBC Thin Client
          application name: JDBC Thin Client, hash value=2546894660
        current SQL:
        
      delete from availability where handle=:1
      
      ----- End of information for the OTHER waiting sessions -----
      The db version is 11.1.0.7.0 and the relation between cont_ctrl_prms & availability is parent -> child.

      There is a FK on availability as below with no rule like delete cascade
      ALTER TABLE AVAILABILITY ADD
          CONSTRAINT FK7710155BB7FB8C0D FOREIGN KEY
          (
            CONTENTCONTROLPARAMS_ID
            )
            REFERENCES CONT_CTRL_PRMS (ID)
      Now my question is: since there is not delete cascade rule and no UPDATE involved i guess this can't be caused by the missing index on the FK, so any other idea why the deadlock was generated?


      Thanks in advance.
        • 1. Re: Deadlock caused by missing index on FK ?
          P.Forstmann
          I am not sure that you can exclude missing foreign key index cause. If this would be the case deadlocks generally occur when transactions lock same data but not in the same order. For example for 3 concurrent sessions:
          T1: lock row r1 in table 1
          T2: lock row r2 in table 2
          T3: lock row r3 in table 3
          T3: lock row r2 in table 2 -- waits for T2
          T2: lock row r1 in table 1 -- waits for T1
          T1: lock row r3 in table 3 -- waits for T3 that waits for T2 that waits for T1 => deadlock.
          • 2. Re: Deadlock caused by missing index on FK ?
            Jonathan Lewis
            DanyC wrote:
            Hi all,

            I have an interesting case where a deadlock was raised and looking at the trace file i see
            This looks like the trace file dumped by session 1057.
            What's the statement that 1057 is waiting on ?

            Regards
            Jonathan Lewis
            http://jonathanlewis.wordpress.com
            Author: <b><em>Oracle Core</em></b>
            • 3. Re: Deadlock caused by missing index on FK ?
              DanyC
              Hey Jonathan,

              I think i've missed the most important bit :) which is
              ----- Current SQL Statement for this session (sql_id=cp8nh5fnm5xyr) -----
              update data_master_token set token=:1 where data_master_name=:2
              and the table looks like
              desc LSM.data_master_token
              Name             Null     Type               
              ---------------- -------- ------------------ 
              DATA_MASTER_NAME NOT NULL VARCHAR2(255 CHAR) 
              TOKEN                     NUMBER(19)         
              Cheers,
              • 4. Re: Deadlock caused by missing index on FK ?
                Jonathan Lewis
                Jonathan Lewis wrote:

                This looks like the trace file dumped by session 1057.
                What's the statement that 1057 is waiting on ?
                I have a quick shot at faking it up anyway - here's my deadlock graph:

                I assumed that the SQL for 1057 was accessing something outside the parent/child set because the object id of the row waited for was higher than the child object id (not guaranteed to be true, of course, but a reasonable guess).
                Deadlock graph:
                                       ---------Blocker(s)--------  ---------Waiter(s)---------
                Resource Name          process session holds waits  process session holds waits
                TM-00019dbb-00000000        19      43    SX             26      40    SX   SSX
                TX-000a001f-00002903        26      40     X             21      60           X
                TX-0005001a-000032a5        21      60     X             19      43           X
                 
                session 43: DID 0001-0013-0000200C     session 40: DID 0001-001A-0000019C 
                session 40: DID 0001-001A-0000019C     session 60: DID 0001-0015-00005B0C 
                session 60: DID 0001-0015-00005B0C     session 43: DID 0001-0013-0000200C 
                 
                Rows waited on:
                  Session 43: obj - rowid = 00019DBD - AAAZ29AADAAAAKBAAA
                  (dictionary objn - 105917, file - 3, block - 641, slot - 0)
                  Session 40: no row
                  Session 60: obj - rowid = 00019DBB - AAAZ27AADAAAAGBAAE
                  (dictionary objn - 105915, file - 3, block - 385, slot - 4)
                 
                ----- Information for the OTHER waiting sessions -----
                Session 40:
                  
                delete from parent where id = 1
                 
                Session 60:
                delete from child where id = 3
                 
                Information for THIS session:
                 
                ----- Current SQL Statement for this session (sql_id=1v6ndbw5ypwzy) -----
                 update t1 set n1 = 1
                Sequence of steps:
                Table t1 has one row.
                Table parent has three rows, p1, p2, p3
                Table child has three rows c1, c2, c3
                The parent/child row links are the obvious ones.
                The foreign key has been declared, but has no supporting index

                Session 43     delete c2 - acquires SX lock on child (TM/3)
                Session 60     update t1
                Session 40     delete c1, c3 - acquires SX lock on child (TM/3)
                session 43     update t1 - start waiting on session 60 (TX/6)
                Session 60     delete c3 - start waiting on session 40 (TX/6)
                Session 40     delete p1 - start waiting on session 43 - SX to SSX conversion (TM/3 -> TM/5)


                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                Author: <b><em>Oracle Core</em></b>
                • 5. Re: Deadlock caused by missing index on FK ?
                  Mohamed Houri
                  It is not a necessity to have an on delete cascade rule to produce a deadlock. You have a not very common case of deadlock involving TM and TX locks at the same time. Jonathan Lewis blogged about a similar case here:

                  http://jonathanlewis.wordpress.com/2011/08/29/deadlock-2/#more-7018


                  But as far as I know when, you have this
                  Deadlock graph:
                                         ---------Blocker(s)--------  ---------Waiter(s)---------
                  Resource Name          process session holds waits  process session holds waits
                  TM-0000b228-00000000        35    1057    SX             32    1068    SX   SSX
                  there is a big chance that this is due to a non indexed Foreign key

                  Hope this helps

                  Mohamed Houri
                  • 6. Re: Deadlock caused by missing index on FK ?
                    DanyC
                    Thank you all for your reply.

                    Jonathan - very much impressed with the detail answer.


                    Many thanks,
                    Dani