db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peter Ondruška <peter.ondru...@kaibo.eu>
Subject Re: SYSCS_DIAG.TRANSACTION_TABLE stale records
Date Fri, 11 Aug 2017 19:08:10 GMT
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 <BBergquist@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

Mime
View raw message