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.
This is very similar to what happened about a month ago where I had a system in
production do this an 64Gb of transaction log files where left over.
I have this database and can do analysis on it if someone can point me in a
direction to look and tools to use.
Brett
