Subject mon$statement_id, take 2
Author Douglas Tosi

Please allow me try again and explain the problems I´m having with
Today I created a reproducible test case.
I can send you if needed. It´s a small c# program but you can also
reproduce using isql (and fast fingers).
Here´s what happens:

I open two connections to the database. Connections A and B.
Now each of these connections will start working at the same time and
do the following:

open a read-commited transaction
select count(*) from a different table

As each connection will query a different table, the execution times
will be different and that´s important for the test. One of them must
finish first.
I´m using tables T1 with 2M record and T2 with 200K records.

The following select is used to poll the monitoring table every second:

m.mon$attachment_id aid, m.mon$transaction_id tid,
m.mon$statement_id sid, m.mon$state state,
m.mon$sql_text sql_text, m.mon$stat_id stat_id
from mon$statements m
where m.mon$attachment_id <> current_connection and
m.mon$sql_text is not null

Here are the results:

16:54:32: Worker 1: Start (table t1)
16:54:32: Worker 2: Start (table t2)

Snapshot: 16:54:33
stmt=04, text=select count(*) from t2
stmt=14, text=select count(*) from t1

Snapshot: 16:54:36
stmt=04, text=select count(*) from t2
stmt=14, text=select count(*) from t1

16:54:36: Worker 2: End

Snapshot: 16:54:37
stmt=11, text=select count(*) from t1

Snapshot: 16:54:38
stmt=11, text=select count(*) from t1

16:54:39: Worker 1: End

Both worker threads start and the snapshots are ok.
But when worker 2 ends, the statement that worker 1 issued (that is
still running) appears as id 11 instead of 14 as before.
Worker 1 did nothing to cause this change.

If this is as designed, please let´s discuss if it can be reviewed and
perhaps changed for a future version.

Let me tell you why I believe this is important

I´m gladly taking snapshots of the monitoring tables, looking for
something wrong.
Then I see there is a certain statement id 15 that is causing trouble.
What do I do? Try to gather more detail about statement 15.
Maybe I´ll find where its coming from and can go talk to the person responsible.
Maybe I´ll wait a little while and check back to see if it´s still
there causing trouble.
But if its id keeps changing every snapshot, how can I do that? How
can I "zoom in" that statement once I found it?

Douglas Tosi