sp_reset_connection doesn't reset isolation level by Oyster

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.

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.

This is crazy, and completely breaks separation of concerns. Please fix it!

This is very dangerous. Fix it!

"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.

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

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

How can i obtain de file attachment of this issue? Thanks!

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.

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.

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.