Back

Originally published in FoxTALK June 1998


Traversing Transactions

Previously we talked about TableUpdate() and TableRevert() and saw how they enable us to control updates while using buffering on our cursors and tables.  These two guys are really very good for handling the updating or reverting of a single table or cursor, but how do we coordinate the updates or reverts to a number of related tables.  Enter the Transaction.

So what’s a transaction?

There are situations where we need to update more than one table or cursor and the combination of the updates represents one action on the database.  This action has to be an “all or none” operation that is, either all of it happens or none of it happens.  For example in writing the code to save a new invoice we need to add a record to the invoice header table, add a group of records to the invoice line item table, update the balance in the customer record, and add a record to the accounts receivable table.  If any of these operations fail we need to reverse the earlier ones that did succeed and make sure that none of these updates get into the data. Transactions are designed for this very purpose.

We use the BEGIN TRANSACTION command to start the transaction tracking in Visual FoxPro and the ENDTRANSACTION command to complete the transaction or the ROLLBACK command to reverse the transaction.

How do transactions work?

When Visual FoxPro encounters a BEGIN TRANSACTION it starts to track the file update operations that occur.  For each update action Visual FoxPro secures the required locks but it does not do the update yet.  When an ENDTRANSACTION is encountered Visual FoxPro then does all of the file writes involved in the transaction and releases the locks that were obtained.  If a ROLLBACK command is encountered Visual FoxPro will release the obtained locks without doing any of the file write operations.

Through this process Visual FoxPro is able to assure that either all of the file updates are done or none of them are done.  As can be seen in looking at this process it is a very good idea to keep the BEGIN TRANSACTION and ENDTRANSACTION/ROLLBACK commands as physically close to each other in the code as is possible.  Doing this reduces the period of time that the resources are locked.

What tables and cursors can participate in a transaction?

Transactions in Visual FoxPro are only available for tables that are in a database (DBC).  Free tables cannot participate in a transaction and a rollback or other abnormal end of the transaction does not affect any updates made to free tables during a transaction.

This can cause some interesting effects if a free table is the source of data for a local view that is managed by a transaction.  Let’s examine the steps in the process of a fictitious transaction.  In this transaction there is one table in a database named Table1 and a view named View1 that gets its data from a free table named Table2.  The code for the transaction is listed below:

BEGIN TRANSACTION

IF TableUpdate( 1, .F., “View1”)

   IF TableUpdate ( 1, .F., “Table1”)

      END TRANSACTION

   ELSE

      ROLLBACK

   ENDIF

ELSE

   ROLLBACK

ENDIF

Now the questions, if the TableUpdate for Table1 fails will the ROLLBACK undo the changes to View1 and set it back to its state prior to the transaction?  The answer is that yes it will roll back the changes to View1.  Will the changes to Table2 that were a result of updating View1 be reversed by the ROLLBACK?  No, because Table2 is a free table and therefore is not participating in the transaction.  This can leave your data in an undefined state, you have to manage the free table your self.  It is best not to depend on transactions when free tables are involved in any way.

Show us the code!

So how do we integrate transactions into our forms?  It can be done on a form by form basis or you can do it generically in a form class.  Below is some pseudocode that will give you the outline of the structure for incorporating transactions into your updates.

* Set a variable for tracking a failure

LOCAL llRollBack

llRollBack = .F.

* Wait until all data processing is complete before beginning the transaction

BEGIN TRANSACTION

* Now do each update checking the result

IF NOT TableUpdate( … )

   llRollBack = .T.

ELSE

   IF NOT TableUpdate( … )

      llRollBack = .T.

   ELSE

      IF NOT TableUpdate( … )

         llRollBack = .T.

      ENDIF

   ENDIF

ENDIF

IF llRollBack

   ROLLBACK

ELSE

   ENDTRANSACTION

ENDIF

The code above is pseudocode, so don’t take it literally.  Here we used a series of nested IF statements to control the actions.  We could have put all of the alias’ in an array and use a FOR/ENDFOR loop to go through them exiting the loop when they were all done or when any one of them failed.

What can go wrong?

In the pseudocode above let’s assume that the transaction failed and that we issued the ROLLBACK.  What, exactly, do we roll back to?  Are the buffers returned to their pre edit state, or are the buffers still dirty with the user’s edits?

The answer is that the buffers are restored to the dirty state that they were in prior to the BEGIN TRANSACTION.  This means we still have to deal with the user’s edit.  We have the control here, we can put the user back into the form and let them decide what to do, or we can TableRevert all the alias’ and discard the user’s work.  This is all up to us in the code that we write.

What happens if the computer shuts off during the transaction?  Obviously Visual FoxPro has no record of the transaction when the system is restarted.  So did some of the tables get updated or not?  None of the tables got updated and the result is just as if a ROLLBACK had been executed.

Another situation that has occurred and caused a lot of problems is this one, a developer writes a form and puts a BEGIN TRANSACTION in the Init and issues either an ENDTRANSACTION or a ROLLBACK in the destroy depending on which button the user chose to exit the form.  What problems could this cause?  Well for one no other user can do anything with any of the tables involved in this form until the current user exits the form and releases the locks.  I have seen this situation used as an argument against transactions.  Well, come on now, anything that is used incorrectly can cause problems.  Transactions used correctly will limit the time between the beginning and ending to the smallest period possible.  The Init to Destroy of a form is certainly NOT the smallest time period possible.  It is a time period that is not even under the control of the developer.

It has been said that Visual FoxPro’s transactions are not as “robust” as the transaction handling of client server database servers.  I say, so what?  Is the fact that a semi tractor has brakes that are more “robust” than the breaks on my car a reason to NOT use my car’s breaks?  NO it is not.  Visual FoxPro’s transactions are helpful and they serve a purpose.  They should be used, even when the data is stored in a client server database.

Summary

Transactions in Visual FoxPro are a very valuable feature of the product.  They allow us to group a number of update operations into one “all or none” operation.  In today’s world we seldom build forms that only manage a single table, the multi-table form has become the norm.  Managing these multi-table updates becomes a very important aspect of our work and transactions are invaluable in doing this.