Article ID: 215520 - View products that this article applies to.
This article was previously published under Q215520
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.
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 ConnectionWith 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:
Use Query Hints to Lower the Level on a QueryYou 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:
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.
For more information, see the Data Access section of the COM+ Application Guidelines for Visual Basic at the following Microsoft Web site:
Article ID: 215520 - Last Review: May 13, 2003 - Revision: 2.3