Re: SQL for NOT_LOCKED?

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

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 26 2001 - 14:25:19 CST