EF6 с TransactionScope - IsolationLevel.ReadUncommitted, но сначала получил ReadCommitted

Question

Существует проблема с производительностью и блокировкой при использовании EF для случая обновления из запроса на MSSQL 2008. Поэтому я поставил уровень изоляции транзакции ReadUncommitted, надеясь решить ее следующим образом:

До

using (myEntities db = new myEntities())
{
            var data = from _Contact in db.Contact where _Contact.MemberId == 13 select _Contact; // large results
            for (var item I data)
                  item.Flag = 0;
            db.SaveChanges(); // Probably db lock
}

После

using (var scope =
    new TransactionScope(TransactionScopeOption.RequiresNew,
    new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
    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
    }
}

Мы используем SQL Profiler для подтверждения. Тем не менее, приведем эти сценарии в порядок, (ожидаем, что чтение будет снято для первого сценария.)

Аудит Логин

set transaction isolation level read committed

SP:StmtStarting

SELECT 
 [Extent1].[ContactId] AS [ContactId], 
 [Extent1].[MemberId] AS [MemberId], 
FROM [dbo].[Contact] AS [Extent1]
WHERE [Extent1].[MemberId] = @p__linq__0

Аудит Логин

set transaction isolation level read uncommitted

Хотя я мог бы повторно отправить этот запрос и сделать его в правильном порядке (будет показано чтение-незафиксировано для следующих запросов, тот же SPID), мне интересно, почему он отправил команду чтения-незафиксировано после команды чтения-фиксации и как это исправить, используя EF и TransactionScope? Благодарю.

Answer 1

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

Я проверяю это, добавляя 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.

Answer 2

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

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

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

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

Answer 3

Я думаю, что лучшее решение - выполнить обновление путем создания прямого запроса (а не выбора и обновления объекта по объекту). Для работы с объектами, а не с запросами, вы можете использовать https://www.nuget.org/packages/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'Анализ показывает, что именно происходит. Чтобы сделать его еще более актуальным, следующий код показывает разницу внутри и снаружи 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. Длинные транзакции - следует избегать длинных транзакций, чтобы избежать взаимных блокировок.

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

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

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