BEGIN TRANSACTION, END TRANSACTION, Rollback, TXNLEVEL()

The transaction commands let you combine updates to a group of tables into a single process so all the changes either happen or don’t happen. Transactions can be nested—TXNLEVEL() lets you find out how deep you are.

Usage

BEGIN TRANSACTION
END TRANSACTION
ROLLBACK
nCurrentLevel = TXNLEVEL()

One of the biggest complaints about FoxPro over the years has been the lack of transaction processing. It was possible to save an invoice header, then have a problem saving the detail records and have no clean way out. If power failed in the middle, you could get incomplete records. No more … sort of.

When you need to make updates to multiple tables and it’s an all-or-nothing deal (like invoice headers and details), wrap the whole process in a transaction. BEGIN the TRANSACTION, then go through each table attempting to update it. If you succeed in updating all the tables, END the TRANSACTION and go on with your life. If you can’t update one of the tables, ROLLBACK the transaction, and either figure out and solve the problem or revert all the tables to their original state. (You may even want to wrap single table updates in a transaction if they involve updates to multiple records.)

So why do we say “sort of?” Because there’s still a small window of time where a system crash or a power outage could leave you with messed-up data. Don’t throw out those uninterruptable power supplies just yet. Nonetheless, with transactions, the window’s much smaller than before.

Each record the transaction affects is locked at the time it becomes part of the transaction. (In fact, not only are these records locked, but they can’t even be read by other workstations, so you want to keep transactions as short as possible.) END TRANSACTION and ROLLBACK unlock all the affected records.

You can nest one transaction inside another. This might happen when, say, an order entry branches off to add a new customer. If both the customer and order entry save routines use transactions, and the order entry save routine calls the customer save routine, the order can’t be saved unless the customer save is successful.

The limit for nesting is five levels—shades of the READ levels of old. Just as read levels could be checked with RDLEVEL(), transaction levels can be checked with TXNLEVEL().

Transactions apply only to tables (and views) contained in databases. You can’t use them on free tables. To be more specific, you can do things to free tables when a transaction is in progress, but the changes can’t be rolled back. For remote views, consider using the backend data source’s transaction capabilities through SQLSetProp(), SQLCommit() and SQLRollback().

Example

* Save invoice header and details in a transaction.
* This is only a sketch of the real code, which would
* probably include various error checking and handling.
BEGIN TRANSACTION

lGoOn = .T.

SELECT header
IF NOT TABLEUPDATE()
   lGoOn = .F.
ENDIF

IF lGoOn
   SELECT detail

   IF NOT TABLEUPDATE(.t.,.t.)
      lGoOn = .F.
   ENDIF
ENDIF

IF lGoOn
   END TRANSACTION
ELSE
   ROLLBACK
   =TABLEREVERT(.f.,"header")
   =TABLEREVERT(.t.,"detail")
ENDIF

You may be confused by the calls to TableRevert() after the Rollback—we were at first, too. What’s going on here is that updating is normally a two-step process. The user updates the buffers by making changes (through whatever interface you provide). Then, calls to TableUpdate() copy the changes from the buffers to the actual tables. When you use transactions, you add another layer to that. TableUpdate() copies the changes from the buffers to another set of buffers (call them “transaction buffers”) and END TRANSACTION copies from those buffers to the actual tables.

When you issue ROLLBACK, the changes are cleared from the transaction buffers, but the original buffers still contain the changed data. The TableRevert() calls discard those changes. In a real application, you probably wouldn’t just give up and revert your changes like that, but would try to solve whatever problems prevented the save, and try again.

See Also

Buffering, SQLCommit(), SQLSetProp(), SQLRollback(), TableRevert(), TableUpdate()