Re: Locking Question (nowait, UPDATE etc)

"Johannes Eggers" <jeggers_at_tetrix.com> wrote:
> > This just sounds completely wrong. Lets find out what are your needs.
> > I bet there is a very solid solution utilizing Oracle.
>
> It sounds awful/wrong too, but I haven't found anything else that
> supports what we're trying to do, which is the following:

The following description one step up from the prior one, but is still a technical implementation rather than a business use case description. Why do you want to do this stuff in the first place?

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

What are they marked as before the export is attempted?

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

So while evaluating the where clause is time-consuming, it is not the most time-consuming step, as that is the external factor (i.e. step 2 from below). Right?

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

What happens if step 3 fails? You probably can't rollback step 2 if it is external, so you will have exported records that aren't marked.

Is the success/fail deteremined on a record level or on a batch level?

I think this may have to be broken into separate transactions.

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

OK. Could you just mark them as "export-attempted" in step 1?

> (b) records that are in the process of being exported must not be
> modified by other sessions

Why not? Say the Exporter finishes steps 1,2, and 3; then a microsecond later another process updates a record Exporter just exported. This is allowed, right? So why not let another process update a record while Exporter is exporting it? It seems like the end results are the same, unless there is something you haven't included here.

> (c) other sessions attempting to update the records that are locked must
> time-out within a few seconds

This is the part that just seems wrong. I can understand not wanting it to wait for several minutes. But wanting it to instead just randomly fail doesn't sound any better. Does the update need to be done, or doesn't it? If it doesn't, then why are you doing it? If it does, then frequent, random failure is not a viable option, is it?

> To achieve (a) we could either mark the records using a market column, or
> we could have the java-client keep track of the rowids of the records and
> pass those to the stored procedure that updates the records as
> export-success/export-failed
>
> To achieve (b) we need to lock the records. Since we need to lock the
> records to achieve (b) we may as well use a marker-column

I don't think I understood that. Is there a 'not' missing from there?

Anyway, to achieve (b) you don't need to lock the records if you have a marker column and the other updates inspect this column.

> To achieve (c) we need to have these sessions time-out (nowait behavior)
> when doing udates.
>
> (a) and (b) aren't the problem, but (c) is:

But (c) is a problem due to (a) and especially (b).

> Whenever a session attempts
> to UPDATE or DELETE records that are locked by another session using, the
> session trying to update/delete will wait until the 1st (locking) session
> commits or rolls back. The only way for the updating/deleting session to
> detect that it will lock is to run a SELECT FOR UPDATE NOWAIT prior to
> performing the update/delete. This implies that it has to run the entire
> (time-consuming) where-clause as part of the SELECT FOR UPDATE only to
> subsequently delete/update those records (which requires the same complex
> where-clause to be executed again).

But it doesn't. The select for update can return the rowids when it does the time consuming where clause, then the update or delete can use these rowid to rapidly do their thing, no need to re-execute the expensive where clause.

Xho

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