Shortest method to know all fields updated by another user (in Table Buffering mode) 3

[Rajesh Karunakaran]

Dear all,

When the current user is on a particular record, what is the shortest method to identify all the fields that were updated by another user?
The table is in Optimistic Table Buffering at both ends. However, this is regarding only a single record.

Rajesh

 

[Chris Miller]

I think I understand the problem with my first test. While the second change is made based on the DBF opened up at the same time and in the same state, the REPLACE that does the second change is done after the first change is saved.

Here is a variation of my code, that indeed finds no conflict by my check and still fails to tableupdate. I then try to bring the workarea into a state enabling the TABLEUPDATE by two steps:

1. TABLEREVERT
2. redo the same changes

For step 2 I use my conflict check code to record a list of changed felds, copy their values into a partial record object loChanged, which is finally applied.

Close Tables All
Clear

Create Table Persons (id Integer Autoinc, Firstname V(32), Lastname  V(32))
Insert Into Persons (Firstname, Lastname) values ('Rajeesh','Karunkaran') && errors for demonstation purposes
Use

* Say User 1 and User 2 start their editing sessions quite at the same time
Set Multilocks On
Select 0
Use Persons Alias Persons1 && workarea of User 1. Usually just named 'Persons' on his own computer
CursorSetProp('Buffering',5) && buffering of workarea 'Persons1' set to optimisitc table buffering

Select 0
Use Persons Alias Persons2 Again && workarea of User 2
CursorSetProp('Buffering',5) && buffering of workarea 'Persons2' set to optimisitc table buffering

* User 1 and User 2 edit a tthe same time
Select Persons1
Replace Lastname With 'Karunakaran'

Select Persons2
Replace Firstname With 'Rajesh'


Select Persons1
llConflict = .F.
lcStates = GetFldState(-1)
For lnState = 2 to Len(lcStates)
    If Val(Substr(lcStates,lnState,1))>1
       ? 'Status of field '+ Field(lnState-1)+' is '+Substr(lcStates,lnState,1)
       ? 'Oldval:'+ OldVal(Field(lnState-1))
       ? 'Curval:'+ CurVal(Field(lnState-1))
       ? 'User 1 changed this field value to "'+ Evaluate(Field(lnState-1))+'"'
       * Step 2: Check difference in OLDVAL and CURVAL:
       If Not OldVal(Field(lnState-1))==CurVal(Field(lnState-1))
          llConflict = .T.
       Endif
    EndIf
EndFor

If llConflict
   ? 'A conflict is to be expected.'
EndIf
llSuccess = TableUpdate(1,.F.)
? 'User 1 lastname change success:', llSuccess
? '----------------------'

* Now the section of code of interest, how to detect possible conflicts
* Step 1: Determine changed fields
Select Persons2
llConflict = .F.
lcStates = GetFldState(-1)
For lnState = 2 to Len(lcStates)
    If Val(Substr(lcStates,lnState,1))>1
       ? 'Status of field '+ Field(lnState-1)+' is '+Substr(lcStates,lnState,1)
       ? 'Oldval:'+ OldVal(Field(lnState-1))
       ? 'Curval:'+ CurVal(Field(lnState-1))
       ? 'User 2 changed this field value to "'+ Evaluate(Field(lnState-1))+'"'
       * Step 2: Check difference in OLDVAL and CURVAL:
       If Not OldVal(Field(lnState-1))==CurVal(Field(lnState-1))
          llConflict = .T.
       Endif
    EndIf
EndFor

If llConflict
   ? 'A conflict is to be expected.'
EndIf
llSuccess = TableUpdate(1,.F.)
? 'User 2 first name change success:', llSuccess
If NOT llSuccess
   * Retry after TABLEREVERT and restoring the changes:
[highlight #FCE94F]   lcFieldlist = '' && list of changed fields
   lcStates = GetFldState(-1)
   For lnState = 2 to Len(lcStates)
      If Val(Substr(lcStates,lnState,1))>1
         lcFieldlist = lcFieldlist+','+Field(lnState-1)
      EndIf
   EndFor
   lcFieldlist = Substr(lcFieldlist,2)
   If !Empty(lcFieldlist)
      Scatter Fields &lcFieldlist Name loChanges
      TableRevert(.F.)
      Gather Name loChanges
   EndIf[/highlight]
   
   ? 'Retry of the same change:'
   llSuccess = TableUpdate(1,.F.)
   ? 'User 2 first name change success:', llSuccess
EndIf

Select 0
Use Persons Again
? 'Name in the DBF:', Persons.Firstname, Persons.Lastname

The essential part is highlighted.

PS: This is not an approach to use in general when TABLEUPDATE fails. Because the TABLEREVERT() does bring your buffer to 0, you then repeat the collected changes on the basis of the current data and succeed. It's only valid to do so if the general conflict check didn't find a conflict between your changes and the current state of the DBF.

Chriss

 

[Chris Miller]

I'm not sure now, whether this actually changes, if your backend isn't DBF but a remote database like MS SQL Server. Because towards an SQL Server TABLEUPDATE actually creates and sends the SQL I described and they should not conflict on non-colliding changes. Towards a DBF/workarea VFP could just do something like a GATHER of values and detecting the OLDVAL/CURVAL mismatch in fields sets off false alarms, perhaps?

Even though the second user saving does not touch the field that causes a conflict, the CURVAL and OLDVAL of that field differ. It should not matter to VFP, as there is no task to save a buffered change of this field. I'm not sure whether I'd call this a bug or just a bad design.

Chriss

 

[Mike Lewis]

To be honest, I haven't read these last few posts in detail (they are very long, with a lot of code to work through). I would only add that I never use GETFLDSTATE() when detecting or dealing with multi-user collisions with optimistic locking. I have always used the (much simpler) code I posted earlier.

Of course, I am prepared to accept that I am wrong about all this.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike, this is only lengthy code as I didn't yet defined functions, its quite repetetive code.

But what I have found is that after a TABLEUPDATE() returns .F., the only way that works is making use of TABLEREVERT(), if you don't want to use the force option of TABLEUPDATE().

Chriss

 

[Rajesh Karunakaran]

Dear mjcmkrsr

Yes, you are extremely close to what I think I need to do. I have already described almost the same thing in one of earlier post.

Chriss,

You have taken a lot of effort and wrote that deeply detailed program codes.
Thanks for that. I did not go through it line by line but I have taken essentials from it.

In fact, I had made a simple form with 2-3 textboxes bound to fields and some 10-12 saving lines to see what VFP says for different scenarios and I have posted my observations in one of my earlier posts. Also, I am not planning for a generalised routine but a specific one which should match our scenarios.

I think, it's a good idea to use SETFLDSTATE, after replacing the values from other user, to set the field status as 'Not Updated'.
So, I can Replace CURVAL() wherever required, then either...
use SETFLDSTATE to reset fields to 'Not Updated' status and use TABLEUPDATE without the Force parameter to save
OR
TABLEUPDATE with the Force parameter.

Let me check which is appropriate for our case.

Mike,

Yes, you are correct. GETFLDSTATE will help in identifying only own changes and not changes by others.
Anyway, my code would be a mix of GETFLDSTATE, SETFLDSTATE, CURVAL(), OLDVAL() & TABLEUPDATE().

Even though I feel like, I have enough information now to setup my code, let's keep waiting for some more valuable ideas from our experts.

Thanks a lot everyone
Rajesh

 

[TamarGranor]

I just want to add one point that I haven't seen here. If you do this checking before a save, there is always the chance that between the time you check and when you actually save, someone else will have saved. That's why it's fairly common to only do the checking after a failure.

Tamar

 

[Chris Miller]

Tamar,

I said this:

The usual thing to do is try TABLEUPDATE, if there is a conflict identify it

I didn't program that way around, but the whole code is just for demonstration purposes. It's also true that you still need to check for the success of TABLEUPDATE(). But also, if you found a conflict, it's very unlikely to go away.

Chriss

 

[Chris Miller]

Here's some refinement in the direction of your initial idea to simply determine what was changed by other users.

The idea is that you determine a list of your changes that (by current knowledge) don't conflict with any change and a list of field that do conflict:

* After a Tableupdate fails you call this:
lcNonConflictingFields = '' && list of changed fields not colliding with other changes
lcConflictingFields = '' && list of changed fields which do collide with other changes

lcStates = Getfldstate(-1)
For lnState = 2 To Len(lcStates)
   If Val(Substr(lcStates,lnState,1))>1
      If Not Oldval(Field(lnState-1))==Curval(Field(lnState-1))
         lcConflictingFields = lcConflictingFields+','+Field(lnState-1)
      Else
         lcNonConflictingFields = lcNonConflictingFields+','+Field(lnState-1)
      Endif
   Endif
Endfor
lcConflictingFields = Substr(lcConflictingFields,2)
lcNonConflictingFields = Substr(lcNonConflictingFields,2)

If !Empty(lcConflictingFields+lcNonConflictingFields)
   If !Empty(lcNonConflictingFields)
      Scatter Fields &lcNonConflictingFields Name loNonConflictingChanges
   Endif
   If !Empty(lcConflictingFields)
      Scatter Fields &lcConflictingFields Name loConflictingChanges
   Endif
   Tablerevert(.F.)
   Gather Name loNonConflictingChanges
   llSuccess = Tableupdate(1,.F.)
   If llSuccess
      * Do you also want to also force in conflicting changes?
      Gather Name loConflictingChanges
      llSuccess = Tableupdate(1,.T.)
   Endif
Endif

If the final llSucess is .F. you rinse and repeat. There might be the case Tamar warned about, that a recent update from another user strikes again. You could combine all fields, but then you actually have about the same as the old case. The forced TABLEUPDATE() done in the second step should never return .F., but you never know, in that case you would have other trouble than just a collision of changes.

If the first tableupdate of nonconflicting changes already fails again, then you should not even start to force in other changes but repeat the whole process of determining the conflicts, you'll get to the forcable changes again soon enough.

In effect, this takes in all other users changes, in teh step of the TABLEREVERT(). You don't just revert to OLDVALs, you effectivly empty the buffer from the current record and make the CURVALs the OLDVALs of your reverted buffer, that is the changes of other users now become the new starting point and even the same changes then can go through fine, as in the case one user changes lastname only, the other firstname only.

So the TABLEREVERT is the core power of all of this, but it would be no good idea to skip the first try of TABLEUPDATE and always save changes to a partial record object, revert the record and then reapply the changes. Because that means they are interpreted as done from that new state and not from the original state. And that has potential for less conflicts, likely no conflicts, even though you would want to detect the actual conflicts to have the other alternative of the forced Tableupdate: to skip some of the changes. Every querstion you ask users about which value to finally keep risks the state to change again. But in a normal even 1000 users scenario not every record is changed by multiple users all the time. So you often even have the 1-5 minutes the user needs to decide for his or the already stored change.

Chriss

 

[Rajesh Karunakaran]

Tamar,

Yes, I will be doing that way only.

Chriss,

Thank you very much for your code.
After the first Tableupdate() becomes success, there comes implementation of our Conflict business rules, isn't it?

Will make it a common function after making changes if required for suitability to our environment.
May have to add some more flexibilities as well.

Dear all,
This has been a very good discussion I feel. Came across a lot of ideas, concepts and learned of course!
Still aren't we waiting for more opinions )

Thank you everyone once again

Rajesh

 

[Chris Miller]

Will make it a common function...May have to add some more flexibilities as well.

Sure, just looking for field states different from 1 (no change) was surely not getting all the corner cases you could have to consider. It would be a good idea to "simulate" different scenarios by preparing situations of different conflicts, like changes in a record that another user already deleted. This may make it valid to recall a record and apply the changes, then. As Mike said, it all depends on the business cases, how to react, what to force in and what not. Also, interaction with the user about the problem detected might be important in some scenarios, automatic decisions are not always the best, even though time matters to not get into further conflicts.

I would estimate this is only a problem if a table is very frequently used, but even in those cases, it's questionable you have many collisions in the same record. It's true that modifications usually concentrate on the latest data, older records often are just archive material. So in the end it's not that tables with more records have less often changes of different users in the same records, in fact, their large size points out much is done with them.

Wide tables could be considered problematic to this and may point out that a 1:1 separation of fields would be a good idea. Or even a normalization process. Tables with less fields obviously also have less potential for conflicts. All in all there are certainly also many ideas to make editing the same record by different users a less likely case.

Chriss

 

[Chris Miller]

OLDVAL() tells you the value of a field at the point where you started to edit it (or, more exactly, at the point where it entered the buffer in your program).

That's really a good discussion point. From my experimenting, I have actually added to my understanding of OLDVAL. It can change from the point you first USE a table. It actually becomes fixed at the point you start to change a field. At that point, CURVAL is buffered as OLDVAL.

Chriss

 

[Rajesh Karunakaran]

Chriss,

Wide tables could be considered problematic to this and may point out that a 1:1 separation of fields would be a good idea

As I understand "1:1 separation", I think, you mean to separate those fields (simultaneous multi user editable) into another table and relate it with the main table through a unique field.

We have already done this for some purposes and I will check feasibility of doing same for my current scenario.

Rajesh

 

[Chris Miller]

Exactly that, Rajesh. It's splitting records and storing some fields in a main table and others in a secondary table. Like a 1:n related table, just allowing n to only be 1 by making the foreign key a primary key in that table, too. You could also take out optional fields into the secondary table and have a 1:0-1 relation where the secondary record is optional. And there could also be a redesign with a 1:n relation, if that makes any sense.

If, for example, followup_date is a date of some status history of a record, it could make sense to establish that status history in a separate table or even a structure of tables.
Say, for example the main table is an order table, then there are several stages an order gets through, starting with being made by the customer through being processed, being sent and finalized. And then there can be returned orders, complaints, etc. etc. and it would be worth storing data about such events in a status history.

Chriss

 

[Rajesh Karunakaran]

Dear all,

I just deleted my last post. What I had written was not correct and would have created all the confusions once again afresh!
I am not sure whoever might have seen and working on it. Please ignore!

Rajesh

 

[Chris Miller]

I got notified of a new post but didn't see one, not even the [Post Deleted].

The situation of twice a buffered table on the same computer isn't unusual, though. I guess you just thought you debugged one form/datasesion, whereas you debugged the other form/datasession and so the observation was simply wrong interpretation.

It's true that doing something in the same IDE or on the same computer in two IDE session can still differ from the situaton with two separate computers, for example, because of caching effects, but usually you can test scenarios on your development computer and not need to go as far as running a virtual machine or even a real second computer.

Chriss

 

[Rajesh Karunakaran]

Chriss,

The situation of twice a buffered table on the same computer isn't unusual, though. I guess you just thought you debugged one form/datasesion, whereas you debugged the other form/datasession and so the observation was simply wrong interpretation.

No, that was not the wrong query in my post.
I wrote, I was wondering when OLDVAL() and CURVAL() differed, why my GETFLDSTATE returned 1 instead of 2.
Then I realised, I had not changed the field value in the form and GETFLDSTATE would return 1 only.

Rajesh