Re: Problem with deadlocks

I indexed all my foreign keys.

20 simultaneous users and 1 batch job pumping in 1979 transactions over a 30 minute period and NO DEADLOCKS.

That was it! You are a genious!

<n.a.ekern_at_usit.uio.no> wrote in message news:94g8ge$p8m$1_at_readme.uio.no... Hi there!

You don't mention foreign keys. Do you have them? Are they indexed? If you have them and they are not indexed, then lucky you, you may have found the main problem! Put one index on each foreign key and see if that helps. This is described in the Oracle documentation, 'Application Developers Guide'.

If you don't have unindexed foreign keys then you might have to code concurrency control into your application code by setting explicit table locks, such as 'select ... for update' or 'Lock Table ... in exclusive mode' or using the dbms_lock package. I would be very anxious to do such locking, because if a session holding a lock starts hanging (or just doesn't commit) then after a short while others start hanging too, I guess you might find it to be worse than all the deadlocks.

Best regards and good luck,
Njål

> My application is being concurrently used by 11 people and over a course
 of
> 30 minutes and 800 plus transactions we experienced 84 deadlocks. The
> application is coded so that the users can retry transactions without
 losing
> any data, but they are complaining that the deadlocks occur too often.
>
> The application has 5 different types of transactions, 3 which do a mix
 of
> read/write operations and 2 which only do read operations. The database
 has
> 9 tables, 8 with primary key constraints, and 1 with an alternate key
 index.
> The table that doesn't have a primary key constraint is used for
 auditing.
>
> The deadlocks used to be more frequent, but I analyzed and changed the
> transactions to ensure that they all access tables in the same sequence
 and
> that brought the deadlock frequency count down some.
>
> What else can I do to bring the deadlock frequency count down more?
>
>
>
Received on Tue Jan 23 2001 - 10:53:57 CST