SQLServer Failed to get ALLOW_SNAPSHOT_ISOLATION to work in C # code (it works in Management Studio)

Question

Recently, I had to solve problems with locking, such as a transaction was locked when resources were locked by another process and was selected as a victim of a deadlock. Restart the transaction.

After reading several articles and analyzing the context of my system, I decided to make the most common decision: ALTER DATABASE MyDb SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE MyDb SET ALLOW_SNAPSHOT_ISOLATION ON;

I want ALLOW_SNAPSHOT_ISOLATION because this isolation makes sense on my system.

I have successfully implemented the flow described in the "Allow Snapshot Isolation" section of https://www.databasejournal.com/features/mssql/article.php/3566746/Controlling-Transactions-and-Locks-Part-5-SQL-2005 -Snapshots.htm in two sessions in SQL Server Management Studio.

Pseudocode:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN 1
select value from MyTable where ID=1 --reads original value 

  --On another session:
  BEGIN TRAN 2
  update mytable set value=2 where ID=1
  COMMIT TRAN2

-- back to session 1
select value from MyTable where ID=1 --still reads original value 

The above example works as expected. This tells me that the database is configured correctly.

My problem is with C # code. Although I was able to prevent blocking situations ( READ_COMMITTED_SNAPSHOT ), I was not able to replicate the "Allow snapshot" behavior in my C # code. I tried with TransactionScope and without it. My goal is to make it work with TransactionScope .

# - : , 20 , . 20 , SQL Server Management Studio . 20 . - ALLOW_SNAPSHOT_ISOLATION

( Dapper):

static TransactionScope CreateTransactionScope()
{
    var transactionOptions = new TransactionOptions();
    transactionOptions.Timeout = TransactionManager.MaximumTimeout;
    transactionOptions.IsolationLevel = IsolationLevel.Snapshot; //also tried IsolationLevel.ReadCommitted
    return new TransactionScope(TransactionScopeOption.RequiresNew, transactionOptions);
}
...
using (var transactionScope = CreateTransactionScope())
{
    T ret;
    using (var connection = new SqlConnection(_connectionString))
    {
        //connection.Execute("SET TRANSACTION ISOLATION LEVEL SNAPSHOT"); this makes no difference
        ret = TestWithTransactionScope(connection);
    }
    transactionScope.Complete();
    return ret;
}
...
public object TestWithTransactionScope(IDbConnection c)
{
    var sql = "select value from MyTable where ID=1";
    var firstRead = c.Query<string>(sql).Single();
    System.Threading.Thread.Sleep(25000);
    var secondRead = c.Query<string>(sql).Single();
    return string.Format("firstRead: {0}, secondRead: {1}", firstRead, secondRead);
}

TransactionScope:

...
using (var connection = new SqlConnection("..."))
{
    connection.Open();
    connection.Execute("SET TRANSACTION ISOLATION LEVEL SNAPSHOT");
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            var ret = TestWithTransactionScope(connection, transaction);
            transaction.Commit();
            return ret;
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}
...

public object TestWithTransactionScope(IDbConnection c, SqlTransaction t)
{
    var sql = "select value from MyTable where ID=1";
    var firstRead = c.Query<string>(q, null, t).Single();
    System.Threading.Thread.Sleep(25000);
    var secondRead = c.Query<string>(q, null, t).Single();
    return string.Format("firstRead: {0}, secondRead: {1}", firstRead, secondRead);
}

?

.Net 4.5, Dapper 1.50.2 SQL Server 2014

1

TransactionScope:

using (var transaction = connection.BeginTransaction(IsolationLevel.Snapshot))

, TransactionScope.