Update Conflict Resolution in Visual FoxPro

Jim Booth

What does buffering give us?

In the olden days of FoxPro 2.x we used to use indirect editing, that is editing memvars rather than the fields directly.  Why did we do this?  Because this gave us control over when and if the users work would be saved.  The word was that editing the fields directly would make immediate changes to the file that we could not reverse easily.

Was this true?  Well if it was then typing in a GET would cause the disk light to flash as the disk was written to and this did not happen.  FoxPro was using a memory buffer to hold the file’s data and was writing it to disk at a later time.  The problem was that we had no control over when and if that buffer would be written to disk.

Data buffering in VFP gives us that control.  Data buffering also allows us to react to and handle the problems that can occur in multi-user environments.

Buffering Modes

There are four buffering modes in VFP.  These modes are divide into Row or Table and Pessimistic and Optimistic.

Row versus Table

Row buffering allows a single row of data to be “dirty” at any time.  The word dirty means that there is data in the buffer that has pending changes that have not been either committed or discarded.  Table buffering allows multiple dirty records in the buffer.

Row buffering has the “magic” side effect that if there are changes and anything moves the record pointer VFP will attempt to do a commit of the buffer to disk.  This can cause unwanted updates to occur.

Pessimistic versus Optimistic Locking Strategies

The other aspect of buffering modes is the locking strategy.  The two strategies are pessimistic and optimistic.  Pessimistic locking secures a lock on a record immediately as an edit of that record begins.  This lock is held until the commit or discard is done.

Optimistic locking does not hold a lock during an edit, rather when an attempt is made to commit an edit the record is examined to see if it has changed since the edit began and if it has the commit is frustrated.


A transaction in VFP is a mechanism for wrapping multiple update operations into a single “all or none” operation.  These multiple operations may be updates to multiple tables or even multiple records within a single table.  Transactions can be nested inside each other up to 5 levels deep.

What, exactly, does a transaction do?  When VFP enters a transaction it begins keeping track of all data update operations.  It secures all locks required but does not actually do the update until the END TRANSACTION command is encountered.  Hitting a ROLLBACK command will discard all of the updates and release the locks, but it does not clean the buffers.  You must issue a TableRevert() to do that.

Enter the Update Conflict

The function we use to commit buffered data to disk is TableUpdate().  This function will write the buffered data to disk (of course a transaction will hold the actual writing to disk until the transaction is completed).

The TableUpdate() returns a value that indicates whether the update was successful.  Why might the update fail?  There are many reasons that an update might fail, the server is down, the network cable is broken, but the most common one is that there is an update conflict.  This means that the record on disk does not match the record that the buffer started with indicating that someone else has changed the record while it was being edited.  VFP’s conflict detection is at the record level.

So what happens when the update conflict occurs

You have some choices; you can discard the user’s work for them and have them start over again with the new data from disk; you can leave the record as it currently is in the buffer and let the user choose to discard their own work by clicking the Revert button; or you could bring the level of detection down to the field.

Now for the code that demonstrates the field level detection.

This code makes use of three methods of referring to the data values;


This function tells us what value a field had in our buffer before we edited it.


This function tells us what value a field has on disk now.


This reference tells us what value a field has in our buffer now.

With these three references we can find out if there are any fields that have changed on disk and that we have also changed.  If no field are in this category then we can force the update to succeed, otherwise we have to fail the update.

Below is listed code from a Resolve method of a form class that generically applies this approach.

LPARAMETERS pcAlias, pcDatabase

* Define constants for the arrays

#DEFINE BUFFERNEW laFields( lnCnt, 2 )

#DEFINE TABLENEW laFields( lnCnt, 3 )

LOCAL llRet, laFields(1), lnCnt, lnNext, llGotOne, llView, lcUser, ;


DIMENSION laFields(1)

llRet = .T.

llView = ( CursorGetProp( "SOURCETYPE", pcAlias ) = 1 )

IF llView

      * Fail the resoution for updateable views

      RETURN .F.


SET DATABASE TO (pcDatabase)

* Attempts to resolve a failed TableUpdate(). Compares OldVal() and Buffer values to find the

* changed values, then checks CurVal() to see if these are among the fields that are different

* in the file.  If the changed fields are not among the different file values, the buffer

* is updated from the table and the TableUpdate() is forced.  Otherwise the TableUpdate is allowed

* to fail and the SaveChanges will also fail.

* Select the work area being resolved

SELECT (pcAlias)

* Build an array of fields names


* Get the first modified record

lnNext = GETNEXTMODIFIED(0,pcAlias)

* As long as we have a modified record and we have not failed

DO WHILE lnNext <> 0 AND llRet

      * Set for no conflicts

      llGotOne = .F.

      * Move to the modified record

      GOTO lnNext

      * Check buffer against OLDVAL() to build a list of changed fields

      * result of comparison is stored in column 2 of laFields and

      * check changed fields against CURVAL() to find any conflicts

      * Store conflict detection result in column 3 of laFields

      FOR lnCnt = 1 TO ALEN(laFields,1)

            BUFFERNEW = OLDVAL( laFields( lnCnt, 1 ) ) <> ;


            TABLENEW = OLDVAL( laFields( lnCnt, 1 ) ) <> ;

                       CURVAL( laFields( lnCnt, 1 ) )


               EVALUATE(pcAlias+"."+laFields(lnCnt,1)) <> ;

               CURVAL( laFields( lnCnt, 1 ) )

* If both the buffer and the curval() are new

* from the oldval()

                  * and they are not the same value

                  * Set conflict flag

                  llGotOne = .T.



      * If any conflicts

      IF llGotOne

            * Set to fail

            llRet = .F.

            * LOOP back to exit DO WHILE



* There are no field collisions so we will fix the buffere

* field values to match the disk image for the unedited

* fields

            FOR lnCnt = 1 TO ALEN(laFields,1)

                  IF TABLENEW

                     REPLACE (pcAlias+"."+laFields(lnCnt,1)) WITH ;

                             CURVAL( pcAlias+"."+laFields( lnCnt, 1 ) )





      * Force TableUpdate()

      IF NOT TableUpdate( .F., .T., pcAlias )

            llRet = .F.




      * Get the next modified record

      lnNext = GETNEXTMODIFIED(lnNext,pcAlias)