SqlTransaction and TransactionScope leak isolation level

The TransactionScope class seems to "leak" it's isolation level to future queries on the same (pooled) connection.

I would expect the isolation level of the connection to be restored when the transaction ends or is disposed.

Here is a snippet which reproduces the behavior:

SqlConnection.ClearAllPools();
var conn = new SqlConnection(new SqlConnectionStringBuilder { DataSource = @".\sqlexpress", IntegratedSecurity = true }.ConnectionString);

Action printIsolationLevel = () =>
{
	var cmd = conn.CreateCommand();
	cmd.CommandText = @"SELECT CASE transaction_isolation_level 
						WHEN 0 THEN 'Unspecified' 
						WHEN 1 THEN 'ReadUncommitted' 
						WHEN 2 THEN 'ReadCommitted' 
						WHEN 3 THEN 'Repeatable' 
						WHEN 4 THEN 'Serializable' 
						WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
						FROM sys.dm_exec_sessions 
						where session_id = @@SPID";
	Console.WriteLine(cmd.ExecuteScalar());
};

conn.Open();
printIsolationLevel(); // "ReadCommitted"
conn.Close();

using (var scope = new TransactionScope(
					TransactionScopeOption.RequiresNew,
					new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Serializable }))
{
	conn.Open();
	printIsolationLevel(); // "Serializable"
	conn.Close();
}

conn.Open();
printIsolationLevel(); // "Serializable" !!?
conn.Close();

TransactionsScope is in S.Transactions. Of course I don't know where the bug is..

Further investigations suggest that this may be a problem with SqlTransaction as well.

This may be a Sql connection pooling issue causing retention of the isolation level that was obtained from the transaction that was created and completed by the TransactionScope. From: Dan Moseley [mailto:notifications@github.com] Sent: Wednesday, April 12, 2017 5:19 PM To: dotnet/corefx <corefx@noreply.github.com> Cc: Subscribed <subscribed@noreply.github.com> Subject: Re: [dotnet/corefx] TransactionScope leaks isolation level (#18277) TransactionsScope is in S.Transactions. Of course I don't know where the bug is.. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdotnet%2Fcorefx%2Fissues%2F18277%23issuecomment-293742713&data=02%7C01%7Cjcarley%40microsoft.com%7Cdc1af23791ba4c8bfb2a08d48202bc91%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636276395643083008&sdata=dFoWFD%2BpU7st62p6b%2BHwT%2BR%2F6cQwe9sZTjTslBkrpzU%3D&reserved=0>, or mute the thread<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAMw2-Gl4lfajvOBedRdZ-ej2kfkpBXdLks5rvWoKgaJpZM4M7d7l&data=02%7C01%7Cjcarley%40microsoft.com%7Cdc1af23791ba4c8bfb2a08d48202bc91%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636276395643083008&sdata=E%2FxOU%2BlEEKUw2%2FdjCGqp6Z4KPLE5Q0NCYYhF3PqrqcU%3D&reserved=0>.

The fact that the isolation level change goes on to pollute the connection pool makes the much more worrisome. However, even on a single connection we can see weird behavior where the transaction isolation level leaks out of the transaction scope:

SqlConnection.ClearAllPools();
var conn = new SqlConnection(new SqlConnectionStringBuilder { DataSource = @".\sqlexpress", IntegratedSecurity = true }.ConnectionString);

Action<SqlTransaction> printIsolationLevel = (SqlTransaction transaction) =>
{
	var cmd = conn.CreateCommand();
	cmd.Transaction = transaction;
	cmd.CommandText = @"SELECT CASE transaction_isolation_level 
						WHEN 0 THEN 'Unspecified' 
						WHEN 1 THEN 'ReadUncommitted' 
						WHEN 2 THEN 'ReadCommitted' 
						WHEN 3 THEN 'Repeatable' 
						WHEN 4 THEN 'Serializable' 
						WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
						FROM sys.dm_exec_sessions 
						where session_id = @@SPID";
	Console.WriteLine(cmd.ExecuteScalar());
};

conn.Open();

printIsolationLevel(null); // ReadCommitted

using (var transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
	printIsolationLevel(transaction); // Serializable
}

printIsolationLevel(null); // Serializable

conn.Close();

@saurabh500

@saurabh500 Including you because it looks like you were the last person to touch SqlConnection.

This looks like a question for SqlConnection or SqlCommand.

@corivera

@corivera ?

Right now SqlClient doesn't support TransactionScope in .Net Core .
We are looking into SqlTransaction right now.

I performed some investigation, and realized this behavior is by design.
Here is my test code:

public static void Test()
{
    string connString = "Server=tcp:<hostname>,1433;User ID=<id>;Password=<pw>;Connect Timeout=600;";

    SqlConnection.ClearAllPools();
    var conn = new SqlConnection(new SqlConnectionStringBuilder(connString).ConnectionString);

    Action<SqlTransaction> printIsolationLevel = (SqlTransaction transaction) =>
    {
        var cmd = conn.CreateCommand();
        cmd.Transaction = transaction;
        cmd.CommandText = @"SELECT CASE transaction_isolation_level 
                WHEN 0 THEN 'Unspecified' 
                WHEN 1 THEN 'ReadUncommitted' 
                WHEN 2 THEN 'ReadCommitted' 
                WHEN 3 THEN 'Repeatable' 
                WHEN 4 THEN 'Serializable' 
                WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
                FROM sys.dm_exec_sessions 
                where session_id = @@SPID";
        Console.WriteLine(cmd.ExecuteScalar());
    };

    conn.Open();

    printIsolationLevel(null); // ReadCommitted

    using (var transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
    {
        printIsolationLevel(transaction); // Serializable
    }

    printIsolationLevel(null); // Serializable

    using (var transaction = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
    {
        printIsolationLevel(transaction); // ReadUncommitted
    }

    printIsolationLevel(null); // ReadUncommitted

    conn.Close();
}

Default isolation level is ReadCommitted, and it is replaced to new isolation level value when a transaction object is created. The isolation level value remains in connection of server side (client side does not store anything after transaction is disposed) even after transaction is finished because there is no need to reset it to default since isolation level is only consumed by transaction, and the value will be replaced to new value anyway when new transaction is started.

This behavior is intended and described in TDS protocol design document (https://msdn.microsoft.com/en-us/library/dd339887.aspx). When a transaction object is disposed, it internally executes ROLLBACK, which sends TM_ROLLBACK_XACT request to server.

Detail payload of the TM_ROLLBACK_XACT request is shown below:

 fBeginXact         =   BIT
  
 XACT_FLAGS         =   fBeginXact
                        7FRESERVEDBIT
  
 ISOLATION_LEVEL    =   BYTE
  
 XACT_NAME          =   B_VARBYTE
 BEGIN_XACT_NAME    =   B_VARBYTE
  
 RequestPayload     =   XACT_NAME
                        XACT_FLAGS
                        [ISOLATION_LEVEL
                        BEGIN_XACT_NAME]

If fBeginXact is 1, then ISOLATION_LEVEL can specify the isolation level to use to start the new transaction, according to the definition at the end of this table. If fBeginXact is 0, then ISOLATION_LEVEL SHOULD NOT be present.

fBeginXact bit is obviously set to 0 when transaction object is disposed, and RequestPayload does not have ISOLATION_LEVEL value intentionally.

You can see this payload implementation at:
https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/TdsParser.cs#L6374

ISOLATION_LEVEL
Value: 0x00
Description: No isolation level change requested. Use current.

According to the spec document, ROLLBACK request does not change isolation level intentionally, and let it use current. So, this behavior is intended rather than a bug.

@geleems

@geleems it isn't true that isolation level only affects transaction. As long as SQL is in autocommit mode (the default), each statement executes in an implicit transaction using the current isolation level. This is what caused me to report the bug.

This is particularly problematic because of connection pooling, since the isolation levels can leak and affect any other part of the application.

@madelson

@madelson

since isolation level is only consumed by transaction

I meant isolation level parameter is only consumed by method that creates transaction, which is SqlConnection.BeginTransaction(). Once isolation level is set to certain value, it will remain at that setting through the session as intended until you change it. If you want to just change the isolation level setting, and don't want transaction, you can do as following:

SqlConnection.BeginTransaction(IsolationLevel.ReadCommitted).Dispose();

However, I identified it is a problem that isolation level is not cleaned up when connection moves back to pool.

@geleems

@geleems I agree that the behavior with pooling represents the biggest problem here; that's what burned us.

Maybe it's a separate issue, but I do think that most callers to the API would expect the isolation level to reset on dispose despite the documentation that says otherwise. It seems weird that the recommended usage pattern would be to follow each transaction with another empty transaction just to get reasonable behavior. Much like the scoping of the foreach var (which changed in C#5), this seems like it could be one of those cases where despite the current behavior being intentional it is so confusing that it might be worth changing.

Here's a link with some additional context on this issue: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/916b3d8a-c464-4ad5-8901-6f845a2a3447/sql-server-2014-reseting-isolation-level?forum=sqldatabaseengine

Reset session isolation level on pool return (fixes dotnet#96)

fix: reset transaction isolation level after transaction completes (d…

Add design note comparing issues dotnet#96 and dotnet#146

/triage

Re-issue session isolation level on TransactionScope re-enlistment (#…

Reset pooled session isolation level before reuse (fixes dotnet#96) (d…

Navigation Menu

Repository navigation

SqlTransaction and TransactionScope leak isolation level #96

22 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

SqlTransaction and TransactionScope leak isolation level #96

Description

Activity

danmoseley commented on Apr 13, 2017

madelson commented on Apr 13, 2017

jimcarley commented on Apr 13, 2017

madelson commented on Apr 14, 2017

jimcarley commented on Apr 14, 2017

danmoseley commented on Apr 19, 2017

saurabh500 commented on Apr 26, 2017

geleems commented on May 1, 2017

madelson commented on May 2, 2017

geleems commented on May 2, 2017

madelson commented on May 2, 2017

madelson commented on Aug 8, 2017

22 remaining items

priyankatiwari08 commented on Sep 17, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions