"A command is already in progress" - connection pooling default configuration failing

[fredczj]

Hi,

When using default pooling configuration on localhost DB, I have plenty of "a command is already in progress", and sometimes, "connection already open" issues. I use traditional connection procedure services.AddEntityFrameworkNpgsql().AddDbContextPool<DBApiContext>(opt => opt.UseNpgsql(configuration.GetConnectionString("DBConnection"))); Connection String only activates Pooling=true. "DBConnection": "User ID=****;Password=****;Server=localhost;Port=5432;Database=*****;Pooling=true;", I used postgresql v9.5 and v10. .NETCore and PostgreSQL are hosted on the same machine. It sounds like the Pooling has not started ?

By the way, the connection to the DB is very easy:

var userQuery = this._db.PlaPlayer.Where(p => p.Nickname==credent.username);
if( userQuery.Count() != 1 ) 

Is there any way to track the Pooling activity? Or to enable it ? The model is easy too:

    public partial class DBApiContext : DbContext
    {
        public virtual DbSet<PlaPlayer> PlaPlayer { get; set; }

        public DBApiContext(DbContextOptions<DBApiContext> options)
            : base(options)
        { }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<PlaPlayer>(entity =>
            {
                entity.ToTable("pla_player");

                entity.Property(e => e.Id)
                    .HasColumnName("id");

                entity.HasIndex(e => e.Nickname)
                    .HasName("nickname_unique")
                    .IsUnique();

                entity.Property(e => e.Nickname)
                    .IsRequired()
                    .HasColumnName("nickname");
            });
        }
    }

[roji]

The error "command already in progress" indicates that the same connection is being used concurrently. In EF Core this would generally mean that you're trying to use the same DbContext instance twice at the same time. It seems from your code sample that you're keeping a reference to the same DbContext instance (this._db) - this could be the source of your problem.

In general, DbContext instances are light and should be created every time you need to access the database - you should not in general be caching them in your code (although see EF Core 2.0's new pooled contexts). Carefully look at what your code is doing and when each instance is used, to understand whether the same instance is accidentally used concurrently. If you can't figure it out try to post a minimal code sample which reproduces the issue.

[fromm1990]

I have a similar problem. I suspect this have something to do with the implementation of the DbContextPool. While debugging locally I did not have any issue, however, while launching the application to Google Cloud i got an exception stating that "A command is already in progress". I then changed from AddDbContextPool to AddDbContext and the issue vanished.

[fredczj]

Hey, I have been ahead in having a more decent implementation, and a better understanding of what happen in EntityFramework. I have read this: http://mehdi.me/ambient-dbcontext-in-ef6/ , which gave few ways to implement DB (including postgresql) things. Now my access are all bounded with "with" statements, making the pooling to postgresql as it should be.

[roji]

Guys, I'm going to need a concrete code sample which reproduces the issue, otherwise I'm not going to be able to investigate...

[roji]

Closing as no repro has been provided. If someone can post a minimal scenario which demonstrates the issue we'll revisit.

[thefringeninja]

A bit off topic but this was the #1 search result when googling "A command is already in progress." This can also happen if you don't dispose the reader before executing another command against the same connection.

[MeghanaJai]

Hello,

I have a similar problem.

private void worker_Netted_DoWork(object sender, DoWorkEventArgs e) { // Set status toolStripStatus.Text = "Retrieving netted..."; DisplayRetrievingMessage(dataGridView_Netted);

        String formattedDates = Convert.ToString(e.Argument);
         formattedDates = dateTimePicker_StartDate.Value.ToString(formattedDates);
        Console.WriteLine("Treasury Netted " + formattedDates);

        // Execute the procedure
        _myDB.createCommand();
        _myDB.getNpgCommand().CommandTimeout = 0;
        _myDB.getNpgCommand().CommandText = "pkg_maker_ihm_maker_data_netted";
        _myDB.getNpgCommand().CommandType = CommandType.StoredProcedure;

        // Set datareader and dataset for postgre   
        _myDB.getNpgCommand().Parameters.Add(new Npgsql.NpgsqlParameter("p_date", formattedDates));
        DataTable results = new DataTable();
        _myDB.launchNpgCommand(results);
        e.Result = results;
    }

{ // Set status toolStripStatus.Text = "Retrieving blocked..."; DisplayRetrievingMessage(dataGridView_Blocked);

        String formattedDates = Convert.ToString(e.Argument);
        formattedDates = dateTimePicker_StartDate.Value.ToString(formattedDates);

        Console.WriteLine("Treasury Blocked " + formattedDates);

        // Execute the procedure
        _myDB.createCommand();
        _myDB.getNpgCommand().CommandText = "pkg_maker_ihm_maker_data_blocked";
        _myDB.getNpgCommand().CommandType = CommandType.StoredProcedure;
        // Set datareader and dataset for postgre   
        _myDB.getNpgCommand().Parameters.Add(new Npgsql.NpgsqlParameter("p_date", formattedDates));          
        DataTable results = new DataTable();
        _myDB.launchNpgCommand(results);

        e.Result = results;
    }

I am getting below error mentioned : {"A command is already in progress: pkg_maker_ihm_maker_data_blocked"}

Both the functions separately working fine, but together when i execute, its giving error. Could you please help.

[austindrenski]

@MeghanaJai This is the repository for the Entity Framework Core provider, but your example does not appear to us EF Core. For assistance, please open a ticket in the repo for the ADO.NET driver, https://github.com/npgsql/npgsql.

[MeghanaJai]

Its regarding NPGSQL. I am giving the stacktrace of the error.

at Npgsql.NpgsqlConnector.StartUserAction(ConnectorState newState, NpgsqlCommand command) at Npgsql.NpgsqlCommand.d__92.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult() at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at CRELibrary.DAO.POSTGRE.PostgreDatabase.launchNpgCommand(DataTable _resultListe) in D:\CrePostGreMigration\IHM\CRELibrary\DAO\POSTGRE\PostGreDataBase.cs:line 94 at CRE.frmTresorerie.worker_Netted_DoWork(Object sender, DoWorkEventArgs e) in D:\CrePostGreMigration\IHM\CRE\GUI\FORM\FormTresorerie.cs:line 744 at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

[YohDeadfall]

As @austindrenski said, there are a few repositories in our project. This one is for Entity Framework Core provider issues only. Your problem doesn't fit here, since you use the driver only. Therefore, move the issue to the appropriate repository if you think it's a bug, but I would recommend you to take a look on https://github.com/npgsql/npgsql/issues/2080 which is highly related if not the same.

[MeghanaJai]

I understand, but using Backgroundworker is creating problem is what I understood. I referred the link you have mentioned but I didnt get much help. So I am copying my code snippet of using Backgroundworker. Could you guide me what alternative can be used. Let me know if any other solutions are available, I will refer to that. Please let me know what can I use in the place of "RunWorkerAsync"

private System.ComponentModel.BackgroundWorker worker_Netted; private System.ComponentModel.BackgroundWorker worker_Treasury; private System.ComponentModel.BackgroundWorker worker_QTR;

         if (!worker_Netted.IsBusy)
            worker_Netted.RunWorkerAsync(formattedDates);

        if (!worker_Blocked.IsBusy)
            worker_Blocked.RunWorkerAsync(formattedDates);

        if (!worker_QTR.IsBusy)
            worker_QTR.RunWorkerAsync(formattedDates);

After executing this, it will call the function which I have mentioned in my last comments. Thankyou for the cooperation.

[roji]

@MeghanaJai please make an effort to understand the difference between the Npgsql Entity Framework Core provider, and the Npgsql ADO.NET provider. Your code seems to be using only ADO.NET, and not Entity Framework Core, and so belongs in http://github.com/npgsql/npgsql/ and not here. Please open a new issue in that repo rather than responding here.

In addition, please make an effort to properly format your code blocks, preferably with syntax highlighting, it's quite difficult to read your code.

[statpeek]

Hello All,

Is there any update on workarounds for this error? I am getting this for my Background Worker (after moving from SQL Server to Npsql).

Here is just a small snippet of code that is causing me headaches:

// Add Any Franchises that don't exist
var franchiseDumps = this.context.RequestDumps.Where(rd => rd.Processed == false && rd.DumpType == "leagueteams");
foreach (RequestDump teamDump in franchiseDumps)
{
    var league = this.context.Leagues.Include(l => l.MaddenFranchises).FirstOrDefault(l => l.Id == teamDump.LeagueID);
    var teams = Jeeves.GetJSONFromKey<List<DTOMaddenTeam>>(teamDump.JsonDump, "leagueTeamInfoList");

    foreach (DTOMaddenTeam team in teams)
    {
        this.UpdateFranchise(team, league);
    }

    this.logger.LogInformation($"DTO Service Processed League Teams on count {this.executionCount}");
}

For clarification, I have added my context as transient, and allowed pooling, but the problem still persists.

[roji]

@statpeek looks like this was cross-posted as https://stackoverflow.com/questions/61052687/a-command-is-already-in-progress/61054398, provided a comment there with the explanation.

[ercanerdogan]

I had a similar problem and then I have added my context as transient my problem has been fixed. I just want to share my solution.

services.AddDbContext<DBContext>(<connectionstring>, ServiceLifetime.Transient);

this worked for me.

[Keerthikan]

A bit off topic but this was the #1 search result when googling "A command is already in progress." This can also happen if you don't dispose the reader before executing another command against the same connection.

Would scoping it within a using not be enough?

[zabidin901]

@statpeek looks like this was cross-posted as https://stackoverflow.com/questions/61052687/a-command-is-already-in-progress/61054398, provided a comment there with the explanation.

This is still happening and this solution is not valid (even with pooling turned off and materializing the request). I'm on version 5.0.2.

This seems to only happen when multiple subsequent calls are sent. I have an API where the controller makes an Async call to a manager class to make a db query. That DB query works fine the first few times...then the fourth or fifth time I see this error:

Connection is already open
 at Npgsql.NpgsqlConnection.CheckClosed()
   at Npgsql.NpgsqlConnection.Open(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.OpenAsync(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.<OpenInternalAsync>d__58.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.<OpenInternalAsync>d__58.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
...

I have also listed out the context Id and added console logs to make sure I'm not reusing the same DBContext. Turned pooling off and even made sure I have materialized the query. Still happens.

[roji]

@zabidin901 please open a new issue with a runnable code sample.

[code33]

in my Program.cs inject type change services.AddSingleton( => new NpgsqlConnection(connStr)); to services.AddScoped( => new NpgsqlConnection(connStr)); then fix