CODEWITHSUNDEEP
c#multithreadingentity-frameworkasync-await
g36
g36

Reputation: 513

EF SqlQuery throws not supported MultipleActiveResultSets exception if previous operation timeouts out

I have the following logic:

public async Task UpdateData(DbContext context)
{
    try
    {
        await LongUpdate(context);
    }
    catch (Exception e)
    {
        try
        {
            await context.Database.ExecuteSqlCommandAsync($@"update d set d.UpdatedAt = GETDATE() from SomeTable d where id > 11");
        }
        catch (Exception ex)
        {
            throw;
        }
    }
}
// this operations takes about 1 minute
private static async Task<int> LongUpdate(DbContext context)
{
    context.Database.CommandTimeout = 5; // change this to 15 to see MultipleActiveResultSets exception
    return await context.Database.SqlQuery<int>($@"update otherTable set UpdatedAt = GETDATE();SELECT @@ROWCOUNT").FirstOrDefaultAsync();
}

As presented above there are two update operations both awaited. LongUpdate takes more than minute.

When timeout is set to 5s: LongUpdate throws timeout exception and the second update is executed successfully.

When I increase timeout to 15s or more: LongUpdate throws timeout exception but second update immediately throws: System.InvalidOperationException: The connection does not support MultipleActiveResultSets..

Shouldn’t await prevent this exception? Why this depends on timeout value?

Upvotes: 4

Views: 556

Answers (3)

Michael Ushakov
Michael Ushakov

Reputation: 2809

According to EF docs Database property should not be used in a way you do. So because it is as i think incorrect way we could not even consider what is happenning. All you db operations should go via Database Context using DbSet<T> with Save or SaveAsyncmethod ofDbContext` call after changes in datasets. Of course you could execute raw sql but other way like this:

public static IList<StockQuote> GetLast(this DbSet<StockQuote> dataSet, int stockId)
{
    IList<StockQuote> lastQuote = dataSet.FromSqlRaw("SELECT * FROM stockquote WHERE StockId = {0} ORDER BY Timestamp DESC LIMIT 1", new object[] { stockId })
                                         .ToList();
    return lastQuote;
}

To create DbContext (in below example to MySql) with command timeout you coulde use something like this:

public static class ServiceCollectionExtension
{
    public static IServiceCollection ConfigureMySqlServerDbContext<TContext>(this IServiceCollection serviceCollection, string connectionString,                                                                         
         ILoggerFactory loggerFactory, int timeout = 600)
            where TContext : DbContext
    {
        return serviceCollection.AddDbContext<TContext>(options => options.UseQueryTrackingBehavior(QueryTrackingBehavior.TrackAll)
                .UseLoggerFactory(loggerFactory)
                .UseMySql(connectionString, ServerVersion.AutoDetect(connectionString), sqlOptions => sqlOptions.CommandTimeout(timeout))
                .UseLazyLoadingProxies());
    }
}

just call services.ConfigureMySqlServerDbContext<ModelContext>(Settings.ConnectionString, loggerFactory);

I think if you change your approach you get rid of exceptions.

Upvotes: 1

MD. RAKIB HASAN
MD. RAKIB HASAN

Reputation: 3956

The command timeout is distinct from the connection timeout. A value set with this API for the command timeout will override any value set in the connection string. Database.CommandTimeout Property is use for Gets or sets the timeout value, in seconds, for all context operations.

private static async Task<int> LongUpdate(DbContext context)
{
    context.Database.CommandTimeout = 5; // change this to 15 to see MultipleActiveResultSets exception
    return await context.Database.SqlQuery<int>($@"update otherTable set UpdatedAt = GETDATE();SELECT @@ROWCOUNT").FirstOrDefaultAsync();
}

here you set CommandTimeout, If your query not execute in 5 second then TimeoutException fired and after that you are trying to execute another query in catch block, but you use same context here, which is already timeout and its throws: System.InvalidOperationException:. So to fix this you have to initialize your context again.

public async Task UpdateData(DbContext context)
{
    try
    {
        await LongUpdate(context);
    }
    catch (Exception e)
    {
        try
        {
            context = new MyContext()// initialize your DbContext here.
            await context.Database.ExecuteSqlCommandAsync($@"update d set d.UpdatedAt = GETDATE() from SomeTable d where id > 11");
        }
        catch (Exception ex)
        {
            throw;
        }
    }
}

Upvotes: 0

Amirhossein Mehrvarzi
Amirhossein Mehrvarzi

Reputation: 18974

Shouldn’t await prevent this exception?

It depends on your pattern. We need to ensure that all access is sequential. In another word, the second asynchronous request on the same DbContext instance shouldn't start before the first request finishes (and that's the whole point). Although This is typically done by using the await keyword on each async operation, in some cases we may not achieve it. In your case, the first part of LongUpdate method execution, context.Database.SqlQuery<int>() is not an async method itself. It will provide results synchronously for FirstOrDefaultAsync(). I think this is not a problem with EF async behavior.

Why does it depend on the timeout value?

After a specific amount of time, the SQL query execution enters a critical state that can't leave it without spending more time than what you set as CommandTimeout, but your code moves forward and, the exception happens.

Note the applications that have IO-related contention will benefit the most from using asynchronous queries and save operations according to Performance considerations for EF 4, 5, and 6. The page EF async methods are slower than non-async lists some noticeable points.

Upvotes: 0

Related Questions