Re: row level (transactional) locking problem

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 9 Feb 2004 21:05:47 -0000
Message-ID: <000c01c3ef50$841a15b0$6702a8c0@Primary>

There are various options for parent/child lock conflicts, most commonly due to the absence of required foreign key indexes.

For example, you update a parent row
(including a 'no change' update to the
parent id). This requests a lock on the child table in share mode.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- From: "Boris Dali" <boris_dali_at_yahoo.ca> To: <oracle-l_at_freelists.org> Sent: Monday, February 09, 2004 8:09 PM Subject: Re: row level (transactional) locking problem

One follow-up question, Jonathan.

If not IOTs, nor bitmap indexes - what could be the other reasons for mode=4 lock requests from the blocked sessions trying to do an update (I know for inserts it can be things like pk constraint enforcement, but I can't think of any reason other than ITL shortage if the sql waiting/blocked is an update)?

Here's an example from utllockt output (165 blocks say 441 requesting lock mode=4):

WAIT_SES LTYPE REQUEST HELD LOCK_ID1 LOCK_ID2 -------- ----- ------- ----- -------- -------- ....

165      None  
 205     Trans Share   Exclu   262161 45045
 434     Trans Share   Exclu   262161 45045
    66   Trans Exclusi Exclu  1114185 46270
 441     Trans Share   Exclu   262161 45045
 226     Trans Exclusi Exclu   262161 45045

....

Thanks,
Boris Dali.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Feb 09 2004 - 15:05:47 CST