Re: SQL for NOT_LOCKED?

Mark D Powell wrote:
>
> 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/

There may be benefit from using 'select for update skip locked' for multiple users trying to get 'locked' access to a common set of rows

HTH
Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"