MON$ Monitoring Tables: Transitioning to External Alerting in Firebird 3.0+

Direct Answer

To separate a transient transaction spike from a persistent sweep block, poll MON$ tables at fixed intervals and require the same oldest transaction to remain active across multiple consecutive polls before alerting. For cross-attachment visibility in Firebird 3.0+, grant the monitoring user the RDB$ADMIN database role; Firebird 3.0 does not provide a well-established narrower read-only role for viewing other attachments' MON$ rows.

Distinguishing Transient Spikes from Persistent Blocks

1. Poll MON$DATABASE or MON$TRANSACTIONS every 30–60 seconds from the monitoring attachment.
2. Calculate the sweep-blocking gap using the NextTransaction and OldestActive values exposed by the MON$ tables.
3. Record OldestActive and its transaction start timestamp on every poll.
4. Treat the event as transient when the gap exceeds threshold for fewer than two consecutive polls and the oldest transaction ID changes between polls.
5. Treat it as a persistent block when the same transaction ID remains OldestActive across three or more consecutive polls while the gap stays above threshold.
6. Suppress repeated alerts until the gap falls below threshold or the oldest transaction ID changes.

Privilege Configuration for a Dedicated Monitoring User

Firebird 3.0+ scopes MON$ rows to the current attachment by default. A monitoring account sees only its own rows unless it holds RDB$ADMIN on the database. No separate read-only monitoring role is documented for Firebird 3.0 cross-attachment visibility.

1. Create a dedicated database user, for example MONITOR, used only by the alerting agent.
2. Grant RDB$ADMIN to MONITOR in the target database.
3. Connect the agent as MONITOR and specify RDB$ADMIN as the role for that attachment.
4. Revoke unnecessary object privileges from MONITOR so it cannot modify application data.
5. Verify visibility by running SELECT COUNT(*) FROM MON$TRANSACTIONS while other connections are active; the result should include rows from attachments other than the monitoring session.

If RDB$ADMIN is too broad, accept attachment-scoped monitoring and expose application-specific metrics through the application itself, or test a newer Firebird version for finer-grained monitoring privileges.

Verification Query

Confirm the gap calculation from the monitoring attachment:

SELECT MON$NEXT_TRANSACTION, MON$OLDEST_ACTIVE, MON$NEXT_TRANSACTION - MON$OLDEST_ACTIVE AS sweep_gap FROM MON$DATABASE;

If these columns are not present in MON$DATABASE for your build, query the equivalent columns from MON$TRANSACTIONS. Repeat the query while a long-running transaction is open from another attachment; a stable gap and unchanged oldest transaction ID across polls indicate a persistent block.

Assumptions and Uncertainty

• This guidance assumes Firebird 3.0.x. Firebird 4.0 may change MON$ visibility or privilege semantics; confirm the exact version before deploying role grants.
• The three-poll persistence rule is an operational heuristic, not a Firebird-enforced definition.
• RDB$ADMIN grants rights beyond monitoring. Restrict the monitoring user's non-MON$ object privileges to the minimum required.

Missing diagnostic detail: What exact Firebird version is in use? This determines whether RDB$ADMIN is the only cross-attachment monitoring path or whether a narrower privilege exists.