Share via

Managing Updates Using Transactions

  • Article

Even with buffering, things can go wrong. If you want to protect update operations and recover from an entire section of code as a unit, use transactions.

Adding transactions to your application provides protection beyond Visual FoxPro record and table buffering by placing an entire section of code in a protected, recoverable unit. You can nest transactions and use them to protect buffered updates. Visual FoxPro transactions are available only with tables and views contained in a database.

Wrapping Code Segments

A transaction acts as a wrapper that caches data update operations to memory or to disk, rather than applying those updates directly to the database. The actual database update is performed at the end of the transaction. If for any reason the system cannot perform the update operations on the database, you can roll back the entire transaction and no update operations are performed.

Note

Buffered update operations made outside a transaction are ignored within a transaction in the same data session.

Commands that Control Transactions

Visual FoxPro provides three commands and one function to manage a transaction.

To

Use

Initiate a transaction.

BEGIN TRANSACTION

Determine the current transaction level.

TXNLEVEL( )

Reverse all changes made since the most recent BEGIN TRANSACTION statement.

ROLLBACK

Lock records, commit to disk all changes made to the tables in the database since the most recent BEGIN TRANSACTION, and then unlock the records.

END TRANSACTION

You can use transactions to wrap modifications to tables, structural .cdx files, and memo files associated with tables within a database. Operations involving variables and other objects don't respect transactions; therefore, you cannot roll back or commit such operations.

Note

When using data stored in remote tables, transaction commands control only updates to the data in the local copy of the view cursor; updates to remote base tables are not affected. To enable manual transactions on remote tables use SQLSETPROP( ), and then control the transaction with SQLCOMMIT( ) and SQLROLLBACK( ).

In general, you should use transactions with record buffers rather than with table buffering, except to wrap TABLEUPDATE( ) calls. If you place a TABLEUPDATE( ) command in a transaction, you can roll back a failed update, address the reason for the failure, and then retry the TABLEUPDATE( ) without losing data. This ensures the update happens as an "all-or-nothing" operation.

Though simple transaction processing provides safe data update operations in normal situations, it doesn't provide total protection against system failures. If power fails or some other system interruption occurs during processing of the END TRANSACTION command, the data update can still fail.

Use the following code template for transactions:

BEGIN TRANSACTION   
* Update records
IF lSuccess = .F. && an error occurs
   ROLLBACK
ELSE && commit the changes
   * Validate the data
   IF && error occurs
      ROLLBACK
   ELSE 
      END TRANSACTION
   ENDIF
ENDIF

The following rules apply to transactions:

  • A transaction starts with the BEGIN TRANSACTION command and ends with the END TRANSACTION or ROLLBACK command. An END TRANSACTION statement without a preceding BEGIN TRANSACTION statement generates an error.

  • A ROLLBACK statement without a preceding BEGIN TRANSACTION statement generates an error.

  • A transaction, once begun, remains in effect until the corresponding END TRANSACTION begins (or until a ROLLBACK command is issued), even across programs and functions, unless the application terminates, which causes a rollback.

  • Visual FoxPro uses data cached in the transaction buffer before using disk data for queries on the data involved in transactions. This ensures that the most current data is used.

  • If the application terminates during a transaction, all operations roll back.

  • A transaction works only in a database container.

  • You cannot use the INDEX command if it overwrites an existing index file, or if any .cdx index file is open.

  • Transactions are scoped to data sessions.

Transactions exhibit the following locking behaviors:

  • Within a transaction, Visual FoxPro imposes a lock at the time a command directly or indirectly calls for it. Any system or user direct or indirect unlock commands are cached until the completion of the transaction by ROLLBACK or END TRANSACTION commands.

  • If you use a locking command such as FLOCK( ) or RLOCK( ) within a transaction, the END TRANSACTION statement will not release the lock. In that case, you must explicitly unlock any locks explicitly taken within a transaction. You should also keep transactions containing the FLOCK( ) or RLOCK( ) commands as brief as possible; otherwise, users could be locked out of records for a long time.

Nesting Transactions

Nested transactions provide logical groups of table update operations that are insulated from concurrent processes. BEGIN TRANSACTION...END TRANSACTION pairs need not be in the same function or procedure. The following rules apply to nested transactions:

  • You can nest up to five BEGIN TRANSACTION...END TRANSACTION pairs.

  • Updates made in a nested transaction aren't committed until the outermost END TRANSACTION is called.

  • In nested transactions, an END TRANSACTION only operates on the transaction initiated by the last issued BEGIN TRANSACTION.

  • In nested transactions, a ROLLBACK statement only operates on the transaction initiated by the last issued BEGIN TRANSACTION.

  • The innermost update in a set of nested transactions on the same data has precedence over all others in the same block of nested transactions.

Notice in the following example that because changes in a nested transaction aren't written to disk but to the transaction buffer, the inner transaction will overwrite the changes made to the same STATUS fields in the earlier transaction:

BEGIN TRANSACTION &&  transaction 1
   UPDATE EMPLOYEE ; &&  first change
      SET STATUS = "Contract" ;
      WHERE EMPID BETWEEN 9001 AND 10000
   BEGIN TRANSACTION &&  transaction 2
      UPDATE EMPLOYEE ;
         SET STATUS = "Exempt" ;
         WHERE HIREDATE > {^1998-01-01}  &&  overwrites
   END TRANSACTION &&  transaction 2
END TRANSACTION    &&  transaction 1

The following nested transaction example deletes a customer record and all its related invoices. The transaction will roll back if errors occur during a DELETE command. This example demonstrates grouping table update operations to protect updates from partial completion and to avoid concurrency conflicts.

Example of Modifying Records in Nested Transactions

Code

Comments

DO WHILE TXNLEVEL( ) > 0

ROLLBACK

ENDDO

Cleanup from other transactions.

CLOSE ALL

SET MULTILOCKS ON

SET EXCLUSIVE OFF

Establish environment for buffering.

OPEN DATABASE test

USE mrgtest1

CURSORSETPROP('buffering',5)

GO TOP

Enable optimistic table buffering.

REPLACE fld1 WITH "changed"

SKIP

REPLACE fld1 WITH "another change"

MESSAGEBOX("modify first field of both" + ;

"records on another machine")

Change a record. Change another record.

BEGIN TRANSACTION

lSuccess = TABLEUPDATE(.T.,.F.)

Start transaction 1 and try to update all modified records without force.

IF lSuccess = .F.

ROLLBACK

AERROR(aErrors)

DO CASE

CASE aErrors[1,1] = 1539

...

CASE aErrors[1,1] = 1581

...

CASE aErrors[1,1] = 1582

If the update failed, roll back the transaction. Get the error from AERROR( ). Determine the cause of the failure. If a trigger failed, handle it. If a field doesn't accept null values, handle it. If a field rule was violated, handle it.

CASE aErrors[1,1] = 1585

nNextModified = getnextmodified(0)

DO WHILE nNextModified <> 0

GO nNextModified

RLOCK()

FOR nField = 1 to FCOUNT()

cField = FIELD(nField)

if OLDVAL(cField) <> CURVAL(cField)

If a record was changed by another user, locate the first modified record. Loop through all modified records, starting with the first record. Lock each record to guarantee that you can update. Check each field for any changes. Check the buffered value against the value on disk, and then present a dialog box to the user.

nResult = MESSAGEBOX;

("Data was changed " + ;

"by another user — keep"+ ;

"changes?", 4+48, ;

"Modified Record")

 

IF nResult = 7

TABLEREVERT(.F.)

UNLOCK record nNextModified

ENDIF

If user responded "No," revert the one record and unlock it.

EXIT

ENDIF

ENDFOR

Break out of the "FOR nField..." loop.

ENDDO

Get the next modified record.

BEGIN TRANSACTION

TABLEUPDATE(.T.,.T.)

END TRANSACTION

UNLOCK

Start transaction 2 and update all non-reverted records with force. End transaction 2. Release the lock.

CASE aErrors[1,1] = 109

...

CASE aErrors[1,1] = 1583

...

CASE aErrors[1,1] = 1884

...

OTHERWISE

MESSAGEBOX( "Unknown error "+;

"message: " + STR(aErrors[1,1]))

ENDCASE

If the record is in use by another user, handle it. If a row rule was violated, handle it. If there was a unique index violation, handle it. Otherwise, present a dialog box to the user.

ELSE

END TRANSACTION

ENDIF

End transaction 1.

Protecting Remote Updates

Transactions can protect you from system-generated errors during data updates on remote tables. The following example uses a transaction to wrap data-writing operations to a remote table.

Example of a Transaction on a Remote Table

Code

Comment

hConnect = CURSORGETPROP('connecthandle')

SQLSETPROP(hConnect, 'transmode',

DB_TRANSMANUAL)

Get the connect handle and enable manual transactions.

BEGIN TRANSACTION

Begin the manual transaction.

lSuccess = TABLEUPDATE(.T.,.F.)

IF lSuccess = .F.

SQLROLLBACK (hConnect)

ROLLBACK

Try to update all records without force. If the update failed, roll back the transaction on the connection for the cursor.

AERROR(aErrors)

DO CASE

Get the error from AERROR( ).

CASE aErrors[1,1] = 1539

...

If a trigger failed, handle it.

CASE aErrors[1,1] = 1581

...

If a field doesn't accept null values, handle it.

CASE aErrors[1,1] = 1582

...

If a field rule was violated, handle it.

CASE aErrors[1,1] = 1585

nNextModified = GETNEXTMODIFIED(0)

DO WHILE nNextModified <> 0

GO nNextModified

If a record was changed by another user, handle it. Loop through all modified records, starting with the first record.

FOR nField = 1 to FCOUNT()

cField = FIELD(nField)

IF OLDVAL(cField) <> CURVAL(cField)

nResult = MESSAGEBOX;

("Data has been changed ;

by another user. ;

Keep changes?",4+48,;

"Modified Record")

Check each field for any changes. Check the buffered value against the value on disk, and then present a dialog box to the user.

IF nResult = 7

TABLEREVERT(.F.)

ENDIF

EXIT

ENDIF

ENDFOR

nNextModified = ;

GETNEXTMODIFIED(nNextModified)

ENDDO

If user responded "No," revert the one record. Break out of the "FOR nField..." loop. Get the next modified record.

TABLEUPDATE(.T.,.T.)

SQLCOMMIT(hConnect)

Update all non-reverted records with force and issue a commit.

CASE aErrors[1,1] = 109

* Handle the error

Error 109 indicates that the record is in use by another user.

CASE aErrors[1,1] = 1583

* Handle the error

Error 1583 indicates that a row rule was violated.

CASE aErrors[1,1] = 1884

* Handle the error

Error 1884 indicates that the uniqueness of the index was violated.

OTHERWISE

* Handle generic errors.

 

MESSAGEBOX("Unknown error message:" ;

+ STR(aErrors[1,1]))

ENDCASE

Present a dialog box to the user. End of error handling.

ELSE

SQLCOMMIT(hConnect)

END TRANSACTION

ENDIF

If all errors were handled and the entire transaction was successful, issue a commit and end the transaction.

See Also

Concepts

Managing Conflicts When Updating Data

Reference

Update Criteria Tab, View Designer

DBSETPROP( ) Function

CURSORSETPROP( ) Function

Other Resources

Updating Data

Programming for Shared Access