Re: Locking Question (nowait, UPDATE etc)

Holger Baer <holger.baer_at_science-computing.de> wrote:
> Johannes Eggers wrote:
> >
> > 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.
>
> 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.
>
> 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.

Except for the external component. 1) read the database. 2) do something external to the database with that data 3) update the rows read in 1) to mark them as successful.

If 3 gets thrown an serialization error, it can't necessarily undo 2.

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

Except that using one big transaction without doing locking up front means it will be thrown a serialization error >99% of the time (if I'm understanding the usage pattern he is talking about correctly).

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB