Locks when no index on child FK
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Locks when no index on child FK

  1. #1
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Locks when no index on child FK

    Hi Guys & Gals,

    I have some info on this (googling etc) and the current state of my understanding is:

    If you have an FK constraint but no index on the FK column(s) of the child table, then if, in the parent table, you UPDATE the column involved in the constraint or DELETE a row, Oracle must take a table lock on the child table (until COMMIT in <=8i, temporarily in >=9i).

    Q:
    Does the same thing happen if you SELECT FOR UPDATE in the parent table?

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    No I don't believe so. SELECT FOR UPDATE requires row-share (SS) lock on parent. UPDATE of PK (or DELETE of row) requires share lock (S) on child and row-exclusive lock (SX) on parent.

    Of course this is easy to test out...

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by padders
    Of course this is easy to test out...
    Yes ! ! ! Being a bit slow today.
    Code:
    In 8i:
    select for update:
    RLSID LK      CTIME LOCK_MODE     
    ----- -- ---------- -------------
       14 TM        488 Row-S (SS)    
       14 TX        488 Exclusive (X)
    on parent only
    
    update PK:
    RLSID LK      CTIME LOCK_MODE     
    ----- -- ---------- ------------- 
       14 TM         34 Row-X (SX)    
       14 TM         34 Share (S)     
       14 TX         34 Exclusive (X) 
    on parent and child(ren).
    Now I have to go to quiet corner and think exactly what that means . . .

    padders, thanks very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width