INFO: COM+ and MTS Always Use SERIALIZABLE Transaction Isolation Level

This article was previously published under Q215520
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
Database connections that are opened in transactional Microsoft Transaction Server (MTS) or COM+ 1.0 components are enlisted in the activity's Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When COM+ enlists a connection in an MS DTC transaction, the transaction isolation level is set to SERIALIZABLE, which is the highest possible isolation setting. By default, you cannot set MTS/COM+ to a different level.
MORE INFORMATION
When you use SQL Server 6.5, 7.0, and 2000, you can lower the transaction isolation level after you open the connection. This can improve throughput and relieve database blocking problems because it reduces database locking.

However, before you lower the transaction isolation level from SERIALIZABLE, you should carefully consider the implications. The primary issue is that data may change after it has been queried. This presents a problem, for example, when your application updates the data that is returned by a query or uses it to affect an update within a transaction. Therefore, it is recommended that you keep the isolation level at SERIALIZABLE. In some cases, you can reduce the setting to REPEATABLE READ.

One logical exception is a transaction that only does reads. In this case, a lower transaction level improves the throughput without putting the consistency of your database at risk.

Use T-SQL Command to Lower the Level on a Connection

With SQL Server 6.5, 7.0, and 2000 you can use the SET TRANSACTION ISOLATION LEVEL T-SQL command to set the isolation level to a different value for a particular connection. You can do this inside or outside a transactional component at any time. When you run the following Microsoft Visual Basic ActiveX Data Objects (ADO) code fragment in an MTS component, the default SERIALIZABLE transaction level changes to READ COMMITTED for subsequent queries:
Dim cn As New ADODB.ConnectionDim rs As New ADODB.Recordsetcn.ConnectionString = "DSN=Pubs; PWD=sa"cn.Opencn.Execute "Set TRANSACTION ISOLATION LEVEL READ COMMITTED"				

Use Query Hints to Lower the Level on a Query

You can also use query hints to affect the transaction level for a particular query. The following Visual Basic ADO code fragment changes the locking behavior for a particular query to READ COMMITTED:
Dim cn As New ADODB.ConnectionDim rs As New ADODB.Recordsetcn.ConnectionString = "DSN=Pubs; PWD=sa"cn.Openrs.ActiveConnection = cnrs.Open "select * from authors with (READCOMMITTED)"				
For more detailed information on isolation levels and locking, see the SQL Server documentation.

NOTE: Setting the ADO connection object's IsolationLevel property has no effect on a COM+/MTS transaction.
REFERENCES
For more information, see the Data Access section of the COM+ Application Guidelines for Visual Basic at the following Microsoft Web site:
isolation deadlock blocking serializable read commited dirty reads ACID COM+ MTS COMPlus
Properties

Article ID: 215520 - Last Review: 02/21/2014 00:33:29 - Revision: 2.3

Microsoft Transaction Services 1.0, Microsoft Transaction Services 2.0, Microsoft COM+ 1.0, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbdatabase kbdtc kbinfo KB215520
Feedback