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.
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.
Example 4-14. Testing for Transaction Support:-
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 Function
Canceling 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.
Example 4-15. 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 Sub
Nesting 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.
Example 4-16. 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 = Nothing
Setting Transaction Options:-There are two types of options that we can specify when using transactions through ADO:
Attributes:-
The Attributes property specifies the automatic creation of new transactions. By using the Attributes property of the Connection object, we can define whether new transactions are created when the current one has ended.
IsolationLevel:-
By setting the value of the IsolationLevel property, you can determine whether the current transaction can read the changes that are as of yet not committed by another transaction.
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.
Table 4-5. The XactAttributeEnum Values of the Attributes Property
|
Constant
|
Value
|
Description
|
|
none
|
0
|
Default. Indicates that neither of the following constants
have been chosen.
|
|
adXactCommitRetaining
|
131072
|
Indicates that a new transaction will be created after the
CommitTrans method is called.
|
|
adXactAbortRetaining
|
262144
|
Indicates that a new transaction will be created after the
RollBackTrans method is called.
|
You can use both the adXactCommitRetaining and the adXactAbortRetaining constants at the same time, as shown in Example 4-17.
Example 4-17. Using the Attributes Property with Multiple Constants:-
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
' commit transaction #1, start transaction #2
con.CommitTrans
' rollback transaction #2, start transaction #3
con.RollbackTrans
' 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 = Nothing
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.
Table 4-6. The IsolationLevelEnum Values
|
Constant
|
Value
|
Description
|
|
adXactUnspecified
|
-1
|
Indicates that the data provider is using an isolation level
that cannot be determined.
|
|
AdXactChaos
|
16
|
Indicates that you cannot write over changes that have been
made by higher level transactions.
|
|
AdXactBrowse
|
256
|
Indicates that you can view changes that have not yet been
committed by other transactions.
|
|
adXactReadUncommitted
|
256
|
Same as adXactBrowse. Kept for compatibility with earlier versions of ADO.
|
|
adXactCursorStability
|
4096
|
Default. Indicates that you can only view changes from other transactions
once they have been committed.
|
|
adXactReadCommitted
|
4096
|
Same as adXactCursorStability. Kept for
compatibility with earlier
versions of ADO.
|
|
adXactRepeatableRead
|
65536
|
Indicates that from one transaction, you cannot see changes
that have been made in other transactions until they are committed, but you
can requery the data source to see newly created records.
|
|
AdXactIsolated
|
1048576
|
Indicates all transactions are completely isolated from each
other.
|
|
adXactSerializable
|
1048576
|
Same as adXactIsolated. Kept for compatibility with earlier versions of ADO.
|
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.
Example 4-18. Using the IsolationLevel Property:-
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 = Nothing
The 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.
