You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Peter Ondruška <pe...@kaibo.eu> on 2017/07/11 17:10:17 UTC
SYSCS_DIAG.TRANSACTION_TABLE stale records
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
Re: SYSCS_DIAG.TRANSACTION_TABLE stale records
Posted by Peter Ondruška <pe...@kaibo.eu>.
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 <BB...@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.hillegas@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
RE: SYSCS_DIAG.TRANSACTION_TABLE stale records
Posted by "Bergquist, Brett" <BB...@canoga.com>.
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.hillegas@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).
Re: SYSCS_DIAG.TRANSACTION_TABLE stale records
Posted by Rick Hillegas <ri...@gmail.com>.
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