Microsoft KB Archive/295570

= INFO: Transactions and Isolation Levels in COM+ =

Article ID: 295570

Article Last Modified on 5/28/2003

-

APPLIES TO


 * Microsoft COM+ 1.0, when used with:
 * Microsoft Windows 2000 Standard Edition

-



This article was previously published under Q295570



SUMMARY
When a component that is registered with COM+ connects to a database, it starts a transaction with a specific isolation level. Once a transaction is in progress, you can use the SET ISOLATION LEVEL SQL command to change the isolation level. SET ISOLATION LEVEL changes the isolation level for the duration of the transaction. In addition, some resource managers such as Microsoft SQL Server allow you to use optimizer hints to change the isolation level for the duration of a single query. Another way to use a different isolation level is to use nested transactions, which allow you to specify one isolation level for the outermost transaction with different isolation levels for inner transactions. COM+ does not support specifying different isolation levels for nested transactions.



Two Types of Transactions in COM+
COM+ uses the Microsoft Distributed Transaction Coordinator (MS DTC) to create and manage distributed transactions for you. However, local transactions are created and managed by the component code that uses Microsoft ActiveX Data Objects (ADO) or Microsoft OLE DB.

Local Transactions
If you set the transaction attribute of a component to Not Supported or Disabled in COM+, you can use local transactions with ADO by using the BeginTrans, CommitTrans, and AbortTrans methods, as well as similar methods, of OLE DB. In this scenario, COM+ has no effect on the isolation level that is used for this transaction. If you do not use the IsolationLevel property of the ADO Connection object to specify an isolation level, the resource manager specifies the default isolation level, which is Read Committed for both SQL Server and Oracle. If you do set the Connection object's IsolationLevel property, remember that this sets the starting isolation level of the next transaction that you begin.

Distributed Transactions
If you mark your component's transaction attribute as Required, Supported, or Requires New, COM+ creates a transaction for you. When a transaction is created, COM+ must specify the starting isolation level that is to be used for that transaction. In COM+ 1.0, the default isolation level is SERIALIZABLE. In COM+ 1.5, you can specify the default isolation level. You can use the SET ISOLATION LEVEL SQL command or optimizer hints to increase or decrease the isolation level during the transaction.

If you set the ADO Connection object's IsolationLevel property to try to change the isolation level, you do not affect a component in a COM+ declarative transaction because this setting only affects the next transaction that you begin.

Distributed transactions that use the X/Open XA protocol for databases such as Oracle or DB2 do not allow the isolation level to be propagated; thus, the XA resource managers for the different vendors dictate the isolation level behavior.

