Re: Microsoft Txn Server / Locking issues

For the session that is hanging, can you query all of the enqueues for that SID from V$LOCK?

The rows with type = 'TM' are "DML locks" against any tables or indexes which are being modified by the session. For "TM" locks, the ID1 column in V$LOCK contains the "DATA_OBJECT_ID" for the object being locked. You can translate it by querying DBA_OBJECTS by DATA_OBJECT_ID. There is no index on that column, and OBJECT_ID is often the same as DATA_OBJECT_ID, so you can try querying on OBJECT_ID as well. The LMODE column should indicate the mode being held. I don't have the translations for LMODE on the tip of my tongue, but there is a script named TFSCLOCK.SQL available from MetaLink and I have a script named "locks.sql" on my website (www.evdbt.com/tools.htm) that is similar...

Anyway, that'll tell you what objects are being modified...

Another thing you can look at is which of the objects locked by the session in question are being waited for by other sessions. In this case, look for all rows in V$LOCK where TYPE='TM' and ID1 is the same as the ID1 of the session you've been looking at. Make sure to display the LMODE (which should be "0" if waiting) and the REQUEST column values (which should be non-zero)...

Once you know what object is being locked and once you confirm that all of these other sessions are "waiting" for it, then the real fun starts...

Querying V$SQL or V$SQLAREA where "UPPER(SQL_TEXT) LIKE '%<object-name>%' AND COMMAND_TYPE IN (3,5,9)", where 3=INSERT, 5=UPDATE, and 9=DELETE. I'm pretty sure those are the SQL command types; that's how they're documented in the OCI reference, at least...

Anyway, the idea is to look for INSERT, UPDATE, or DELETE statements against the objects that your guilty session is locking. The current statement might be a harmless SELECT, but sometime in the past there was an INSERT, UPDATE, or DELETE which initiated the lock. Come to think of it, don't forget SELECT ... FOR UPDATE either, but I don't know the COMMAND_TYPE for that...

Hope this helps. Sounds like some real sleuthing...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, July 31, 2002 4:41 PM

> Anybody have any experience with Microsoft Txn Server? I am running into
> issues with a few Windows2000 servers running Microsoft Txn Servers that
are
> connecting to my 8.1.7.4 HP-UX database. There are several concurrent
txns
> running that do a lot of inserting and data loading. Most of the time it
> runs okay, but 3 or 4 times a day, I get several txns stuck waiting on
> enqueues. I can easily see what session is holding up all the others, and
> it always is an apparent idle session (wait event = SQL*Net waiting from
> client"), that is coming from one of these windows2000 servers. Once I
kill
> that session, all the other enqueues go away. I have taken systemstate
> dumps and have been working with Oracle Support, but they are starting to
> say that it is an App issue - and it may be, but I just can't see how an
> idle session can be causing 30+ sessions to wait for it. And most times,
> the last statement run in this idle session is a SELECT.
>
> I have also increased my initrans for the tables and indexes and made sure
> the FKs are indexed.
>
> Any ideas?
>
> TIA
>
>
>
>
> John Fedock
> "K" Line America, Inc.
> www.kline.com
> * john.fedock_at_kline.com
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Fedock, John (KAM.RHQ)
> INET: John.Fedock_at_KLINE.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: Tim Gorman
  INET: Tim_at_SageLogix.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).