Изменять уровень изоляции только в отдельных транзакциях ADO.NET

Question

Каков наилучший способ реализации разных уровней изоляции для отдельных транзакций при использовании клиентской среды, ORM или аналогичных для построения запросов, которые не поддерживают подсказки запросов, такие как WITH(NOLOCK)?

Представьте себе приложение, использующее уровень ReadUncommitted для ряда сложных и длительных запросов (хорошо осведомленных о связанных рисках), и оно должно работать с NHibernate и его критериями запроса (или QueryOver/LINQ, просто без объединения строк!).

NHibernate не поддерживает подсказку with(nolock) (кроме случаев использования нативного SQL, который в настоящее время используется во многих случаях).

Итак, чтобы заменить собственные строки SQL и их утомительный строительный код, я хочу использовать транзакции с IsolationLevel.ReadUncommitted для замены 'with(nolock)'.

Но соединение остается на измененном уровне изоляции даже после фиксации / отката, когда все работает на новом уровне. Даже после connection.Close() он возвращается в пул соединений и повторно используется с измененным уровнем изоляции.

Первоначально я заметил это, потому что я тестировал открытие соединения с уровнем изоляции Snapshot и отправку простого запроса, чтобы отключить Read Uncommitted, если включен режим Snapshot в базе данных (простой переход к снимку в общем случае невозможен). В тестовой базе данных отключен режим моментальных снимков, поэтому я получил исключение и установил для моей переменной UseReadUncommitted значение true в блоке catch, но более поздние запросы из "нового"/ повторно используемого соединения все равно получили то же исключение.

Я написал простой класс для переноса обработки транзакций в блоке using, автоматически сбрасывая IsolationLevel в.Dispose(). Но это, кажется, вызывает два дополнительных обращения к БД, и я не уверен, может ли измененный уровень изоляции "пережить" удаление в определенных ситуациях и повлиять на другие запросы. Код работал с первой попытки, он предназначен для простых соединений / транзакций ADO.NET (я сделаю еще один для сессий NHibernate, если хорошо!).

Какие-либо предложения?

public class TransactionContainerTempIsolationLevel : IDisposable
{
    public IsolationLevel OldIsolationLevel { get; private set; }

    public IsolationLevel TempIsolationLevel { get; private set; }

    public IDbTransaction Transaction { get; private set; }

    private readonly IDbConnection _conn;

    public TransactionContainerTempIsolationLevel(IDbConnection connection, IsolationLevel tempIsolationLevel)
    {
        _conn = connection;
        LocalIsolationLevel = localIsolationLevel;

        var checkTran = _conn.BeginTransaction();
        if (checkTran.IsolationLevel == tempIsolationLevel)
        {
            Transaction = checkTran;
        }
        else
        {
            OldIsolationLevel = checkTran.IsolationLevel;
            checkTran.Dispose();
            Transaction = _conn.BeginTransaction(tempIsolationLevel);
        }
    }

    public void Dispose()
    {
        Transaction.Dispose();
        if (OldIsolationLevel != TempIsolationLevel)
        {
            using (var restoreTran = _conn.BeginTransaction(OldIsolationLevel))
            {
                restoreTran.Commit();
            }
        }
    }
}

Answer 1

Тот факт, что многие ORM не поддерживают (динамические) подсказки запросов, является позором. Установка уровня изоляции или запись представлений обертки и TVF являются обычными обходными путями.

Но соединение остается на измененном уровне изоляции даже после фиксации / отката, когда все работает на новом уровне. Даже после connection.Close() он возвращается в пул соединений и повторно используется с измененным уровнем изоляции.

Да, это ошибка проектирования в SQL Server, которая была исправлена ​​в 2014 году.

В тестовой базе данных отключен режим моментальных снимков, поэтому я получил исключение

Именно так я и узнал об этом. Тревожная находка.

Код, который вы разместили, должен работать в целом. Это требует дополнительных поездок в базу данных, как вы говорите. На самом деле восстановление старого уровня изоляции вызывает две поездки туда и обратно. Всего в вашем коде я насчитываю 2 или 6 циклов в зависимости от того, был ли изменен уровень или нет.

Единственный разумный способ справиться с утечкой уровня изоляции в <2014, который я обнаружил, это всегда использовать явную транзакцию для каждого доступа к базе данных. Это, на мой взгляд, хорошая идея в большинстве случаев в любом случае. Вам часто нужно выбрать уровень изоляции и обеспечить атомарность в любом случае. И в случае, если вы можете перейти к SNAPSHOT (что я рекомендую), вы, вероятно, захотите выполнить много запросов в одной транзакции моментального снимка, чтобы все запросы видели одни и те же данные.

В>=2014 уровень по умолчанию для вновь открытого соединения - READ COMMITTED.

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

Таким образом, вы можете просто использовать одну транзакцию и позволить утечке уровня. Если вы действительно хотите восстановить, я бы предложил этот T-SQL:

SELECT isolation_level FROM sys.sessions WHERE session_id = @@SPID
SET TRANSACTION ISOLATION LEVEL X
BEGIN TRAN

Надеюсь, это хорошо работает. Это одно путешествие туда и обратно. Вам нужно еще одно путешествие туда и обратно, чтобы восстановить старый уровень.

Если вы действительно заинтересованы в производительности, вы можете сохранить свой собственный простой пул соединений с соединениями в известном состоянии.

Или используйте одну строку подключения на уровень изоляции. Сделайте их уникальными, используя AppName,

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

Я рекомендую вам использовать самое простое решение, которое работает для вас.