RE: "NOWAIT"
Date: 1998/05/14
Message-ID: <355A9E53.B90463BA_at_UNIXFOR.ATH.FORTHNET.GR>#1/1
[Quoted] [Quoted] 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.
[Quoted] 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 :
Forms 4.5 Version 4.5.7.0.12
Oracle7 Server Release 7.3.2.2.0 - Production Release
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.2.2.0 - Production
PL/SQL Version 1.2.1.4.0 (Production)
Oracle Procedure Builder V1.5.6.12.1 - Production
[Quoted] 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
Received on Thu May 14 1998 - 00:00:00 CEST