Visual Basic

Deciding Where to Put Transaction Scope

Where should transaction scope lie? Who or what will control it and coordinate commits and rollbacks?

What is transaction scope?

With technologies such as DAO, RDO, and ADO, the transactional scope is limited to some database object or other.

With DAO that object is the Workspace object. Transactions are always global to the Workspace object and aren't limited to only one Connection or Database object. Thus if you want multiple transactional scopes you need to have more than one Workspace object.

In RDO, transactions are always global to the rdoEnvironment object and aren't limited to only one database or resultset. So with RDO, to have multiple transaction scopes you need multiple rdoEnvironment objects.

If you want to have simultaneous transactions with overlapping, non-nested scopes, you can create additional rdoEnvironment objects to contain the concurrent transactions.

With ADO, the transactional scope is on the Connection object-if the provider supports transactions (you can check for a Transaction DDL property [DBPROP_SUPPORTEDTXNDDL] in the Connection's Properties collection). In ADO, transactions may also be nested, and the BeginTrans method returns a value indicating what level of nesting you've got. In this case, calling one of the standard transaction methods affects only the most recently opened transaction. But if there are no open transactions, you get an error.

ADO can also automatically start new transactions when you call one of the standard close methods for a transaction. For a given connection, you can check the Attributes property, looking to see if the property has a value greater than zero. It can be the sum of adXactCommitRetaining (131072)-indicating that a call to CommitTrans automatically starts a new transaction, and adXactAbortRetaining (262144)-indicating that a call to RollbackTrans automatically starts a new transaction.

Cooperating components and transaction scope

Regardless of which object provides transactional scope and methods, the salient point is that there is only one such object for each data access library. Whatever the object, all code that needs to cooperate in the same transaction has to gain access to that same instance of the object. This is a small issue in a two-tier architecture, where all the components are on the same machine (and more often than not where there is only one client executable). There are no process boundaries or machine boundaries being crossed, and the place where the transaction-owning object will be instantiated is a no-brainer.

In a tiered system with a number of cooperating components- perhaps on different machines-some means has to be found of passing object references to the shared transaction-owning object. This is one of the reasons why it typically doesn't make the bestsense to build data access code and transaction scope into individual business objects in a tiered system.

Frequently, compromises in design have to be made to allow the passing of this data access object so that a number of business components (which should have no database access specific knowledge or abilities) can cooperate in the same transaction. This is where Microsoft Transaction Server (MTS) solves the problem with a degree of elegance with its Context object and the CreateInstance, SetComplete, and SetAbort methods. Taking a lesson from this, a good design pattern is to create a class with the purpose of acting as shared transaction manager for cooperating components, or else to use MTS to do the same thing.

Such strategies allow the grouping of components on different machines in the same transaction, and moving the point of transactional control to the place where the sequence of interactions is also controlled. Frequently this is at some functional location, quite near to the user interface. (See Chapter 2 for more detailed information about business objects.)

Another issue arises here, namely that of stateful and stateless work. MTS prefers statelessness, where everything can be done to complete a unit of work in as few calls as possible between client and server. However, transactional work is by its very nature stateful and built from a series of interactions. This harks back to the three approaches to client/server interaction we considered earlier. This is also discussed in Chapter 2.