Как SqlConnection управляет IsolationLevel?

Question

В этой статье MSDN говорится, что:

Уровень изоляции имеет область действия для всего соединения, и, будучи установленным для соединения с помощью инструкции SET TRANSACTION ISOLATION LEVEL, он остается в силе до тех пор, пока соединение не будет закрыто или не будет установлен другой уровень изоляции. Когда соединение закрывается и возвращается в пул, уровень изоляции из последнего оператора SET TRANSACTION ISOLATION LEVEL сохраняется. Последующие соединения, повторно использующие объединенное соединение, используют уровень изоляции, который действовал во время соединения.

Класс SqlConnection не имеет члена, который может содержать уровень изоляции. Так как же соединение знает, на каком уровне изоляции работать?

Я спрашиваю об этом из-за следующего сценария:

1. Я открыл транзакцию с использованием TransactionScope в режиме Serializable, скажем, "T1".
2. Открыл соединение для Т1.
3. T1 завершен / удален, соединение возвращается в пул соединений.
4. Вызывается другой запрос для того же соединения (после получения его из пула соединений), и этот запрос выполняется в сериализуемом режиме!!!

Проблема:

1. Как объединенное соединение все еще знает, какой уровень изоляции был связан с ним???
2. Как вернуть его на какой-то другой уровень транзакций???

Разрешение:
Причина, по которой объединенные в пул соединения возвращают сериализуемый уровень изоляции, заключается в следующей причине:

1. У вас есть один пул соединений (скажем, CP1)
2. CP1 может иметь 50 соединений.
3. Вы выбираете одно соединение C1 из CP1 и выполняете его с помощью Serializable. У этого соединения установлен уровень изоляции. Что бы вы ни делали, это не будет сброшено (если только это соединение не используется для выполнения кода на другом уровне изоляции).
4. После выполнения запроса C1(Serializable) возвращается к CP1.
5. Если шаги 1-4 выполняются снова, тогда используемым соединением может быть какое-то другое соединение, отличное от C1, скажем, C2 или C3. Таким образом, уровень изоляции также будет установлен на Serializable.
6. Таким образом, Serialzable медленно устанавливается на несколько соединений в CP1.
7. Когда вы выполняете запрос, в котором не выполняется явная настройка уровня изоляции, соединение, выбранное из CP1, будет определять уровень изоляции. Например, если такой запрос запрашивает соединение и CP1 использует C1(Serializable) для выполнения этого запроса, тогда этот запрос будет выполняться в режиме Serializable, даже если вы явно не устанавливали его.

Надеюсь, что это очищает несколько сомнений.:)

Answer 1

Уровни изоляции реализованы в базовой СУБД, скажем, SqlServer. Установка уровня изоляции наиболее вероятно устанавливает команды SQL, которые устанавливают уровень изоляции для соединения.

СУБД поддерживает уровень изоляции до тех пор, пока соединение остается открытым. Поскольку подключения помещаются в пул, он остается открытым и сохраняет настройки, сделанные ранее.

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

Answer 2

SqlConnection.BeginTransaction принимает IsolationLevel аргумент, и это, как один контролирует уровень изоляции соединений SqlClient. Другим вариантом является использование универсального System.Transactions и указать уровень изоляции в TransactionOptions.IsolationLevel передается конструктору TransactionScope. Как в программной модели SqlClient, так и в System.Transactions уровень изоляции должен быть явно указан для каждой транзакции. Если не указан, будет использоваться значение по умолчанию (Read Committed для SqlClient, Serializable for System.Transactions).

Объединенные соединения не используются повторно вслепую. У них есть скрытые внутренние члены для отслеживания текущего состояния, такого как текущая транзакция, ожидающие результаты и т. Д., И инфраструктура может очистить соединение, возвращаемое в пул. То, что состояние не отображается в модели программирования, это не значит, что его там нет (это относится к любому классу библиотеки, любой конструктор класса может скрыть элемент под internal зонтик).

И, наконец, любое соединение повторно используется из пула, который он вызывает sp_reset_connection которая является серверной процедурой, которая очищает состояние сеанса на стороне сервера.

Answer 3

Не возвращает уровень изоляции к исходному значению. В примере с использованием сущностей для сброса уровня потребовалась пустая транзакция (хотя она не требует фиксации (нет необходимости в.Complete()).

Попытка изменить уровень ISO с помощью SP на сервере БД не работает. Выход:

До: ReadCommitted
Во время: Сериализуемый
После: Сериализуемый
После сброса SP Попытка: Сериализуемый
Во время сброса XACT: ReadCommitted
После сброса XACT: ReadCommitted

// using Dbg = System.Diagnostics.Debug;
XactIso.iso isoEntity = new XactIso.iso();
using (isoEntity)
{
    Dbg.WriteLine("Before: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());

    var xactOpts = new TransactionOptions();
    xactOpts.IsolationLevel = System.Transactions.IsolationLevel.Serializable;

    using (TransactionScope xact = new TransactionScope(TransactionScopeOption.Required, xactOpts))
    {
        Dbg.WriteLine("During: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
        xact.Complete();
    }

    Dbg.WriteLine("After: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());

    isoEntity.usp_SetXactIsoLevel("ReadCommitted");

    Dbg.WriteLine("After Reset by SP Attempt: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
    // failed

    var xactOpts2 = new TransactionOptions();
    xactOpts2.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
    using (TransactionScope xact2 = new TransactionScope(TransactionScopeOption.Required, xactOpts2))
        Dbg.WriteLine("During Reset by XACT: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
    // works w/o commit

    Dbg.WriteLine("After Reset by XACT: " + isoEntity.usp_GetXactIsoLevel().SingleOrDefault());
}

откуда по ссылке

proc [Common].[usp_GetXactIsoLevel]
as
begin          
    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
     from sys.dm_exec_sessions 
    where session_id = @@SPID;
end

и (не работал):

proc [Common].[usp_SetXactIsoLevel]
    @pNewLevel    varchar(30)
as
begin

    if @pNewLevel = 'ReadUncommitted'
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    else if @pNewLevel = 'ReadCommitted'
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    else if @pNewLevel = 'RepeatableRead'
        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    else if @pNewLevel = 'Serializable'
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    else if @pNewLevel = 'Snapshot'
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    else
        raiserror('Unrecognized Transaction Isolation Level', 16, 1);         
end        

Answer 4

In SQL Server 2014 the isolation level for pooled connection is reset when connection is returned to pool. See this forum post

"in SQL 2014, for client drivers with TDS version 7.3 or higher, SQL server will reset transaction isolation level to default (read committed) for pooled connections. for clients with TDS version lower than 7.3 they will have the old behavior when running against SQL 2014."

Update 2017-04-22

Unfortunately this was later "unfixed" in SQL Server 2014 CU6 and SQL Server 2014 SP1 CU1 since it introduced a bug:

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

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