Re: Oracle Processes Stalling Problem

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
Received on Thu Jan 28 1999 - 04:50:03 CST