Buffering and Locking in Visual FoxPro - an Overview

Where do we start?

It seems that working with data buffering in Visual FoxPro causes a lot of confusion. I believe that this is largely due to the rather confusing implementation of buffering, and the somewhat odd use of nomenclature (by conventional standards) associated with the topic.

For example, to set buffering for a Visual FoxPro DBF file (which is a table) we have to use the heavily overloaded CURSORSETPROP() function. Why not a separate, unambiguous, “SetBufferMode()” function? To confirm a pending transaction, the command is END TRANSACTION. Why not “COMMIT” as in every other database language - a choice which is even more peculiar since the standard 'ROLLBACK' command is used to reverse a transaction?

Furthermore, perhaps because Visual FoxPro implements record (as opposed to 'Page') locking, the issue of controlling the placing and releasing of locks is inextricably bound up with buffering. For example, in order to enable row buffering, (which only operates on a single record) SET MULTILOCKS must be “ON”. According to the help file this setting merely determines Visual FoxPro's ability to lock multiple records in the same table - and it is scoped to the current data session anyway. This does not seem very logical and so it is not really surprising that people get confused by it all.

What do we mean by 'buffering' anyway?

The principle is actually very simple. When you make a changes to some data, those changes are not written to the source table, instead they go into a 'holding area' (the 'Buffer') until such time as you instruct Visual FoxPro to either save them to permanent storage or discard them. This holding area is what you actually 'see' in Visual FoxPro when using a buffered table and is, in reality, an updateable cursor based on the source table. All changes are made to this cursor and are only written to the underlying table when the appropriate "update" command is issued.

Buffering strategies

The buffering strategy determines when and how changes that are held in a buffer get sent to the underlying table. There are three options here:

·         No Buffering:               This was the only option in all versions of FoxPro prior to Visual FoxPro Version 3.0 and it is still the default behavior for Visual FoxPro tables today. Any changes made are written directly and immediately to the underlying table. There is no 'undo' capability unless it is programmed explicitly - by using SCATTER and GATHER, for example. Set by using ‘1’ as the parameter for CursorSetProp()

·         Row buffering:            Changes are not sent to the underlying table unless one of two things happens. Either an explicit call is made to the TableUpdate() function, or the record pointer is moved in the underlying table. Note that ANY movement of the record pointer, however it is initiated for a table which is in row buffered mode, always causes an 'implicit' TableUpdate(). Set by using either ‘2’ or ‘3’ as the parameter for CursorSetProp()

·         Table buffering:           Changes are never sent automatically to the underlying table, instead either an explicit call to TableUpdate() or TableRevert() command must always be used to affect changes that are held in the buffer. Attempting to close a table-buffered table while it still has uncommitted changes caused Visual FoxPro to generate an error in Version 3.0 but this behavior was changed in later versions so that pending changes are simply lost. (There is no error, and no warning that changes are about to be lost, the buffered table is just closed). Set by using either "4" or "5" as the parameter for  CursorSetProp()

Now you may be wondering, at this point, why there are TWO possible parameters for each of the strategies that implement buffering. The answer is, as indicated in the introduction, because there are also two Locking strategies.

Locking strategies

Visual FoxPro always needs to lock the physical record in a table while changes are being made to its contents and there are two ways in which automatic locking (as opposed to explicit use of the RLock()/FLock() functions) can be established.

·         Pessimistic locking.         The record is locked as soon as a user starts making changes. (The lock is actually placed as soon as a valid key press is detected.) This prevents any other user from making or saving changes to that record until the current user has completed their changes and released the record by either committing or reverting their changes

·         Optimistic locking.          An attempt to lock the record is only made when changes are actually being sent to the table. This means that even though one user is making changes to data, the record remains available to other users who could also make, and possibly save, changes to the same record while the first user is still working with it

Buffering modes

The buffer "mode" for a table is, therefore, the specific combination of the Buffering and Locking strategies that are in force. There are a total of five buffering modes for a table as illustrated by Table 1.

Table 1. Visual FoxPro Buffering Modes

Mode	Locking	Buffering	Comment
1	Pessimistic	None	The only option for FP2.x, default for VFP, tables
2	Pessimistic	Row	Lock placed by KeyPress Event. Record pointer movement forces save
3	Optimistic	Row	Lock placed by TableUpdate(). Record pointer movement forces save
4	Pessimistic	Table	Lock placed by KeyPress Event. Save must be initiated explicitly
5	Optimistic	Table	Lock placed by TableUpdate(). Save must be initiated explicitly

When working with Visual FoxPro we must be careful to distinguish between the individual strategies which we are specifying for Buffering and Locking and the buffering mode which results from the combination of them. Unfortunately, as we shall see, Visual FoxPro itself is less careful about this distinction.

What does all this mean when creating data-bound forms?

This is where things start to get a little more complex (and not only because of the nomenclature). Let us consider the 'normal' situation where tables are added to form by the native dataenvironment. The form has a property named 'Buffermode' that has three possible settings:

·         0 None (default)

·         1 Pessimistic

·         2 Optimistic

Notice that these actually refer to the options for the locking strategy and have nothing to do with buffering at all!  In fact the form will determine the buffering strategy for its tables all by itself, based upon their usage.  If a form uses two tables which have a one-to-many relationship, they display the 'many' side of the relationship in different ways.

If the “many” table is used as the record source for a grid it is opened, by the form, in table buffered mode. However, if the 'many' table is merely used to bind to controls that only display the table one row at a time then the buffering for the table will be the same as for the 'one' table for all settings of the Form's Buffermode property.

If you create a little test form and run through all the options for the form's Buffermode property, you will notice that even with the form's BufferMode property set to 0-(None) the cursor created by Visual FoxPro is still opened in row buffered mode when the tables are opened by the form's dataenvironment. It appears that, to a form at least, "No buffering" and "Row Buffering" are one and the same thing!

However, this is NOT the case when tables are opened directly with a USE command. If tables are opened explicitly in the Load() method then the form's Buffermode property has no impact whatsoever, and the tables are opened according to settings appropriate to the Data Session. For forms running in the default data session (DataSession = 1) the settings specified in the Options Dialog are used. Note that the Options dialog the options really do set the buffer mode. It has five options which correspond to the five modes defined in Table 1 above and which use the same numeric identifiers as the CursorSetProp() function. If the form is running a private datasession, then tables opened with the USE command will be set to whatever mode is specified for that datasession and this, by default, will be for no buffering at all.

Are you confused yet?

As far as I can tell, the situation is actually as follows:

·         For tables opened by a Form's dataenvironment, it does not matter whether the form is running in the Default or a Private datasession. Tables are always buffered to at least Optimistic Row level.

·         The form's BufferMode property actually determines the locking strategy, not the buffer mode, but only for tables which are opened by that form's dataenvironment. 

·         In the default datasession, tables that are opened explicitly have both their buffering and locking strategies set to the option chosen in the global Options Dialog.

·         In a Private DataSession, tables that are opened explicitly have their buffering and locking strategies set according to the settings that apply for that datasession (Default = "No buffering").

These results can be verified by setting the various options in a simple form and displaying the CURSORGETPROP( “BUFFERING“ ) result.

So how should we set up buffering in a form?

The short answer, as always, is 'it depends'. If you use the form's dataenvironment to open tables then you can normally leave the choice of buffering to Visual FoxPro. Otherwise you can simply use the CursorSetProp() function in your code to set up each table as required. Either way you need to be aware of the consequences so that you can code your update routines appropriately.

Using BufferModeOverride

The dataenvironment class provides a property for each table (or, more accurately, 'cursor') named "BufferModeOverride". This will set the buffer mode for that table (and only that table) to one of its six options - yes, that's right, SIX options, not five - as follows:

·         0 None

·         1 (Default) Use form Setting

·         2 Pessimistic row buffering

·         3 Optimistic row buffering

·         4 Pessimistic table buffering

·         5 Optimistic table buffering

There are two points to note about this. First, notice that while the numbers 2 through 5 match the parameters for CursorSetProp() and are the same as those available through the Options dialog, the value required for setting "no buffering" is now 0 instead of 1. This is yet another inconsistency in the set up for buffering! Second, notice that the default value for this property is "1 - Use Form Setting". The form 'setting' referred to is, of course, the "BufferMode" property which, as we have already seen, is actually for choosing the locking strategy to be applied. There is no form setting for controlling buffering!

Having said that, setting the BufferModeOverride property will ensure that the table is opened using the buffer mode that you specify. At least this property is correctly named, it overrides everything else and forces the table into the specified buffer mode in all situations.

Using CursorSetProp()

Irrespective of how a table is opened, you can always use the CursorSetProp() function to change the buffer mode of a table. However, if you are not using the form's dataenvironment to open your tables, then you have two options depending on whether your forms use the Default DataSession or a Private DataSession. In the first case you can simply set the required mode in the Options dialog and forget about it. All tables will always be opened with that setting and you will always know where you are.  If you use a Private DataSession then you need to do two things.

First you must ensure that the environment is set up to support buffering. A number of environment settings are scoped to the datasession and you may need to change the default behavior of some, or all of the following (see the SET DATASESSION topic in the help file for a full list of settings affected):

·         SET MULTILOCKS           Must be set to ON to enable buffering, Default is OFF

·         SET DELETED                 Default is OFF

·         SET DATABASE              "No database" is set by default in a Private DataSession

·         SET EXCLUSIVE             Default is OFF for a Private DataSession

·         SET LOCK                      Default is OFF

·         SET COLLATE                Default is 'MACHINE'

·         SET EXACT                    Default is OFF

Second you need to explicitly set the buffer mode of each table using the CursorSetProp() function with the appropriate parameter because the settings in the Options Dialog do not apply to private datasessions.

So what mode of buffering should I use in my forms?

To us the answer is simple. You should always use table buffering with an optimistic locking strategy (i.e. Buffer Mode 5). The reason is simply that, with the exception of building an index, there is nothing you can do in any other mode that cannot be done in this mode. While row buffering can be useful in development, we do not believe it has any place in a working application. The reason is simply that there are just too many ways in which the implicit TableUpdate() (caused by moving the record pointer) can be triggered, and not all of them are under our direct control. For example, the KeyMatch() function is defined in the Help file as;

      Searches an index tag or index file for an index key

Seems harmless enough - surely searching an index file cannot cause any problems. But a note (in the Remarks section right at the end of the topic) also states that:

KEYMATCH( ) returns the record pointer to the record on which it was originally positioned before KEYMATCH( ) was issued.

Hang on right there! Surely 'returns the record pointer' implies that it moves the record pointer - which indeed it does. The consequence is that if you are using row buffering and want to check for a duplicate key by using KeyMatch(), you will immediately commit any pending change. (Of course in Version 6.0 or later you can always use IndexSeek() instead.) However, the same issue arises with many of the commands and functions that operate on a table - especially the older ones that were introduced into FoxPro before the days of buffering (e.g. CALCULATE, SUM and AVERAGE).

More importantly, the fact that you have a table set up for table buffering does not prevent you from treating the table as if it were actually row buffered. Both the TableUpdate() and TableRevert() functions have the ability to on the current row only. The only practical difference, therefore, is that that no update will happen unless you explicitly call TableUpdate(). This may mean that you have to write a little more code, but it does prevent a lot of problems.

Changing the buffer mode of a table

We said, at the start of the last section, that you can always use CursorSetProp() to set or change the buffering mode of a table. This is true, but if the table is already table buffered, it may not be so simple because changing the buffering state will force Visual FoxPro to check the state of any existing buffers.

If the table is Row Buffered and has uncommitted changes, Visual FoxPro simply commits the changes and allows the change of mode. However, if the target is table buffered, and you try to change its buffer mode while there are uncommitted changes, Visual FoxPro complains and raises Error 1545 ("Table buffer for alias "name" contains uncommitted changes"). This is a problem because you cannot index a table, or make a free table or cursor transactable,  while it is table buffered, so the only way to do these things is to switch, temporarily, to row buffering. Of course, the solution is simple enough - just ensure there are no changes pending before you try to change the buffer mode.

Published Tuesday, October 18, 2005 7:11 PM by andykr