Re: deadlocks

There should be a deadlock trace file in the directory where your users traces get created. It will show you the object and rowids involved in the deadlock, as well as the Oracle session id of each process. You also should see the chunk of code or sql statement that caused the deadlock.

If you see an indication that no rows were involved in the deadlock (row: none), then the problem is probably having INITRANS set too low on a table and then having the block packed too tightly (PCTFREE too low, PCTUSED too high). This can happen when you have varchar2 data columns that grow with updates. Rows get squeezed too tightly, then no additional itl entries can get dynamically created to accomodate additional row locks in that block. It becomes effectively a block lock. Then, you have two processes that deadlock cooincidentally on separate blocks, randomly chosen.

Normally, a deadlock is simply weak application design. Check the rows and the sql statement and have the two competing processes lock tables IN THE SAME ORDER. As a last resort, expand you error handling and sql to include the NO WAIT option, then have an exception handler for that situation.

Hope this helps.

Roger Snowden

rsnowden_at_us.oracle.com

G.Clark wrote:
>
> 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 Sat Jun 06 1998 - 23:51:54 CDT