Re: multiple transactions in table

On Fri, 23 Feb 2001 11:38:52 -0500, "Chirag (Mark) Patel" <patelc_at_mcss.cas.mcmaster.ca> wrote:

>I am using Oracle 8.0 and Powerbuilder 6.5 to develop the database
>application. It seems that when multiple users log in to the application,
>the transactions (add,delete, update) seem to be occuring only for one
>user and for the rest of them, the screen kind of freezes and the
>application no longer responds.
>
>I am not in charge of Oracle, but my belief is that the DBA needs to set
>some roles or provileges in order for multiple users to get access to
>tables at the same time. So far, we "granted" previleges to the necessary
>users and the DBA says he has given the previlges to users for certain
>roles like insert, update, modify, etc. I tested it out yesterday and
>it worked fine, but this morning, the freezing issue came up. Is there
>anything else the DBA should set? I am not sure why it's freezing,
>probably because more than 2 or more people are running the program. Any
>suggestions?

Of course, developers never err, and it is always the DBA who is at fault. However, as usual, in this case it looks like an unjustified complained.
Your application generates locking issues, and most likely it is simply raising a table level lock too often. The only one who can resolve this is ... guess what? The developer. One common source of problems is simply firing updates without either a) issue a select for update before the actual update. Select for update results in a row level lock
b) issue an explict lock table in row share mode

If you don't follow either strategy, any update will result in a table level lock. This is causing your so-called 'freezing' So, if you should get in touch with your DBA, ask him whether he has locking issues lately. If he does, he will no doubt pin-point your application as the source of the problem.

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Feb 23 2001 - 11:52:24 CST