使用双嵌入式Firebird的TFDConnection删表时触发元数据更新失败

Let's break down your issue and walk through practical fixes to resolve this metadata update error when using two embedded Firebird connections.

Your Current Flow & The Core Problem #

First, let's align on your execution pipeline to ground our solution:

• Set up a design-time target TFDConnection
• Spin up a runtime source TFDConnection
• Wipe all indexes and tables from the target
• Recreate those objects
• Copy all source data to the target
• Error triggers here: When attempting to drop TT_SYS_WEEKS and its indexes (via DROP INDEX <OWNER>TT_I1_SYS_WEEKS and related ALTER TABLE statements)
• Finally, recreate TT_SYS_WEEKS and populate it

All connection combinations work smoothly except when both are embedded Firebird. In that scenario, you hit an unsuccessful metadata update error that blocks the table deletion.

Why This Happens with Embedded Firebird #

Embedded Firebird is file-based, so it has far stricter locking rules than the client-server variant. Here are the key culprits:

1. Uncommitted Metadata Transactions: Operations like DROP INDEX or ALTER TABLE require an explicit commit to release file locks. Skipping this leaves locks held, blocking subsequent steps.
2. Concurrent File Access: Embedded Firebird doesn’t support multiple active connections to the same database file by default. If your source and target point to the same file (even via separate TFDConnections), lock conflicts will occur during metadata changes.
3. Lingering Object References: If a dataset, query, or cursor on either connection is still active on TT_SYS_WEEKS or its indexes, the embedded engine can’t modify the metadata for those objects.

Step-by-Step Fixes #

1. Wrap All Metadata Operations in Explicit Transactions

Every time you run a metadata-altering statement, wrap it in a transaction and commit immediately. This releases locks right away:

// Example for dropping the index
TargetConnection.StartTransaction;
try
  TargetConnection.ExecSQL('DROP INDEX <OWNER>TT_I1_SYS_WEEKS');
  TargetConnection.Commit;
except
  TargetConnection.Rollback;
  raise;
end;

// Repeat this pattern for ALTER TABLE and DROP TABLE steps

Don’t rely on auto-commit here—explicit transaction control is non-negotiable for embedded Firebird.

2. Avoid Concurrent Embedded Connections to the Same DB

If your source and target are the same embedded database file, you can’t have both connections open at once. Adjust your flow:

• Fully close the source connection before starting step 6 (dropping TT_SYS_WEEKS)
• Reopen the source only after all target metadata changes are committed

// After copying data to target:
SourceConnection.Close;

// Execute step 6 with explicit transactions (as shown above)
// ...

// Reopen source if needed for step 7
SourceConnection.Open;

3. Use a Single Connection (If Possible)

If your source and target are the same database, ditch the two separate TFDConnections. Using one connection eliminates cross-connection lock conflicts entirely—this is the cleanest fix if your use case allows it.

4. Close All Active Datasets/Cursors First

Before modifying metadata, ensure no queries or datasets are still pointing to TT_SYS_WEEKS or its indexes on either connection:

// Close any datasets tied to the problematic table
SourceDataset.Close;
TargetDataset.Close;

// Now run your DROP/ALTER statements

Lingering cursors hold locks that block metadata changes—closing them first removes that barrier.

5. Match Firebird Embedded Versions

Double-check that both connections are using the exact same version of the Firebird embedded library. Mismatched versions can cause obscure locking and metadata handling glitches that are hard to debug.

Bonus Troubleshooting Tip #

Enable Firebird’s trace logging to get granular details about the error. The trace log will show exactly which lock is stuck and why the metadata update failed—this is invaluable for edge cases specific to your setup.

内容的提问来源于stack exchange，提问作者Jan Doggen