Re: SQL for NOT_LOCKED?

In article <94smf3$brf$1_at_nnrp1.deja.com>,   mikedba_at_my-deja.com wrote:
> Mark:
>
> > Your ability to limit the cursor really depends on how the records
> > will be processed, and if when they are updated that they drop out of
> > the driving cursor result set so that if the cursor is re-opened the
> > updated records will not be picked up again.
>
> Yes, this sounds like what I had in mind. In other words, since there
> are just a handful of users, and the number of queries per hour will be
> low because of the time it takes the user to enter the missing
> information, I figured I'd just requery each time a user needs another
> record to edit. In this way, the only records that could appear in the
> result set are ones that have not been changed, but that might be in
> the process of being changed by another user. As long as my result set
> is larger than the number of users, I should be able to walk the result
> set looking for a record I can lock.... and always find one that I can
> lock.
>
> If this were some kind of "machine-driven" process that sent thousands
> of queries per minute, then this scheme would be too slow. However,
> from what you've said, it looks like this brute-force approach should
> work for the kind of data-entry process we're doing.
>
> If I'm re-querying for each "next" request, it sounds like I don't need
> to worry about "snapshot too old".
>
> > if the snapshot too old occurs you may need to distribute your users
> > to different parts of the table.
>
> We've considered this. If the brute-force approach works and doesn't
> overload the system, then we won't have to worry about trying this.
>
> > I should mention that the select for update should also check that
> > the row being selected still meets the driving query criteria.
>
> If I'm understanding you correctly, this too will be resolved by just
> re-querying.
>
> So even if my somewhat crude approach works for this case, I'll keep
> all of your points in mind for other apps in the future.
>
> If I'm still missing your point, please beat me over the head with it,
> if you have the time.
>
> mIKE
>

Mike, I am not sure you should re-query every time just to advance to the next record. Opening the driving cursor is probably the most expensive part of this process so by grabbing a set of records (size based on how long it takes to process a row) and advancing through them you save the expense of requerying. No one else will be able to access the rows until they are commited, but the nowait will prevent the other users from being lockwaited so they can continue on to unprocessed rows.

The part about checking that the row still meets the query condition is to prevent user B from updating a row that user A has already updated and commited. The driving cursor will have the row data as it appeared when it was selected meaning several fields will be null. This means you should only be displaying one row at a time; the row returned by the select for update. If the missing fields are no longer null then you want to skip displaying the row and advance to the next one where the fields are sill missing data.

If you need to display multiple rows at once then you need to modify the approach I put forward.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/