Firebird MGA and the Long-Running Transaction Trap

You run a nightly report inside a Firebird transaction. The report finishes, but someone forgets to commit. Over the next few days, ordinary updates to the same tables start slowing down, and the database file grows even though you are not inserting many rows. The likely cause is not missing indexes or hardware. It is Firebird’s Multi-Generational Architecture (MGA) waiting on that forgotten transaction before it can remove old record versions.

The practical takeaway: bound every transaction to the shortest possible lifetime, and monitor the gap between the oldest active transaction and the next transaction ID. If that gap grows, garbage collection is stalled and old versions are accumulating.

What MGA Stores When a Row Changes

Firebird does not overwrite a row in place when it is updated. Instead, it writes a new version of the record and stamps both versions with transaction IDs (TIDs). A transaction reads the version that was current when it started, based on its isolation level. In Snapshot isolation, for example, the transaction sees a fixed view of the database as it existed at start time.

Readers do not block writers, and writers do not block readers, because each transaction works with its own visible versions. The cost is storage: old versions remain on disk until garbage collection (GC) determines that no active transaction can still see them.

Why One Idle SELECT Can Stall Cleanup

Garbage collection can only remove a record version when every transaction that might need it has finished. A long-running read-only transaction holds the database “open” at its starting point. Updates and deletes that happen afterward create new versions, but the old versions cannot be reclaimed until the long-running transaction commits or rolls back.

Over time, this produces version chains. To find the current row, the engine may have to walk through several older versions, increasing disk I/O and slowing queries that previously ran quickly.

Worked Example: Watching Versions Pile Up

Use a scratch database, not production. Connect with isql as SYSDBA or the database owner.

isql -u SYSDBA -p masterkey /path/to/test.fdb

In session A, start a read-only transaction and leave it open:

SET TRANSACTION READ ONLY;
SELECT * FROM employees WHERE id = 42;
-- do not commit or roll back yet

In session B, update the same row in separate committed transactions:

UPDATE employees SET salary = salary * 1.1 WHERE id = 42;
COMMIT;

Repeat the update several times. Each committed update creates another record version that session A still needs to see.

From the operating system shell, inspect the database header with gstat:

gstat -h /path/to/test.fdb | grep -E "Oldest|Next"

Look at these header fields:

• Oldest Transaction (OIT): the oldest transaction that could still affect garbage collection.
• Oldest Active Transaction (OAT): the oldest transaction currently running.
• Oldest Snapshot Transaction (OST): the oldest snapshot or read-only transaction.
• Next Transaction: the next transaction ID Firebird will assign.

If Next Transaction is far ahead of Oldest Snapshot Transaction, and the gap keeps growing, session A is blocking GC. The versions created by session B cannot be removed yet.

Close session A with ROLLBACK; or COMMIT;, then run a sweep:

gfix -sweep /path/to/test.fdb

gfix requires SYSDBA or database owner privileges. Sweep runs online, but it consumes disk I/O and CPU, so schedule it during low-usage periods. If you ran the UPDATE statements in session B and want to undo them, issue ROLLBACK; in that session before committing, or restore the scratch database from a backup.

After sweep, re-run gstat -h. The gap between Oldest Snapshot and Next Transaction should shrink, though it will not reach zero while other transactions are active.

Limits and What Sweep Cannot Fix

MGA removes read/write locking, but it does not eliminate write/write contention. Two transactions updating the same row at the same time still serialize, and one will wait or fail. Deadlocks remain possible.

Sweep also cannot remove versions that an active transaction still needs. If your application routinely holds transactions open across user think time, no amount of sweeping will stop version accumulation. The fix is application-level: commit or roll back promptly, avoid read-only transactions left open overnight, and prefer READ COMMITTED where a stable snapshot is not required.

Check Before You Deploy

1. Run gstat -h on your production database during peak hours.
2. Compare Oldest Snapshot Transaction against Next Transaction.
3. If the gap is large and stable, identify the holding transaction using your application’s transaction log or Firebird monitoring tables.
4. Schedule gfix -sweep only after the long-running transaction ends, and verify the gap shrinks afterward.

Firebird’s MGA is safe when transaction lifetimes are short. The most common performance problem is not the architecture itself, but a single transaction that outlives its welcome.