EF6 с TransactionScope — IsolationLevel.ReadUncommitted, но сначала получен ReadCommitted

Согласно следующему примечанию в документации ADO.NET Изоляция моментальных снимков в SQL Server, уровень изоляции не привязан к области действия транзакции, пока базовое соединение объединено в пул:

Если соединение объединено в пул, сброс его уровня изоляции не приводит к сбросу уровня изоляции на сервере. В результате последующие соединения, использующие одно и то же внутреннее соединение из пула, начинаются с уровнями изоляции, установленными на уровне соединения из пула. Альтернативой отключению пула соединений является явная установка уровня изоляции для каждого соединения.

Таким образом, я прихожу к выводу, что до SQL Server 2012 установка уровня изоляции на любой другой уровень, кроме ReadCommitted, требует либо включения пула соединений при создании сомнительного SqlConnection, либо явной установки уровня изоляции в каждом соединении, чтобы избежать непредвиденного поведения, включая взаимоблокировки. В качестве альтернативы пул соединений можно очистить, вызвав Метод ClearPool, но, поскольку этот метод не привязан ни к области действия транзакции, ни к базовому соединению, я не думаю, что это уместно, когда несколько соединений выполняются одновременно с одним и тем же объединенным внутренним соединением.

Ссылаясь на сообщение SQL Server 2014 сбрасывает уровень изоляции в форуме SQL и моих собственных тестах, такие обходные пути устарели при использовании SQL Server 2014 и клиентского драйвера с TDS 7.3 или выше.

Я думаю, что это отвлекающий маневр, вызванный использованием события входа в систему аудита. Это не показывает момент, когда клиент сообщает серверу «установить уровень изоляции транзакции для чтения незафиксированного». Он показывает вам, какой уровень изоляции будет позже, когда это соединение будет выбрано из пула и использовано повторно.

Я проверяю это, добавляя Pooling=false в строку подключения. Затем вход в систему аудита всегда показывает зафиксированный уровень изоляции транзакции.

Я до сих пор не нашел в SQL Profiler способа увидеть момент, когда EF устанавливает уровень транзакции, или какой-либо явный begin tran.

Я могу подтвердить, что он устанавливается где-то, прочитав и записав уровень:

    const string selectIsolationLevel = @"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";

    static void ReadUncommitted()
    {
        using (var scope =
            new TransactionScope(TransactionScopeOption.RequiresNew,
            new TransactionOptions{ IsolationLevel = IsolationLevel.ReadUncommitted }))
        using (myEntities db = new myEntities())
        {
            Console.WriteLine("Read is about to be performed with isolation level {0}", 
                db.Database.SqlQuery(typeof(string), selectIsolationLevel).Cast<string>().First()
                );
            var data = from _Contact in db.Contact where _Contact.MemberId == 13 select _Contact; // large results but with nolock

            foreach (var item in data)
                item.Flag = 0;

            //Using Nuget package https://www.nuget.org/packages/Serilog.Sinks.Literate
            //logger = new Serilog.LoggerConfiguration().WriteTo.LiterateConsole().CreateLogger();
            //logger.Information("{@scope}", scope);
            //logger.Information("{@scopeCurrentTransaction}", Transaction.Current);
            //logger.Information("{@dbCurrentTransaction}", db.Database.CurrentTransaction);

            //db.Database.ExecuteSqlCommand("-- about to save");
            db.SaveChanges(); // Try avoid db lock
            //db.Database.ExecuteSqlCommand("-- finished save");
            //scope.Complete();
        }
    }

(Я говорю «вроде», потому что каждое утверждение выполняется в своем сеансе)

Возможно, это долгий способ сказать, да, транзакции EF работают правильно, даже если вы не можете доказать это с помощью Profiler.

Я думаю, что лучшим решением является выполнение обновления путем создания прямого запроса (а не выбора и обновления сущности за сущностью). Чтобы работать с объектами, а не с запросами, вы можете использовать EntityFramework.Extended:

db.Contact.Update(C => c.MemberId == 13, c => new Contact { Flag = 0 });

Это должно сгенерировать что-то вроде UPDATE Contact SET Flag = 0 WHERE MemberId = 13, которое намного быстрее, чем ваше текущее решение.

Если я правильно помню, это должно генерировать собственную транзакцию. Если это должно быть выполнено в транзакции с другими запросами, все еще можно использовать TransactionScope (у вас будет две транзакции).

Также уровень изоляции может остаться нетронутым (ReadCommitted).

[ИЗМЕНИТЬ]

Анализ Chris's точно показывает, что происходит. Чтобы сделать его еще более актуальным, следующий код показывает разницу внутри и снаружи TransactionScope:

using (var db = new myEntities())
{
    // this shows ReadCommitted
    Console.WriteLine($"Isolation level outside TransactionScope = {db.Database.SqlQuery(typeof(string), selectIsolationLevel).Cast<string>().First()}");
}

using (var scope =
    new TransactionScope(TransactionScopeOption.RequiresNew,
    new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
    // this show ReadUncommitted
    Console.WriteLine($"Isolation level inside TransactionScope = {db.Database.SqlQuery(typeof(string), selectIsolationLevel).Cast<string>().First()}");

    using (myEntities db = new myEntities ())
    {
        var data = from _Contact in db.Contact where _Contact.MemberId == 13 select _Contact; // large results but with nolock

        for (var item I data)
              item.Flag = 0;
        db.SaveChanges(); // Try avoid db lock
    }

    // this should be added to actually Commit the transaction. Otherwise it will be rolled back
    scope.Complete();
}

Возвращаясь к настоящей проблеме (возникновению взаимоблокировок), если мы посмотрим, что выводит Profiler в течение всего процесса, мы увидим что-то вроде этого (удалены GOs):

BEGIN TRANSACTION 
SELECT <all columns> FROM Contact 
exec sp_reset_connection

exec sp_executesql N'UPDATE Contact
    SET [Flag] = @0
    WHERE ([Contact] = @1)
    ',N'@0 nvarchar(1000),@1 int',@0=N'1',@1=1

-- lots and lots of other UPDATEs like above

-- or ROLLBACK if scope.Complete(); is missed
COMMIT

Это имеет два недостатка:

1. Много повторных обращений — к базе данных выдается много запросов, что увеличивает нагрузку на ядро ​​базы данных, а также занимает гораздо больше времени для клиента.

2. Длинная транзакция: следует избегать длинных транзакций в качестве ориентира минимизация взаимоблокировок

Таким образом, предлагаемое решение должно работать лучше в вашем конкретном случае (простое обновление).

В более сложных случаях может потребоваться изменение уровня изоляции.

Я думаю, что если кто-то имеет дело с большой обработкой данных (выбрать миллионы, сделать что-то, обновить и т. д.), хранимая процедура может быть решением, поскольку все выполняется на стороне сервера.