Table drops not actually done by Firebird until application closed

Viewed 695

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.

1 Answers
Related