Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Re: enq: TM - contention

Jonathan LewisJun 30 2015 — edited Jul 6 2015

When you delete or modify a parent key value Oracle has to check that there are no dependent child rows.

If there is no index the only way to do this safely is to lock the child table in a way that blocks all concurrent changes to the table.

If there is a supporting index in place Oracle need only "lock" the relevant block in that index to check that (a) there are no child rows and (b) no-one can insert a child row while the parent transaction is in flight.

(Technically the leaf block is "pinned" in memory with an exclusive pin - which means anyone trying to modify that leaf block will go into a "buffer busy wait" until it is freed.)

Regards

Jonathan Lewis

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 3 2015
Added on Jun 30 2015
8 comments
1,422 views