RE: Help with Locking Issue

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).