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

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

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

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

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

Вот автономное воспроизведение. Вы увидите, что третий запрос унаследовал уровень 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!
Источник

3 ответа

Решение

В 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 с пакетом обновления 1 (SP1) CU1, поскольку в нем была ошибка:

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

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

Источник

Пул соединений вызывает 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;,

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

SqlConnection.ClearPool(yourSqlConnection);

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

Источник

Для тех, кто использует EF в.NET, вы можете исправить это для всего приложения, установив другое имя приложения для каждого уровня изоляции (как также указано @Andomar):

//prevent isolationlevel leaks
//https://stackru.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections
public static DataContext CreateContext()
{
    string isolationlevel = Transaction.Current?.IsolationLevel.ToString();
    string connectionString = ConfigurationManager.ConnectionStrings["yourconnection"].ConnectionString;
    connectionString = Regex.Replace(connectionString, "APP=([^;]+)", "App=$1-" + isolationlevel, RegexOptions.IgnoreCase);

    return new DataContext(connectionString);
}

Странно, что это все еще проблема 8 лет спустя...

Источник

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

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

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

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

Источник
Другие вопросы по тегам