|
|
|
|
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,
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
|
|
|
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,
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
|
|
|
Sorry for the late response to this but I did want to comment. We are using ClientXADataSource extensively with Glassfish. Our transactions are correctly
reported in the SYSCS_DIAG.TRANSACTION_TABLE. The only time that they have stuck around is when the connection between Glassfish and the Derby Network Server has been severed before the XA “prepare” or “commit” phase has been reached or due to a XA transaction
timeout bug in Derby which I fixed and supplied and is in the latest builds (10.10.2.0 is what I am using).
Having the transaction stay around is of course the correct thing since XA is the distributed protocol and until prepare/commit/rollback has been performed,
Derby (the XA resource) has no idea the state of the transaction.
I think I would write a little program to lists the XA transactions that are still open and see if those reported by the SYSCS_DIAG.TRANSACTION_TABLE are not
in fact real XA transactions that have not been finalized.
From: Rick Hillegas [mailto:[hidden email]]
Sent: Tuesday, July 11, 2017 8:56 PM
To: [hidden email]
Subject: Re: SYSCS_DIAG.TRANSACTION_TABLE stale records
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:
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
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
Canoga Perkins
20600 Prairie Street
Chatsworth, CA 91311
(818) 718-6300
This e-mail and any attached document(s) is confidential and is intended only for the review of the party to whom it is addressed. If you have received this transmission in error, please notify the sender immediately and discard the original message and any
attachment(s).
|
|
|
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
|
|
Locked
