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.
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 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.
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.
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.
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.
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.
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."
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.