A timeout exception occurred in C# LINQ due to an unexpected change in the isolation level from the default setting to Serializable

Question

In my scenario, I have a method called GetEntries that invokes another method called Get within a transaction scope. Get method contains three LINQ queries. The first query retrieves a single collection, the second query also retrieves a single collection, and the third query performs a group join using both collections.

Pseudo code

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

The Get method is called inside a TransactionScope

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

Upon investigation, it appears that the first query executed with the expected isolation level, which is "ReadCommitted." However, the second query unexpectedly executed with the "Serializable (4)" isolation level. This change in isolation level caused a timeout exception, as reported in the blocked-process-report.

To determine why the isolation level changed, I'm unable to pinpoint the exact cause at the moment. However, this issue has occurred sporadically in our production server.

Why it happens and how to reproduce it in a local machine? How to fix it?

Exception:

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

Can you post the code for the transaction including where you set the IsolationLevel? — Peter Dongan
– Peter Dongan, Commented Jun 19, 2023 at 15:26
When you do .ToArray(); you are fetching the entire collection to the client side, and then I believe your GroupJoin() is also going to be done on the client side. Why do that? See e.g. LINQ query has bad performance while putting .ToArray() inside from/in. — dbc
– dbc, Commented Jun 19, 2023 at 15:38
@RamNivas - I don't currently have an environment to test it (which is why I added a comment instead of an answer), but I believe you can remove the ToArray() calls. Give it a try and see if the performance improves. Note sure about reproducing the issue in local. — dbc
– dbc, Commented Jun 19, 2023 at 18:34
Reading further, in your Where clause d => d.Id == Id && Ids.Contains(d.Id), the expression Ids.Contains(d.Id) is effectively constant. Since we know d.Id == Id the value is the same as Ids.Contains(Id). Why not just evaluate that outside the database query, and modify your query accordingly? And similarly for Ids.Contains(r.Id). Furthermore, if Ids.Contains(Id) is false, isn't your query guaranteed to return nothing? Do you really need Ids at all? Or did you mean d.Id == Id || Ids.Contains(d.Id)? — dbc
– dbc, Commented Jun 19, 2023 at 19:34

Sanjeev kaushik · Accepted Answer · 2023-06-26 13:43:23Z

-2

You can increase the SQL command timeout in LINQ

dataContext.CommandTimeout = 3 * 60; // 3 Mins

edited Jun 26, 2023 at 13:43

user16612111

answered Jun 23, 2023 at 10:16

Sanjeev kaushik

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

A timeout exception occurred in C# LINQ due to an unexpected change in the isolation level from the default setting to Serializable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related