Frequent update of a buffered table from disc copy 2

[Rajesh Karunakaran]

Hi,

I have a form which updates a table. The form uses buffered copy. Multiple records are being edited simultaneously and it's multi user. Any user can partially edit a record and switch to another before finalizing any of the record they're updating.

Now, if the user is switching to another record, I am saving the current record (yes, using TABLEUPDATE()) before moving onto the selected one. Here, when the chosen record is shown, I want the form to be refreshed with the current values in the underlying table (assuming that another user might already have made some changes).

Is there any straight method for this?

Thanks in advance,
Rajesh

 

[Mike Lewis]

In general, CURVAL() gives you the actual valueof a given field on disk. But in this case, since you want to refresh the entire form, a simple THISFORM.Refresh should do the job, assuming that the other user has already committed his changes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Hi Mike, hope you keep fine. It's been some days...

Yes, I am already doing a refresh whenever a record is shown.
Let me check that. I was in the impression that it's not getting refreshed.

Thanks.
Rajesh

 

[Rajesh Karunakaran]

Mike,

I think, I asked a completely idiotic question. You can beat me if you have a stick :-((
The refresh is already happening. But, that obviously, will happen if the changes are committed.

I feel like I should plug in a feature to save the record automatically from time to time (say using a timer or so). Anyway, I have a feature to save partially (time to time) but that is manually triggered at present.

Thank you for your time!

Rajesh

 

[Mike Lewis]

Rajesh,

Actually, it was me who misunderstood your question. For some reason, I assumed that you did a TABLEREVERT() before refreshing the form. If, on the contrary, you had done a TABLEUPDATE(), then the refresh wouldn't have retrieved the other user's edits. By the time you did the refresh, your edits would have been saved, so the refresh would only have read back those same edits.

This makes me wonder exactly what you want to achieve. If the form is open for editing, and the user has already made some edits, do you really want those edits to suddenly disappear, to be replaced by some data that the user has not seen before and which is not relevant to the work they are actually doing?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Why shouldn't you want to see the latest data before you edit it?

When you have a grid of data opened this morning and it's past lunch by now, before you edit anything you'd want to see the changes made by others. And that's not only an issue with data displayed for long without any refresh. If a table is edited very frequently you'd want to have the latest data.

I think of the way .NET has observable collections and propertychange events/eventhandling.

If you just browse a table you can have the effect you only see the latest data when you click on a cell, which is just in time, but wouldn't it be fancy if any change automatically propagetes to other clients right away? Provided only a few other clients actually display the same data that is updated by a save from another client?

With browse you can set a refresh interval, that doesn't work with grids, if I remember correctly. Actually you don't want to refresh ever so often when nothing happens and I usually have refresh set to 0, to never autorefresh. And the next problem is that you can't refresh a view with buffered changes.

Mike mentioned curval already. So you might go on a limb here and actually grab all fields CURVAL to see if you could do what views lack and at least update all not yet touched fields with CURVAL data. If you therefore REPLACE the CURVAL in, you just make it hard to yourself, as the field then is coming into the buffer of yet unsaved data while it is just the CURVAL of the field. So you'd alos need to set fldstates to unchanged to not cause a conflict where there isn't one.

The only easy situation with single record is nothing of it is yet buffered, then you could refresh just this record before you edit. And then the edit would start from the data on disk or in the remote database server.



Chriss

 

[Mike Lewis]

I'm still a bit confused about what's going on here.

Rajesh, are you saying that your user has the data on the screen for a long period, and that the data can change during that time (because other users are editing it), and you want to be sure that your user sees an up-to-date version?

If so, and assuming that your user is not himself editing the data, then a timer would be a good solution. Just set it for a suitable interval, and refresh the form when that interval occurs.

But the fact that you mentioned the data is buffered suggests that your user is in fact editing the data. This is more difficult. You want the user to edit the most up-do-date version, but you don't want the data to suddenly change while the user is in the middle of editing it. (That is pretty well guaranteed to upset the user.)

In that case, you have two broad choices.

You can use a pessimistic buffer mode. This will lock out one user while another user is editing the data. You would need to trap error code 109, give the user a friendly message to tell them what is going on, and then issue a RETRY.

Or you can use an optimistic buffer mode. In that case, with table buffering, you should call TABLEUPDATE() WITH .F. as the second parameter. If the function returns .F., you can use AERROR() to detect the error. If this indicates error 1585, it means that another user has edited the data since you read it into your buffer. You then have to decide how to handle that.

You might decide that, in those circumstances, you want to tell your what has changed and let them decide how to handle it. One way to do that is to loop through all the fields in the table (for example, using FCOUNT() and FIELD()), and check each field's CURVAL() against its OLDVAL(). If they are different, it means that field has changed. You could then either overwrite the change (call TABLEUPDATE() again, with .T. as the second parameter), or update the field as it exists in your buffer (REPLACE <fieldname> with CURVAL("<fieldname").

The above is an over-simplified explanation. It's difficult to give more details without knowing more about the situation, and I don't want to make it sound more confusing than it already is.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I would put it a bit simpler:

You can refresh any records from disc, that are currently not having any buffered changes. Which would be the case for most data, if a user simply keeps a form open for long. I also thought of frequent changes to a table. I only mentioned the case of a form opened for a long time as it's the extreme case of how outdated that data could have become.

When two users edit data frequently, I agree, you wouldn't want to have the value you just edited be overwritten by a refresh, and that's what VFP also prevents with its policy to not allow refreshes until buffers are emptied either by saving changes with TABLEUPDATE or by TABLEREVERT. But in some situations that might actually be wanted, for the fields you didn't yet edited. So if you edit them, you begin on the current value and then saving that causes no conflict.

There is no good merge mode, say user A is more responsible for the left 3 fields and user B for the right 3 fields and there is are just a few overlaps, the edits of the other user could appear quite immediately. I lack a good example, I thought of table reservations in a restaurant or room reservations in a hotel, but both don't change that frequently.

I haven't tried, but is it possible to let TABLEUPDATE not report a conflict if user A edits field 1, user B edits field 6 and each user just causes the update of the edited field? That's clearly not possible when you set updatetpye to keyfield and updatable fields, but I don't know if using the updatetype "keyfields only" means the changes are actualy merged into each other. I think TABLEUPDATE prepares UPDATE-SQL that sets all updatable fields when they are changed, at least it is possible, as the buffered state is precise to the field, it's set/getfldstate, not set/getrecordstate.

I personally design my data in a way there is less concurrent editing of same records. It helps to have group data organizational, into campaigns, projects. So prevent situations where you actually want and need to merge changes of multiple users.





Chriss

 

[Rajesh Karunakaran]

Hi all,

Sorry for coming back here after a delay.
I think, I should describe the scenario a bit more clearer.

1. The process is kind of consultations. There are multiple consultants in the office who will serve multiple customers simultaneously.

2. The reception will enter the arrival of a customer.

3. All consultants can see all customers who are in the "WAITING" list. Any of the consultant can select
any of such customers (in waiting list) for start of consultation.

4. Once the consultation started, the customer enters into "BEING CONSULTED" list. This list also is available to all consultants (their screen will show both "WAITING" list and "BEING CONSULTED" list.

5. Basically, after finishing a stage in the process, the customer can approach any of the consultants
who is free at that time and then the consultant will choose that customer's data and starts editing.

6. Now, if a consultant is sitting with a customer, there may be many entries/editing in his consultation data. However, if the consultant wants to choose another customer, he has to save the current record first.
(I am doing TABLEUPDATE() here. So the current data will be updated in disc)

7. In this way, all consultants may be working on multiple customer's consultation data.

***********************************************************
However, no more than one consultant should be able to
work on a particular customer data at the same time.
I am not sure if I have implemented this control properly.
***********************************************************

8. Now, suppose the consultant finishes customer A and calls customer B in his cabin (for B's next stage in the process), here the data of B in consultant's screen is in buffered state. However, by this time, another consultant might have made some editing in B's data.

So, whenever a consultant switches to a different customer's data (which is actually being edited by multiple consultants), I need to show the latest data, i.e., the data of the last editing happened on that customer (here we do not know which consultant might have made the changes or may be there was not any changes made at all)

9. Then, once the process is actually finished, the last consultant sitting with the customer will flag it as "Completed". The the entry of that customer will disappear from the "BEING CONSULTED" list.

This is the process.
I hope I have explained the scenario good enough.

So now, suppose a consultant is working on customer A and he is trying to switch to customer B. Here, I am compelling the consultant to save A data before he can switch to B. So, when another consultant switches to A, I do a REFRESH and the latest data is shown in my controls. It seems, it works. However, our friends here may please advice if this is fool-proof, the proper way etc etc... to avoid any kind of data conflict.

Thank you in advance for your valuable time.
Rajesh

 

[Chris Miller]

Well, that's a good case for an RLOCK() on the head record of a customer. The changes that were possible can be fetched after you have a lock on the record.

To not need to lock any other detail records the application should always try to get a lock for the head record before changing anything else.

So, once you get the head record lock you can refresh all data about the customer. By the rule of not editing anything of the customer before you have a lock there shouldn't be any buffered changes that hinder you to refresh the data. And by another rule to only unlock the head record when all buffered changes are saved, you also don't need to think about the risk of any uncommitted changes.

Chriss

 

[Rajesh Karunakaran]

Hi Mike,

I had not answered your queries. Basically, not more than 1 user will be updating the same record at same time, or rather, they're not supposed or allowed to do that. If that happens or allowed, yeah, that's really complex. Apart from that, I have explained the scenario in my previous post.

Hi Chris,

Yes, I need to introduce a current record locking feature, either by RLOCK or by having a field to denote 'In-Use' status or something similar. Let me check.

I shall come back here to update if I make some enhancements

Thank you all,
Rajesh

 

[Chris Miller]

The thing about an in-use status is, you won't be able to detect which of two clients set it, if they do so concurrently. It's a rare case, but the locking mechanism is giving you the confidence you have the lock, if you manage to get it, or someone else has the lock and your software could perhaps tell who, when you store that somewhere.

People have mixed experience with locking, but one thing is for sure: pessimistic buffering will only lock when you edit, so your edit causes a lock, that's nice from the perspective of doing things just-in-time, but with VFPs file protocol problems I prefer manually locking.

Chriss

 

[Mike Lewis]

Rajesh,

Thanks for explaining your situation.

As I mentioned above, you can choose between pessimistic and optimistic buffering. Based on what you have now said, it looks like you need pessimistic. This is the simplest way to avoid multi-issue conflicts. It is easy to program, but has the disadvantage that you risk locking one user out while another is editing.

With optimistic, on the other hand, you have to decide how you want to handle multi-user conflicts. There is no one-size-fits-all solution to that. Depending on the circumstances, you might want to merge the updates (where different users are updating different fields), combine the updates (where both users are altering the same numeric value); refuse to accept one or other of the updates; or give the user to choice of which updates to accept (this is usually the least satisfactory option).

With pessimistic, you can choose either to manually lock the records being updated (as described by Chris, above), or let VFP do it automatically.

With automatic locking, you set the buffer mode to one of the two pessimistic modes. When a user starts to edit the record, the lock will be applied. When you either commit or revert the record, the lock is released. If another user tries to edit the record while it is locked, error 109 is generated. Your error-handler should trap that error, then give the user a friendly message ("Someone else is editing this data. Please try again later."), then issue RETRY. Your form also needs a way of exiting from edit mode (for example, with a Close or Cancel button) in case the user doesn't want to hang around.

The above method is one that I have often used in my applications. It is simple and effective. But it does suffer from the "gone to lunch" syndrome, that is, if a user leaves his desk while in the middle of editing, all other users will be locked out of editing that record (they will still be able to read it). For that reason, it might be a good idea to add a timer which would automatically revert the offending user's edits (after a suitable warning). Personally, I have never found it necessary to do that, it might be necessary in some cases.

I hope this will help you to decide how best to proceed.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Dear Chris & Mike,

Thank you very much for your advices.
Let me see which way would be ideal and appropriate for data integrity.

Rajesh

 

[Chris Miller]

Just by the way: When TABLEUPDATE detects conflicts, that's not threatening the integrity of the data. It just sees some of the OLDVAL() values have changed, especially with where type set to key field and updatable fields, which means the where clause tries to find the record with the same key (completely sufficient for updating the correct record) but also a record where the updatable fields all match to the OLDVAL() they had when you read the data in. So that where type finds a record only if no other change was made to it in the meantime. It doesn't point out a corruption of the DBF file.

You don't mean table corruption problems, maybe, but I think this should help to better understand what conflicts mean vs things like the "not a table" error.

Chriss