Firebird Interview Question:

How to detect applications and users that hold transactions open too long?

Submitted by: Administrator

To do this, you need Firebird 2.1 or a higher version. First, run gstat tool (from your Firebird installation's bin directory), and you'll get an output like this:

gstat -h faqs.gdb

Database "faqs.gdb"
Database header page information:
Flags 0
Checksum 12345
Generation 919
Page size 4096
ODS version 11.1
Oldest transaction 812
Oldest active 813
Oldest snapshot 813
Next transaction 814

Now, connect to that database and query the MON$TRANSACTIONS table to get the MON$ATTACHMENT_ID for that transaction, and then query the MON$ATTACHMENTS table to get the user name, application name, IP address and even PID on the client machine. We are looking for the oldest active transaction, so in this case, a query would look like:

SELECT ma.*
FROM MON$ATTACHMENTS ma
join MON$TRANSACTIONS mt
on ma.MON$ATTACHMENT_ID = mt.MON$ATTACHMENT_ID
where mt.MON$TRANSACTION_ID = 813;
Submitted by: Administrator