Transaction Counters in monitoring tables vs. header page

Thomas Steinmaurer wrote:

I'm not sure how to interpret the following result in the monitoring tables regarding some transaction counters. I'm querying the monitoring tables of a Firebird 2.1.2 database (Classic on Windows) with an explicitely started client transaction (using IBO) set to read-only, repeatable read, auto-commit = false.

With the following result.

• In MON$DATABASE:

  MON$OLDEST_TRANSACTION => 29117
  MON$OLDEST_ACTIVE => 29710
  MON$OLDEST_SNAPSHOT => 29710
  MON$NEXT_TRANSACTION => 29710
  

• In MON$TRANSACTIONS I see two records with the following MON$TRANSACTION_IDs:

  29710
  29698
  

  Both marked with MON$STATE = 1

• Querying the header page gives the following result:

  OIT: 29117
  OAT: 29709
  OST: 29709
  NT: 29710
  

A few questions:

• Why isn't there a gap between MON$OLDEST_ACTIVE and MON$NEXT_TRANSACTION? I would expect at least a gap of 1.
• Why do I see a record in MON$TRANSACTIONS with MON$TRANSACTION_ID = 29698 but not in any transaction counters either via MON$DATABASE or querying the header page, even if this transaction in MON$TRANSACTIONS is marked as active (MON$STATE = 1?)
• Why is there a difference regarding transaction counters when querying MON$DATABASE and the header page?

Vlad Khorsun answers:

Why isn't there a gap between `MON$OLDEST_ACTIVE` and `MON$NEXT_TRANSACTION`? I would expect at least a gap of 1.

Tx numbers are recalculated at any transaction start. The process run as follows :

• lock and read header page
• increment Next value
• assign Next value to the new tx number
• update OITOATOST by values from in-memory variables
• release and write header page
• create transaction lock
• calculate new values of OITOATOST and save it into in-memory variables

As you can see all tx numbers (except of Next) have the latest values at in-memory variables and previous values at header page.

Should i say that MON$DATABASE reflects in-memory variables ? :)

Why do I see a record in MON$TRANSACTIONS with MON$TRANSACTION_ID = 29698 but not in any transaction counters either via MON$DATABASE or querying the header page, even if this transaction in MON$TRANSACTIONS is marked as active (MON$STATE = 1?)

If my guess is correct, then

1. as RC RO transaction is pre-committed (marked as committed in TIP at tx start) it not affected OATOITOST
2. as this transaction is still active you see it in MON$TRANSACTIONS

Why is there a difference regarding transaction counters when querying MON$DATABASE and the header page?

See above