"Bergquist, Brett" <bbergqu...@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
