Microsoft KB Archive/309544

= BUG: BeginTransaction changes the isolation level for subsequent transactions =

Article ID: 309544

Article Last Modified on 3/13/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1
 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft .NET Framework 1.1

-



This article was previously published under Q309544



SYMPTOMS
When you call the SqlConnection.BeginTransaction method and specify an isolation level in the IsolationLevel property, after the transaction is committed or rolled back, the IsolationLevel property is not reset to the default of IsolationLevel.ReadCommitted for subsequent commands. This can cause unexpected locking or cause the application to read the wrong version of a record.



RESOLUTION
Use one of the following workarounds to set the isolation level back to the default IsolationLevel.ReadCommitted:  Close and re-open the connection. If you are using connection pooling (which is the default), make sure that you did not specify &quot;connection reset=false&quot; in the connect string. &quot;Connection Reset&quot; is &quot;true&quot; by default.  Run a SQL statement through a SqlCommand object to manually reset the transaction isolation level as follows: [Microsoft Visual Basic .NET]

Dim resetCMD As New SqlCommand(&quot;SET TRANSACTION ISOLATION LEVEL READ COMMITTED&quot;, sqlConn) resetCMD.ExecuteNonQuery [Microsoft Visual C# .NET]

SqlCommand resetCMD = new SqlCommand(&quot;SET TRANSACTION ISOLATION LEVEL READ COMMITTED&quot;, sqlConn); resetCMD.ExecuteNonQuery; [Microsoft Visual C++ .NET]

SqlCommand resetCMD = new SqlCommand(&quot;SET TRANSACTION ISOLATION LEVEL READ COMMITTED&quot;, sqlConn); resetCMD.ExecuteNonQuery; [Microsoft JScript .NET]

var resetCMD:SqlCommand = new SqlCommand(&quot;SET TRANSACTION ISOLATION LEVEL READ COMMITTED&quot;, sqlConn); resetCMD.ExecuteNonQuery;   Call BeginTransaction, and immediately follow this with Commit. The default is to open with IsolationLevel.ReadCommitted; however, you do not have to specify this value explicitly. For example: [Visual Basic .NET]

Dim tempTrans As SqlTransaction = sqlConn.BeginTransaction tempTrans.Commit [Visual C# .NET]

SqlTransaction tempTrans = sqlConn.BeginTransaction; tempTrans.Commit; [Visual C++ .NET]

SqlTransaction tempTrans = sqlConn.BeginTransaction; tempTrans.Commit; [JScript .NET]

var tempTrans:SqlTransaction = sqlConn.BeginTransaction; tempTrans.Commit; 



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Additional query words: IsolationLevel kbreadme

Keywords: kbtshoot kbbug kbsqlclient kbreadme kbpending KB309544

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.