Skip to content

Instantly share code, notes, and snippets.

@nycdotnet
Created January 2, 2018 11:01
Show Gist options
  • Save nycdotnet/830b915a6d3c95b0b386145b2dc9ac3d to your computer and use it in GitHub Desktop.
Save nycdotnet/830b915a6d3c95b0b386145b2dc9ac3d to your computer and use it in GitHub Desktop.
SQL Server Isolation Modes
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();
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment