Subject Re: [firebird-support] Why MON$STATEMENTS.MON$TRANSACTION_ID is mostly empty (null)?
Author Mark Rotteveel
On 2019-10-23 14:49, jonatan.lauritsen@... [firebird-support]
> Sometimes I receive error message (during development and tests, of
> course):
> deadlock
> update conflict with concurret update
> concurrent transaction number is 2926
> Then I can find the other attachment with the long-running transaction
> using:
> select a.mon$user, a.mon$attachment_name, a.mon$remote_address, a.*
> from mon$transactions t
> left join mon$attachments a on
> (t.mon$attachment_id=a.mon$attachment_id)
> where t.mon$transaction_id=2926
> I would like to see the long-running statement as well, but
> MON$STATEMENTS.MON$TRANSACTION_ID is mostly empty (null) for almost
> all the records.
> Why is that? And is it possible to relate statements to the
> transactions?
> Firebird 3.0.4.

As far as I know, a statement is only associated with a transaction if
it is being executed or - for result set producing statements - has a
cursor open. As soon as the execution is complete (or the cursor is
closed), the statement is no longer associated with the transaction.
The monitoring tables are a snapshot of activity (at the time the first
query was run on a monitoring table in the current transaction), so if a
statement is not executing (nor has an open cursor) at that time, it
will not have a transaction id associated.

Also be aware that long running transactions don't necessarily have long
running statements, an update conflict means that a record being updated
has uncommitted changes by another transaction. That modification could
have completed some time ago, but the transaction itself is still open.