SQL 서버:풀링된 연결 간의 절연 수준 누출

SQL 서버:풀링된 연결 간의 절연 수준 누출

이전 스택 오버플로 질문(트랜잭션 범위 및 연결 풀링 및 SqlConnection의 분리 관리 방법)에서 확인할 수 있습니다.레벨?), 트랜잭션 격리 레벨은 SQL Server 및 ADO.NET(시스템)과 풀링된 연결에서 누출됩니다.ADO.NET을 기반으로 하기 때문에 트랜잭션 및 EF.

즉, 다음과 같은 위험한 일련의 이벤트가 모든 애플리케이션에서 발생할 수 있습니다.

1. 데이터 일관성을 보장하기 위해 명시적인 트랜잭션이 필요한 요청이 발생합니다.
2. 중요하지 않은 읽기만 수행하기 때문에 명시적 트랜잭션을 사용하지 않는 다른 모든 요청이 수신됩니다.이 요청은 이제 직렬화 가능한 것으로 실행되므로 위험한 차단 및 교착 상태를 유발할 수 있습니다.

질문:이 시나리오를 방지하는 가장 좋은 방법은 무엇입니까?지금 모든 곳에서 명시적인 트랜잭션을 사용해야 합니까?

여기 자숙하는 비난이 있습니다.세 번째 쿼리가 두 번째 쿼리에서 직렬화 가능 수준을 상속했음을 알 수 있습니다.

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!

연결 풀은 연결을 재활용하기 전에 sp_reset 연결을 호출합니다.트랜잭션 분리 수준 재설정은 sp_reset 연결에서 수행하는 작업 목록에 없습니다.이는 풀링된 연결 전반에 걸쳐 "직렬화 가능한" 누출이 발생하는 이유를 설명합니다.

각 쿼리가 올바른 분리 수준인지 확인하여 시작할 수 있습니다.

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);

으로 비용이 들 수 에, 은 이는잠으비많실들쿼드다때물없전니습걸필화문요가를에기리는패한있지만수재로적이용이▁this▁are다▁have니▁should없습▁not▁queries▁call▁to이는▁is▁so▁but,라고 부를 필요가 없습니다.ClearPool()자주.

SQL Server 2014에서는 이 문제가 해결된 것으로 보입니다.TDS 프로토콜 7.3 이상을 사용하는 경우.

SQL Server 버전 12.0.2000.8에서 실행 시 출력은 다음과 같습니다.

ReadCommitted
Serializable
ReadCommitted

유감스럽게도 이 변경 사항은 다음과 같은 문서에 언급되어 있지 않습니다.

• SQL Server 2014의 데이터베이스 엔진 기능에 대한 동작 변경 사항
• SQL Server 2014의 데이터베이스 엔진 기능에 대한 변경 사항 수정

그러나 그 변화는 마이크로소프트 포럼에서 문서화되었습니다.

2017-03-08 업데이트

안타깝게도 이 문제는 나중에 SQL Server 2014 CU6 및 SQL Server 2014 SP1 CU1에서 버그가 발생한 이후 "수정되지 않았습니다.

FIX: SQL Server 2014에서 SQL Server 연결이 해제되면 트랜잭션 분리 수준이 잘못 재설정됨

"SQL Server 클라이언트 측 소스 코드에서 TransactionScope 클래스를 사용하고 트랜잭션에서 SQL Server 연결을 명시적으로 열지 않는다고 가정합니다.SQL Server 연결이 해제되면 트랜잭션 격리 수준이 잘못 재설정됩니다."

해결 방법

매개 변수를 통과하면 운전자가 다음을 사용할 수 있습니다.sp_executesql이렇게 하면 저장 프로시저와 유사한 새 범위가 적용됩니다.배치가 종료된 후 스코프가 롤백됩니다.

따라서 누출을 방지하기 위해 아래와 같이 더미 파라미터를 통과시킵니다.

using (var conn = new SqlConnection(connString))
using (var comm = new SqlCommand(@"
SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID
", conn))
{
    conn.Open();
    Console.WriteLine(comm.ExecuteScalar());
}
using (var conn = new SqlConnection(connString))
using (var comm = new SqlCommand(@"
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID
", conn))
{
    comm.Parameters.Add("@dummy", SqlDbType.Int).Value = 0;  // see with and without
    conn.Open();
    Console.WriteLine(comm.ExecuteScalar());
}
using (var conn = new SqlConnection(connString))
using (var comm = new SqlCommand(@"
SELECT transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID
", conn))
{
    conn.Open();
    Console.WriteLine(comm.ExecuteScalar());
}

.NET에서 EF를 사용하는 경우 분리 수준별로 다른 앱 이름을 설정하여 전체 애플리케이션에 대해 이 문제를 해결할 수 있습니다(@Andomar 참조).

//prevent isolationlevel leaks
//https://stackoverflow.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 트랜잭션에서만 분리 수준 변경

기본적으로 '사용 중' 블록에 묶이는 클래스로, 이전에 원래 격리 수준을 쿼리하고 나중에 복원합니다.

그러나 기본 격리 수준을 확인하고 복원하려면 DB에 왕복 두 번의 작업이 추가로 필요하며, 변경된 격리 수준이 유출되지 않을 것이라고 확신할 수는 없습니다. 위험은 거의 없습니다.

언급URL : https://stackoverflow.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections