Hi Peter,
How are you disconnecting the sessions? I would expect to see 1
transaction for every active session, as the following script demonstrates:
-- 1 active session = 1 open transaction
connect 'jdbc:derby:memory:db;create=true' as conn1;
select count(*) from syscs_diag.transaction_table;
-- 2 active sessions = 2 open transactions
connect 'jdbc:derby:memory:db' as conn2;
select count(*) from syscs_diag.transaction_table;
-- 3 active sessions = 3 open transactions
connect 'jdbc:derby:memory:db' as conn3;
select count(*) from syscs_diag.transaction_table;
-- 2 active sessions = 2 open transactions
disconnect;
set connection conn1;
select count(*) from syscs_diag.transaction_table;
-- 1 active session = 1 open transaction
set connection conn2;
disconnect;
set connection conn1;
select count(*) from syscs_diag.transaction_table;
Thanks,
-Rick
On 7/11/17 10:10 AM, Peter Ondruška wrote:
Dear all,
the documentation mentions "The SYSCS_DIAG.TRANSACTION_TABLE
diagnostic table shows *all of the transactions that are currently *in
the database." Is it really correct? In my case I have an application
server (Payara) connected to database with ClientXADataSource. Over
time the record count in this table grows. When I stop application
server and all database sessions are disconnected, record count stays
with no change and I would expect that it drops as transactions are
definitely closed. The only way to "clean" the diagnostic table is to
restart database.
All the records are same (different XID of course):
XID GLOBAL_XID USERNAME TYPE STATUS FIRST_INSTANT SQL_TEXT
79512765 NULL APP UserTransaction IDLE NULL NULL
except one SystemTransaction:
XID GLOBAL_XID USERNAME TYPE STATUS FIRST_INSTANT SQL_TEXT
79241843 NULL NULL SystemTransaction IDLE NULL NULL
and one UserTransaction (as expected):
XID GLOBAL_XID USERNAME TYPE STATUS FIRST_INSTANT SQL_TEXT
79604720 NULL APP UserTransaction IDLE NULL SELECT *
FROM syscs_diag.transaction_table
Regards,
--
Peter Ondruška
kaibo, s.r.o., ID 28435036, registered with the commercial register
administered by the Municipal Court in Prague, section C, insert 141269.
Registered office and postal address: kaibo, s.r.o., Kališnická
379/10, Prague 3, 130 00, Czech Republic.
https://www.kaibo.eu
