Re: Locking Question (nowait, UPDATE etc)

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 02 Apr 2003 10:19:58 +0200
Message-ID: <3E8A9D2E.3040502@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 transaction  which as a result gets you read consistency.

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).

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 - 02:19:58 CST