Table drops are not actually executed by Firebird until the application is closed

I have a .NET test application that creates a table, uses them, and then throws them many times.

When I run this application against Firebird 3.0.2 database with ADO.Net Firebird 5.9.1 data provider, it doesn't work soon as it has to create a table with the same name as the previously dropped one: the table is already there!

Restarting the app dodges the problem, but I don't want to restart it after every test.

This question is very similar but hangs on a blob instead and uses Firebird tool directly isql

instead of .Net application.

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

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

Below is the MCVE error for Firebird. Replace the first two lines with the appropriate code for the connection string. All other code is the ADO.Net Firebird and NUnit data provider. This is not as accurate as my actual application, but I think it is the same basic problem.

[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();
        }
    }
}

      

        
        
        
      

    

The selection from the table alone was insufficient. The problem only came up after I added the delete to the test. It fails on the last transactional commit, which is drop, with the 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 the Github discussion , this issue seems to be limited to Firebird's ADO.Net data provider only. He narrowed it down to upgrading from 2.7.7 to 3.0.0.