0

A connection pool in FirebirdClient seems to be broken. It should queue my queries when is already empty but I get an exception instead. I need a help how to deal with this problem.

I'm using FirebirdClient version 7.1.1. I am running a console .Net Core app (version 2.2.0) and I am connecting Firebird 3.0. I execute 4 concurrent queries. I have MaxPoolSize set to 2. So there is not enough connections to execute all queries.

using System;
using System.Threading.Tasks;
using FirebirdSql.Data.FirebirdClient;

namespace ConsoleApp3
{
    class Program
    {
        private const string connStr =
            "User=SYSDBA;Password=***;Database=/opt/firebird/bases/db.fdb;DataSource=192.168.2.1;Port=3050;Connection lifetime=15;Pooling=true; MinPoolSize=0;MaxPoolSize=2;";

        private static void Connect(string name)
        {
            Console.WriteLine($">>>>>>STARTING>>>>>>>>>>>> {name}");

            var cmd = new FbCommand(
                "select count(*) from MON$ATTACHMENTS a where a.mon$remote_process like '%ConsoleApp3.dll'");
            using (cmd.Connection = new FbConnection(connStr))
            {
                try
                {
                    cmd.Connection.Open();
                    var id = Convert.ToInt32(cmd.ExecuteScalar());
                    Task.Delay(1000);
                    Console.WriteLine($">>>>>>Done>>>>>>>>>>>> {name} RESULT {id}");
                }
                catch (Exception e)
                {
                    Console.WriteLine($">>>>>>>ERROR>>>>>>>>>>> {name}");
                    Console.WriteLine(e);
                }
            }
        }

        static void Main(string[] args)
        {
            var t1 = Task.Run(() => Connect("conn1"));
            var t2 = Task.Run(() => Connect("conn2"));
            var t3 = Task.Run(() => Connect("conn3"));
            var t4 = Task.Run(() => Connect("conn4"));

            Task.WaitAll(t1, t2, t3, t4);

            Console.WriteLine(">>>>>>>>>>>> DONE>>>>>>>>>>>>>>>");
            Console.ReadKey();
        }
    }
}

I expect that queries that don't have free connection will be queued and executed when the connection to database will be reclaimed to the connection pool but instead I get a following exception:

System.InvalidOperationException: Connection pool is full.
   at FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Pool.CreateNewConnectionIfPossibleImpl(ConnectionString connectionString)
   at FirebirdSql.Data.FirebirdClient.FbConnectionPoolManager.Pool.GetConnection(FbConnection owner)
   at FirebirdSql.Data.FirebirdClient.FbConnection.Open()
   at ConsoleApp3.Program.Connect(String name) in C:\Projects\ConsoleApp3\ConsoleApp3\Program.cs:line 22
Arioch 'The
  • 15,799
  • 35
  • 62
  • hint: try maillist - https://stackoverflow.com/tags/firebird-.net-provider/info – Arioch 'The Sep 26 '19 at 16:30
  • As far as I know, your expectation is wrong. Why do you think your query would be queued? In any case, this is probably more suitable to ask on the Firebird-net-provider mailing list / Google group. – Mark Rotteveel Sep 26 '19 at 16:51
  • I based my expectation on the behaviour of the client Npgsql for postgres database. The same code with that client works correctly. I don't have an exception and I have proper data. – Piotr Kościelniak Sep 26 '19 at 19:36
  • maybe it is client for postgress that behaves wrong? or maybe it is unspecified implementation-defined behavior. Anyway, it would be more efficient to ask provider developer about the choice he made, like was suggested above. – Arioch 'The Sep 27 '19 at 07:22
  • 1
    It seems that not only Npgsql behaves that way. As it stated on that page https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling `If the maximum pool size has been reached and no usable connection is available, the request is queued.` – Piotr Kościelniak Sep 29 '19 at 20:00
  • @PiotrKościelniak good find. However by this source snippet - `SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;..."` - it seems to me the talk is not about generic rule for all SQL servers, but about implementation detail of Microsoft SQL Server. One may argue it is better for other clients/servers to mimic MS SQL behavior, but it is not clear that is mandatory. Perhaps you'd ask FB Provider developer about reason for his choice. Maybe there is some config option to override it. – Arioch 'The Sep 30 '19 at 09:02
  • @Arioch'The thank you for the answer. I hope that some FB Provider developer will find this question and give me some clue how to deal with my problem. – Piotr Kościelniak Oct 01 '19 at 14:29
  • I gave no answer as I have none. And I would not hold my breath that developer would find you, he is rare guest on SO. Better you find him - see the first two comments up there. – Arioch 'The Oct 01 '19 at 16:06

0 Answers0