Search code examples
transactionsfirebirdfirebird-3.0

Commit/Rollback transaction not listed in MON$TRANSACTIONS

We have a strange issue in a customer's Firebird database. We noticed an active transaction not listed in the mon$transactions table.

According to the gstat utility, transaction 231.768.260 is active:
enter image description here

However, running a simple SELECT * FROM mon$transactions where mon$transaction_id = 231768260 does not return such transaction ID.

I thought it could be in limbo, so I decided run gfix with limbo switch, and the results are no transactions in limbo:

enter image description here

I tried to manually rollback it:

enter image description here

And it warned me that transaction is active.

Is there some way to correct this situation? Right now, the only thing I can think of is to do a backup/restore - creating a fresh new DB and discard this one.

To be honest this kind issue is rare but frustrating.

Any tip will be appreciate.

Solution

  • As Mark pointed, a reboot of Firebird service removed the problematic transaction.

    After that I noticed two things:

    1. first connection to the server was very slow - I think FB was garbage collecting.
    2. some minutes later after first connection, queries were slower than they used to be.

    Anyway, I decided to do a backup/restore and everything is fine.