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

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.