db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: SYSCS_DIAG.TRANSACTION_TABLE stale records
Date Fri, 11 Aug 2017 12:47:51 GMT
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).
Mime
View raw message