Cannot drop Firebird table when using multiple connections

Question

I would like to safely drop Firebird table. I have 3 transactions, one to recreate table, one to do something with the table (just inserting a single row to keep it simple) and the last one to drop the table. If all these txns are executed using single connection these works. If I use a different connection, then the drop command fails with

lock conflict on no wait transaction unsuccessful metadata update object TABLE "DEMO" is in use

private static void Test() {
        using var conn1 = new FbConnection(ConnectionString);
        using var conn2 = new FbConnection(ConnectionString);
        using var conn3 = new FbConnection(ConnectionString);
        conn1.Open();
        conn2.Open();
        conn3.Open();
        ExecuteTxn(conn1, cmd => {
            cmd.CommandText = "recreate table demo (id int primary key)";
            cmd.ExecuteNonQuery();
        });
        ExecuteTxn(conn2, cmd => {
            cmd.CommandText = "insert into demo (id) values (1)";
            cmd.ExecuteNonQuery();
        });            
        ExecuteTxn(conn3, cmd => {
            cmd.CommandText = "drop table demo";
            cmd.ExecuteNonQuery();
        });            
    }

    private static void ExecuteTxn(FbConnection conn, Action<FbCommand> todo) {
        using (var txn = conn.BeginTransaction())
        using (var cmd = conn.CreateCommand()) {
            cmd.Transaction = txn;
            todo(cmd);
            txn.Commit();
        }
    }

I realized that changing the transaction options as

txn = conn.BeginTransaction(new FbTransactionOptions { TransactionBehavior = FbTransactionBehavior.Wait }))

seems to help. But I'm not sure if this the right thing to do or just a coincidence...

Using Firebird 3.0.6, FirebirdSql.Data.FirebirdClient.dll 7.5.0.0

Answer 1

As far as I understand it, the problem has to do with how Firebird caches certain metadata, which might result in existence locks being retained, which will prevent deletion of the object. In addition, it is possible - this is a guess! - that the Firebird ADO.net provider retains the statement handle with the insert statement prepared, which will also result in an existence lock being retained.

Executing in a WAIT transaction (optionally with a timeout) is considered an appropriate workaround by the Firebird core developers.

For reference, see the following tickets:

• CORE-3766 - Transaction can`t change metadata if it is run in no_wait and there is another connect that once had queried these metadata
• CORE-6382 - Triggers accessing a table prevent concurrent DDL command from dropping that table

In certain cases, switching from Firebird ClassicServer or Firebird SuperClassic to Firebird SuperServer can also prevent this problem.

However, if you want a more in-depth explanation, it might be worthwhile to ask this question on the firebird-devel mailing list.