Re: Locking Question (nowait, UPDATE etc)

Holger Baer <holger.baer_at_science-computing.de> wrote in message news:<3E8A9D2E.3040502_at_science-computing.de>...
> Johannes Eggers wrote:
> > "Holger Baer" <holger.baer_at_science-computing.de> wrote in message
> > news:3E847E99.7090906_at_science-computing.de...
> >
> >
> >>Either I'm missing a point here, or you've never heard of read
> >
> > consistency.
> >
> >>Why (b)? After the export is finished, you don't care if someone modifies
> >
> > your
> >
> >>records, so why care while you're exporting? Or has the exported-success
> >
> > flag
> >
> >>any meaning to other sessions that they won't modify them afterwards?
> >
> >
> > After data is exported, any modifications mark the record as not-exported
> > (i.e. in need of being exported again). For that reason I can not have any
> > interference while the record is being exported: For the purpose of
> > maintaining an accurate export-state, the export must be atomic with respect
> > to any other modifications.
> >
> > This can not be a unique requirement. The only thing here that is maybe a
> > bit different is that the operation that is required to be atomic can take
> > several minutes, and that I don't cant have the front-end locked up for that
> > period of time if it happened to want to save data for the rows being
> > exported.
> >
> > On the other hand, it can't be that esoteric after all, otherwise Oracle
> > wouldn't have put in the NOWAIT option for SELECT FOR UPDATE or, with 9i,
> > the WAIT xxx option for SELECT FOR UPDATE.
> >
> > JEE
> >
>
> I still don't get the point. But I believe that the reason why you use the
> select for update is just because you found no other way to start a trans-
> action which as a result gets you read consistency.
>

Wrong. If you have a cursor loop and want to update rows of the cursor tables inside the cursor, you HAVE TO make cursor statement FOR UPDATE, and use WHERE CURRENT OF clause in update statements. Otherwise you'll get ORA-1555. Of course, you can write pure SQL update statements, but not everybody is good at it.

> Have you tried to set the isolation level to serializable? (I don't have
> the semantics at hand, sorry). If I'm not misled by my memory, just setting
> the transaction level to read only (not applicable in your case) or serializable
> will implicitly start a transaction even with a select statement (until you
> commit of course).
>

Wrong again. Read only transaction doesn't allow updates. Serializable doesn't start at select. Even though you can trigger it with some fake update.

> A transaction is by definition atomic: it's all or nothing. So there shouldn't
> be a difference between your application starting a transaction in advance
> to the select statement without an FOR UPDATE and the way you're doing it
> now. (With the exception of course that the for update blocks your users ;-))
>
> Since it is allowed that others do change the records later on, you don't
> have to worry about them while you're exporting, just make sure, what your
> application sees is consistent over time, i.e. use transactions properly.
>
>
> HTH
>
> Holger
Received on Wed Apr 02 2003 - 13:11:29 CST