PostgreSQL: A command is already in progress in Asp.Net Core

Dung Do Tien Apr 13 2021 409

I have a project, it wrote by Asp.Net Core and I use Dapper to help connect with the PostgreSQL database. I have articlecategories table and want to get all data from it. I wrote code as below:

public class NewsDao : BaseDao, INewsDao
{
    public async Task<IEnumerable<ArticleCategoryDto>> ListArticleCategory()
    {
        StringBuilder query = new StringBuilder();
        query.Append("SELECT id, name, description, parentid, metatitle, metadescription, alias, metakeyword ");
        query.Append("FROM articlecategories ");
        query.Append("WHERE status > 0 ");

        return await QuerySqlAsync<ArticleCategoryDto>(query.ToString());
    }
}

When I push 100 users same time(CCU) it works fine for me but when I change to 200 CCU it throws many errors A command is already in progress: SELECT id, name, description, parentid, metatitle, metadescription, alias, metakeyword FROM articlecategories WHERE status > 0

Npgsql.NpgsqlOperationInProgressException (0x80004005): A command is already in progress: SELECT id, name, description, parentid, metatitle, metadescription, alias, metakeyword FROM articlecategories WHERE status > 0 
   at Npgsql.NpgsqlConnector.<StartUserAction>g__DoStartUserAction|233_0(<>c__DisplayClass233_0& )
   at Npgsql.NpgsqlConnector.StartUserAction(ConnectorState newState, NpgsqlCommand command, CancellationToken cancellationToken, Boolean attemptPgCancellation)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 473

And I register a connection with DI as below:

var connectionsDic = "Connection string here";
services.AddTransient<IDbConnection>(db => (NpgsqlConnection)new DbConnectionFactory(connectionsDic).CreatePostgreSqlConnection("ConnectionString"));

And I add DI for NewsDao class as below:

services.AddScoped<INewsDao, NewsDao>();

How can I resolve it?

Have 2 answer(s) found.
  • D

    Dương Trang Quốc Apr 13 2021

    Do you push CCU by tool or real traffic?

    If you push by tool and they are same IP, you will get this error because you register DI for INewsDao interface by using Scope lifetime.

    Because Scope only re-instanse by URL some case they use the same connection and throw the error A command is already in progress

    So you only need to change AddScoped to AddTransient your issue will be resolved.

    services.AddTransient<INewsDao, NewsDao>();

    I hope it work for you.

  • N

    Nguyễn Danh Bảo An Apr 15 2021

    This error throws when you open a connection in a loop. For example:

    for(var category in ListCategory){
        StringBuilder query = new StringBuilder();
        query.AppendFormat("SELECT * from articles where categoryid = {0}", category.Id);
        var result = await QuerySingleOrDefaultAsync<int>(query.ToString());
    }

    if data is very small you can get it all and using LinQ to query or cache it.

    You also need to check the max connection of your database, as I know it defaults to 100, you can change it to 1000.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close