VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

Systems Analysis - System analysis and Design tutorial for Software Engineering

You are here: Visual Basic > Advanced VB6 tutorial > Chapter 9

Managing Database Transactions

Client-server DBMSs support a feature known as the database transaction to help manage data integrity and (in some cases) improve performance.

A transaction is a set of actions performed on your data that you want to consider as a logical group. You need the concept of a transaction so that all the changes made by a group of actions can stand or fall together. This promotes better data integrity.

The need for database transactions arises when a user or process needs to make multiple changes to the data, and the changes must all stand or fall together as a logical unit. In other words, if only part of the changes end up in the data, the system's integrity will be compromised.

Consider, for example, an operation that attempts to eliminate a customer from the system, along with all the customer's orders and the detail lines for each order. In a relational database system, this will involve at least three tables. Now imagine that the process starts and successfully eliminates the customer's record, as well as some of the order header records. At that point, the server goes down with only part of the process accomplished. When the system comes back up, there will be "orphaned" order header and perhaps order detail records.

The concept of a database transaction helps to avoid situations such as that given in the example. A database transaction defines a group of operations that must stand or fall together. Typically, the following steps occur:

  1. A client process defines the operations that make up a group by beginning a transaction.

  2. When the client finishes all the operations necessary for the transaction, the server then writes or commits all the operations at the same time on a signal from the client. At this point, the transaction is finished.

  3. If something happens in the middle of the process to halt further activity, nothing is committed and the data integrity is preserved.

  4. The client can also explicitly tell the server to abandon or roll back all the operations in a transaction.

In ADO, transactions are implemented with three methods (BeginTrans, CommitTrans, and RollbackTrans) and three events of the Connection object (BeginTransComplete, CommitTransComplete, and RollbackTransComplete), as illustrated in Listing 9.20.

The three transaction methods are described as follows:

  • The BeginTrans method defines the start of a transaction in code. All subsequent data operations on the Connection object will be part of a single transaction until the CommitTrans or RollbackTrans method is called or the connection to the database ends without any action.

  • The CommitTrans method terminates a transaction successfully. All data operations on the current connection that have taken place because of the matching BeginTrans will become permanent in the underlying data.

  • The RollbackTrans method aborts the operations of a transaction. All data operations on the current connection that have taken place because of the matching BeginTrans will be abandoned and will not appear in the underlying data.

In Listing 9.20, the Sub procedure FundsTransfer manipulates data to transfer funds between two accounts. You begin a transaction with the BeginTrans method before manipulating data. If there is an error anywhere in the data manipulation process, you should call Rollback to cancel all changes. Otherwise, call CommitTrans to save all changes.


Public Sub FundsTransfer
On Error GoTo Transfer_Error
'Code to debit first account
'Code to credit second account
Exit Sub
Resume Exit_Transfer
End Sub

You can have more than one transaction pending at the same time on a Connection object. This will work as long as you are careful to pair BeginTrans/CommitTrans/RollbackTrans sequences. These nested transactions will work because a CommitTrans or RollbackTrans method only undoes the actions because the most recent pending BeginTrans method was called.

In Listing 9.21, the programmer has defined three nested transactions. Transaction A is the outer transaction; its zeginTrans and CommitTrans methods contain the others. Transaction B contains Transaction C for the same zeason. Notice which of the transactions each action belongs to.


ConnAccounts.BeginTrans ' start of Transaction A
'actions here are part of Transaction A
ConnAccounts.BeginTrans 'start Transaction B
'actions here are part of Transaction B
ConnAccounts.BeginTrans 'start Transaction C
'actions here are part of Transaction C
ConnAccounts.CommitTrans 'end Transaction C
'actions here are part of Transaction B
IFollowing logic ends Transaction B
If <something is wrong> Then
ConnAccounts.RollbackTrans 'B and C roll back
ConnAccounts.CommitTrans 'B and C are committed
End If
'actions here are part of Transaction A
ConnAccounts.CommitTrans Îend Transaction A

An outer transaction controls whether an inner transaction's CommitTrans will be honored. If the RollbackTrans method is called for an outer transaction, the transactions nested inside it will be rolled back as well, regardless of whether they ended with a CommitTrans or a RollbackTrans.

If an inner transaction is cancelled with RollbackTrans, of course, the inner transaction (along with any other transactions nested further inside it) is rolled back, regardless of whether the outer transaction is committed or rolled back.

Review Listing 9.21 again, and notice that the innermost transaction, Transaction C, terminates with a call to CommitTrans. However, all of Transaction C's actions will be rolled back, along with those of Transaction B, if the code detects that something is wrong when it comes time to end Transaction B. In such a case, the code calls the RollBackTrans method on Transaction B, automatically ignoring the CommitTrans of any transactions nested within it (in this case, Transaction C).

The ADO Connection object also has three transaction-related events that correspond to the completion of their respective like-named methods:

  • The BeginTransComplete event

  • The CommitTransComplete event

  • The RollbackTransComplete event

The BeginTransComplete event receives the following parameters:

  • TransactionLevel As Long - A number telling you where this transaction is in the hierarchy of nested transactions (1 is the highest, or outermost transaction, and the value of the parameter increases for more deeply nested transactions).

  • pError As ADODB.Error - A single Error object that contains information if the value of the following parameter, adStatus, is adStatusErrorsOccurred.

  • adStatus As ADODB.EventStatusEnum - If you examine the event procedure stub for this parameter in VB, you will notice that it is the only parameter that isn't passed ByVal. This is because you are allowed to change it. The original value of the parameter as passed to the event reflects, as its name implies, the status of the attempt to begin, commit, or Rollback a transaction (adStatusOK or adStatusErrorsOcurred). You can set the status in the event procedure's code so that this event does not fire again (adStatusUnWantedEvent).

  • Connection As ADODB.Connection - Points to the Connection object that owns this transaction. Not needed in a VB environment, as each Connection object has its own separate set of transaction events (this is not the case, for example in some C++ environments that use ADO).

The other two transaction events, RollbackTransComplete and CommitTransComplete, do not have BeginTransComplete's first parameter (TransactionLevel), but they do have the remaining three parameters (pError, adStatus, and pConnection).


<< Previous | Content | Next >>

Home | About Us | Privacy Policy | Contact Us

Copyright © | All Rights Reserved