This discussion is archived
6 Replies Latest reply: Jan 6, 2012 9:28 AM by 473111 RSS

Deadlock caused by missing index on FK ?

473111 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 ?
    473111 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 ?
    473111 Newbie
    Currently Being Moderated
    Thank you all for your reply.

    Jonathan - very much impressed with the detail answer.


    Many thanks,
    Dani

Legend

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