Apr 23, 2010

Unindexed Foreign Keys

 Less Overhead on Unindexed Foreign Keys

   A table level share lock is placed on unindexed foreign keys in a child table when doing an update or delete on primary key column in the referenced parent table. the lock is realesed immediate after obtaining it. If multiple pk key are  updated or deleted  , the lock is obtained and released once per row .The obtaining and realesing of the shared lock are as follow as

  1) Get a save point .
  2) Obtain a share lock .
  3) Roll back to save point .


  The  share lock has only one purpose , to check whether you have pending transactions against any rows . If that case , the share lock request would fail becuse of exclusive row locks.

Prior to Oracle 9i

No comments: