Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
BUG: BeginTransaction() changes the isolation level for subsequent transactions
This article was previously published under Q309544
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.
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
"connection reset=false" in the connect string. "Connection Reset" is "true" 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("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn)
resetCMD.ExecuteNonQuery()
[Microsoft Visual C# .NET]
SqlCommand resetCMD = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn);
resetCMD.ExecuteNonQuery();
[Microsoft Visual C++ .NET]
SqlCommand resetCMD = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", sqlConn);
resetCMD.ExecuteNonQuery();
[Microsoft JScript .NET]
var resetCMD:SqlCommand = new SqlCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED", 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();
Microsoft has confirmed that this is a bug in the Microsoft
products that are listed at the beginning of this article.
Article ID: 309544 - Last Review: March 13, 2006 - Revision: 1.5
APPLIES TO
- Microsoft .NET Framework 1.1
- Microsoft ADO.NET (included with the .NET Framework)
- Microsoft ADO.NET 1.1
| kbtshoot kbbug kbpending kbreadme kbsqlclient KB309544 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate