Firebird's Oldest Active Transaction: Why Idle Sessions Inflate Your Database

A database that grows while its row counts stay flat

Firebird implements multi-version concurrency control (MVCC): when a row is updated or deleted, the old version is not overwritten in place. It stays on the page until the engine is sure no active transaction could still need to see it. That design is what lets readers and writers work without blocking each other, but it has a cost: cleanup is bounded by transaction visibility, not by wall-clock time.

The practical symptom is a database file that keeps growing while your row counts are flat, and scans that get slower because each one has to step over more dead record versions. The usual cause is not a missing index. It is a transaction that started and never finished — an idle "in transaction" connection, a long report, or a batch job stuck waiting on something.

Why the Oldest Active Transaction sets the cleanup ceiling

Firebird tracks the Oldest Active Transaction (OAT): the lowest transaction ID that is still active, plus the oldest snapshot that any active transaction might need. A record version can only be removed once it is older than that boundary. This is why a single idle transaction can pin garbage for the entire database, no matter how many other sessions commit.

Sweep is the cooperative background process that walks the database and removes versions older than the OAT. It is triggered by the SweepInterval configuration parameter or invoked manually with gfix -sweep. Sweep cannot reclaim versions newer than the OAT. Running it more often will not help if a transaction is holding the boundary back.

Finding the transaction that is holding the line

The MON$ monitoring tables (available from Firebird 2.1 onward) make this concrete. Column names and availability differ by release; MON$OLDEST_ACTIVE and MON$OLDEST_SNAPSHOT are not present in the earliest monitoring-table implementations. Confirm your engine version first, running as SYSDBA or the database owner:

SELECT rdb$get_context('SYSTEM','ENGINE_VERSION') FROM rdb$database;

Then read the database header counters. On versions that expose them:

SELECT d.mon$oldest_transaction,
       d.mon$oldest_active,
       d.mon$oldest_snapshot,
       d.mon$next_transaction
FROM mon$database d;

To see which attachment is holding the oldest transaction, join MON$TRANSACTIONS to MON$ATTACHMENTS and order by transaction ID. Run this as SYSDBA or the database owner; other users may see only their own attachments, depending on version and privileges:

SELECT FIRST 10
       a.mon$attachment_id,
       a.mon$user,
       a.mon$remote_address,
       t.mon$transaction_id,
       t.mon$state,
       t.mon$timestamp
FROM mon$attachments a
JOIN mon$transactions t ON t.mon$attachment_id = a.mon$attachment_id
ORDER BY t.mon$transaction_id;

In MON$TRANSACTIONS, MON$STATE distinguishes active from idle transactions in the versions that define it. An idle transaction is still active for garbage-collection purposes: it has started and has not committed or rolled back. The oldest row in that result is the one to investigate.

Checking header counters with gstat and sweeping with gfix

gstat -h prints the database header counters — oldest transaction, oldest active, oldest snapshot, next transaction — plus a version count. It gives a fast before-and-after check without writing SQL against the live database. Run it on the server host as a user with read access to the database file:

gstat -h /path/to/your.fdb

Compare the oldest-transaction and version-count lines before and after any change. Do not treat file size alone as proof of garbage: page fragmentation, blob storage, and ordinary data growth also contribute.

To force a sweep, gfix -sweep is intended to run against a database in use, but it is a state-changing maintenance operation that consumes I/O and can compete with production work. Other gfix maintenance modes require exclusive access, so confirm which mode you are invoking. Reproduce the experiment on a restored copy first:

gfix -sweep -user SYSDBA -password <password> /path/to/your.fdb

After the sweep, re-run gstat -h and re-query the MON$ counters. If the oldest active transaction has not advanced, the sweep had nothing to reclaim beyond the boundary — the pinning transaction is still there.

Trade-offs and version-sensitive details

• Sweeping and aggressive garbage collection consume I/O. Schedule them where they do not compete with peak load, and expect the benefit to be bounded by the OAT.
• Firebird 4's read-committed read-consistency mode reduces version accumulation but changes visibility semantics and adds per-statement overhead. Verify the behavior against your version's documentation before switching isolation modes.
• Do not assume a single SweepInterval default across versions. The shipped value and its interaction with automatic sweep have changed; read firebird.conf for the version you actually deploy.
• Avoid quoting fixed figures for file growth, sweep duration, or reclaimable space. They depend on workload, page size, hardware, and transaction patterns.

What to do with this

The engineering decision is simple even though the diagnosis is not: keep transactions short, and never leave one open across user think-time or network waits. Commit or roll back explicitly. Be wary of pooled connections configured to start a transaction on the first statement and hold it until the connection is returned — that pattern turns an idle pool into a garbage collector's worst case.

As a practical check, watch the OAT over a normal working day. If it advances steadily, cleanup is keeping up. If it stalls while sessions are idle, you have found the pinning attachment. Fix the transaction lifetime first; sweep second.