Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with Locking Issue

RE: Help with Locking Issue

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Thu, 18 Apr 2002 12:23:39 -0800
Message-ID: <F001.00448AB6.20020418122339@fatcity.com>


Jay ,
Thanks for the feedback. One to store in the memory back when you inherit projects.
Ron

>>> jhostetter_at_decommunications.com 04/18/02 02:53PM >>> I had already checked that, but since you asked, I double checked. The primary key on the FIXED_ASSET_ACTIV table has two columns - both are foreign keys from other tables. I created a separate index for the 2nd column in the PK. This fixed my problem!

So, the foreign key was indexed, it just wasn't indexed correctly.

Thanks,
Jay

>>> <Scott.Shafer_at_dcpds.cpms.osd.mil> 04/18/02 01:17PM >>> Jay, do you have any unindexed foreign keys on those tables? If so, Oracle
will take out a lock on any transaction involving the parent or child, IIRC. HTH, Scott Shafer
San Antonio, TX
210-581-6217

> -----Original Message-----
> From: Jay Hostetter [SMTP:jhostetter_at_decommunications.com]
> Sent: Thursday, April 18, 2002 11:58 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Help with Locking Issue
>
> I have been spending most of my morning trying to resolve a
locking
> issue. I think I could me missing the forest for the trees. This is
what
> happens: a user kicks off two identical jobs from two different PCs.
Each
> of these jobs is doing the same thing, but against different rows of
data
> (they are processing "work orders" in our system, but each job is
> processing a different work order).
> One session will wait until the other session completes. I am
trying to
> figure out what they are waiting on. At first I assumed a locked
record,
> but I don't think that is the case. I did quite a bit of research
on
> MetaLink. I even rebuilt the table in case INITRANS and PCTFREE
might be
> too small, but that didn't seem to help either.
> Here is the output from the query in note 1020047.6.
>
> Sess Op Sys OBJ NAME or
> ID USERNAME User ID TERMINAL TRANS_ID TY Lock Mode
Req
> Mode
> ---- -------- ---------- -------- ----------------- -- -----------
> -----------
> 12 KEN468 ken468 KEN468-1 FIXED_ASSET_ACTIV TM Row Excl
> 12 KEN468 ken468 KEN468-1 Trans-196694 TX Exclusive
> 14 KEN468 Batch BATCH FIXED_ASSET_ACTIV TM Row Excl
> 14 KEN468 Batch BATCH Trans-196694 TX --Waiting--
Share
> 14 KEN468 Batch BATCH Trans-65597 TX Exclusive
>
>
> So session 14 is waiting for a share lock. Session 12 has an
exclusive
> lock that is blocking session 14. How do I find out what session 12
has
> locked that is needed by session 14?
>
> Thanks,
> Jay
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jay Hostetter
> INET: jhostetter_at_decommunications.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
--
Author: Jay Hostetter
  INET: jhostetter_at_decommunications.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Apr 18 2002 - 15:23:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US