Apache Database › Derby › Apache Derby Users

SYSCS_DIAG.TRANSACTION_TABLE stale records

classic Classic list List threaded Threaded
♦
♦
 4 messages Options
Peter Ondruška-4
Reply | Threaded
Open this post in threaded view
♦
♦
|  
Report Content as Inappropriate
♦
♦

SYSCS_DIAG.TRANSACTION_TABLE stale records

Peter Ondruška-4
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
Rick Hillegas-3
Reply | Threaded
Open this post in threaded view
♦
♦
|  
Report Content as Inappropriate
♦
♦

Re: SYSCS_DIAG.TRANSACTION_TABLE stale records

Rick Hillegas-3
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
Bergquist, Brett-2
Reply | Threaded
Open this post in threaded view
♦
♦
|  
Report Content as Inappropriate
♦
♦

RE: SYSCS_DIAG.TRANSACTION_TABLE stale records

Bergquist, Brett-2

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:

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-4
Reply | Threaded
Open this post in threaded view
♦
♦
|  
Report Content as Inappropriate
♦
♦

Re: SYSCS_DIAG.TRANSACTION_TABLE stale records

Peter Ondruška-4
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 <[hidden email]> 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:[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:

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
Loading...
Free forum by Nabble Edit this page