Home Dashboard Directory Help
Search

sp_reset_connection doesn't reset isolation level by Oyster


Status: 

Closed
 as By Design Help for as By Design


29
1
Sign in
to vote
Type: Bug
ID: 243527
Opened: 12/5/2006 5:59:59 AM
Access Restriction: Public
1
Workaround(s)
view
15
User(s) can reproduce this bug

Description

"sp_reset_connection" MS SQL Server 2005 internal stored procedure doesn't reset connection isolation level to the default one (which is "Read Committed"). This SP is executed for connection from connection pool (ADO.NET connection pool, for example) before it's reopened. As a result, for newly created SqlConnection object isolation level is undefined (it may depend on previous DB activity if connection was obtained from connection pool).
Details
Sign in to post a comment.
Posted by aqx on 3/6/2014 at 3:24 PM
It's obvious that you cannot change sp_reset_connection's default behavior to not break existing code, but you can introduce additional parameter like "sp_reset_connection @reset_isolation_level=1" (with default of 0).
Posted by marsovo on 10/14/2013 at 9:29 AM
This is utter madness. If you have an existing codebase it becomes basically impossible to add transactionality without having to change the entirety of it. How can changing this nondeterministic current behavior possibly be more risky than it currently is? The current behavior is dangerous and unexpected. To wit:

http://stackoverflow.com/questions/14957330/
http://stackoverflow.com/questions/18146860/
http://stackoverflow.com/questions/9851415/
http://stackoverflow.com/questions/3759897/
http://stackoverflow.com/questions/2425550/
http://www.9nit.com/sql-server/sql-server-isolation-level-leaks-across-pooled-connections-77437.html
Posted by xor88 on 3/10/2013 at 3:03 AM
I understand that this behavior might not be changable for compatibility reasons. As this behavior is obviously not what customers want I suggest adding a way of configuring this. Either a flag in the connection string, or a server option or a SQL login option or a server trace flag.

I suggest that you at least run this suggestion by an engineer. Chances are they will say that this is a low-cost, low-risk fix with great benefit.
Posted by shlomiw on 12/23/2012 at 12:59 AM
This is horrible! no other way to put it. Just horrible! I was so shocked to discover this!
This is not 'by design' feature - this is a 'bug design'!
It should be, no, it MUST be, configurable.
Posted by Andomar on 7/23/2012 at 2:04 AM
This issue results in "mystical" errors in production software. Detecting and fixing this is outside the reach of even an expert client side developer. It's hard to disagree with the other posters who refer to this situation as "crazy".

Given that native client uses "connection isolation level as part of connection pooling hash key", why not extend this behavior to the ADO.NET client to do the same? A new ADO.NET connection could always use the "read committed" pool.
Posted by Sergey Barskiy on 4/19/2012 at 4:48 AM
I cannot say I agree with the response that the best practice is to always set isolation level when connection is open. If I just want to issue a Select query with default server isolation level, why should I ever need to set isolation level explicitly just because this connection was serializable at some point for totally different reason and totally different process?
That does not make any sense to me.
Thanks.
Posted by corphicks on 9/29/2011 at 2:38 AM
I agree with WaldenL, there should be a setting to disable this "by design" behaviour. Rewriting all transactions in a big software to set transaction isolation every time despite there's a database-level default is a ridiculous recommendation for a workaround.
Plus, this irregularity in funcionality is widely undocumented in BOL, and in official documentation resources. Thus, waving people away with this "by design" talk is highly unfair.
Posted by Ben Challenor on 2/4/2011 at 5:44 AM
This is crazy, and completely breaks separation of concerns. Please fix it!
Posted by Joshua25640735 on 1/14/2011 at 9:18 AM
This is very dangerous. Fix it!
Posted by WaldenL on 5/4/2010 at 10:58 AM
"By Design" or not we need a way to prevent this behavior. Perhaps a connection-string option "Reset-Isolation=true" or something to that effect. Preserving the isolation level can cause something as simple as a "Select" to deadlock with an update if the select is (unintentionally) run in a serializable isolation level. You can specify the isolation level on a begin transaction, but not on a simple connection open, so you cannot reset the isolation level without an additional roundtrip to the server.
Posted by Rob Schoedel on 3/25/2010 at 2:14 PM
Evgeny,
I'm researching a similar issue. I *think* what is happening is that because your code is requesting new connections, you get a new connection the first 7 times. Then after 30 seconds, the first connection sees that it's not being used anymore, so the pool releases it for your next connection request #8. But every time it releases a connection for re-use, it issues a sp_reset_connection. And continuing connection request #9 is filled by connection #2, etc. The article below gives some insight on the type of cleanup done by sp_reset_connection.

http://stackoverflow.com/questions/596365/what-does-spresetconnection-do

-Rob
Posted by EvgenyV on 1/3/2010 at 7:25 AM
Hi!
I'm using MSSQL 2008 now.

Please help me to understand DB server behaviour. sp_reset_connections.
There is a following test code

            try
            {
                for (int i = 0; i < 200000; i++)
                {
//System.Data.SqlClient.SqlConnection.ClearAllPools();

                    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(@"Password=aaa;Persist Security Info=True;User ID=sa;Initial Catalog=LOCAL;Data Source=my_laptop");
                    conn.Open();
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted, "SSS");
                    cmd.Transaction = trans;
                    cmd.CommandText = String.Format("select * from actgr");
                    DataSet ds = new DataSet();

                    System.Data.SqlClient.SqlDataAdapter adapt = new System.Data.SqlClient.SqlDataAdapter(cmd);

                    adapt.TableMappings.Add("Table", "Table_0");

                    adapt.Fill(ds);
                    System.Threading.Thread.Sleep(5000);
                    trans.Commit();
                }

            }
            catch (Exception ex)
            {

                ReportError(ex);
            }

First 6-7 iterations (or ~30 seconds mey be?) I see following DB trace result:
-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
-----------
BEGIN TRANSACTION SSS
select * from actgr
COMMIT TRANSACTION

.... and then rest of iteratios
Audit logout
exec sp_reset_connection
-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read uncommitted
-----
BEGIN TRANSACTION SSS
select * from actgr
COMMIT TRANSACTION

The questions:
1. Why the first 6 iterations still running in mode READ COMMITTED inspite of fact I've explicitly opened transaction as READ UNCOMMITTED
2. Why after about 30 seconds DB server decided to reset connection and allows me to run command in mode I actually requested from the beginning. It looks very-very strange...
Actually as I understood it is "by design" when getting some connections from connection pool. I have no options to control the isolation level using connection string.

If i'm uncommenting the first line in the loop System.Data.SqlClient.SqlConnection.ClearAllPools();
I NEVER getting requested READ UNCOMMITTED mode. It always running as READ COMMITED

How can I solve all these the probles?

Thanks in advance,
Evgeny
Posted by Federico Bridger on 8/21/2008 at 8:18 AM
How can i obtain de file attachment of this issue? Thanks!
Posted by Microsoft on 12/21/2007 at 11:39 AM
Thank you for your feedback.

Regarding your question:
>>>>But what about side-effects? Do you think it's OK when isolation level set for one SqlConnection (using "SET TRANSACTION ISOLATION LEVEL", ADO.NET transactions or TransactionScope - it doesn't matter) might affect isolation level for another new SqlConnection?

Yes, there is potential side effect for applications that use connection pooling and the transaction isolation level gets changed. As a general best practice, application should explicitly set desired transaciton isolation level when they open a new connection or before they do some transactional operation.

>>> Finally, if this is by design then please at least propose some workaround for this behavior.
The best practice here is to explicitly set transaction isolation level in your application. This is also very useful when some one changed the server default isolation level and your application relies on a particular isolation level.
Posted by Oyster on 12/5/2006 at 11:57 PM
But what about side-effects? Do you think it's OK when isolation level set for one SqlConnection (using "SET TRANSACTION ISOLATION LEVEL", ADO.NET transactions or TransactionScope - it doesn't matter) might affect isolation level for another new SqlConnection?

Finally, if this is by design then please at least propose some workaround for this behavior.
Posted by Microsoft on 12/5/2006 at 12:20 PM
Thank you for using Microsoft SQL Server product. The problem you reported is a by design behavior. The reason server keeps the isolation level for pooled connection is because native client driver is using the connection isolation level as part of connection pooling hash key. The client driver expects the connection to stay in the same isolation level after reset. It will break our backward compatibility if we change this behavior.
Please let us know if you have additional comments.
Sign in to post a workaround.
Posted by marsovo on 10/14/2013 at 9:31 AM
If you are using TransactionScope and manage your connection strings centrally, you can check Transaction.Current and if it isn't null, change the "Application Name" part of the connection string so connections with explicit TransactionScopes won't be shared with others.
File Name Submitted By Submitted On File Size  
ConnectionPoolAndIsolationLevelBug.zip (restricted) 12/5/2006 -