PostgreSQL: A command is already in progress in Asp.Net Core
Dung Do Tien
Apr 13 2021
402
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.
-
D0
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
INewsDaointerface by usingScopelifetime.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
AddScopedtoAddTransientyour issue will be resolved.services.AddTransient<INewsDao, NewsDao>();I hope it work for you.
-
N0
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.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.