Answers

Question and Answer:

  Home  Firebird

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

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;

 175 views

More Questions for you: