Microsoft KB Archive/215520

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

Article ID: 215520

Article Last Modified on 5/13/2003

-

APPLIES TO


 * 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

-



This article was previously published under Q215520



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.Connection Dim rs As New ADODB.Recordset

cn.ConnectionString = "DSN=Pubs; PWD=sa" cn.Open cn.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.Connection Dim rs As New ADODB.Recordset

cn.ConnectionString = "DSN=Pubs; PWD=sa" cn.Open

rs.ActiveConnection = cn rs.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.

