Oracle-L: Help with Locking Issue

From: Jay Hostetter <jhostetter_at_decommunications.com>
Date: Thu, 18 Apr 2002 08:58:27 -0800
Message-ID: <F001.0044825F.20020418085827@fatcity.com>

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). Received on Thu Apr 18 2002 - 11:58:27 CDT