RE: "NOWAIT"

In article <355A9E53.B90463BA_at_UNIXFOR.ATH.FORTHNET.GR>,   no_at_unixfor.ath.forthnet.gr wrote:
> We got several answers in our posting. We d' like to thank you for your
> interest. Unfortunately, (probably because we
> didn't provide adequate information), we do not have an "easy and
> clear" solution.

There is no "easy and clear" solution to this problem. The big obstacle is that your users can post the inserts via INSERT sql commands from stored procedures and forms, but these are not immediately followed by a COMMIT. You said there could be a 5-minute delay before the commit. THIS IS UNACCEPTABLE!!!! If you only have one or two users, you can get away with writing applications like this. But you are working in a MANY-USER situation, and those methods DO NOT WORK.

It doesn't matter what system you are working on--even a non-database system.  You will still have this underlying problem. Your inserts must be transaction based--they must be inserted and IMMEDIATELY committed or rolled back. You can't have the 5-minute delay... even a 5-second delay is nearly unacceptable.

Write your applications so that when the inserts are posted, they are immediately committed. There is no other solution.

If a whole batch of records must be inserted as a group, then use Forms, and allow the user to enter them on the form, but do not attempt to post them to the DB before user does a commit. Then, the commit will post and commit the entire batch in a single transaction. If an insert fails on any row, your form should do a rollback, and will still have the data on the screen. You can code an appropriate action into the process to handle the insert failure.

Regards,
Steve Cosner

> The Scenario where we need the "Insert with NOWAIT" functionality is the
> following:
>
> For some reasons many users are trying to insert in a table a set o
> records and when all the records are inserted, these different sessions
> are executing a commit or rollback. It is possible that these different
> sessions are trying to insert the same records, ( The uncommitted
> records are violating the primary or unique constraints of the table).
> It is also possible that the time between an insert or post and the
> corresponding commit or rollback is more than 5 minutes.
>
> The different Users are trying to insert the records using the INSERT
> command from PL/SQL stored program units or using the POST Forms
> built-in. We have both users using Forms and users using stored PL/SQL.
>
> The versions of the Oracle products we are using are :
 <snip>
>
> We believe (and hope (:-) ) that the above scenario is not very rare. We
> are looking for a tested workaround solution, (with LOCKS and/or
> database triggers?), As far as we can understand, the heart of the
> problem resides on the fact that even though we cannot see the
> uncommitted records we are enforced by the database server to wait until
> the release of the exclusive row locks by a commit or rollback.
> Is there any nice and clean method to safely understand that another
> session has currently locked the new row we want to insert?
>
> Thank you for your help.
>
> ----------
> From: Rod Corderey[SMTP:Lane_Associates_at_compuserve.com]
> Sent: ÐáñáóêåõÞ, 8 ÌáÀïõ 1998 2:58 ìì
> To: sp_at_unixfor.ath.forthnet.gr
> Subject: Re: "NOWAIT"
>
> Hi Nicholas,
>
> it depends on how the inserts are being executed, whether by a form, and
>
> which version, or whether by a PLSQL procedure or whether by a trigger.
>
> There are different options depending on the scenario.
>
> Could you post a little more information?
>
> regards
>
> Rod Corderey
>
> Lane Associates
> Lane_Associates_at_Compuserve.com
> http://www.Lane-Associates.com
>
> >
> > Could you please help us with the follow problem.
> >
> > Table A has two keys.
> > Field pk is a primary key and uk is a unique key.
> >
> > A session inserts into A the record (pk_vall, uk_vall) successfully.
> > The session does not commit the insertion.
> > Another session does attempts to insert the same record and because
> > there is
> > the uncommitted previously inserted record from another session has to
 

> > wait until
> > the first session execute a commit or a row back releasing the
 row-lock.
> >
> > We want to change the above scenario.
> > What we want is to make each session attempting to insert a record to
 be
> > able to
> > understand that at the specific moment a row-lock for the specific row
 

> > exists and
> > abort the insertion immediately.
> >
> > We may say we need a "NOWAIT" behavior for the insert operation.
> >
> > Which is the best method we can accomplish what we need ?
> > ( We are looking for a work-around solution)
> >
> > Thank you very much
> >
> > Nicholas Economou
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri May 15 1998 - 00:00:00 CEST