Re: Oracle Processes Stalling Problem

In article <36B040DB.1249F205_at_tertio.com>,   Robert Pritchard <robert_pritchard_at_tertio.com> wrote:
> That is pretty much the format we use for each transaction, althouhg each
> process connects to the database when it is initially started rather than at
> each transaction.
> Each transaction commits after it is complete.
>
> There are no errors occuring that I can find (and I've sent the logs from the
> stalled database to Oracle support and they have not found anything either)
> such as resource busy
>
> The process that hangs doesn't crash or report any error, it just sits there
> stuck on its select.
>
> There are no foreign keys defined on the table.
>
> Thanks
> Rob
> (feel free to email me direct if you have any suggestions, as I am totally
> clueless!).
>
> Jean-Claude Matarazzo wrote:
>
> > 1. Perhaps, you have a problem of transaction, how do you do your
> > transaction :
> > __________________________________________________________
> >
> > CONNECT user/password
> >
> > select * from YOUR_TABLE where a_condition FOR UPDATE OF a_column NOWAIT;
> >
> > IF there is an error ORA-0054 : resource busy .. your record is locked.
> > ELSE
> > update YOUR_TABLE set a_column=new_value where a_condition ;
> >
> > COMMIT;
> >
> > The first transaction begin at the connection and ended with COMMIT or
> > rollback.
> > After doing a commit or rollback, another transaction begin...
> >
> > All the errors are returned, are you sure ?
> >
> > 2. Have you foreign keys on the table you want to lock ?
> > ________________________________________
> >
> > Robert Pritchard a écrit:
> >
> > > I am currently attempting to track down a very puzzling bug in a oracle
> > > accessing system.
> > > The problem is that after a certain period of time the entire thing
> > > hangs up. This is based on Oracle 7.2.3 running on a HP 10.20 box with
> > > all the relevant recommended patches installed.
> > > The system itself comprises of a total of 44 tables which are accessed
> > > by a number of different processes (the database access is done using
> > > Pro C). The stall alwasy seems to occur on a SELECT FOR UPDATE on a
> > > particular table. The table in question is simply a counter containing
> > > three rows, which are updated by three seperate processes (and accessed
> > > but not altered by a fourth) after they have completed their relevant
> > > tasks. Has far as I can tell there are no conflicting locks, Oracle
> > > itself returns no deadlock or other error and the problem still occcurs
> > > when the NOWAIT parameter is included.
> > > This has had myself and Oracle support stumped for a while now. I have
> > > sent them all the trace logs and relevant lock table output etc so it is
> > > nothing obvious.
> > > I would appreciate any insight anyone can offer.
> > > Thanks
> > > Rob
>

Maybe instead of looking at the obvious, select for update clause, it is an OS related problem. Have you verified that your system isn't running short of swap or freemem via sar? Then what about Oracle temporary space?

You said 'everything hangs'. If this means all Oracle processes on the system then I would suspect some Os related process like part of the Oracle SGA or an Oracle process getting swaped out that has possession of a resource that the rest of the application queues up waiting for.

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Jan 29 1999 - 12:11:21 CST