INFO: Transactions and Isolation Levels in COM+

Article translations Article translations
Article ID: 295570 - View products that this article applies to.
This article was previously published under Q295570
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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.

MORE INFORMATION

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.

REFERENCES

For more information, refer to the following MSDN Web site:
Configuring Transactions
http://msdn.microsoft.com/en-us/library/ms687663.aspx
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
215520 INFO: COM+ and MTS Always Use SERIALIZABLE Transaction Isolation Level

Properties

Article ID: 295570 - Last Review: February 27, 2014 - Revision: 2.2
APPLIES TO
  • Microsoft COM+ 1.0, when used with:
    • the operating system: Microsoft Windows 2000
Keywords: 
kbnosurvey kbarchive kbinfo KB295570

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com