Table drops not actually done by Firebird until application closed

Question

I have a .net test application which creates table, uses them, then drops them, many times.

When I run this application against a Firebird 3.0.2 database with Firebird ADO.Net Data Provider 5.9.1, it fails at soon as it has to create a table with the same name than a previously dropped one: table does already exist!

Restarting the application dodge the trouble, but I do not want to restart it after each test.

This question is very similar but is hanging at the drop instead, and use directly the Firebird isql tool instead of a .Net application.

Is there a way to actually drop the tables in Firebird without restarting the application?

This application tests many other databases, without this issue: SQL-Server, SQL-Server Compact Edition, MySql, SQLite, Oracle, PostgreSQL.

Here is a MCVE failing for Firebird. Replace the two first lines with suitable code for having a connection string. All other code is just Firebird ADO.Net data provider and NUnit. It does not fail exactly as my actual application, but I think it is the same underlying trouble.

[Test]
public void CreateSelectDrop()
{
    var cfg = TestConfigurationHelper.GetDefaultConfiguration();
    var cnxStr = cfg.Properties[Environment.ConnectionString];
    using (var cnx = new FbConnection(cnxStr))
    {
        cnx.Open();
        using (var tran = cnx.BeginTransaction())
        {
            using (var cmd = cnx.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "create table test (id int not null primary key)";
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        }
    }

    using (var cnx = new FbConnection(cnxStr))
    {
        cnx.Open();
        using (var tran = cnx.BeginTransaction())
        {
            using (var cmd = cnx.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert into test (id) values (1)";
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        }
    }

    using (var cnx = new FbConnection(cnxStr))
    {
        cnx.Open();
        using (var tran = cnx.BeginTransaction())
        {
            using (var cmd = cnx.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select id from test";
                using (var reader = cmd.ExecuteReader())
                {
                    Assert.IsTrue(reader.Read());
                    Assert.AreEqual(1, reader.GetInt32(0));
                    Assert.IsFalse(reader.Read());
                }
            }
            tran.Commit();
        }
    }

    using (var cnx = new FbConnection(cnxStr))
    {
        cnx.Open();
        using (var tran = cnx.BeginTransaction())
        {
            using (var cmd = cnx.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "delete from test";
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        }
    }

    using (var cnx = new FbConnection(cnxStr))
    {
        cnx.Open();
        using (var tran = cnx.BeginTransaction())
        {
            using (var cmd = cnx.CreateCommand())
            {
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "drop table test";
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        }
    }
}

Only selecting from the table was not enough. The trouble appeared only after I have added the delete to the test. It fails at the last transaction commit, the one of the drop, with message:

FirebirdSql.Data.FirebirdClient.FbException : lock conflict on no wait transaction
unsuccessful metadata update
object TABLE "TEST" is in use
  ----> FirebirdSql.Data.Common.IscException : lock conflict on no wait transaction
unsuccessful metadata update
object TABLE "TEST" is in use
   at FirebirdSql.Data.FirebirdClient.FbTransaction.Commit()
   at NHibernate.Test.DialectTest.FirebirdDialectFixture.CreateSelectDrop()

According to Nathan Brown in a Github discussion, this trouble seems bounded to Firebird ADO.Net Data provider only. He has narrowed it to switching from its 2.7.7 version to 3.0.0.

Answer 1

It appears the table drops are delayed until the connections having used them are actually closed, not only returned to the pool. Clearing the connection pool causes those delayed drops to be actually done.

So adding something like following code before table drops solves this issue in my case:

using (var connection = GetConnection())
{
    FbConnection.ClearPool(connection);
}

This solution was found here in an isolated test among more than 5 000.

It looks like there is another option, calling FbConnection.ClearAllPool() instead. Although I have not checked it, the former is likely to clear only the connection pool for supplied connection connection-string, while the later clears the pool of all connections, regardless of their connection string.

Since it is a test application with some generic logic and also some specificity, the code I will actually use as a solution will be:

// Firebird will pool each connection created during the test and will 
// marked as used any table referenced by queries. It will delays those
// tables drop until connections are actually closed.
// This results in other tests failing when they try to create tables with
// same name.
// By clearing the connection pool the tables will get dropped. This is done
// by the following code.
// Moved from NH1908 test case, contributed by Amro El-Fakharany.
var clearConnection = Sfi.ConnectionProvider.GetConnection();
try
{
    var fbConnectionType = clearConnection.GetType();
    var clearPool = fbConnectionType.GetMethod("ClearPool");
    clearPool.Invoke(null, new object[] {clearConnection});
}
finally
{
    Sfi.ConnectionProvider.CloseConnection(clearConnection);
}