Dear Brett, I did not mention but I use latest stable Derby (10.13). I have checked with https://bitbucket.org/ondruska/xadbreco and no XA transactions are reported.
p. On 11 August 2017 at 14:47, Bergquist, Brett <bbergqu...@canoga.com> wrote: > 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:rick.hille...@gmail.com] > *Sent:* Tuesday, July 11, 2017 8:56 PM > *To:* derby-user@db.apache.org > *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). > -- 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
