Обсуждение: Unable to commit: transaction marked for rollback
Hello! I'm intermittantly getting this error message in a java app. using Geronimo / Hibernate / Postgres 8.3.9 javax.transaction.RollbackException: Unable to commit: transaction marked for rollback Can someone give me a scenario where this would happen? "unable to commit" makes everyone immediatly go to database issue. But it seems like an app issue to me. I was thinking that maybe it's a 2 phase commit / XA or something like that. (TX open, phase 1 commit fails, phase 2 commit throws this error?) I can't imagine how this would happen within a single transaction setup. Thanks! Dave
David Kerr <dmk@mr-paradox.net> wrote: > javax.transaction.RollbackException: Unable to commit: transaction > marked for rollback > > Can someone give me a scenario where this would happen? It sounds like the right exception for the case where a previous statement generated an error within a database transaction. After that, any attempt to run a statement would generate this at the PostgreSQL level: ERROR: current transaction is aborted, commands ignored until end of transaction block until a ROLLBACK or COMMIT (which would be treated as a ROLLBACK because of the transaction state) is executed. That sounds like exactly the case for which RollbackException was created: http://java.sun.com/javase/6/docs/api/javax/transaction/TransactionRolledbackException.html -Kevin
On 7/1/2010 11:30 AM, Kevin Grittner wrote: > David Kerr<dmk@mr-paradox.net> wrote: > >> javax.transaction.RollbackException: Unable to commit: transaction >> marked for rollback >> >> Can someone give me a scenario where this would happen? > > It sounds like the right exception for the case where a previous > statement generated an error within a database transaction. After > that, any attempt to run a statement would generate this at the > PostgreSQL level: > > ERROR: current transaction is aborted, commands ignored until end > of transaction block > > until a ROLLBACK or COMMIT (which would be treated as a ROLLBACK > because of the transaction state) is executed. > > That sounds like exactly the case for which RollbackException was > created: > > http://java.sun.com/javase/6/docs/api/javax/transaction/TransactionRolledbackException.html > > -Kevin > Would postgres normally log the error in the TX? (it's not, which is why i'm asking) Thanks! Dave
David Kerr <dmk@mr-paradox.net> wrote:
> Would postgres normally log the error in the TX?
A previous statement should have generated the original exception,
if that's what you mean.
> (it's not, which is why i'm asking)
Where are you checking? If you're talking about exceptions you're
fielding, it wouldn't be the first time I've seen sloppy exception
handling hide the first exception (the one that actually matters)
and throw a secondary exception. One common mistake is to catch the
first exception and try to do some cleanup, but allow an exception
to be thrown from the cleanup code -- completely hiding the first
exception.
The general pattern I follow to prevent such problems is:
ResourceX rx = null;
ResourceY ry = null;
try
{
rx = new ResourceX();
ry = rx.createY();
<use resources>
ry.close();
ry = null;
rx.close();
rx = null;
}
finally
{
if (ry != null)
{
try
{
ry.close();
}
catch (Exception e2)
{
// ignore
}
ry = null;
}
if (rx != null)
{
try
{
rx.close();
}
catch (Exception e2)
{
// ignore
}
rx = null;
}
}
There are, of course, variations on this, but you get the gist of
it.
Have you checked the PostgreSQL log file for clues? If you're not
seeing what you need, you could tweak the logging to show more.
-Kevin
On 7/1/2010 12:20 PM, Kevin Grittner wrote: > David Kerr<dmk@mr-paradox.net> wrote: > >> Would postgres normally log the error in the TX? > > A previous statement should have generated the original exception, > if that's what you mean. ok. >> (it's not, which is why i'm asking) > > Where are you checking? If you're talking about exceptions you're > fielding, it wouldn't be the first time I've seen sloppy exception > handling hide the first exception (the one that actually matters) > and throw a secondary exception. One common mistake is to catch the > first exception and try to do some cleanup, but allow an exception > to be thrown from the cleanup code -- completely hiding the first > exception. I'm checking the Postgres logs. (I'm not Java savvy, but i'll pass the info along to my developers) > > There are, of course, variations on this, but you get the gist of > it. > > Have you checked the PostgreSQL log file for clues? If you're not > seeing what you need, you could tweak the logging to show more. We're trying that, it's (of course) intermittent and happens after like 5 hours. so huge logfiles, etc as usual. With log_statements=none turned on PG logs database errors. so it seems like whatever's happening isn't being considered an "error". more of a natural database transaction (like a lock) or something. does that sound about right? Thanks Dave
David Kerr <dmk@mr-paradox.net> wrote:
>> Have you checked the PostgreSQL log file for clues? If you're
>> not seeing what you need, you could tweak the logging to show
>> more.
>
> We're trying that, it's (of course) intermittent and happens after
> like 5 hours. so huge logfiles, etc as usual. With
> log_statements=none turned on PG logs database errors. so it seems
> like whatever's happening isn't being considered an "error". more
> of a natural database transaction (like a lock) or something. does
> that sound about right?
Hmmm.... I was just guessing at the cause. With default logging,
if I do this:
test=# begin;
BEGIN
test=# select foo;
ERROR: column "foo" does not exist
LINE 1: select foo;
^
test=# select 'foo';
ERROR: current transaction is aborted, commands ignored until end
of transaction block
test=# commit;
ROLLBACK
I see this in the log:
ERROR: column "foo" does not exist at character 8
STATEMENT: select foo;
ERROR: current transaction is aborted, commands ignored until end
of transaction block
STATEMENT: select 'foo';
If you aren't seeing something like that, I'm probably off-base in
my guess at the cause. Nothing else leaps to mind, unfortunately.
:-(
-Kevin
On 7/1/2010 2:08 PM, Kevin Grittner wrote: > David Kerr<dmk@mr-paradox.net> wrote: > >>> Have you checked the PostgreSQL log file for clues? If you're >>> not seeing what you need, you could tweak the logging to show >>> more. >> >> We're trying that, it's (of course) intermittent and happens after >> like 5 hours. so huge logfiles, etc as usual. With >> log_statements=none turned on PG logs database errors. so it seems >> like whatever's happening isn't being considered an "error". more >> of a natural database transaction (like a lock) or something. does >> that sound about right? > > Hmmm.... I was just guessing at the cause. With default logging, > if I do this: > > test=# begin; > BEGIN > test=# select foo; > ERROR: column "foo" does not exist > LINE 1: select foo; > ^ > test=# select 'foo'; > ERROR: current transaction is aborted, commands ignored until end > of transaction block > test=# commit; > ROLLBACK > > I see this in the log: > > ERROR: column "foo" does not exist at character 8 > STATEMENT: select foo; > ERROR: current transaction is aborted, commands ignored until end > of transaction block > STATEMENT: select 'foo'; > > If you aren't seeing something like that, I'm probably off-base in > my guess at the cause. Nothing else leaps to mind, unfortunately. > :-( > > -Kevin > Yeah nothing at all in the logs (literally, zero log events while the process runs with default logging). that's why i'm leaning towards something in the app. or maybe the XA driver. thanks! Dave
David Kerr wrote: > Hello! > > I'm intermittantly getting this error message in a java app. > using Geronimo / Hibernate / Postgres 8.3.9 > > javax.transaction.RollbackException: Unable to commit: transaction marked for > rollback This looks like a JTA exception not a JDBC exception. i.e. something has marked the TM-managed transaction for rollback via Transaction.setRollbackOnly() This can happen for all sorts of reasons, from an explicit request from your application, through to container handling of an unexpected exception from an EJB, etc. -O
Hi, It can be caused by other reasones then SQL error. You wrote that error occurs after 5 hours, if you processing such long time in one transaction, then server can mark transcation for rollback automaticlly (it can "think", deadlock occured, too slow, infinite loop, etc...). You need to check Geronimo, setting about transactions timeouts. I've looked at error from your example, because I didn't belived such behavior. Unfortunatly it's true. Postgresql disallow to execute next statement if error occured - this is realy bad approach, because error can be from dupliacte keys etc. :( Regards, Radek Dnia czwartek 01 lipiec 2010 o 23:08:02 Kevin Grittner napisał(a): > David Kerr <dmk@mr-paradox.net> wrote: > >> Have you checked the PostgreSQL log file for clues? If you're > >> not seeing what you need, you could tweak the logging to show > >> more. > > > > We're trying that, it's (of course) intermittent and happens after > > like 5 hours. so huge logfiles, etc as usual. With > > log_statements=none turned on PG logs database errors. so it seems > > like whatever's happening isn't being considered an "error". more > > of a natural database transaction (like a lock) or something. does > > that sound about right? > > Hmmm.... I was just guessing at the cause. With default logging, > if I do this: > > test=# begin; > BEGIN > test=# select foo; > ERROR: column "foo" does not exist > LINE 1: select foo; > ^ > test=# select 'foo'; > ERROR: current transaction is aborted, commands ignored until end > of transaction block > test=# commit; > ROLLBACK > > I see this in the log: > > ERROR: column "foo" does not exist at character 8 > STATEMENT: select foo; > ERROR: current transaction is aborted, commands ignored until end > of transaction block > STATEMENT: select 'foo'; > > If you aren't seeing something like that, I'm probably off-base in > my guess at the cause. Nothing else leaps to mind, unfortunately. > > :-( > > -Kevin
On 03/07/10 16:36, Radosław Smogura wrote: > I've looked at error from your example, because I didn't belived such > behavior. Unfortunatly it's true. Postgresql disallow to execute next > statement if error occured - this is realy bad approach, because error can be > from dupliacte keys etc. :( If you want to handle errors and continue, use a subtransaction with BEGIN SAVEPOINT and ROLLBACK TO SAVEPOINT. Most of the time it's a performance advantage - and perfectly suitable for apps - to avoid keeping a rollback point for each statement, and instead chuck the whole transaction away if something goes wrong. Most apps expect to retry the whole transaction if anything goes wrong anyway, since it's often hard to tell if the issue can be resolved by retrying just one (possibly altered) statement or not. It's also safer. If Pg allowed a transaction to continue after an INSERT failed, it'd end up committing an incomplete set of the requested changes and hoping that the user/app noticed. I don't like that - I'd much rather have to explicitly handle the issue, and otherwise have an error in a transaction be an error that aborts the transaction, not just the particular statement that caused the error. For bulk insertion, you're almost always better off copying the data into a temporary table, cleaning it up if necessary, then using INSERT INTO ... SELECT to insert it duplicate-free. I guess an optional mode that issued an implicit savepoint before each transaction and allowed "ROLLBACK TO LAST STATEMENT" would be nice, though, as a convenience to avoid all the savepoint management on the rare occasions when you do want statement-level rollback. -- Craig Ringer
Unfortunatly in my apps I'm using JPA bridges and I don't belive that those use savepoints. Bad in this is that if I do mass processing it's obvious that some "records" can be badly processed, and I don't want to break transaction, (much more with JPA I can't use savepoints because there is no fixed point of send changes to database). I just want to write in this transaction information to 2nd table or mark record as failed. Yesterday I realised that my application can not work as I suspected. I need to test it for this special case. In any way I think about simple and special patch, just and only for JPA bridges to add SAVEPOINT before each query. Dnia niedziela 04 lipiec 2010 o 04:20:22 Craig Ringer napisał(a): > On 03/07/10 16:36, Radosław Smogura wrote: > > I've looked at error from your example, because I didn't belived such > > behavior. Unfortunatly it's true. Postgresql disallow to execute next > > statement if error occured - this is realy bad approach, because error > > can be from dupliacte keys etc. :( > > If you want to handle errors and continue, use a subtransaction with > BEGIN SAVEPOINT and ROLLBACK TO SAVEPOINT. > > Most of the time it's a performance advantage - and perfectly suitable > for apps - to avoid keeping a rollback point for each statement, and > instead chuck the whole transaction away if something goes wrong. Most > apps expect to retry the whole transaction if anything goes wrong > anyway, since it's often hard to tell if the issue can be resolved by > retrying just one (possibly altered) statement or not. > > It's also safer. If Pg allowed a transaction to continue after an INSERT > failed, it'd end up committing an incomplete set of the requested > changes and hoping that the user/app noticed. I don't like that - I'd > much rather have to explicitly handle the issue, and otherwise have an > error in a transaction be an error that aborts the transaction, not just > the particular statement that caused the error. > > For bulk insertion, you're almost always better off copying the data > into a temporary table, cleaning it up if necessary, then using INSERT > INTO ... SELECT to insert it duplicate-free. > > I guess an optional mode that issued an implicit savepoint before each > transaction and allowed "ROLLBACK TO LAST STATEMENT" would be nice, > though, as a convenience to avoid all the savepoint management on the > rare occasions when you do want statement-level rollback. > > -- > Craig Ringer
On 04/07/10 17:59, Radosław Smogura wrote: > Unfortunatly in my apps I'm using JPA bridges and I don't belive that those > use savepoints. Bad in this is that if I do mass processing it's obvious that > some "records" can be badly processed, and I don't want to break transaction, > (much more with JPA I can't use savepoints because there is no fixed point of > send changes to database). I just want to write in this transaction > information to 2nd table or mark record as failed. Your best bet in that situation is not to insert bad data in the first place, but I know that's not always possible. Really, all you can do is do each change that might fail in its own transaction, and if it fails discard and regenerate the object graph you tried to commit. You might think that you could perform an explicit flush wrapped in a savepoint after each change, but the JPA state of the objects you're using would still be messed up and you'd need to discard the half-committed objects and re-do anyway. You'd have things like oplock/version fields inconsistent with the database state. On the occasions I need to do anything like this, I clone a copy of my to-be-committed object graph ( SerializationHelper is really useful for this ) and, if the JPA transaction fails, I restore the saved copy of my object graph in place of the half-committed broken objects left by JPA. PostgreSQL's design - killing the whole transaction when something goes wrong - is a really good fit for JPA, because you cannot generally recover from an error in JPA without re-doing your transaction, no matter what database you are using. > Yesterday I realised that my application can not work as I suspected. I need > to test it for this special case. In any way I think about simple and special > patch, just and only for JPA bridges to add SAVEPOINT before each query. Given that a JPA implementation may choose to flush changes in various ways, including multi-valued inserts, I be really nervous about that. How would you report back to the app that some data was ignored, anyway? The changes were applied some time ago, and have only just been flushed. AFAIK JPA doesn't provide any kind of error listener or callback mechanism, and I wouldn't want to trust to SQL warnings for something as important as ignored changes. Now, the JPA provider could use savepoints internally to ignore certain kinds of error, translating them to warnings it reported ... somehow ... but that seems like an exceedingly ugly thing to do. It's hard for the JPA provider to tell the difference between a "minor" error and a really bad one, especially as the definition of those things changes depending on the app. BTW: Please don't top post. -- Craig Ringer
I wanted to follow up on this with what we think is the solution.
we increased the value in this Geronimo config.xml
<module name="org.apache.geronimo.configs/transaction/2.1.4/car">
<gbean name="TransactionManager">
<attribute name="defaultTransactionTimeoutSeconds">3600</attribute>
</gbean>
</module>
If we decrease that to like 15 seconds, we can reproduce the problem every time.
So, as a fix, we've increased it to a high enough value. Now, of course, the key
is to remember this so that if it happens again we'll know that we hit the new
max timeout.
Thanks!
Dave