Re: Locking Question (nowait, UPDATE etc)

Johannes Eggers wrote:
>
> We have data in tables, some of which we need to export (through a java
> client). When the export is completed we need to mark the records in the
> table as export-success or, if the export fails, we need to mark the records
> as export-failed. The export may take several minutes. The where-clause
> that determines what data is exported is reasonably time-consuming. The
> length of time of the export is arbitrary and dependent on external factors.
>
> The process works as follows:
>
> 1.) Java client retrieves records to export (using stored proc call that
> returns refcursor)
> 2.) Java client exports records
> 3.) Java client calls stored proc to mark records as exported-success or
> exported-failed (in same transaction as 1)
>
>
> It is imperative that
> (a) when we mark records as exported-success or exported-failed then we only
> mark those records that we tried to export, i.e. the records retrieved in
> (1)
> (b) records that are in the process of being exported must not be modified
> by other sessions
> (c) other sessions attempting to update the records that are locked must
> time-out within a few seconds

[...]

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?

If not, then you just don't need to lock those rows. The flag export-success will be set row by row, (or even with a bulk update if possible), so this won't interfere with anything else other sessions might be modifying.

So I really don't understand why you want to lock those rows, just don't do it. Or look into your execution paths and try if you can improve the selects....

Regards,

Holger Received on Fri Mar 28 2003 - 10:55:53 CST