Re: Deadlock problem? Me too!

Andrew Zitelli (zitelli_at_tus.ssi1.com) wrote:
: jtwigge_at_attistel.co.uk (Jon Twigge) wrote:
: > We are having problems on an SMP box with multiple parallel threads of
: > execution causing apparently random deadlock messages. This is
: > happening across a number of different jobs and we are increasingly
: > convinced that they are not genuine data-related deadlocks.
: > Single-streaming fixes the problem.
 

: I have observed what could be related problems. I am running Oracle
: 7.2.2 under HP/UX 10.01 on an HP 9000/K200 with dual processors.
: I have observed problems with certain large transactions. We have
: some transactions that insert several thousand records, spread across
: roughly 300 tables. I recently ran an experiment where I performed
: an Oracle startup and then processed several of these transactions
: without any other Oracle sessions running. Periodically, the Pro*C
: program processing the transactions would go idle for several minutes.
 

: While monitoring the locks and processes through the Server Manager, I
: observed the process was waiting for a requested lock during this idle
: period. Eventually processing would resume and processing would run to
: normal completion. This problem has never been observed on our single
: processor HP/UX systems. Does anyone have any suggestions for
: resolving this problem? Thanks!
 

: -- Andy Zitelli

We are running on Hp/UX dual processor, DEC/Aplpah multi processor mode

One of the major problem that weare working on is same as you have described.
Couple of things that we did, which resolved some of our problems are,

Making sure that there were no Table level locks held by Oracle, this happens when there is no Index created on the foreign key. In fact with ensuring that all the foreign keys had indexes on them all of our deadlocks and Transactions waits dissapeared after we fixed this.

Making sure that there are enough resources allocated essp in large transactions in sense of depth (no of rows) and breadth (no of tables).

The main contention waits we observed were in the free list, enqueue waits and timeouts.

Of course we are still working on the issues but thought I would share soem of our observations with you.

-- 
****************************************
**   Prashanth The Peasant            **
**                           _        **  
**          	  ()__()    (_)       ** 
**           	  ( oo )   ,-'        ** 
**            	 /^`\%'^\ /    	      ** 
**   		(/(  . )\)            ** 
**   		  Q/--\Q              **
**                                    **
** Life's complex, It has real and    **
** imaginary parts.                   **
****************************************