Managing Multiple Transactions With ActiveX Data Objects

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

' 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 = 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.