Unexpected Transaction Isolation Level in .NET with multiple connections

Question

I've put together a sample application to mimic some of our main application code functionality. Whilst working on this, I've been thrown a bit of a curve ball into my understanding of TransactionScope and SqlConnection.

The problem I see is explicitly "see" an IsolationLevel of READ COMMITTED where I would expect SERIALIZABLE. Unless my methods are diagnosis are flawed.

Currently my database has Read Snapshot enabled so the screenshots show the READ COMMITTED SNAPSHOT. When this is not enabled, then the screenshots will show READ COMMITTED.

Code

The code is running locally on my machine connecting to a local instance of SQL Server 2014. This is the minimal code required.

So to replicate this scenario from a Console application:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using IsolationLevel = System.Transactions.IsolationLevel;

namespace IsolationLevelConsoleProblem
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var tran = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions
            {
                IsolationLevel = IsolationLevel.Serializable,
                Timeout = TimeSpan.FromMinutes(3)
            }))
            {
                using (var connectionForRead = new SqlConnection(GetConnectionString("For Read")))
                {
                    connectionForRead.Open();

                    using (var commandRead = new SqlCommand("usp_GetAllSimpleTable", connectionForRead))
                    {
                        commandRead.CommandType = CommandType.StoredProcedure;
                        commandRead.CommandTimeout = 120;

                        DataSet dataSet = new DataSet();
                        SqlDataAdapter adapter = new SqlDataAdapter
                        {
                            SelectCommand = commandRead
                        };

                        adapter.Fill(dataSet);
                    }
                }

                using (var connectionForUpdate = new SqlConnection(GetConnectionString("For Update")))
                {
                    connectionForUpdate.Open();

                    using (var commandUpdate = new SqlCommand("usp_UpdateSimpleTable", connectionForUpdate))
                    {
                        commandUpdate.CommandTimeout = 120;
                        commandUpdate.CommandType = CommandType.StoredProcedure;
                        commandUpdate.Parameters.AddWithValue("@Guid", Guid.Parse("{74B3155E-138E-4881-BEE2-67FD141E29DA}"));
                        commandUpdate.Parameters.AddWithValue("@Name", $"Name {DateTime.UtcNow}");
                        commandUpdate.ExecuteNonQuery();
                    }
                }

                tran.Complete();
            }
        }

        private static string GetConnectionString(string name)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = ".\\SQL2014";
            builder.InitialCatalog = "AcmeTransactions";
            builder.IntegratedSecurity = true;
            builder.MaxPoolSize = 200;
            builder.ConnectTimeout = 120;
            builder.ApplicationName = name;

            return builder.ToString();
        }
    }
}

Database

NOTE: I've manually added a row in the table for the GUID specified above in code. I've also added a wait delay in the update stored procedure to view the isolation level in the sessions.

Create Table

CREATE TABLE [dbo].[SimpleTable](
    [Guid] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_SimpleTable] PRIMARY KEY CLUSTERED 
(
    [Guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored Procedure to read

CREATE PROCEDURE [dbo].[usp_GetAllSimpleTable]
AS
BEGIN
    SELECT [Guid]
          ,[Name]
      FROM [dbo].[SimpleTable]

END

Stored Procedure to update

create PROCEDURE [dbo].[usp_UpdateSimpleTable]
(
    @Guid UNIQUEIDENTIFIER,
    @Name NVARCHAR(50)
)
AS
BEGIN
    UPDATE [dbo].[SimpleTable]
       SET [Name] = @Name
     WHERE [Guid] = @Guid

     waitfor delay '00:00:40'
END

Diagnosis

I am using some SQL to list the sessions which includes the isolation level and the SQL being executed. This I believe is correct. I have also tried returning an explicit isolation level check in the stored procedure itself which returns READ COMMITTED.

SELECT 
          d.name
        , s.session_id
        , CASE  
              WHEN s.transaction_isolation_level = 1 
                 THEN 'READ UNCOMMITTED' 
              WHEN s.transaction_isolation_level = 2 
                   AND is_read_committed_snapshot_on = 1 
                 THEN 'READ COMMITTED SNAPSHOT' 
              WHEN s.transaction_isolation_level = 2 
                   AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED' 
              WHEN s.transaction_isolation_level = 3 
                 THEN 'REPEATABLE READ' 
              WHEN s.transaction_isolation_level = 4 
                 THEN 'SERIALIZABLE' 
              WHEN s.transaction_isolation_level = 5 
                 THEN 'SNAPSHOT' 
              ELSE NULL
           END
        , sqltext.TEXT

FROM   sys.dm_exec_sessions AS s
CROSS JOIN sys.databases AS d

join sys.dm_exec_requests er on s.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS sqltext

where d.name ='AcmeTransactions' 
and s.program_name not in ('Microsoft SQL Server Management Studio - Query' , 'Microsoft SQL Server Management Studio')

So when the above is executed whilst the delay is in progress, I see the following:

Further Investigation

If the connectionForRead using code block is moved outside of the Transaction, then I see the following session isolation information from SSMS:

This is how I would perceive the unmodified code to be.

Questions

1. Is my diagnosis correct? The both SQL connections are running as READ COMMITTED?
2. Is this the best way to determine the transaction level of running queries?

Answer 1

You will see this behaviour if you are running an early version of SQL Server 2014. Easiest fix is to apply SP2 (or greater)

See https://support.microsoft.com/en-us/help/3025845/fix-the-transaction-isolation-level-is-reset-incorrectly-when-the-sql for details.