You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Knut Anders Hatlen <kn...@oracle.com> on 2011/08/23 10:16:01 UTC
Re: Have a database that has a phantom "transaction" even after booting the database clean in embedded mode
"Bergquist, Brett" <BB...@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
Re: Have a database that has a phantom "transaction" even after booting
the database clean in embedded mode
Posted by Kathey Marsden <km...@sbcglobal.net>.
On 8/23/2011 6:38 AM, Bergquist, Brett wrote:
> Sorry Kathey I spelt your name wrong in the previous response :(
No worries. BTW if you want an XA issue to sink your teeth into and
provide context , you might want to look at DERBY-1016.
https://issues.apache.org/jira/browse/DERBY-1016
I think Tiago did the hard part of figuring out what the behavior should
be. At this point it *should* be just a couple line code change and a test.
Kathey
RE: Have a database that has a phantom "transaction" even after
booting the database clean in embedded mode
Posted by "Bergquist, Brett" <BB...@canoga.com>.
Sorry Kathey I spelt your name wrong in the previous response :(
-----Original Message-----
From: Bergquist, Brett [mailto:BBergquist@canoga.com]
Sent: Tuesday, August 23, 2011 9:33 AM
To: Kathey Marsden; derby-dev@db.apache.org
Subject: RE: Have a database that has a phantom "transaction" even after booting the database clean in embedded mode
Thanks Kathy. I will do some reading on this.
-----Original Message-----
From: Kathey Marsden [mailto:kmarsdenderby@sbcglobal.net]
Sent: Tuesday, August 23, 2011 9:19 AM
To: derby-dev@db.apache.org
Cc: Bergquist, Brett
Subject: Re: Have a database that has a phantom "transaction" even after booting the database clean in embedded mode
On 8/23/2011 5:21 AM, Bergquist, Brett wrote:
> 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?
>
In A two phase commit XA transaction, if there is a crash between the
prepare and the final commit or rollback, there is not a way for Derby
to know whether the overall distributed transaction was committed or
rolled back, so even after the database is rebooted, the transaction
remains in the prepared state and has to be manually recovered and
appropriately committed or rolled back to match the global transaction.
The prepare just guarantees that either commit or rollback will work.
The JTA spec which provides the Java interfaces for XA transactions is
based on the open group XA Specification, which is a good resource for
understanding XA concepts.
https://www2.opengroup.org/ogsys/jsp/publications/PublicationDetails.jsp?catalogno=c193
HTH
Kathey
PS. There is a single phase commitin XAResource.commit(Xid xid, boolean
onePhase) if your transaction involves just a single database and that
is all you require, but I don't know if you have overall handling of the
XA transaction processing.
RE: Have a database that has a phantom "transaction" even after
booting the database clean in embedded mode
Posted by "Bergquist, Brett" <BB...@canoga.com>.
Thanks Kathy. I will do some reading on this.
-----Original Message-----
From: Kathey Marsden [mailto:kmarsdenderby@sbcglobal.net]
Sent: Tuesday, August 23, 2011 9:19 AM
To: derby-dev@db.apache.org
Cc: Bergquist, Brett
Subject: Re: Have a database that has a phantom "transaction" even after booting the database clean in embedded mode
On 8/23/2011 5:21 AM, Bergquist, Brett wrote:
> 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?
>
In A two phase commit XA transaction, if there is a crash between the
prepare and the final commit or rollback, there is not a way for Derby
to know whether the overall distributed transaction was committed or
rolled back, so even after the database is rebooted, the transaction
remains in the prepared state and has to be manually recovered and
appropriately committed or rolled back to match the global transaction.
The prepare just guarantees that either commit or rollback will work.
The JTA spec which provides the Java interfaces for XA transactions is
based on the open group XA Specification, which is a good resource for
understanding XA concepts.
https://www2.opengroup.org/ogsys/jsp/publications/PublicationDetails.jsp?catalogno=c193
HTH
Kathey
PS. There is a single phase commitin XAResource.commit(Xid xid, boolean
onePhase) if your transaction involves just a single database and that
is all you require, but I don't know if you have overall handling of the
XA transaction processing.
Re: Have a database that has a phantom "transaction" even after booting
the database clean in embedded mode
Posted by Kathey Marsden <km...@sbcglobal.net>.
On 8/23/2011 5:21 AM, Bergquist, Brett wrote:
> 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?
>
In A two phase commit XA transaction, if there is a crash between the
prepare and the final commit or rollback, there is not a way for Derby
to know whether the overall distributed transaction was committed or
rolled back, so even after the database is rebooted, the transaction
remains in the prepared state and has to be manually recovered and
appropriately committed or rolled back to match the global transaction.
The prepare just guarantees that either commit or rollback will work.
The JTA spec which provides the Java interfaces for XA transactions is
based on the open group XA Specification, which is a good resource for
understanding XA concepts.
https://www2.opengroup.org/ogsys/jsp/publications/PublicationDetails.jsp?catalogno=c193
HTH
Kathey
PS. There is a single phase commitin XAResource.commit(Xid xid, boolean
onePhase) if your transaction involves just a single database and that
is all you require, but I don't know if you have overall handling of the
XA transaction processing.
RE: Have a database that has a phantom "transaction" even after
booting the database clean in embedded mode
Posted by "Bergquist, Brett" <BB...@canoga.com>.
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" <BB...@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