-
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?
-
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...
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|