none
SQL Server 2014 reseting isolation level

    Question

  • Hi,

    Working with C# application accessing SQL Server 2012 and 2014 using ADO.NET.

    There seem to be a change regarding isolation level reset when returning connection to pool.

    Previous versions of SQL Server does not reset the isolation level on the connection (unfortunately) as claimed here (http://support.microsoft.com/?id=972915).

    However it seems this has changed with SQL Server 2014.

    If running the attached code it shows that the isolation level in fact is reset when running against a SQL Server 2014 instance but not 2012.

    Can anyone on the SQL Server team confirm that this is an intended change that is here to stay? I have not seen any documentation on this so far in the behavior changes documented here: http://msdn.microsoft.com/en-us/library/ms143359.aspx

        class Program
        {
            static void Main(string[] args)
            {
                TestIsolationLevelReset();
                Console.ReadLine();
            }
    
            public static void TestIsolationLevelReset()
            {
                var connectionString = @"Data Source=(local); Integrated Security=true; Initial Catalog=master;";
                var commandText = @"select cast (transaction_isolation_level as nvarchar) + ' - ' + CAST(@@spid as nvarchar) from sys.dm_exec_sessions where (session_id = @@SPID)";
    
                using (var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
    
                    var cmd = new SqlCommand(commandText, conn);
                    Console.WriteLine(cmd.ExecuteScalar());
                }
    
                using (var tran = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.Serializable }))
                using (var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
    
                    var cmd = new SqlCommand(commandText, conn);
                    Console.WriteLine(cmd.ExecuteScalar());
    
                    tran.Complete();
                }
    
                using (var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
    
                    var cmd = new SqlCommand(commandText, conn);
                    Console.WriteLine(cmd.ExecuteScalar());
                }
            }
        }


    • Edited by twurtz Wednesday, September 3, 2014 11:15 AM fixed code sample
    Friday, August 29, 2014 4:24 PM

Answers

  • Yes, in SQL 2014, for client drivers with TDS version 7.3 or higher, SQL server will reset transaction isolation level to default (read committed) for pooled connections. for clients with TDS version lower than 7.3 they will have the old behavior when running against SQL 2014.

    Normally if you use connection pooling, client driver (like SQLClient) will call "sp_reset_connection" against SQL server when client drive pickup connection from pool. In SQL2014 sp_reset_connection call will reset the isolation level to read committed.

    • Marked as answer by twurtz Wednesday, September 3, 2014 11:43 AM
    Wednesday, September 3, 2014 8:40 AM

All replies

  • Hi twurtz,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Thanks,
    Lydia Zhang

    Tuesday, September 2, 2014 2:01 AM
    Moderator
  • Yes, in SQL 2014, for client drivers with TDS version 7.3 or higher, SQL server will reset transaction isolation level to default (read committed) for pooled connections. for clients with TDS version lower than 7.3 they will have the old behavior when running against SQL 2014.

    Normally if you use connection pooling, client driver (like SQLClient) will call "sp_reset_connection" against SQL server when client drive pickup connection from pool. In SQL2014 sp_reset_connection call will reset the isolation level to read committed.

    • Marked as answer by twurtz Wednesday, September 3, 2014 11:43 AM
    Wednesday, September 3, 2014 8:40 AM
  • Thank you for your reply.

    This sounds very good and a change for the better! :)

    Maybe this is documented somewhere but I have missed it?

    Also how do I know what TDS version my client is using?

    Wednesday, September 3, 2014 11:31 AM
  • Also how do I know what TDS version my client is using?

    The client and server negotiates the TDS protocol version to use when the initial connection is established.  The protocol_version column of the sys.dm_exec_connections DMV shows the negotiated version.  The first byte hex value shows the TDS version (e.g. 0x74 is TDS 7.4):

    SELECT SUBSTRING(CAST(protocol_version as binary(4)), 1, 1)
    FROM sys.dm_exec_connections;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, September 3, 2014 12:37 PM
    Moderator
  • Thank you for your reply.

    I was hoping for a bit more static way of knowing this though :)

    For example:

    • .NET4.5 + SQLServer 2014 = TDS version ???
    • .NET4.0 + SQL Server 2014 = TDS version ???

    Is that possible to know, or must I always ask SQL Server what has been negotiated?

    Wednesday, September 3, 2014 12:53 PM
  • I'm curious, does this really cause any change in behavior?

    Last year I had to chase down some .net apps that were accidentally using the default serializable (4) isolation level, this on whatever old versions of stuff, SQL 2008 and 2008 R2.  But I never noticed any connections getting "poisoned", having the isolation level set to serializable for one transaction and then being picked up by someone else and accidentally still having serializable instead of read committed.  I assume this is because the pooled connections tend to be used by a single app, different apps use different pools.

    I would assume that somewhere in the .net framework it also resets the isolation level when it picks up a connection from the pool.

    Josh

    Wednesday, September 3, 2014 3:23 PM
  • Is that possible to know, or must I always ask SQL Server what has been negotiated?

    The negotiated version is the highest version supported by both client and server.  According to http://msdn.microsoft.com/en-us/library/dd304523.aspx, the Server versions are:

    SQL Server 7.0 7.0
    SQL Server 2000 7.1
    SQL Server 2000 SP1 7.1 Revision 1
    SQL Server 2005 7.2
    SQL Server 2008 7.3.A
    SQL Server 2008 R2 7.3.B
    SQL Server 2012 7.4
    SQL Server 2014 7.4

    I can't speak to the exact TDS versions supported by SqlClient.  On my PC, .NET 4.0 and above against a SQL 2014 box used TDS 7.4 where as 3.5 and earlier used TDS 7.3. 

    Based on this, I wouldn't expect the isolation level behavior change if SQL Server 2012 and 2014 is the only difference.  What version of .NET are you using?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, September 4, 2014 1:25 AM
    Moderator
  • I would assume that somewhere in the .net framework it also resets the isolation level when it picks up a connection from the pool.

    I think that is the sp_reset_connection RPC call mentioned earlier by SQL Team Microsoft.   But it seems the same can be done at the TDS protocol level by setting a status bit in the first request packet.  In either case, the reset occurs when the connection is retrieved for the pool.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, September 4, 2014 1:39 AM
    Moderator
  • I'm using .NET 4.5. The only difference was SQL Server 2012 vs 2014 actually.

    I'm a bit surprised that this has not been documented as a breaking change.

    Thursday, September 4, 2014 12:14 PM
  • I'm using .NET 4.5. The only difference was SQL Server 2012 vs 2014 actually.

    I'm a bit surprised that this has not been documented as a breaking change.

    I agree this change should have been documented.  I don't think it's due to the TDS protocol per-se, but how SQL Server 2014 does a better job of resetting the connection back to default settings.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, September 5, 2014 12:43 AM
    Moderator
  • This seem to been broken in some update to SQL Server 2014. Tested with SQL Server 2014 SP2 and with .NET 4.5 - 4.6 so TDS 7.4 should be used according to https://msdn.microsoft.com/en-us/library/dd339982.aspx. Isolation level is no longer reset.

    Any comments on this? I guess it is a bug?

    Tuesday, March 7, 2017 8:00 PM
  • Microsoft reverted to the old behaviour with SQL 2014 RTM CU6 and SQL 2014 SP1 CU1. The problem was that if you use something like TransactionScope and specified the isolation level for the TransactionScope, a new connection would not have the specified isolation level, but ReadCommitted. Which of course was not good at all. And it was a breaking change.

    Tuesday, March 7, 2017 10:44 PM
  • Ok thanks for a quick clarification. I think this is the issue you are referring to:

    FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014

    Sorry to hear that transaction scopes can still "pollute" the connection pool though. Can lead to some tricky and subtile bugs. Hope there will be another fix for this at some later point.

    I guess the problem you are referring to is if you would:

    1. Create a TransactionScope
    2. Open and close the connection to DB1 within that scope
    3. Then if you open the connection to DB1 again in the same scope it would have been reset to ReadCommitted.
    Wednesday, March 8, 2017 12:26 PM
  • I guess the problem you are referring to is if you would:

    1. Create a TransactionScope
    2. Open and close the connection to DB1 within that scope
    3. Then if you open the connection to DB1 again in the same scope it would have been reset to ReadCommitted.

    Yup. I guess the desired behaviour is that the isolation level is reset only if there is no active transaction. But that too could be considered to be a breaking change, as applications may depend on it.

    Wednesday, March 8, 2017 10:20 PM
  • The new behavior for SQL Server 2014 is better however it also creates a regression within .NET TransactionScope in that only the first opened connection within the scope honors the ISO level.  Additional connections use the default ISO level.  

    From:  http://stackoverflow.com/a/28464323/538763

    Friday, March 10, 2017 2:37 AM