Deadlock Question

I have a program that loads records into the database. We usually have about 6 of these loading programs running all day loading about 70,000 records as they come in. I am running into a deadlock problem because they all have to update this one table among others. In the long run I will change the code to avoid the deadlock. My question is , is there a way to specify to your program to wait for the lock to be released? Hopefully this will avoid the deadlock for now.

In PL/SQL it's possible with FOR UPDATE clause in a
cursor.

F.

Assuming you do actually mean a deadlock then:

A deadlock is a specific circumstance than cannot be resolved by waiting alone. The only resolution is for the process that is causing the deadlock to be rolled back:

http://otn.oracle.com/docs/products/...cnsis.htm#6037

The only way for you to solve this problem is to make sure all locks are always aquired in a manner which prevents deadlocks ie. a row in table A must always be locked before a row in table B can be locked. That way A can never lock a record that B is trying to lock, and vice-versa.

The best way to do this is to write PL/SQL APIs to do all major load tasks. These can then do all the work in a consistent fashion.

If you have a parent-child relation between tables and you are updating the parent table's primary key, the entire child table will be locked in absence of an index on the foreign key. The same is true if you delete the parent table row.

If you are doing any of the above, you can check if you have a foreign key that is not indexed.