Using Domain's sessions and transactions changes default isolation level for any other sql connection. In the sample below I'm using transation with ReadUncommitted isolation level. After disposing this transaction (and even session, domain) new connection (e.g. new SqlConnection) with the same connection string uses isolation level ReadUncommitted by default

Code:

namespace Sample
{
    using System;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    using System.Runtime.CompilerServices;
    using System.Transactions;
    using Xtensive.Orm;
    using Xtensive.Orm.Configuration;

    [HierarchyRoot]
    public class MyEntity : Entity
    {
        [Field][Key] public int Id { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            const string ConnectionString =
                "Data Source=.; Initial Catalog=DO40-Tests;Connection Timeout=300;Integrated Security = true;Max pool size=10";

            Console.WriteLine(GetIsolationLevel(ConnectionString)); // ReadCommitted

            var dc = new DomainConfiguration("sqlserver", ConnectionString);
            dc.Types.Register(typeof(Program).Assembly);
            dc.UpgradeMode = DomainUpgradeMode.Recreate;

            using (var domain = Domain.Build(dc))
            {
                using (var session = domain.OpenSession())
                using (session.Activate())
                {
                    using (session.OpenTransaction(TransactionOpenMode.New, IsolationLevel.ReadUncommitted))
                    {
                        Query.All<MyEntity>().ToArray();
                        Console.WriteLine(GetIsolationLevel(ConnectionString)); // ReadCommitted
                    }

                    Console.WriteLine(GetIsolationLevel(ConnectionString)); // ReadUncommitted
                }

                Console.WriteLine(GetIsolationLevel(ConnectionString)); // ReadUncommitted

            }

            Console.WriteLine(GetIsolationLevel(ConnectionString)); // ReadUncommitted
        }

        private static string GetIsolationLevel(string connectionString)
        {
            using (var conn = new SqlConnection(connectionString))
            using (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";

                conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    reader.Read();
                    return reader.GetString(0);
                }
            }
        }
    }
}

asked Aug 12 '19 at 05:06

Platonov's gravatar image

Platonov
5778

edited Aug 12 '19 at 05:06

Be the first one to answer this question!
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA