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:[email protected]] Sent: Tuesday, August 23, 2011 4:16 AM To: [email protected] Subject: Re: Have a database that has a phantom "transaction" even after booting the database clean in embedded mode "Bergquist, Brett" <[email protected]> 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
