Transactions need to be atomic. There was a time in classical physics when atoms were thought to be the absolute smallest constituents of matter, and the term atomic meant all or nothing. To a database, this means that if a sequence of data modification statements are made within the context of a transaction (that is, they occur between a BEGIN TRANSACTION and a COMMIT TRANSACTION statement), they either all succeed or none of them succeed. If four of the five modification statements succeed but the fifth one doesn't, the four that did succeed are rolled back and the database is returned to the consistent state it was in at the time BEGIN TRANSACTION was declared.

No matter what happens in a transaction, data in the database should remain accessible, any partial changes should be rolled back, and any internal data structures the database might use to keep track of the data (memory-cached indexes, for example) must be correct.

Today, we know that the atom is no longer truly atomic—there are smaller elements of matter. Quantum mechanics gave scientists insight into how things work at a sub-atomic level and postulates a theory that might be true for both transactions and sub-atomic physics—an observer must necessarily affect the outcome of what he observes. Therefore, it might sometimes be best in physics and transactions just not to look.

This is what isolation means—Transaction A might not see or influence the data as it is being manipulated by another transaction, Transaction B. Transaction A might see the data before Transaction B operates on it, or after Transaction B commits, or rolls back, but cannot see or influence anything in between. If you are a fan of quantum mechanics, think of a transaction as unwatched particles expanded into their wave probabilities, exploring every possible trajectory at once.

After changes are committed, the changes must be durable. If the power is switched off, the changes must still be there when the power is turned on. In other words, when a change is committed, the transaction is over and the modifications are permanent.

If you make both of these statements an atomic unit of work by wrapping them in a transaction (atomicity), you are assured that data will always be consistent (consistency) because all transactions will be isolated from one another until their unified success or failure (isolation) and that their ultimate modifications will be a permanent, durable part of the database (durability). In other words, rewrite slightly to add the BEGIN TRANSACTION and COMMIT TRANSACTION statements to produce the following:

BEGIN TRANSACTION

Insert into cash
(
explanation,
debit
)
VALUES
(
'payment to XYZ corp. for widget',
100
)

Insert into accounts_payable
(
explanation,
credit
)
VALUES
(
'payment to XYZ corp. for widget',
100
)

COMMIT TRANSACTION

Relational databases typically implement transactions (and other features, such as replication) by keeping log files. Log files record all data modifications made to the database. In the simplest implementation, when a DBMS encounters BEGIN TRANSACTION, it writes all data modifications from that point on to the log. In this way, if it encounters a ROLLBACK command, it systematically works backward through the log and undoes any changes that have been made up until the BEGIN TRANSACTION is reached. If a system crash occurs, the DBMS notices upon restart that the transaction did not complete and likewise undoes all changes. If, on the other hand, the database encounters a COMMIT statement, the changes are made permanent, the log may be purged (or truncated), and the transaction is closed out.

This is a simplified case. Logging strategies differ widely among database systems—some keep a perpetual log for replication and transactional purposes, others truncate their log files after a transaction is complete. Whatever they do, however, is implementation (and setting) dependent and need not concern you. It is the transaction that is important.

So, transactions are relatively straightforward when you have one database. Any user can, at any time, group a series of data-modification statements between a BEGIN TRANSACTION and COMMIT and rest assured that all SQL statements will either all succeed or all fail atomically. Suppose, however, that a developer wants to have a transaction that spans multiple database systems, perhaps from different database vendors? Suddenly things become more complex.

A transaction is normally specific to a particular database connection. (The system interprets a broken connection or dead client as a reason to ROLLBACK.) The developer needs a different database connection for each database he wants the transaction to span, but how can he juggle the transaction between these databases over different, isolated connections? Internally, databases from different vendors use entirely different, incompatible mechanisms for handling transactions.

Clearly some form of third-party referee or transaction monitor (TM) is needed to coordinate transactions across multiple databases, and all databases must support some form of industry-standard protocol to communicate with this TM. We discuss the database-to-TM protocols in more detail in the Chapter 9, "Compensating Resource Managers." For now, let's take a look at Microsoft's transaction manager, the Distributed Transaction Coordinator (DTC).

This protocol centers around something called escrow. I am simplifying somewhat, but escrow can be thought of as a neutral third party acting as a temporary holder of assets and a kind of transaction referee. The way it works is—if you agree to buy my house, we have effectively agreed to enter into a transaction together. For you to buy my house, you need to give me money, and I need to give you the title. Sounds simple, but a lot can happen in between.

If you give me the money first, but I don't give you the title after depositing your check, the transaction is not complete. However, there isn't much chance that I'm going to return the money, and you will have a very difficult time finding me, I can assure you. So, I am a fugitive of the law, and you are out a lot of money with no house to show for it. There is no way to roll back. On the other hand, if I give you the title to my house first, you might move in, change the locks, and never give me my check. I have no legal recourse because, officially, you own the house. Again, we have an aborted transaction leaving one of the transaction's participants out of balance.

The escrow system was developed so that a neutral third party can mediate the transaction and prevent these kinds of problems. In the escrow way of doing things, the buyer and seller agree on a third-party agent, an escrow agent, who has the following responsibilities:

If we use an escrow agent, let's call him David T. Cohen (DTC for short), our transaction is not much more complex but is safer for both parties. You and I agree to engage DTC or, in database terminology, you and I enlist in the transaction with DTC.

Each of us is hoping for a permanent change of our fortunes—you want the title, and I want the money—but we need to let the DTC manage this process. Normally, in real estate, the buyer and seller do not have any direct contact with one another; it is all done through an escrow attorney.

At this point, the following two phases must occur before DTC is happy and allows the house to change title from me to you:

During the Prepare and Commit phases, in broker's lingo, the house is said to be under contract. This means no one else can bid on it or deliver another offer to the seller. What's more, specific information about the transaction is not made available to parties outside of the transaction until it is concluded. This provides the same effect isolation does according to ACID.

The DTC is a busy guy, and he only waits around for a set period of time when listening for a response from the buyer or a seller. During thePrepare phase, if the buyer or seller does not contact the DTC for some set period of time, the DTC calls off the whole transaction, and he notifies all parties that the deal is off.

If both parties indicate that they are prepared, the DTC then asks both parties to commit. If he doesn't hear from someone for some set period of time thereafter (maybe the seller slipped in the shower), the DTC becomes uncertain about the entire transaction. There is still the possibility of a reconciliation, however, after the seller regains consciousness and checks in with the DTC.

If both parties successfully complete the Prepare andCommit phases, the DTC's job is done.

Like all COM+ services, the mystery surrounding transactions quickly dissipates if you understand the underlying internal processes. In this spirit, then, I propose the following scenario: Process A wants to make a data modification on Database 1 (DB1) and Database 2 (DB2). These two database modifications are, however, related, and must succeed or fail together. A distributed transaction is needed to span the two databases.

It is the responsibility of the client application to request the assistance of the DTC and enlist all the database connections it holds in the distributed transaction. The exact mechanism differs depending on what database API the client is using. Generally, there are three steps:

Steps 1 and 3 seldom vary, but step 2 differs, again, depending on what client-side database access technology you are using. If Open Database Connectivity (ODBC) is being used, Listing 8.1 demonstrates how to use the DTC to create a distributed transaction.

//This is a C++ client application that has two database connections
//on two different database servers on different machines.

//It demonstrates how to use the DTC to create a distributed transaction
//that spans both databases.

//Also note that there will actually be two DTCs involved. The DTC
//we are contacting here will be the controlling DTC and it will
//automatically coordinate with the DTC on the second machine
//behind the scenes.

ITransactionDispenser *pTransactionDispenser;
ITransaction          *pTransaction;
HRESULT hr;

//below: perform ODBC style login
//and put valid connection handles
//into gCon1 and gCon2
MyDBLogon(&gCon1, "server1", "sa", "sasparilla");
MyDBLogon(&gCon2, "server2", "sa", "humtulumpus");
//Step 1
hr = DtcGetTransactionManager(
                       NULL, //get the DTC on the local machine
                       NULL,
                       IID_ITransactionDispenser,
                       0,
                       0,
                       NULL,
                       (void **)&pTransactionDispenser
                       );

hr = pTransactionDispenser->BeginTransaction(
                       NULL,
                       ISOLATIONLEVEL_ISOLATED,
                       ISOFLAG_RETAIN_DONTCARE,
                       NULL,
                       &pTransaction
                       ) ;

//Step 2 in ODBC:
SQLSetConnectOption (gCon1->hdbc,
                       SQL_COPT_SS_ENLIST_IN_DTC,
                       (UDWORD)pTransaction);

SQLSetConnectOption (gCon2->hdbc,
                       SQL_COPT_SS_ENLIST_IN_DTC,
                       (UDWORD)pTransaction);

//execute on database 1
MyExecuteStatement(&gCon1,"delete from current_customer where custid=22");
//execute on database 2
MyExecuteStatement(&gCon2,"delete from legacy_customer where custid=22");

// Step 3: Commit the distributed transaction
hr = pTransaction->Commit(0,0,0);

pTransaction->Release();
pTransactionDispenser->Release();

//deallocate ODBC handles

The following are the basic steps outlined in the code:

This general flow is always the same for client applications using the DTC directly. Obviously, the client applications need to be able to use COM, but their interaction with the DTC is pretty straightforward—request a couple of transaction-oriented interfaces and call their methods. Only the enlisting of the transaction differs significantly, depending on what database technology your client uses.

If your client is using ODBC, its database connections are enlisted in transactions via the SQLSetConnectOption() function:

SQLSetConnectOption (gCon2->hdbc,
                     SQL_COPT_SS_ENLIST_IN_DTC,
                    (UDWORD)pTransaction);

If your client application is not using ODBC, the enlistment process is different. For example, if you are only using Microsoft SQL Server databases and choose to use SQL Server's native database library,dblib, you enlist your transactions this way:

dbenlisttrans (dbp, pTransaction);

There are, of course, differences between dblib and ODBC in terms of how database connections are made and SQL statements are executed. There is one important thing to note while we are on the subject of dblib—unlike ODBC, dblib does not support the automatic transaction enlistment that gives rise to COM+ transactions. dblib cannot participate in implicit COM+ transactions because it does not support the interfaces necessary to coordinate with the COM+ Dispenser Manager.

We talk more about the Dispenser Manager in the upcoming section, "Resource Dispensers: A First Look." For now, you need only understand that although COM+ objects can use dblib to connect with SQL Server, they do not get automatic, context-based transaction support. It is best to stick with ODBC.

// This method enlists the current transaction with the DTC.  COM+
// usually does this implicitly when an object is created, but in the
// case of a pooled object that changes transactions without ever being
// destroyed, re-enlistment must occur.  This involves:
//
// 1. Obtaining an ITransaction pointer from IObjectContextInfo::GetTransaction
// 2. Using the ITransaction pointer to enlist in the transaction with the DTC.
//
// This code was adapted from the account.vc example, included in the Platform SDK.

HRESULT PooledObject::EnlistDTC() {

// Error checking omitted for brevity:

HRESULT hr;
IObjectContext * pIObjectContext;
IObjectContextInfo *pIObjTx;

// Obtain IObjectContext and IObjectContextInfo interfaces:

hr = CoGetObjectContext(IID_IObjectContext, (void**)&pIObjectContext);

if (hr!=S_OK) {
    return hr;
}
hr = pIObjectContext->QueryInterface(IID_IObjectContextInfo, (void **)&pIObjTx);


if (SUCCEEDED(hr))
{
    // Retrieve the ITransaction pointer from IObjectContextInfo
    ITransaction *pITx;
    RETCODE rc;

    hr = pIObjTx->GetTransaction ((IUnknown **)&pTx);

    if (SUCCEEDED(hr)) {

        // Enlist with the DTC.  This is a demonstration of
        // enlistment using ODBC - it will differ depending
        // on the Resource Manager the component is using.
        // In this case, we are using
        // an OLE-DB provider on top of ODBC.

        rc = SQLSetConnectOption(m_hdbc, SQL_ATTR_ENLIST_IN_DTC, (UDWORD)pTx);

        if ((rc) != SQL_SUCCESS && (rc) != SQL_SUCCESS_WITH_INFO) {

            hr = E_FAIL;
        }

        pITx -> Release();
    }

    pIObjTx -> Release();
}

pIObjectContext->Release();
return hr;

}

If you have never worked with distributed transactions before, you might be surprised at the relative simplicity of the preceding code. All the real difficulty involved in coordinating distributed transactions is handled by the DTC and the database systems themselves. For an in-depth look at the DTC, consult the book's sample code (http://www.newriders.com/complus) on Resource Managers. Among other things, this code demonstrates how DTCs on different machines communicate with one another.

Regardless of how the DTC performs its magic on the lowest level, at the highest level all the developer needs to do is inform the DTC of his intentions using a couple of simple interfaces (ITransaction, ITransactionDispenser). Then, with only a couple of additional Win32 API calls (DtcGetTransactionManager, SQLSetConnectOption, or dbenlisttrans), the functionality comes for free.

Keep in mind that all the distributed transaction functionality described here has been around since SQL Server 6.5 shipped, and you can see that COM+ does not add much. COM+ simply leverages the capabilities of the DTC and the transactional capabilities of most relational databases. COM+ does provide, however, a simplified, more abstracted method of using this capability without requiring the object developer to be familiar with the DTC. COM+ objects that participate in transactions do not need to call any Win32 API functions, nor do they ever need to request and manipulate any DTC-specific interfaces in the manner demonstrated in Listing 8.1.

Let's assume that you have two configured COM+ objects, A and B, both of which are going to modify data on two different databases. A creates B. First, how does COM+ know that these two objects are transactional? Second, how does COM+ know that the two objects are related and will be working together under the umbrella of one distributed transaction?

The answer to the second question is COM+ does not know that these objects will be working together. And the answer to the first question explains why COM+ doesn't need to know—both objects are configured in the COM+ catalog as one of the following, shown in Figure 8.1:

By setting the configuration options for objects A and B, COM+ knows how the objects are to behave in all transactional scenarios. Again, you are confronted with COM+'s declarative model. A functional mindset writes A and B so that they work with a priori knowledge of one another and programmatically enlist their transactions with the DTC. This is in sharp contrast to the COM+ declarative mindset that dictates that you state how the objects will behave in any situation.

We trust that COM+ can handle any discontinuity between objects whose configured attributes conflict by using interceptors or by disallowing the cooperation altogether.

In this case, assume A is configured as Requires New and B is configured as Supported. These settings allow the transaction to flow from A to B. The stage for the distributed transaction is now set. The actual source code for A and B is in Listing 8.3 and 8.4.

Sub CreditAccount()

'A reference to the component that will Debit Accounts:
Dim DebitComponent As ComponentB

'An ADO Connection object to perform database manipulation:
Dim adoConn As New ADODB.Connection

'If we encounter any problems abort the transaction:
On Error GoTo Problem

'Obtain an instance of ComponentB, and tell it to perform its
'operation (debit the account):
Set DebitComponent = New ComponentB
DebitComponent.DebitAccounts

'Assuming the operation above proceeded smoothly, credit the
'appropriate account:

'Establish a connection with SQL Server:
adoConn.Open "Driver={ SQL Server} ;Server=Tetley;Database=CreditAccount"

'Add 10% to the account balances in the CreditAccount Database:
adoConn.Execute "UPDATE CreditAccountTable SET AccountBalance =" & _
                (AccountBalance * 1.1)", Connection, _
                 adOpenDynamic, adLockOptimistic
adoConn.Close

'Let COM+ know everything went smoothly by calling SetComplete:
GetObjectContext.SetComplete
Exit Sub

Problem:
'There was a problem, so tell COM+ to abort the transaction
'by calling SetAbort:
GetObjectContext.SetAbort

End Sub

Sub DebitAccounts()

'ADO Data objects we will be using:
Dim adoConn As New ADODB.Connection

'If we encounter any problems, we will abort the transaction:
On Error GoTo Problem

'Establish a connection with SQL Server:
adoConn.Open "Driver={ SQL Server} ;Server=Tetley;Database=DebitAccount"

'Subtract 10% to the account balances in the DebitAccount Database:
adoConn.Execute "UPDATE DebitAccountTable SET AccountBalance =" & _
                 "(AccountBalance / 1.1)", _
                  Connection, adOpenDynamic, adLockOptimistic

adoConn.Close

'Let COM+ know everything went smoothly:
GetObjectContext.SetComplete
Exit Sub

Problem:

'There was a problem, so tell COM+ to abort the transaction
'by calling SetAbort:
GetObjectContext.SetAbort

End Sub

As you can see, we are using ActiveX Data Objects(ADO) for database manipulation. ADO is the preferred database access technology for a number of reasons outlined in Appendix A, "ADO and OLE-DB," but RDO or the direct ODBC API also can be used.

Notice that neither A nor B do anything other than san update statement to the database for which they have a connection. They do not explicitly engage the DTC, and they don't share any interfaces or other data. Both A and B do call SetComplete() (or SetAbort() if they encounter an error), but other than that, A simply creates B in an ordinary COM fashion.

If A completes its data modifications and calls SetComplete() but B calls SetAbort() (or has an access violation, hangs indefinitely, or so on), all the changes for A are ultimately rolled back. The reverse is also true—if B succeeds but A fails, the data modifications for A and B are undone as the distributed transaction is aborted.

It is very important to realize, however, that SetAbort() and SetComplete() do not initiate any kind of immediate ROLLBACK or COMMIT action at the time they are called. It is tempting to think of SetAbort() as triggering an immediate RDBMS ROLLBACK statement and SetComplete() as firing a COMMIT statement, but as you see in the upcoming section, "COM+ Transaction Behavior: Voting," this is not the literal truth. That said, conceptually at least, this line of reasoning is not far off—a SetAbort() call probably does result in a ROLLBACK (and SetCommit() results in a COMMIT) statement to be issued down the line, but it doesn't happen at the moment SetAbort() or SetComplete() are called. In the section "COM+ Transaction Behavior: Voting," the reasons for this delayed committing are made clear.

Returning now to the code in Listing 8.3, clearly A and B are sharing in a distributed transaction mediated by the DTC, but how is COM+ making this happen? This question can be answered in multiple parts. This first part has to do with Resource Dispenser(RD).

The concept of a RD is simple—a Dynamic Link Library (DLL) that creates and hands out resources such as database connections, threads, or live connections to any resource. RDs are, in themselves, architecturally interesting. In the book's Resource Manager (RM) source code, you find an example of how they operate and interact with other components of COM+.

As I've said, an RD is a DLL that hands out resources. Database connections are resources, and in fact, the ODBC driver manager is a COM+-compatible RD. This means that no matter what database access technology A and B use (ADO, RDO, ODBC API) to get an ODBC connection, they ultimately come into contact with the ODBC driver manager/RD. However, to support COM+ transactions, an RD must do more than simply hand out database connections to requesting objects. Specifically, the RD must be able to look at the context of the object to determine if that object is in a transaction. If the object is in a COM+ transaction, the RD must enlist the connection it gives to the object with the DTC.

The ODBC driver manager is an RD, but RDs cannot investigate the context of objects requesting resources from them on their own. There is another COM+ entity called the Dispenser Manager (DispMan) that has precisely this job. When an RD is first called upon by a client, the RD registers itself with DispMan (all RDs must if they want to propagate transactions). After DispMan and the RD have established a relationship (they hold interfaces to one another), they work together—the RD tells DispMan that a client object has requested a connection, and DispMan investigates the context of the requesting object on behalf of the RD and tells the RD whether to enlist the connection in a distributed transaction with the DTC.

The relationship between DispMan, the ODBC driver manager, and other RDs is interesting, complex, and worthy of detailed discussion. In fact, the Resource Manager example included with the book's source code is dedicated to exploring the roles and interrelationships of RDs, RMs, and DispMan. For now, realize that DispMan and the driver manager work together to determine whether there is a transaction in the object's context and, if so, enlists the database connection with the DTC. This is how COM+ transactions occur.

Now that you understand the basic mechanisms underlying COM+ transactions, you can learn the behavior of objects that participate in them.

I have said that objects can vote on the outcome of a transaction by calling SetAbort() or SetComplete() on the IObjectContext interface (introduced in Chapter 7) they obtain from COM+. The question then arises, when are the results of this vote tallied?

Contrary to intuition, a distributed transaction is not necessarily rolled back the moment an object calls SetAbort(). In this way, SetAbort() is not like ROLLBACK TRANSACTION which has an immediate effect on a relational database. For an object, SetAbort() initiates no action whatsoever—all it does is flip the object's commit bit (this bit is in the object's context) to 0. This bit only becomes significant when COM+ systematically checks the status of all participating objects. This occurs when the root object (the object that originated the transaction) has completed its work and is deactivated.

COM+ attempts to commit a transaction when the root object that originated the transaction deactivates. The root object might deactivate when the object has called SetAbort() or SetComplete() (or even SetDeactivateOnReturn(TRUE)) and has returned from a method call to its client. As you see in the next section and all subsequent sections of this chapter, calling these methods flips one or both status bits in the object's context. Although these status bits can let COM+ know that the root object is allowed to be deactivated, they don't directly cause the object to deactivate. Another entity known as JITA (Just-In-Time Activation) does this.

In the preceding case, COM+ attempts to commit a transaction when the root object has completed its work and is deactivated. Somewhere up the creation chain, however, there must be some client EXE that created the root object. This brings about a third scenario where COM+ can try to commit a transaction: The client EXE itself calls SetAbort() or SetComplete().

ITransactionContextEx *m_pTransactionContext
CoCreateInstance(CLSID_TransactionContextEx, NULL, CLSCTX_INPROC,
                 IID_ITransactionContextEx,
                (void**)&m_pTransactionContext);

Dim myTranContext As New TransactionContextEx

Sub DataModify()

'If we encounter any problems, abort the Transaction:
On Error GoTo Problem

<DO SOMETHING WITH DATABASE>

'if an error occurs in VB, control jumps
'to the error block defined in the "On Error" statement.

'if we get to this portion of the code, then
'everything proceeded smoothly.  We indicate this
'to COM+ by calling SetComplete().

'Note that this DOES NOT mean that the transaction will
'commit.  COM+ must tally ALL components particpating in
'the transaction and get their approval before it
'commits.

GetObjectContext.SetComplete
Exit Sub

Problem:

'Let COM+ know there was a problem by calling SetAbort.

GetObjectContext.SetAbort

End Sub

A transactional object can get all it needs from the IObjectContext interface. However, IObjectContext is kind of a blunt instrument when it comes to setting an object's Consistency Happy and Done bits. You might have noticed IObjectContext offers no way to change the consistency or Done bit independently. Each method in this interface sets both bits at the same time.

Another drawback to IObjectContext is that all four of its voting and duration methods assume that you are in a transaction; they all manipulate the consistency bit, as well as the Done bit. Suppose, however, that your object is not in a transaction but wants to flip its Done bit to indicate to JITA that it is ready to be deactivated or wants to stick around. It is true that your non-transactional object still can do this by calling EnableCommit(), DisableCommit(), SetAbort(), or SetComplete(), but these methods result in the manipulation of the consistency bit (which is not looked at in a non-transactional context) and results in misleading source code. IContextState, however, is a refinement of IObjectContext that lets you set the consistency and Done bit separately. It is also logically simpler and can make for clearer code because it only contains methods that have to do with voting on a transaction or deciding whether to allow deactivation. This is in sharp contrast to IObjectContext with its hodgepodge of creation, voting, and informational methods.

Now that I've made the case for IObjectContext, let's look at itsmethods:

typedef enum {
    TxCommit = 0,
    TxAbort = 1
}  tagTransactionVote;