This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
he declarative programming model provided by COM+ and Microsoft® Transaction Services (MTS) makes it easier for programmers to build transactional applications. Since these technologies were introduced, however, many developers have taken the plunge before really understanding how to build an efficient transactional system properly. As a result, many have become discouraged after building applications that run sluggishly under MTS. It's not because MTS and COM+ are poorly designed. Rather, many people do not seem to want to design for the transaction. Instead, they would rather stick to what they're comfortable with and continue designing objects around the problem domain instead of around the transaction.
Designing Objects for the Problem Domain
Take a look at Figure 1. The flow of execution in this diagram operates as described in the following steps:
How does this look in terms of basic design to you? At quick glance you should see that I have used the middle tier to perform all the necessary business logic of the application. The business logic does not live inside the database in a stored procedure. So I am free to change the database from SQL Server to Oracle or something else if I want to, thus avoiding a lot of problems migrating stored procedures from one database to another.
Stored Procedures and Components
Many current systems already have lots of stored procedures in them, and many of these stored procedures have been written with transactions. If your system uses transactional stored procedures and you want to invoke those stored procedures from transactional MTS and COM+ objects, what will happen? For example, say I have an MTS and COM+ component. It is marked as Transaction Required, and it calls a stored procedure that starts a transaction via BEGIN TRAN. The component then performs some inserts or updates or deletes, and then calls ROLLBACK TRAN. Does the MTS and COM+ transaction get aborted? Is calling ROLLBACK TRAN in a stored procedure similar to calling SetAbort in a transactional component?
Lowering the Isolation Level
When I talked about calling from a transactional MTS and COM+ component to a SQL Server database via ADO, I mentioned that MTS and COM+ automatically move the isolation level to Serializable. This causes the strictest level of locking for all read and write operations when you may not need it. I showed you an object that implemented two interfaces, the read interface and the write interface, and suggested that this should actually be implemented as two different objects since MTS and COM+ only allow you to configure the transaction property on the object. So, if you want the read interface to run at an isolation level of Read Committed, you can't do it by just setting a property in your MTS and COM+ component.
Select au_lname, au_fname from authors (READCOMMITTED)
Lowering the isolation level through SQL allows you to have an object that implements multiple interfaces and is marked Transaction Required in MTS and COM+, but lower the isolation level when you don't need Serializable. This may be preferable to creating two different objects.
An interesting question arises when you lower the isolation level of a connection to something other than Serializable. What happens to the isolation level when the connection is released by an object in one transaction and gets put into the connection pool for some other object to use? After some testing, I found that MTS and COM+ always bump the isolation level back up to Serializable whenever they enlist a connection in a new transaction.
Error Handling with ADO and ODBCMany of you probably started writing database applications long before ADO came on the scene. You used database APIs like Data Access Objects (DAO), Remote Data Objects (RDO), and ODBC. RDO is a set of objects that sits on top of the ODBC layer and allows the programmers working with Visual Basic to call to ODBC via a friendly set of objects. Those of you familiar with ODBC are also used to getting errors back from stored procedures in a rather straightforward way; if the stored procedure raises an error, you can easily catch the error in your code.
When you move to ADO and the native OLE DB provider for SQL Server, SQLOLEDB, the techniques you have used in the past for raising and catching errors is very different from the ODBC mechanism. Of course, you can make ADO work like your old ODBC code by simply employing the ODBC provider for ADO, MSDASQL. But if you use the native SQLOLEDB provider, you will need to watch out. The MSDASQL provider allows you to use the ADO objects instead of RDO while still calling through the ODBC layer. I know this discussion of ADO and ODBC may seem way off-topic from the rest of this column, but it really is important since a lot of you use SQL Server and ADO for data access.
If you call the following simple stored procedure (which simply raises an error) from an ADO component that uses the SQLOLEDB or MSDASQL providers, you will always catch the error from the calling component:
CREATE PROCEDURE spRaiseError AS raiserror ('Some generic error string',16,1)
Private Sub cmdTest_Click() Dim ref As CSQLTx Dim rc As Integer Set ref = New CSQLTx rc = ref.ExecFailedSPROC() End Sub
CREATE PROCEDURE spRaiseError AS begin tran insert into authors(au_id, au_lname,au_fname,contract) values('111-11-1111','Flinstone','Fred',1) rollback tran raiserror ('Some generic error string',16,1)
The creators of the OLE DB specification wanted users to be able to find out very granular information about statement executions. They wanted to provide the client application calling a stored procedure or SQL batch the ability to inspect the success or failure of each statement within the procedure. There is a lot of information specified and available from OLE DB, but the implementation of ADO does not always take advantage of this information. This is why you often get back the generic "Errors Occurred" when really a very specific error occurred, but it was just not propagated back through the ADO layer.
In the modified stored procedure shown earlier, more than a single statement is executed. Each statement will return a recordset to ADO. This means that the error being raised at the bottom of the stored procedure is returned to the calling component, but that component needs to iterate through all the recordsets passed back to trap the error.
Take a look at the revised code in Figure 4. In this version, I set a recordset for return of the connection's Execute method, and then iterate through each recordset using the NextRecordset method. When I iterate over the recordset that has the error in it, the error handling routine is thrown. If this same code was executed against the MSDASQL provider, I would have trapped the error on the original connection object's Execute method and would not have had to iterate through each recordset. This is important because the sample code aborts the transaction and raises an error back to the caller.
Assuming that the caller is a transactional MTS and COM+ component, I would want to know that my work had failed so I could then call SetAbort and return to the client. If I don't iterate the recordsets, I would never know about the error. As a result, I might continue trying to do other transacted work when my transaction is already doomed.
From the June 2000 issue of MSDN Magazine.