C# LINQ事务隔离级别异常切换致超时问题排查与解决

我有一个GetEntries方法，在TransactionScope内调用Get方法。Get方法包含三个LINQ操作：前两个分别从数据库获取两个集合（调用ToArray()触发查询），第三个对这两个内存集合执行分组连接。

事务配置的隔离级别为IsolationLevel.ReadCommitted，但实际执行中第一个查询按预期使用ReadCommitted，第二个查询却意外使用Serializable隔离级别，导致超时异常，阻塞进程报告已确认该情况。问题在生产环境偶发，需明确：问题原因、本地复现方法、修复方案。

伪代码 #

internal static class AuditTest
{
    public static IEnumerable<Audit> Get(
        DataContext dataContext,
        Int32 id,
        Int32[] recordIds)
    {
        var collection1 =
            dataContext
            .Table1
            .Where(d => d.MemberId == id && recordIds.Contains(d.RecordId))
            .Select(
                record =>
                    new
                    {
                        // 省略属性映射
                    })
            .ToArray();

        var collection2 =
            dataContext
            .Table2
            .Where(r => r.MemberId == id && recordIds.Contains(r.RecordId))
            .Select(
                record =>
                    new
                    {
                        // 省略属性映射
                    })
            .ToArray();

        return
            collection1
            .GroupJoin(
                collection2,
                record1 => record1.RecordId,
                record2 => record2.RecordId,
                (record1, record1WithRecord2) =>
                    new Audit
                    {
                        // 省略属性映射
                    });
    }

    public static IEnumerable<Entry> GetEntries(
        DataContext dataContext,
        Int32 Id,
        Int32[] Ids)
    {
        var option = new TransactionOptions()
        {
            IsolationLevel = IsolationLevel.ReadCommitted,
            Timeout = TransactionManager.MaximumTimeout
        };

        using (var transactionScope = new TransactionScope(
            TransactionScopeOption.Suppress, option))
        {
            return
                Get(dataContext, Id, Ids)
                .Select(
                    m =>
                        new Entry
                        {
                            Guid = m.Guid,
                            Id = m.Id,
                        });
        }
    }
}

异常信息 #

SqlException "Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   .......

阻塞进程报告 #

<blocked-process-report>
<blocked-process>
    <process taskpriority="0" logused="0" waitresource="KEY: 11:72057594321895424 (67f1cd66ae3e)" waittime="7078" ownerId="26395737" transactionname="SELECT" lasttranstarted="2023-02-02T09:40:11.237" lockMode="RangeS-S" schedulerid="1" kpid="6632" status="suspended" spid="77" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2023-02-02T09:40:11.237" lastbatchcompleted="2023-02-02T09:40:11.240" lastattention="1900-01-01T00:00:00.240" clientapp=".Net SqlClient Data Provider" isolationlevel="serializable (4)" xactid="26395737" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
            <frame line="1" stmtstart="50" stmtend="2520"/>
            <frame line="1"/>
        </executionStack>
        <inputbuf>{{Query 1}}</inputbuf>
    </process>
</blocked-process>
<blocking-process>
    <process status="sleeping" spid="74" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-02-02T09:40:11.210" lastbatchcompleted="2023-02-02T09:40:11.210" lastattention="1900-01-01T00:00:00.210" clientapp=".Net SqlClient Data Provider" isolationlevel="read committed (2)" xactid="26390106" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack/>
        <inputbuf>{{Query 2}}</inputbuf>
    </process>
</blocking-process>

• 连接复用与隔离级别继承：DataContext默认复用数据库连接，如果之前有操作（同一DataContext实例的其他查询或共享连接池的请求）使用了Serializable隔离级别，且连接未被正确重置，后续查询会继承该级别。
• TransactionScope的Suppress选项：使用Suppress意味着当前操作不参与事务，但如果DataContext的连接之前已关联事务，可能导致隔离级别未被正确覆盖。
• LINQ to SQL查询缓存：LINQ to SQL会缓存编译后的查询计划，若缓存的计划关联了Serializable级别，复用计划时会沿用错误的隔离级别。
• 连接池状态残留：连接池中的连接可能保留之前会话的隔离级别设置，新请求获取这类连接时，会直接使用该级别而非代码配置的ReadCommitted。

• 模拟连接复用：在调用GetEntries前，用同一个DataContext实例执行一次Serializable级别的查询，随后立即调用GetEntries，观察第二个查询的隔离级别；或在多线程场景下混合执行不同隔离级别的操作，触发连接池复用残留连接。
• 压力测试：借助JMeter或多线程单元测试工具模拟高并发，同时执行不同隔离级别的查询，提升连接复用和缓存命中的概率。
• 强制缓存命中：多次调用相同参数的GetEntries，让LINQ to SQL复用编译后的查询计划，观察是否出现隔离级别切换。

1. 显式设置查询隔离级别：
   不依赖TransactionScope配置，直接为每个查询指定隔离级别：

   // 在查询前执行SQL设置隔离级别
   dataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
   var collection2 = dataContext.Table2.Where(...).Select(...).ToArray();
   

2. 避免连接复用（临时方案）：
   为每个查询创建新的DataContext实例，确保连接不会继承之前的隔离级别，但需注意频繁创建会影响性能。
3. 调整TransactionScope配置：
   将Suppress改为RequiresNew，强制创建新事务并覆盖隔离级别：

   using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
   {
       var result = Get(dataContext, Id, Ids).Select(...).ToList();
       transactionScope.Complete();
       return result;
   }
   

4. 禁用查询缓存：
   设置DataContext.ObjectTrackingEnabled = false（无需实体跟踪时），或使用CompiledQuery显式指定隔离级别，避免缓存计划带来的问题。

内容的提问来源于stack exchange，提问作者Ram Nivas