Previous Page TOC Index Next Page Home


22

Transaction Processing

What is Transaction Processing?

Transaction processing refers to the grouping of a series of changes into a single batch. The entire batch of changes is either accepted or rejected as a group. One of the most common implementations of transaction processing is a bank ATM (automated teller machine) transaction. Imagine that you go to the ATM machine to deposit your paycheck. In the middle of processing, a power outage occurs. Unfortunately, the bank recorded the incoming funds prior to the outage, but the funds had not yet been credited to your account when the power outage occurred. You would not be very pleased with the outcome of this situation. Transaction processing would prevent this scenario from occurring. With transaction processing, the whole process either succeeds or fails as a unit.

A group of operations is considered a transaction if it meets the following criteria:

If your application contains a group of operations that are atomic and isolated, and if, in order to maintain the consistency of your application, all changes must persist even if the system crashes, then you should place the group of operations in a transaction loop. With Access 95, the primary benefit of transaction processing is data integrity. As you will see in the following section, "Understanding the Benefits of Transaction Processing," with prior versions of Access, transaction processing also provides performance benefits.

Understanding the Benefits of Transaction Processing

In Access 2.0, the benefits of transaction processing are many. This is because Access 2.0 does no implicit transaction processing. With the following code, run in Access 2.0, each time the Update method occurs within the loop, the data is written to disk. These disk writes are costly in terms of performance, especially if the tables are not located on a local machine.

Sub IncreaseQuantity()
   On Error GoTo IncreaseQuantity_Err
   Dim db As DATABASE
   Dim rst As Recordset
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Select OrderId, Quantity From tblOrderDetails", _
               dbOpenDynaset)
   'Loop through recordset increasing Quantity field by 1
   Do Until rst.EOF
      rst.Edit
      rst!Quantity = rst!Quantity + 1
      rst.UPDATE
      rst.MoveNext
   Loop
IncreaseQuantity_Exit:
   Set db = Nothing
   Set rst = Nothing
   Exit Sub
IncreaseQuantity_Err:
   MsgBox "Error # " & Err.Number & ": " & Error.Description
   Resume IncreaseQuantity_Exit
End Sub

This code, and all of the code in this chapter, can be found in the CHAP22EX.MDB database on the sample code CD-ROM in the module called basTrans.

This same code, when run in Access 95, performs much differently. In addition to any explicit transaction processing that you might implement for data-integrity reasons, Access 95 does its own behind-the-scenes transaction processing. This implicit transaction processing is done solely to improve the performance of your application. As the processing loop in the IncreaseQuantity routine executes, Access buffers and then periodically writes the data to disk. In a multiuser environment, by default, Jet automatically (implicitly) commits transactions every 50 milliseconds. This period of time is optimized for concurrency rather than performance. If you feel that it is necessary to sacrifice concurrency for performance, a few Windows registry settings can be modified to achieve the specific outcome that you want. These settings are covered in the next section, "Modifying the Default Behavior of Transaction Processing."

Although, in general, implicit transaction processing along with the modifiable Window's registry settings afford you better performance than explicit transaction processing, it is not a cut-and-dry situation. Many factors impact the performance benefits gained by both implicit and explicit transaction processing:

If you plan to implement explicit transaction processing solely for the purpose of improving performance, you should make sure that you benchmark performance using both implicit and explicit transactions. It is critical that your application-testing environment be as similar as possible to the production environment within which the application will run.

Modifying the Default Behavior of Transaction Processing

Before you learn how to implement transaction processing, let's see what you can do to modify the default behavior of the transaction processing built into Access 95. Three registry settings affect implicit transactions within Access 95. They are ImplicitCommitSync, ExclusiveAsnycDelay, and SharedAsyncDelay. These keys are not automatically found in the System Registry. They must be added to the Registry using the Registry Editor.

The ImplicitCommitSync setting determines whether or not implicit transactions are used. The default is No, which might seem to imply that no implicit transactions occur. Actually, due to a documented Jet 3.0 bug, the value of No means that implicit transactions are used. You generally won't want to change this setting; the benefits offered by implicit transactions are many. Furthermore, by placing a series of commands within an explicit transaction loop, any implicit transaction processing is disabled for that loop.

The ExclusiveAsyncDelay setting specifies the maximum number of milliseconds that elapse before Jet commits an implicit transaction when a database is opened for exclusive use. The default value for this setting is 2000 milliseconds. This setting does not in any way affect databases that are open for shared use.

The SharedAsynDelay setting is similar to the ExclusiveAsyncDelay setting. It determines the maximum number of milliseconds that elapse before Jet commits an implicit transaction when a database is opened for shared use. The default value for this setting is 50 milliseconds. The higher this value, the greater the performance benefits reaped from implicit transactions, but the higher the chances that concurrency problems will result. These concurrency issues are discussed in detail in the section entitled "Transaction Processing in a Multiuser Environment."

In addition to the settings that affect implicit transaction processing in Access 95, an additional registry setting affects explicit transaction processing. The UserCommitSync setting controls whether explicit transactions are completed synchronously or asynchronously. With the default setting of Yes, control doesn't return from a CommitTrans statement until the transactions are actually written to disk. When this value is changed to No, a series of changes are queued, and control returns before the changes are complete.

Properly Implementing Explicit Transaction Processing

Now that you are aware of the settings that affect transaction processing, you are ready to see how transaction processing is implemented. Three methods of the Workspace object control transaction processing:

The BeginTrans method of the Workspace object begins the transaction loop. The moment BeginTrans is encountered, Access begins writing all changes to a log file in memory. Unless the CommitTrans method is issued on the Workspace object, the changes are never actually written to the database file. After the CommitTrans method is issued, the updates are permanently written to the database object. If a Rollback method of the Workspace object is encountered, the log in memory is released. Here's an example:

Sub IncreaseQuantityTrans()
   On Error GoTo IncreaseQuantityTrans_Err
   Dim wrk As Workspace
   Dim db As DATABASE
   Dim rst As Recordset
   Set wrk = DBEngine(0)
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Select OrderId, Quantity From tblOrderDetails", _
               dbOpenDynaset)
   'Begin the Transaction Loop
   wrk.BeginTrans
   'Loop through recordset increasing Quantity field by 1
   Do Until rst.EOF
      rst.Edit
      rst!Quantity = rst!Quantity + 1
      rst.UPDATE
      rst.MoveNext
   Loop
   'Commit the Transaction; Everything went as Planned
   wrk.CommitTrans
IncreaseQuantityTrans_Exit:
   Set wrk = Nothing
   Set db = Nothing
   Set rst = Nothing
   Exit Sub
IncreaseQuantityTrans_Err:
   MsgBox "Error # " & Err.Number & ": " & Error.Description
   'Rollback the Transaction; An Error Occurred
   wrk.Rollback
   Resume IncreaseQuantityTrans_Exit
End Sub

This code uses a transaction loop to ensure that everything completes as planned or not at all. Notice that the loop that moves through the recordset, increasing the quantity field within each record by one, is placed within a transaction loop. If all processing within the loop completes successfully, the CommitTrans method is executed. If the error handling code is encountered, the Rollback method is issued, ensuring that none of the changes are written to disk.

Potential Issues with Transaction Processing

Before you decide that transaction processing is the best thing since sliced bread, you should keep in mind several issues concerning transaction processing. These issues are outlined within this section.

Making Sure the Datasource Supports Transactions

Not all recordsets support transaction processing. For example, FoxPro and dBASE files do not support transaction processing. Neither do certain back-end ODBC database servers. To make matters worse, no errors are encountered when using the transaction processing methods on FoxPro or dBASE tables. It will appear as if everything processed as planned, but actually all references to transactions are ignored. When in doubt, you can use the Transactions property of the Database or Recordset object to determine whether the data source supports transaction processing. The Transactions property are equal to True if the data source does support transaction processing and equal to False if the data source does not support transaction processing. Here's an example:

Sub SupportsTrans(strTableName)
   On Error GoTo SupportsTrans_Err
   Dim wrk As Workspace
   Dim db As DATABASE
   Dim rst As Recordset
   Dim fSupportsTrans As Boolean
   fSupportsTrans = False
   Set wrk = DBEngine(0)
   Set db = CurrentDb
   Set rst = db.OpenRecordset(strTableName, _
               dbOpenDynaset)
   'Begin the Transaction Loop Only if Recordset
   'Supports Transaction
   If rst.Transactions Then
      fSupportsTrans = True
      wrk.BeginTrans
   End If
   'Loop through recordset increasing Quantity field by 1
   Do Until rst.EOF
      rst.Edit
      rst!Quantity = rst!Quantity - 1
      rst.UPDATE
      rst.MoveNext
   Loop
   'Issue the CommitTrans if Everything went as Planned
   'and Recordset Supports Transactions
   If fSupportsTrans Then
      wrk.CommitTrans
   End If
SupportsTrans_Exit:
   Set wrk = Nothing
   Set db = Nothing
   Set rst = Nothing
   Exit Sub
SupportsTrans_Err:
   MsgBox "Error # " & Err.Number & ": " & Error.Description
   'Rollback the Transaction if An Error Occurred
   'and Recordset Supports Transactions
   If fSupportsTrans Then
      wrk.Rollback
   End If
   Resume SupportsTrans_Exit
End Sub

Notice that this code utilizes a Boolean variable called fSupportsTrans. The recordset is tested to see whether the SupportTrans property evaluates to True. If so, the BeginTrans is issued and the fSupportsTrans variable is set equal to True. The fSupportsTrans variable is evaluated two different times in the remainder of the routine. The CommitTrans method is issued only if fSupportsTrans evaluates to True. Within the error handling, the Rollback method is issued only if the fSupportTrans variable is equal to True.

Nesting Transactions

Another issue to be aware of with transactions is that you can nest transactions up to five levels deep. The inner transactions must always be committed or rolled back before the outer transactions. Furthermore, nested transactions are not supported at all for ODBC datasources. This is covered in the section of this chapter entitled "Transaction Processing in a Client/Server Environment."

Neglecting to Explicitly Commit Transactions

When a transaction loop is executing, all updates are written to a log file in memory. If a CommitTrans is never executed, the changes are, in effect, rolled back. In other words, a Rollback is the default behavior if the changes are never explicitly written to disk with the CommitTrans method. This generally works to your advantage. If the power is interrupted or the machine "hangs" before the CommitTrans is executed, all changes are, in effect, rolled back. But, this behavior can get you into trouble if you forget the CommitTrans method. If the workspace is closed without the CommitTrans method being executed, the memory log is flushed and the transaction is implicitly rolled back.

Available Memory and Transactions

Another "gotcha" with transactions occurs when the physical memory on the computer is exhausted by the transaction log. Access first attempts to use virtual memory. The transaction log is written to the temporary directory specified by the TEMP environment variable of the user's machine. This has the effect of dramatically slowing down the transaction process. If both physical and virtual memory are exhausted by the transaction process, an error 2004 results. You must issue a Rollback at this point. Otherwise, you are in danger of violating the consistency of the database.


If your code attempts to commit the transaction after a 2004 error has occurred, the Jet engine commits as many changes as possible, leaving the database in an inconsistent state.

Transactions and Forms

Access handles its own transaction processing on bound forms. You cannot control this transaction processing in any way. If you want to utilize transaction processing with forms, you must create unbound forms.

Transaction Processing in a Multiuser Environment

In a multiuser environment, transaction processing has implications beyond the protection of data. By wrapping a process in a transaction loop, you ensure that you are in control of all records involved in the process. The cost of this additional control is reduced concurrency for the rest of the users of the application. The following code illustrates this scenario:

Sub MultiPessimistic()
   On Error GoTo MultiPessimistic_Err
   Dim wrk As Workspace
   Dim db As DATABASE
   Dim rst As Recordset
   Dim intCounter As Integer
   Dim intChoice As Integer
   Dim intTry As Integer
   Set wrk = DBEngine(0)
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Select OrderId, ProductID, UnitPrice " & _
            "From tblOrderDetails Where ProductID > 50", _
               dbOpenDynaset)
   rst.LockEdits = True
   'Begin the Transaction Loop
   wrk.BeginTrans
   'Loop through recordset increasing UnitPrice
   Do Until rst.EOF
      'Lock Occurs Here for Each Record in the Loop
      rst.Edit
      rst!UnitPrice = rst!UnitPrice * 1.1
      rst.UPDATE
      rst.MoveNext
   Loop
   'Commit the Transaction; Everything went as Planned
   'All locks released for ALL records involved in the Process
   wrk.CommitTrans
   Set wrk = Nothing
   Set db = Nothing
   Set rst = Nothing
   Exit Sub
MultiPessimistic_Err:
   Select Case Err.Number
      Case 3260
         intCounter = intCounter + 1
         If intCounter > 2 Then
            intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical)
            Select Case intChoice
               Case vbRetry
                  intCounter = 1
               Case vbCancel
                  'User Selected Cancel, Roll Back
                  Resume TransUnsuccessful
            End Select
         End If
         DoEvents
         For intTry = 1 To 100: Next intTry
         Resume
      Case Else
         MsgBox "Error # " & Err.Number & ": " & Err.Description
   End Select
TransUnsuccessful:
   wrk.Rollback
   MsgBox "Warning: Entire Process Rolled Back"
   Set wrk = Nothing
   Set db = Nothing
   Set rst = Nothing
   Exit Sub
End Sub

The MultiPessimistic routine employs pessimistic locking. This means that each time the Edit method is issued, the record on which the edit is issued is locked. If all goes well and no error occurs, the lock is released when the CommitTrans is reached. The error-handling code traps for a 3260 error. This error means that the record is locked by another user. The user running the transaction processing is given the opportunity to retry or cancel. If the user selects retry, the code once again tries to issue the Edit method on the record. If the user selects cancel, a Rollback occurs. This causes the changes made to any of the records involved in the process to be canceled.

Two key points should be made about the MultiPessimistic routine. The first point is that as this routine executes, each record involved in the process is locked. This potentially means that all other users will be unable to edit a large percentage, or even any, of the records until the transaction process is complete. This is wonderful from a data-integrity standpoint, but it might not be practical within an environment where users must update data on a frequent basis. For this reason, it is a good idea to keep transaction loops as short in duration as possible. The second point is that if any of the lock attempts are unsuccessful, the entire transaction must be canceled. Once again, this might be what you want or need from a data-integrity standpoint, but it might require that all users refrain from editing data while an important process completes.

With optimistic locking, the lock attempt occurs when the Update method is issued rather than when the Edit method is issued. This does not make too much of a difference; all of the records involved in the transaction remain locked until the CommitTrans or Rollback occurs. An additional difference is in the errors that you must trap for. The code looks like this:

Sub MultiOptimistic()
   On Error GoTo MultiOptimistic_Err
   Dim wrk As Workspace
   Dim db As DATABASE
   Dim rst As Recordset
   Dim intCounter As Integer
   Dim intChoice As Integer
   Dim intTry As Integer
   Set wrk = DBEngine(0)
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Select OrderId, ProductID, UnitPrice " & _
            "From tblOrderDetails Where ProductID > 50", _
               dbOpenDynaset)
   rst.LockEdits = False
   'Begin the Transaction Loop
   wrk.BeginTrans
   'Loop through recordset increasing UnitPrice
   Do Until rst.EOF
      rst.Edit
      rst!UnitPrice = rst!UnitPrice * 1.1
      'Lock Occurs Here for Each Record in the Loop
      rst.UPDATE
      rst.MoveNext
   Loop
   'Commit the Transaction; Everything went as Planned
   'All locks released for ALL records involved in the Process
   wrk.CommitTrans
   Set wrk = Nothing
   Set db = Nothing
   Set rst = Nothing
   Exit Sub
MultiOptimistic_Err:
   Select Case Err.Number
      Case 3197  'Data Has Changed Error
         If rst.EditMode = dbEditInProgress Then
            intChoice = MsgBox("Overwrite Other User's Changes?", _
               vbYesNoCancel + vbQuestion)
            Select Case intChoice
               Case vbCancel, vbNo
                  MsgBox "Update Canceled"
                  Resume TransNotSuccessful
               Case vbYes
                  rst.UPDATE
                  Resume
            End Select
         End If
      Case 3186, 3260  'Locked or Can't be Saved
         intCounter = intCounter + 1
         If intCounter > 2 Then
            intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical)
            Select Case intChoice
               Case vbRetry
                  intCounter = 1
               Case vbCancel
                  'User Selected Cancel, Roll Back
                  Resume TransNotSuccessful
            End Select
         End If
         DoEvents
         For intTry = 1 To 100: Next intTry
         Resume
      Case Else
         MsgBox "Error # " & Err.Number & ": " & Err.Description
   End Select
TransNotSuccessful:
   wrk.Rollback
   MsgBox "Warning: Entire Process Rolled Back"
   Set wrk = Nothing
   Set db = Nothing
   Set rst = Nothing
   Exit Sub
End Sub

Notice that in the MultiOptimistic routine, the lock occurs each time the Update method is issued. All of the locks are released when the CommitTrans is executed. Furthermore, the error handling checks for a 3197 (data has changed) error. The 3197 occurs when the data is changed by another user between the time that the Edit method is issued and just before the Update method is issued.

Transaction Processing in a Client/Server Environment

When utilizing transactions in a client/server environment, several additional issues must be considered. These issues concern when and how transactions occur, what types of transactions are supported, and what potential problems can occur.

Implicit Transactions and the Client/Server Environment

When explicit transactions are not used, the way that transactions are committed on the database server depends upon what types of commands are being executed. In general, each and every line of code has an implicit transaction around it. This means that there is not a way to roll back an action because it is immediately committed on the database server. The exceptions to this rule are any SQL statements issued that modify data. These SQL statements (UPDATE, INSERT, and APPEND) are executed in batches. This means that a transaction loop is implicitly placed around the entire statement. If any records involved in the SQL statement cannot be updated successfully, the entire UPDATE, INSERT, or APPEND is rolled back.

Explicit Transactions and the Client/Server Environment

When explicit transactions are used, ODBC translates the BeginTrans, CommitTrans, and Rollback methods to the appropriate syntax of the back-end server and the transaction processes as expected. The main exception to this rule is when transactions are not supported by the specific back end that you are using. An example of transaction processing with a SQL Server back end is shown in the following code:

Sub TransSQLServer()
   Dim wrk As Workspace
   Dim db As DATABASE
   Dim qdf As QueryDef
   Set wrk = DBEngine(0)
   Set db = CurrentDb
   wrk.BeginTrans
   Set qdf = db.CreateQueryDef("")
   qdf.Connect = ("ODBC;Database=Pubs" & _
         ";DSN=PublisherData;UID=SA;PWD=")
   qdf.ReturnsRecords = False
   qdf.SQL = "UPDATE sales Set qty = qty + 1 " & _
         "Where Stor_ID = '7067';"
   qdf.Execute
   qdf.SQL = "Update titles Set price = price + 1 " & _
         "Where Type = 'Business'"
   qdf.Execute
   wrk.CommitTrans
TransSQLServer_Exit:
   Set wrk = Nothing
   Set db = Nothing
   Set qdf = Nothing
   Exit Sub
TransSQLServer_Err:
   MsgBox "Error # " & Err.Number & ": " & Err.Description
   wrk.Rollback
   Resume TransSQLServer_Exit
End Sub

The TransSQLServer routine begins by creating both Workspace and Database object variables. Next, it executes the BeginTrans method on the workspace. It creates a temporary query definition. Several properties are set for the query definition. These include the Connect property, ReturnsRecords property, and SQL property. When these properties have been set, the temporary query is executed. The SQL property of the query definition is modified and the query is executed again. If both Execute methods complete successfully, the CommitTrans method is issued on the Workspace object. If any error occurs during processing, the Rollback method is issued.

Nested Transactions in a Client/Server Environment

One occasion when transactions might not perform as expected is when your code employs nested transactions. ODBC does not support nested transactions. If your code includes nested transactions, all but the outermost transaction loop will be ignored.

Lock Limits in a Client/Server Environment

A potential pitfall when dealing with client/server databases involves lock limits. Many database servers impose strict limits upon how many records can be concurrently locked. As you saw in the code examples within the "Properly Implementing Explicit Transaction Processing" section of this chapter, a transaction loop can potentially lock a significant number of records. It is important to consider the maximum number of locks supported by your back end when employing the use of transaction loops in your VBA code.

Negative Interactions with Server-Specific Transaction Commands

You should never utilize the server-specific transaction commands when building pass-through queries. These server-specific command can conflict with the BeginTrans, CommitTrans, and Rollback methods, causing confusion and potential data corruption.

Improving the Integrity of the Time and Billing Application Using Transaction Processing

As you continue to develop the Time and Billing application, you might find it necessary to use VBA code to accomplish certain tasks. These tasks might require that several processes complete successfully or not at all. As these situations arise, you should consider placing them in a transaction loop. An example of such a situation is the frmArchivePayments form (see Figure 22.1). frmArchivePayments allows the user to specify a date range. This date range is used as the criteria to determine what data is sent to the tblPaymentsArchive table and removed from the tblPayments table. When this process is run, you want to ensure that the process runs in entirety or not at all. Here's the code:

Sub cmdArchivePayments_Click()
On Error GoTo Err_cmdArchivePayments_Click
   Dim wrk As Workspace
   Dim db As DATABASE
   Dim strSQL As String
   Set wrk = DBEngine(0)
   Set db = CurrentDb
   wrk.BeginTrans
   strSQL = "INSERT INTO tblPaymentsArchive" & _
         " SELECT DISTINCTROW tblPayments.* " & _
         " FROM tblPayments " & _
         " WHERE tblPayments.PaymentDate Between #" & _
         Me!txtStartDate & _
         "# And #" & _
         Me!txtEndDate & "#;"
   db.Execute strSQL
   strSQL = "DELETE DISTINCTROW tblPayments.PaymentDate " & _
         "FROM tblPayments " & _
         " WHERE tblPayments.PaymentDate Between #" & _
         Me!txtStartDate & _
         "# And #" & _
         Me!txtEndDate & "#;"
   db.Execute strSQL
   wrk.CommitTrans
Exit_cmdArchivePayments_Click:
    Exit Sub
Err_cmdArchivePayments_Click:
    MsgBox Err.Description
    wrk.Rollback
    Resume Exit_cmdArchivePayments_Click
End Sub


Figure 22.1. The frmArchivePayments form allows the user to specify a date range of payments to be archived.

The routine uses the BeginTrans method of the Workspace object to initiate a transaction loop. A SQL statement is built, using the values of the txtStartDate and txtEndDate controls on the form as criteria for the SQL statement. This SQL statement adds all records within the specified date range to the tblPaymentsArchive table. The Execute method is applied to the Database object, using the SQL string as an argument. The SQL string is then modified to build a statement that deletes all records within the specified date range from the tblPayments table. If both SQL statements execute successfully, the CommitTrans method is executed, committing both transactions. If an error occurs, the whole transaction is rolled back.

Summary

If transactions are used properly, many benefits can be gained from them. Transactions help to ensure that all parts of a logical piece of work complete successfully or not at all. In some situations, they can also improve performance. There are many things that you must be aware of when employing the use of transactions. The potential pitfalls vary, depending upon whether you are issuing a transaction in a multiuser Access environment or in a client/server environment. If you take all mitigating factors into account, you can ensure that transactions provide you with the data integrity that you expect to achieve from them.

Previous Page TOC Index Next Page Home