db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Have a database that has a phantom "transaction" even after booting the database clean in embedded mode
Date Tue, 23 Aug 2011 08:16:01 GMT
"Bergquist, Brett" <BBergquist@canoga.com> writes:

> I have a database that shows a phantom "transaction" even after
> booting the database in embedded mode. This is Derby The
> database has >600 log files in the "log" directory. I connect to the
> database using IJ in embedded mode and it takes a while for the
> database to boot. Using "truss" I can see it going through the files
> in the "log" directory. Eventually it does boot and I can query, etc.
> Here is what I see when I boot the database:
> ----------
> bash-3.00# export DERBY_HOME
> bash-3.00# $DERBY_HOME/bin/ij
> ij version 10.8
> ij> connect 'jdbc:derby:/opt/canogaview/glassfish/databases/csemdb';
> ij> select * from syscs_diag.transaction_table;
> XID            |GLOBAL_XID                                                          
      |USERNAME                                   |TYPE                          |STATUS 
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
> ---------------------------------------------------------------
> 5627782391
> |(4871251,d9fa0200553fbadd73756e332d7369743233372c7365727665722c5
> 033373030,73756e332d7369743233372c7365727665722c50333730302c01) |NULL
> |UserTransaction |PREPARED|(15426 02,530516) |NULL
> 5630573339     |NULL                                                                
|APP                                   |UserTransaction               |IDLE    |NULL     
         |select * from syscs_diag.transaction_table 
> 2 rows selected
> ij>
> ------
> Note the transaction that is present even though the database was just
> booted in embedded mode. Shutting down derby and restarting has no
> effect. I also tried checkpointing the database, etc. but still this
> phantom transaction persists.
> Some background, this database is accessed through a Glassfish Java EE
> application using JTA. There were millions of inserts done on the
> database, but the database was shutdown clean. Only on restarting the
> database did I notice the long boot time and found all of the files in
> the "log" directory and started to investigate.

Hi Brett,

Looks like this is a prepared, but not yet committed, XA transaction. It
should go away if you run XA recovery and explicitly commit or abort the
transaction. Something like this:

    EmbeddedXADataSource ds = new EmbeddedXADataSource();
    XAConnection xac = ds.getXAConnection();
    XAResource xar = xac.getXAResource();
    for (Xid xid : xar.recover(XAResource.TMSTARTRSCAN)) {
        // Or, if you prefer:
        // xar.commit(xid, false);

Alternatively, you could try to invoke manual recovery via the GlassFish
CLI (asadmin recover-transactions).

Hope this helps,

Knut Anders

View raw message