db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.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 12:21:22 GMT
I guess I was under the assumption that it would go away if I booted the database clean.  It
seems to me that a database that has been stopped and booted clean would invalidate an existing
transactions and clean them up.  Is this not the case with XA transactions?

Thanks for taking the time to help me understand this!

Brett

-----Original Message-----
From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com] 
Sent: Tuesday, August 23, 2011 4:16 AM
To: derby-dev@db.apache.org
Subject: Re: Have a database that has a phantom "transaction" even after booting the database
clean in embedded mode

"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 10.8.1.2. 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 
|FIRST_ INSTANT       |SQL_TEXT 
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
> --------------------------------------------------------------------------------
> ---------------------------------------------------------------
> 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();
    ds.setDatabaseName("/opt/canogaview/glassfish/databases/csemdb");
    XAConnection xac = ds.getXAConnection();
    XAResource xar = xac.getXAResource();
    for (Xid xid : xar.recover(XAResource.TMSTARTRSCAN)) {
        xar.rollback(xid);
        // Or, if you prefer:
        // xar.commit(xid, false);
    }
    xac.close();

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

Hope this helps,

-- 
Knut Anders



Mime
View raw message