SQL Server Isolation Modes

Program.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace isolationmode_demo
{
    class Program
    {
        // https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
        // In particluar this section under "Managing Concurrency with Isolation Levels"
        // An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement,
        // it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned
        // to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections
        // reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled. 

        const string allowsSnapshotConnectionString = @"Server=(localdb)\MSSQLLocalDB;Integrated Security=true;Initial Catalog=allows_snapshot";

        static void Main(string[] args)
        {
            // I think the way you're going to want to do this is either D or H.
            HWorks();
        }

        static void HWorks()
        {
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "H1" });
            }
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction(IsolationLevel.Snapshot))
                {
                    conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "H2" }, tran);
                    tran.Commit();
                    var t2 = conn.BeginTransaction();
                    t2.Commit();
                }
            }
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "H3" });  //this will fail.
            }
        }

        static void GFails()
        {
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "G1" });
            }
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction(IsolationLevel.Snapshot))
                {
                    conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "G2" }, tran);
                    tran.Commit();
                    conn.Close(); // explicitly calling close does not help.
                }
            }
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "G3" });  //this will fail.
            }
        }

        static void FWorks()
        {
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "F1" });
            }
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction(IsolationLevel.Snapshot))
                {
                    conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "F2" }, tran);
                    tran.Commit();
                    conn.Execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED;");
                }
            }
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "F3" });
            }
        }

        static void FailsE()
        {
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "E1" });
            }
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction(IsolationLevel.Snapshot))
                {
                    conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "E2" }, tran);
                    tran.Commit();
                }
            }
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "E3" });  //this will fail.
            }
        }

        static void WorksD()
        {
            for (int i = 0; i < 1000; i++)
            {
                using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
                {
                    conn.Open();
                    using (IDbTransaction tran = conn.BeginTransaction(IsolationLevel.Snapshot))
                    {
                        conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "D" }, tran);
                        tran.Commit();
                    }
                }
                using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
                {
                    conn.Open();
                    using (IDbTransaction tran = conn.BeginTransaction(IsolationLevel.Snapshot))
                    {
                        conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "D" }, tran);
                        tran.Commit();
                    }
                }
                using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
                {
                    conn.Open();
                    using (IDbTransaction tran = conn.BeginTransaction())
                    {
                        conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "D" }, tran);
                        tran.Commit();
                    }
                }
            }
        }

        static void FailsC()
        {
            // throws  Snapshot isolation transaction failed accessing database 'disallows_snapshot' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction(IsolationLevel.Snapshot))
                {
                    conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "C" }, tran);
                    conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "C" }, tran);
                    tran.Commit();
                }
            }
        }

        static void WorksB()
        {
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction())
                {
                    conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "B" }, tran);
                    conn.Execute("INSERT INTO [disallows_snapshot].dbo.MyData VALUES (@TheData);", new { TheData = "B" }, tran);
                    tran.Commit();
                }
            }
        }

        static void WorksA()
        {
            using (IDbConnection conn = new SqlConnection(allowsSnapshotConnectionString))
            {
                conn.Open();
                using (IDbTransaction tran = conn.BeginTransaction(IsolationLevel.Snapshot))
                {
                    conn.Execute("INSERT INTO dbo.MyData VALUES (@TheData);", new { TheData = "A" }, tran);
                    tran.Commit();
                }
            }
        }
    }
}