Using Transactions

A transaction is defined as a delimited set of changes that are performed on a database's schema or data. They increase the speed of actions that change data, and enable you to undo changes that have not yet been committed.

Transactions offer a great deal of data integrity insurance for situations where an entire series of actions must complete successfully, or not complete at all. This is the all-or-nothing principle that is employed in most financial transactions.

For example, when your employer transfers your monthly salary from their bank to yours, two actions actually occur. The first is a withdrawal from your employer's account, and the second is a deposit into yours. If the withdrawal completes, but for some reason, the deposit fails, you can argue until you're blue in the face, but your employer can prove that they paid you, and are not likely to want to do so again. Similarly, your bank will not be too impressed if the withdrawal fails, but the deposit succeeds. The reality is that the bank will take the money back, and you still end up with no salary. Either way, you get shafted! If, however, the two actions are enclosed in a single transaction, they must both complete successfully, or the transaction is deemed to have failed, and both actions are rolled back (reversed).

You begin a transaction by issuing the BeginTrans method against the Workspace object. To write the transaction to disk, you issue the CommitTrans method, and to cancel, or roll back the transaction, strangely enough, you issue the Rollback method.

Normally, transactions are cached, and not immediately written to disk. But if you're in a real hurry to get home at five o'clock, and immediately switch off your computer before the cache is written to disk, your most recent changes are lost. In Microsoft Jet workspaces, you can force the database engine to immediately write all changes to disk, instead of caching them. You do this by including the dbFlushOSCacheWrites constant with CommitTrans. Forcing immediate writes may affect your application's performance, but the data integrity benefits may outweigh any performance hit in certain situations.

The following code segment demonstrates a typical funds transfer transaction using a Microsoft Jet workspace. As transactions operate at workspace level, you can use them in exactly the same way with ODBCDirect workspaces.

In the following example, and in other examples given throughout this chapter, we have deviated from the Reddick object-naming convention by varying the names for Workspace, Database, and Recordset object variables. We do this because it sometimes makes the code easier to understand. In the following example code, rather than extend the length of the two Database object names, we have given them the names dbC and dbX, for the current and external databases respectively. They could just as easily have been named dbsC and dbsX.

Public Sub TransferFunds()

Dim wrk As DAO.Workspace Dim dbC As DAO.Database Dim dbX As DAO.Database

Set wrk = DBEngine(0) Set dbC = CurrentDb

Set dbX = wrk.OpenDatabase("c:\Temp\myDB.mdb")

On Error GoTo trans_Err

'Begin the transaction wrk.BeginTrans

'Withdraw funds from one account table dbC.Execute "UPDATE Table1 ", dbFailOnError

'Deposit funds into another account table dbX.Execute "INSERT INTO Table22 ", dbFailOnError

'Commit the transaction wrk.CommitTrans dbFlushOSCacheWrites trans_Exit:

'Clean up wrk.Close Set dbC = Nothing Set dbX = Nothing Set wrk = Nothing Exit Sub trans_Err:

'Roll back the transaction wrk.Rollback Resume trans_Exit End Sub

In the above example, changes to both databases either will complete as a unit, or will be rolled back as a unit.

You don't need to use transactions, but if you do, they can be nested up to five levels. It is also important to understand that transactions are global to the workspace—not the database. For example, if you make changes to two databases in the same workspace, and you roll back the changes to one of those databases, the changes made to the other database will also be rolled back.

0 0

Post a comment