SQL Server: утечка уровня изоляции через объединенные соединения

Как показано предыдущими вопросами о переполнении стека ( > TransactionScope и Connection Pooling и Как SqlConnection управляет IsolationLevel?), уровень изоляции транзакций протекает через объединенные соединения с SQL Server и ADO.NET(также System.Transactions и EF, потому что они строятся поверх ADO.NET).

Это означает, что в любом приложении может произойти следующая опасная последовательность событий:

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

Вопрос: Каков наилучший способ предотвратить этот сценарий? Вам действительно нужно использовать явные транзакции везде?

Вот самодостаточное воспроизведение. Вы увидите, что третий запрос унаследовал уровень Serializable из второго запроса.

class Program
{
    static void Main(string[] args)
    {
        RunTest(null);
        RunTest(IsolationLevel.Serializable);
        RunTest(null);
        Console.ReadKey();
    }

    static void RunTest(IsolationLevel? isolationLevel)
    {
        using (var tran = isolationLevel == null ? null : new TransactionScope(0, new TransactionOptions() { IsolationLevel = isolationLevel.Value }))
        using (var conn = new SqlConnection("Data Source=(local); Integrated Security=true; Initial Catalog=master;"))
        {
            conn.Open();

            var cmd = new SqlCommand(@"
select         
        case transaction_isolation_level 
            WHEN 0 THEN 'Unspecified' 
            WHEN 1 THEN 'ReadUncommitted' 
            WHEN 2 THEN 'ReadCommitted' 
            WHEN 3 THEN 'RepeatableRead' 
            WHEN 4 THEN 'Serializable' 
            WHEN 5 THEN 'Snapshot' 
        end as lvl, @@SPID
     from sys.dm_exec_sessions 
    where session_id = @@SPID", conn);

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("Isolation Level = " + reader.GetValue(0) + ", SPID = " + reader.GetValue(1));
                }
            }

            if (tran != null) tran.Complete();
        }
    }
}

Вывод:

Isolation Level = ReadCommitted, SPID = 51
Isolation Level = Serializable, SPID = 51
Isolation Level = Serializable, SPID = 51 //leaked!

Ответы

Ответ 1

В SQL Server 2014 это, похоже, исправлено. Если вы используете протокол TDS 7.3 или новее.

Запуск на SQL Server версии 12.0.2000.8:

ReadCommitted
Serializable
ReadCommitted

К сожалению, это изменение не упоминается ни в одной документации, как:

Но это изменение было задокументировано на форуме Microsoft.

Обновление 2017-03-08

К сожалению, это было позже "незафиксировано" в SQL Server 2014 CU6 и SQL Server 2014 SP1 CU1, поскольку он ввел ошибку:

ИСПРАВЛЕНИЕ: Уровень изоляции транзакции reset неверно, когда соединение SQL Server выпущено в SQL Server 2014

"Предположим, что вы используете класс TransactionScope в исходном коде на стороне клиента SQL Server, и вы явно не открываете соединение SQL Server в транзакции. Когда соединение SQL Server освобождается, уровень изоляции транзакции составляет reset неправильно".

Ответ 2

Пул соединений вызывает sp_resetconnection перед повторным использованием соединения. Сброс уровня изоляции транзакции не входит в список вещей, который делает sp_resetconnection. Это объясняет, почему "сериализуемые" утечки по объединенным соединениям.

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

if not exists (
              select  * 
              from    sys.dm_exec_sessions 
              where   session_id = @@SPID 
                      and transaction_isolation_level = 2
              )
    set transaction isolation level read committed

Другой вариант: соединения с другой строкой подключения не разделяют пул соединений. Поэтому, если вы используете другую строку соединения для "сериализуемых" запросов, они не будут делиться пулом с запросами "прочитанные". Простым способом изменения строки подключения является использование другого входа. Вы также можете добавить случайную опцию, например Persist Security Info=False;.

Наконец, вы можете убедиться, что каждый "сериализуемый" запрос сбрасывает уровень изоляции до его возврата. Если запрос "serializable" не завершен, вы можете очистить пул соединений, чтобы вывести испорченное соединение из пула:

SqlConnection.ClearPool(yourSqlConnection);

Это потенциально дорого, но неудачные запросы редки, поэтому вам не нужно часто вызывать ClearPool().

Ответ 3

Я просто задал вопрос по этой теме и добавил фрагмент кода С#, который может помочь в решении этой проблемы (что означает: изменить уровень изоляции только для одной транзакции).

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

Это в основном класс, который должен быть обернут в блок "using", который запрашивает исходный уровень изоляции до и восстанавливает его позже.

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