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:[email protected]] Sent: Tuesday, July 11, 2017 8:56 PM To: [email protected] 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: 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 ________________________________ 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).
