Re: "NOWAIT"

Nikolas,

Sorry to be so long getting back on this.

There are a number of points and issues here.

Firstly until the rows are committed, regardless of any intermediate POST the newly inserted rows will not exist in the database as far as other sessions are concerned. The value of POST is mainly so that within the current session the rows can be retrieved by validation queries and called forms existing within the same session. A lock can only be taken out on an existing row and therefore until the actual commit, the new rows remain transparent to all other sessions. Further having inserted and committed a new row there will be no lock taken out unless a user attempts to change that row.

It is true to say that if there is a probability of users attempting to insert the same data then row by row commits are normally the best procedure, minimising the potential for contention.

It is also true to say that long transaction times for manually entered data sets should be avoided at all costs

However, there are scenarios where users need to work on a set of rows and commit the whole at the end of perhaps, as you say, 5 minutes or more.

There are a number ways around this including,

Physical organisation. Stream the data entry information so that individual users are working on discrete data sets.

Programmatic. If it is accepted that multi row entry over a long transaction time is necessary then consideration should be given to internally streaming the data by effectively changing its timebase. A method that can be employed is to add a status flag to the row identifying the row as 'In progress' or 'valid'.

During data entry row by row commit is transparently enforced with a status of 'in progress', as part of this commit the pre-insert trigger checks for existance of that row.
Commit is trapped and updates the status to valid as part of the transaction committal.

Rollback is trapped and either rollsback or deletes depending on the stage at which the data entry has reached.

For other areas of the application rows should only be seen where status = valid, nothing else can be perceived to truly exist as any 'in progress' rows are effectively in limbo.

There are down sides to this type of approach and it needs to be very carefully thought through, but often a combination of the physical and the programmatic will resolve the situation with minimum development effort.

Hope this has been helpful, if not get back in touch or contact me directly.

regards

Rod Corderey

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

Nikolas Oikonomou 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.
>
> 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
>
> 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 Fri May 22 1998 - 00:00:00 CEST