deadlocks

Hi, could someone please help. We are using dynamic sql to update tables at our central site with data from remote sites. We also run 3 copies of a process containing the dynamic sql to update the store data into the central db. This basically allows us to do parallel updates for our stores rather than sequential updates, one store after another.

During the update, each parallel process tries to update the same table, but for a different store. The bulk of the transactions are inserts. We are told that dynamic sql, locks an entire db block during an update and we believe this to be the source of our deadlock problem.

Can anyone recommend a method around the deadlock probelm. We have discussed doing a rewrite, and due to the complexity and size of this option have ruled it out at this point. Is there something we can do with the database? Would turning off DELAYED_LOGGING_BLOCK_CLEANOUTS help us? This init param was introduced in 7.3 for parallel server. WE ARE NOT RUNNING PARALLEL SERVER OR PARALLEL QUERY. We have also experimented with reducing the number of updates between commits. This did not improve the situation, it actually made it worse.

Thank you for your help. Received on Tue Jun 02 1998 - 14:38:15 CDT