db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: SYSCS_DIAG.TRANSACTION_TABLE stale records
Date Wed, 12 Jul 2017 00:55:31 GMT
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



Mime
View raw message