Need HELP with Deadlocks

I am on 7.3.4.0 with AIX 4.2. Currently, we have a third party batch job which is logically threaded by a location and we are running several threads at once. We have been running this job for approx. 18 months with no deadlocking problems. I have checked all of the tables it is accessing to make sure that the foreign keys it has are indexed, so, that is not the problem. What we are experiencing are deadlocks, we find that the thread run fine for a short time, then, the deadlocks start to occur. I looked at the SQL each thread is running, all are trying to insert into the same table except for one, the one is trying to select from a different table. I have checked DBA_WAITERS and have found all the threads waiting. I have checked DBA_blockers and there are no rows returned. The one thread that is doing the select seems to be blocking all the others, and all the others are blocking the one that is doing the select, I have not been able to figure out why. The tables that are being inserted and selected, has the freelists set to 10 and the initrans set to 10 as well, so, I don't think it's a problem of trying to insert into the same block, but, maybe it is.

Anyone have any suggestions as to how to solve this problem? I would greatly appreciate the help.

TIA
Eric Peterson
Programmer/Analyst DBA
Maurices Inc.
eric_NOpeterson_at_mauricesSPAMMERS.inrgALLOWED.com

Note that the views I may have expressed here are solely my own and not that of my employer's. To email me directly, remove the NO SPAMMERS ALLOWED from the email address.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 27 2000 - 00:00:00 CDT