Erik Svenson
Microsoft

Introduction

Developing any application for use in a multi-user environment can be a very tricky operation, particularly if the application is reliant on an underlying database. The users of the system expect it to be robust, easy to use, and perhaps most important, fast.

Database application developers are frequently lulled into a false sense of security that multi-user programming is easy. After all, it’s just a simple matter of locking a few records, writing the data, then releasing the locks, right? What could be simpler?

While conceptually easy, developing well-behaved applications for use by a thousand customers requires careful design, not only in the database access area, but through the application as a whole. When designed correctly, the coding of the actual product becomes (relatively) effortless.

Client/Server applications present other multi-user challenges because the application’s performance is inherently tied to the performance and scalability of the server database.

It's a Performance and Scalability Issue, Really

Think about it: you’ve been contracted to design and develop an order entry system for a large mail-order catalog company that will service 100 online users. What is your first thought? Well, while some may argue about features, ease of use, etc., one of the first concerns should be performance.

No, you’re not in the wrong place. While the focus here is in multi-user programming techniques with Visual FoxPro, one must be crystal clear on the reasons for needing to understand such techniques: performance and scalability. That is, the application that you’ve developed may work well for you in your office while you’re developing the application, but once it’s been deployed and you’ve got your 100 users pounding on it, will it perform as well.

It's the Design, not the Programming

This session is not intended to coach you on the best practices for application design (Microsoft doesn’t pay me enough to write a book, so . . . ). However, I will say that components of your application need to be designed to take advantage of the correct buffering model. For example, batch processing components that run at night or on the weekend should usually use tables exclusively (or at least with FLOCK()). As another example, on-line transaction processing components like order entry forms should use optimistic buffering (record or table buffering, or both). Choosing the correct access method for the components of your application will go a long way toward guaranteeing that your application runs smoothly and quickly.

Visual FoxPro's Buffering Model

Visual FoxPro’s record and table buffering features is perhaps the most underrated, yet most significant feature of the product. But hey, let’s face it gang: this stuff isn’t sexy to magazine reviewers. You don’t see fancy .AVI files and wizbang graphics coming to mind with this topic. However, it’s powerful stuff to all the database developers out there who’ve been working miracles with GATHER/SCATTER for the last several years.

The most basic tenet of good, multi-user programming is restricting the number of records the application has locked at any one time as well as the amount of time those records are locked. There are four levels of data buffering with local (.DBF) data:

• Pessimistic Record Buffering
  
  
• Optimistic Record Buffering
  
  
• Pessimistic Table Buffering
  
  
• Optimistic Table Buffering

I won’t go into the definitions of all of these. Suffice it to say that, while all permutations of data buffering have their use, maximum multi-user performance will be gained by using either optimistic record buffering or optimistic table buffering. Some of the cases for the different buffering modes are:

Pessimistic Record and Table Buffering

The pessimistic cases are useful in situations where your application is not as concerned with maximum multi-user performance, but is concerned with guaranteeing locks on important, perhaps sensitive, data. You would typically see this in decision support components in which the manager wants to make changes to important items during the day and guarantee that s/he has the locks on the data

Optimistic Record and Table Buffering

By far the most common usage of Visual FoxPro’s buffering technology will be the use of optimistic buffering. This will allow for maximum throughput for data writes by multiple users (the canonical order entry system).

Transactions

Transactions play an important role in data buffering. There is a lot of confusion out there about the difference between transactions and data buffering and when to use which technology. Before I tell you which to use when (don’t cheat and read ahead), let me describe some of the differences between transactions and data buffering:

Locks

Consider the following example:

BEGIN TRANSACTION
UPDATE orders SET discount = 20 WHERE customer_id = 1096
END TRANSACTION

If, say, fifty records were updated in the above example (yes, Visual FoxPro now has an SQL - UPDATE statement!), each record that’s updated is locked before the update. Makes sense, right? What is not obvious is that the locks are held for the duration of the transaction. So, in this case, fifty record locks are generated and held until the END TRANSACTION. This ensures that the data updates in the transaction can be guaranteed to be written to disk (short of a power failure) without worrying about updates from other processes.

The various buffering modes allow you to control when the record locks are taken. Pessimistic buffering lock the record(s) as soon as an edit is made, whereas record locks are not attempted until the data is written in the optimistic case.

Managing Data Conflicts

The Visual FoxPro buffering technology is particularly geared toward helping you manage data update conflicts (for example, you read a column whose value is ‘FRED’, you change it to ‘DAN’ and, before you write the change, someone has already changed it to ‘JOHN’). Transactions do not manage such conflicts. Your changes will overwrite the underlying indiscriminately, regardless of what other processes might have modified it.

Drumroll Please . . .

Now, the answer to the above question: when do you use transactions versus data buffering? The answer (I know you read ahead, but oh well): use both.

Consider the following PRODUCTS table:

PRODUCT_ID	NAME	IN_STOCK	REORDER	DISCONTINUED
1	A Bout of Stout	1000	100	.F.
2	Ginger Porter	500	100	.F.
3	Cream of Wheaten	250	200	.F.
4	Hefe Weisen	769	450	.F.
5	Pepper Pale Ale	800	125	.F.
6	Svenson's Silver Horn Lager	400	300	.F.
7	Extra Special Bitter	320	50	.F.
8	FPA	800	315	.F.

Table 1: Unedited PRODUCTS table

You set up optimistic table buffering (you want it to be fast, right, so you want to delay the lock and writes until the very end) and update the REORDER column as follows:

PRODUCT_ID	NAME	IN_STOCK	REORDER	DISCONTINUED
1	A Bout of Stout	1000	20	.F.
2	Ginger Porter	500	300	.F.
3	Cream of Wheaten	250	1000	.F.
4	Hefe Weisen	769	450	.F.
5	Pepper Pale Ale	800	125	.F.
6	Svenson's Silver Horn Lager	400	300	.F.
7	Extra Special Bitter	320	50	.F.
8	FPA	800	315	.F.

Table 2: PRODUCTS table with 3 edited records

Then, when you click the Save button to commit the changes, the following code runs:

=TABLEUPDATE (.T., .F., ‘products’)

If no other user updated any of the three fields that you did, all would be well. However, if someone changed, say, the “Ginger Porter” product by changing the REORDER value to 200:

PRODUCT_ID	NAME	IN_STOCK	REORDER	DISCONTINUED
1	A Bout of Stout	1000	20	.F.
2	Ginger Porter	500	200	.F.
3	Cream of Wheaten	250	1000	.F.
4	Hefe Weisen	769	450	.F.
5	Pepper Pale Ale	800	125	.F.
6	Svenson's Silver Horn Lager	400	300	.F.
7	Extra Special Bitter	320	50	.F.
8	FPA	800	315	.F.

Table 3: PRODUCTS table with 1 edited record from another user

your update would fail. Worse yet, your update would partially succeed because the “A Bout of Stout” product would be updated. Now you are left with an incomplete transaction because you wanted all three products to be updated. Now consider the following code sample:

BEGIN TRANSACTION
llStatus = TABLEUPDATE (.T., .F., ‘products’)
IF llStatus != .T.
      ROLLBACK
      INSERT INTO errlog (errdatetime, table) ;
VALUES (datetime(), ‘products’)
      =MESSAGEBOX (“Update failed and was rolled back.”)
ELSE
      END TRANSACTION
ENDIF

By starting a transaction, all updates via TABLEUPDATE () are, themselves, buffered until the transaction is either committed with END TRANSACTION or undone with ROLLBACK.

Managing Conflicts

The above code sample describes a simple yet common algorithm to control buffered updates to database tables. However, being FoxPro developers, we want more control. Therefore, Visual FoxPro provides interfaces to allow you to handle how updates are made or undone, even down to the field level. The functions that are used to manage update conflicts are:

• GETFLDSTATE ()
  
  
• GETNEXTMODIFIED ()
  
  
• OLDVAL ()
  
  
• CURVAL ()

GETFLDSTATE () returns the state of a field or the entire row. The state can be any of the following:

Return value	Edit or deletion status
1	Field has not been edited or deletion status has not changed.
2	Field has been edited or deletion status has changed.
3	Field in an appended record has not been edited or deletion status has not changed for the appended record.
4	Field in an appended record has been edited or deletion status has changed for the appended record.

GETNEXTMODIFIED () lets you walk through each record that has been modified. In Table 2 above, the following code will use GETFLDSTATE () and GETNEXTMODIFIED () to examine each modified field in each modified record:

* Loop through each modified record, starting from record 1
lnNextModifiedRecord = GETNEXTMODIFIED (0, ‘products’)
DO WHILE lnNextModifiedRecord > 0
*      Loop through each field in the modified record
      FOR lnCounter = 1 to FCOUNT (‘products’)
*         Check the field state of each field to see what 
*         fields have been edited
         IF GETFLDSTATE (FIELD (lnCounter)) = 2
            WAIT WINDOW “Field “ + FIELD (lnCounter) ;
               + “was modified”
         ENDIF
   ENDFOR

*       Get the next modified record
lnNextModifiedRecord = ;
GETNEXTMODIFIED (lnNextModifiedRecord, ‘products’)
ENDDO

CURVAL () lets you examine the values of modified fields as they exist on disk. OLDVAL () lets you get the values from modified fields as they existed before the edit was made. Using the values from Table 1 and Table 2 above, the values of CURVAL (‘reorder’) and OLDVAL (‘reorder’) would be:

Visual FoxPro actually maintains three different system cursors for the current values, edited values, and old values so you don’t have to. This provides two crucial benefits. First, you no longer have to use SCATTER and GATHER to buffer updates in arrays, which saves a lot of coding. Second, it’s faster than using arrays.

Conclusion

The buffering technology in Visual FoxPro is powerful stuff. I don’t know of any other product on the market that provides such a rich environment for developing robust, fast, and scalable multi-user applications.