We had a similar situation that ended up being a network setting issue. The server was showing blocking locks. It turned out to be that the client application was getting a network error. After the error it re-established the database connection and re-submitted the transaction. The problem on the server side was that the Oracle process had not detected the client's death and was waiting for 'SQL*Net message from client'. We changed the Windows TCP setting for the number of retry attempts to 15 (as per Metalink) and the so-called locking problem has not been seen since. I guess we could have enabled Dead Connection Detection but we decided to fix it at the source.
HTH
Tony Aponte
Home Shopping Network, Inc.
-----Original Message-----
From: Magaliff, Bill [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 20, 2002 2:19 PM
To: Multiple recipients of list ORACLE-L
Subject: db locking quandry
We have a client running OPS (no load balancing or transparent failover
enabled due to middle-tier software limitation) who is running into db
locking issues. Not sure they're related to OPS but pursuing that line of
thought.
Here's the basic scenario:
1) application (ours) access Oracle 8.1.7 via standard Net8 . . . had
been divided so that different userid's go through different OPS nodes, but
we disabled that for testing
2) multiple sessions each running lengthly transactions involving many
tables (up to 20) - each txn inserts one or several rows into each of these
20 tables and then commits at the end
3) application log files showed txn's hanging while inserting into the
n'th table in the list. Realized that for each of these tables INITRANS had
been set to 1. bumped that up on most of these tables (to either 8 or 16,
depending on how much we anticipate each table being hit) and that seemed to
get us further along in the list. But they still encounter locking. Oracle
recommended changing GC_FILES_TO_LOCK to 0, and channelling all connections
through a single node, which they did but the locking still occurs. System
state dumps and trace files show a variety of things, but rather
inconsistent - sometime waiting on a high water mark enqueue, sometimes
(today) waiting on SQLNet message from client (in this case it appears that
Oracle is waiting for the app, but the app logs indiate it's waiting for
Oracle). Our client is trying to get a sniffer to evaluate potential
network issues.
I've been reading about OPS locking issues - and they might try disabling
OPS for a day just to see if this keeps happening.
Oh yeah - and of course this is occurring in production and is not
reproducable on any other system!
Wanted to throw this out for thoughts of where to look next . . .
thanks
-bill
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
