Re: row level (transactional) locking problem
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 9 Feb 2004 21:05:47 -0000
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@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
