Managing Multiple Transactions
Transaction management is used to maintain the integrity of a data source when operations on one or more data sources need to be treated as a single operation.
The most common example of transaction management comes from banking. Take, for instance, the steps involved in transferring money from a savings account to a checking account. First, you must remove the desired amount of money from the savings account, and then that amount must be added to your checking account. Suppose that somebody walked by and pulled the plug of the ATM machine just when it had completed removing your money from your savings account, but before it added it to your checking account.
By using three methods (BeginTrans, CommitTrans, and RollbackTrans), you can create single transactions from multiple operations.
Tip
The BeginTrans, CommitTrans, and RollbackTrans methods are not available when you are using a client-side Connection object with RDS.
Starting a Transaction: The BeginTrans and CommitTrans Methods
A transaction begins with a call to a Connection object’s BeginTrans method and ends with a call to the CommitTrans method. The CommitTrans method indicates that the transaction is completed and that the data should be saved, or committed, to the data source.
The following code illustrates the use of the BeginTrans and CommitTrans methods:
' begin a new transaction con.BeginTrans ' ' do some manipulation of the data here ' ' commit the manipulations of the data to the data source now con.CommitTrans
Not all data providers support transactions, and you should check before using them. You can tell whether the current data provider supports transactions by checking for the Transaction DDL dynamic property by using the Properties collection of the Connection object. If it appears in the Connection object’s Properties collection, then your data provider supports transaction management through the BeginTrans, CommitTrans, and RollbackTrans methods.
Example 4-14 shows how you can test for the support of transactions by your data provider, and how you can work with or without it depending on the result of your test.
Public Sub TestForTransactionSupport( )
Dim con As ADODB.Connection
Set con = New ADODB.Connection
' open the connection on a given data source
con.Open "BiblioDSN"
' if the data provider supports transactions, begin one
If (SupportsTransactions(con)) Then con.BeginTrans
' manipulate data here
'
' if the data provider supports transactions, commit changes
If (SupportsTransactions(con)) Then con.CommitTrans
' close the Connection and clean up
con.Close
Set con = Nothing
End Sub
Private Function SupportsTransactions( _
conConnectionToTest As ADODB.Connection) As Boolean
On Error GoTo ERR_SupportsTransactions:
Dim lValue As Long
' simply try to access the property to verify whether the data provider
' supports transactions
lValue = conConnectionToTest.Properties("Transaction DDL").Value
' if we got this far, the property exists and the data provider
' supports transactions
SupportsTransactions = True
Exit Function
ERR_SupportsTransactions:
Select Case (Err.Number)
' property does not exist, therefore the data provider does not
' support transactions
Case adErrItemNotFound:
SupportsTransactions = False
Case Else:
' another error
End Select
End FunctionCanceling a Transaction: The RollbackTrans Method
It doesn’t make sense to keep track of transactions if you cannot cancel them, so the Connection object implements the RollbackTrans method. The RollbackTrans method cancels the current transaction, which is defined as the entire set of operations performed on the data source since the last call to the BeginTrans method. Once the RollbackTrans method is called, your data source will never see the changes that were made during the last transaction.
A common time to use the RollbackTrans method is immediately following an error that has occurred during the processing of data. Example 4-15 demonstrates the RollbackTrans method.
Public Sub Rollback( )
On Error GoTo ERR_Rollback:
Dim con As ADODB.Connection
Set con = New ADODB.Connection
' open the connection on a given data source
con.Open "BiblioDSN"
' begin a transaction
con.BeginTrans
'
' manipulate data here
'
' commit changes
con.CommitTrans
' skip rollback and close the connection
GoTo CloseConnection
ERR_Rollback:
' an error has occurred, abort changes
con.RollbackTrans
CloseConnection:
' close the Connection and clean up
con.Close
Set con = Nothing
End SubNesting Transactions
If your data provider supports transactions, there is a good chance that it also supports nested transactions. For instance, Microsoft Access can support nested transactions up to five levels deep.
The BeginTrans method returns a Long value that represents the level of nesting for the newly created transaction. The first level is considered level one (1), not zero (0). When you nest transactions, you must resolve the more recently created transaction with either the CommitTrans or RollbackTrans method before you can resolve previously created transactions.
Example 4-16 illustrates the use of nested transactions.
Dim con As ADODB.Connection
Dim lLevel As Long
Set con = New ADODB.Connection
' the connection must be open to utilize transactions
con.Open "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=C:\Program Files" _
& "\Microsoft Visual Studio" _
& "\VB98\Biblio.mdb"
' record the level of the newly created transaction and print it
lLevel = con.BeginTrans( )
Debug.Print lLevel
' inside level 1 transaction
' record the level of the newly created transaction and print it
lLevel = con.BeginTrans( )
Debug.Print lLevel
' inside level 2 transaction
' record the level of the newly created transaction and print it
lLevel = con.BeginTrans( )
Debug.Print lLevel
' inside level 3 transaction
' commit changes to the level 3 transaction
con.CommitTrans
' commit changes to the level 2 transaction
con.CommitTrans
' commit changes to the level 1 transaction
con.CommitTrans
' close the Connection and clean up
con.Close
Set con = NothingSetting Transaction Options
There are two types of options that we can specify when using transactions through ADO:
Automatic creation of new transactions
When you call either the CommitTrans or the RollbackTrans methods, you are ending the current transaction. By default, you must call BeginTrans once again to start another transaction, but you can change this behavior by setting the value of the Attributes property. Table 4-5 lists these values.
You can use both the adXactCommitRetaining and the
adXactAbortRetaining constants at the same time,
as shown in Example 4-17.
Dim con As ADODB.Connection
Set con = New ADODB.Connection
' the connection must be open to utilize transactions
con.Open "BiblioDSN"
' set the attributes to automatically create a new transaction
' when both the CommitTrans and the RollbackTrans methods are
' called
con.Attributes = adXactCommitRetaining _
+ adXactAbortRetaining
' start transaction #1
con.BeginTrans
' do something here
' commit transaction #1, start transaction #2
con.CommitTrans
' do something here
' rollback transaction #2, start transaction #3
con.RollbackTrans
' do something here
' set the attributes so that neither CommitTrans nor RollbackTrans
' will create a new transaction
con.Attributes = 0
' commit transaction #3
con.CommitTrans
' close the Connection and clean up
con.Close
Set con = NothingTip
The Attributes property is not available to a client-side Connection object when using RDS. In addition, not all data providers support the transactions, and, therefore, they won’t support the Attributes property. Be sure to check for the Transaction DDL property in the Properties collection of your Connection object to see whether your data provider supports transactions before you attempt to use the Attributes property.
Isolation level
The IsolationLevel property is used to indicate how transactions relate to each other. By setting its value, you can determine whether the current transaction can read the changes that are as of yet not committed by another transaction.
IsolationLevel is a read/write property that can take any one of the following IsolationLevelEnum constants shown in Table 4-6.
Tip
The constant adXactUnspecified is the only valid
value for the IsolationLevel property when you are using a
client-side Connection object when using RDS.
Example 4-18 shows how you can use the IsolationLevel property to determine the level of isolation for the current transactions.
Dim con As ADODB.Connection
Set con = New ADODB.Connection
Dim sLevel As String
' open the connection
con.Open "BiblioDSN"
' select message based on the current isolation level
Select Case (con.IsolationLevel)
Case adXactUnspecified:
sLevel = "Isolation level cannot be determined."
Case adXactChaos:
sLevel = "You cannot write over changes that have been " _
& "made by higher level transactions."
Case adXactBrowse Or adXactReadUncommitted:
sLevel = "You can view changes not yet committed by other " _
& "transactions."
Case adXactCursorStability Or adXactReadCommitted:
sLevel = "You can only view changes from other " _
& "transactions that have been committed."
Case adXactRepeatableRead:
sLevel = "You can only view changes from other " _
& "transactions that have been committed and you " _
& "can requery data to see new records."
Case adXactIsolated Or adXactSerializable:
sLevel = "All transactions are isolated from each other."
End Select
' display isolation level message
MsgBox sLevel
con.Close
Set con = NothingThe IsolationLevel property is both read- and write-enabled, but it does not take effect until you call the BeginTrans method of the Connection object. It is possible that the data provider will automatically change the level of isolation when it cannot establish the level requested. In such a case, the level will be changed to the next higher level of isolation.
Get ADO: ActiveX Data Objects now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
