Question Following the way of changing a value

Hi guys,

With the help of manie I know how to acces a data properly in the purpose of commit a new value.
Like this:

        bCustomer.value = new-value.
Now I would like to acces it for reading only. As of today I just write querry like this:
FIND FIRST customer WHERE customer.Id = 55 NO-LOCK NO-ERROR.

It works perfectly at the begining of a procedure. But when I did already a bit of data acquisition and other operation what is the best way to consult data when another user can changed it with an "EXCLUSIVE-LOCK".

So I need your help :)

Have a nice day

Cringer Moderator
Staff member
Don't use FIRST. That's not a solution to your problem, but a principle. In particular in your example above. You would only expect a single result as you're looking on what seems to be a unique key. In fact, if your query returns more then one result you want to know about it, and FIRST would hide that.
I'm not sure if I understand the question. Do you mean the process flow is like this:

User 1: find record and waits
User 2 do some updates
User 1: Use the record we found ages ago

Or is the process flow more like

User 1: Find a record
User 2: do some updates
User 1: find old records again and do something
Maybe more like your first case, like this:
User 1: find record and do some update
User 2: Want to access the same record as user 1 will the updating process is runing.
User 1: as finished the update process.
If you have a record in your session's buffers your COPY of it will not be impacted by another user's actions. You can continue to use that copy for as long as you would like.

If the other user changes something you will never know - unless you re-read the record at some point.

There is no notification between sessions of changes to data by other sessions. The only way to see such things is to constantly poll the database looking for changes.

At first glance that might sound terrible. Chaos is sure to ensue. Users will be confused never knowing if the data they are looking at is valid and never being sure if their changes are still changed etc.

But that is not the reality. There is no chaos and users are not confused (at least not because of this issue ;) )

The typical business application has a read to write ratio of 500:1 or more. Data actually changes very slowly. There is also a lot of "locality of reference". Data is is used, and reused, in "working sets" of logically inter-related bits and the odds are very, very low that someone else is going to change your data while you are working with it.

Keeping transaction blocks very small and tightly scoped goes a long ways towards preventing problems because it means that data changes are immediately visible to anyone that cares about them.

You could, in theory, go to a great deal of trouble writing code to try to detect and react to changes coming from other sessions. This will be a very expensive development effort and it will be hugely expensive from a performance perspective -- among other things you will have to read everything a lot more often (500:1 may become 5,000:1).

IMHO you are much better off with a little bit of error handling code and maybe some business logic to protect any parts of the application that are especially prone to conflict; perhaps you could, for instance, ensure that only one user is allowed to update an order and all child tables at a time -- but do that via application-level business logic rather than a db level record-locking scheme.

tamhas Sponsor
If it matters that you have the absolute latest value at some point in your code, then read it again.

In particular, if you are updating a record, read it no-lock, do any UI, read it again, check whether the original is different from the current, if the same, then commit your changes, if not the same, decide if the differences matter, if they don't, then merge and commit, if they do, then go back to the user for correction. There are nice features in temp-tables for managing this with little code.


19+ years progress programming and still learning.
Just chucking in my 2 cents.
Something else to consider when using an EXCLUSIVE-LOCK on a find statement is WebSpeed. By default, WebSpeed has "lockwait timeout" of only 10 seconds and then it raises a STOP condition if it's unable to get an record lock.

So, in some conditions where your code is running in a batch mode .i.e. WebSpeed, AppServer etc. you might want to consider having a NO-WAIT option in combination with testing if the record is LOCKED and handle the situation appropriately.

Use the NO-WAIT option wisely.
0) You should always use the -rereadnolock parameter. I cannot think of a use case for not using it.

1) I pretty much always code FIND ... EXCLUSIVE-LOCK NO-WAIT NO-ERROR. There are not many cases where I would want to get in a queue and wait for the lock.

2) With a FIND ... EXCLUSIVE-LOCK NO-WAIT NO-ERROR the following IF AVAILABLE needs to handle two possible scenarios: no record satisfies the WHERE clause (with FIND by RECID or ROWID perhaps the record got deleted by someone else) and the record exists, but is locked. You do not always need to care which scenario it is -- often it is adequate to simply retry the operation. Or skip it and move on.