Thread: JDBC feature request: auto savepoint per command
Some folk at JBoss (now part of Red Hat) are complaining to me that
there's no way to deal with failing statements within a transaction
in the context of CMT (Container Managed Transactions). The normal
advice would be to do SAVEPOINT and RELEASE around each statement,
but they say this is disallowed:
> It's really a question of granularity. When using CMT a global
> transaction is not committed until the end of method completion.
> Prior to the JDBC connection being used, autocommit is set to false
> and the global transaction is officially started. When the global
> transaction completes, the transaction is committed as a whole. JDBC
> Rollback and commit within a managed transaction is prohibited as
> this is the responsibility of the container. So, setting a savepoint
> and rolling back (and subsequently committing) won't work in this
> context which is what the Postgres driver requires.
Can anyone see a solution for this in the context of the existing driver
facilities? If not, is it reasonable to ask for a driver option to do
auto-savepointing? There is already an equivalent facility in psql, so
the idea doesn't seem unreasonable to me.
regards, tom lane
The idea has been raised on this list before: http://archives.postgresql.org/pgsql-performance/2006-07/msg00245.php There is a negative performance impact but there's no reason it couldn't be a driver option. There isn't one right now though. -- Mark Lewis On Mon, 2007-04-23 at 14:25 -0400, Tom Lane wrote: > Some folk at JBoss (now part of Red Hat) are complaining to me that > there's no way to deal with failing statements within a transaction > in the context of CMT (Container Managed Transactions). The normal > advice would be to do SAVEPOINT and RELEASE around each statement, > but they say this is disallowed: > > > It's really a question of granularity. When using CMT a global > > transaction is not committed until the end of method completion. > > Prior to the JDBC connection being used, autocommit is set to false > > and the global transaction is officially started. When the global > > transaction completes, the transaction is committed as a whole. JDBC > > Rollback and commit within a managed transaction is prohibited as > > this is the responsibility of the container. So, setting a savepoint > > and rolling back (and subsequently committing) won't work in this > > context which is what the Postgres driver requires. > > Can anyone see a solution for this in the context of the existing driver > facilities? If not, is it reasonable to ask for a driver option to do > auto-savepointing? There is already an equivalent facility in psql, so > the idea doesn't seem unreasonable to me. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
Tom Lane wrote: > Some folk at JBoss (now part of Red Hat) are complaining to me that > there's no way to deal with failing statements within a transaction > in the context of CMT (Container Managed Transactions). The normal > advice would be to do SAVEPOINT and RELEASE around each statement, > but they say this is disallowed: > >> It's really a question of granularity. When using CMT a global >> transaction is not committed until the end of method completion. >> Prior to the JDBC connection being used, autocommit is set to false >> and the global transaction is officially started. When the global >> transaction completes, the transaction is committed as a whole. JDBC >> Rollback and commit within a managed transaction is prohibited as >> this is the responsibility of the container. So, setting a savepoint >> and rolling back (and subsequently committing) won't work in this >> context which is what the Postgres driver requires. They seem to be confusing rollback and commit of the managed transaction with use of savepoints.. I don't see why the prohibition on messing with the top-level global transaction affects use of savepoints, since use of savepoints won't affect that top-level transaction? -O
Oliver Jowett <oliver@opencloud.com> writes:
> They seem to be confusing rollback and commit of the managed transaction
> with use of savepoints.. I don't see why the prohibition on messing with
> the top-level global transaction affects use of savepoints, since use of
> savepoints won't affect that top-level transaction?
I got some further comments later in the day, which might mean more to
you than they do to me:
> In this particular case, the client wants the best of both worlds.
> They are using straight JDBC, but they also want to use CMT. In
> essence they want to execute JDBC interactions in a non-atomic manner
> only committing the *global* transaction on completion. While Oracle,
> DB2 etc support this, the savepoint commit()/rollback() issue with
> the PG driver makes this all but impossible given the current code
> base. Effectively the underlying JDBC transaction gets terminated on
> statement failure requiring an explicit savepoint and rollback to
> return the connection to a usable state which again, when using CMT
> is not valid because the connection is still enlisted within a global
> transaction.
I'm honestly not sure how much of this is "it really would violate some
spec or other" versus "we don't feel like putting in a special case".
But the bottom line is they'd like us to act like all the other
databases they support on this point.
regards, tom lane
Tom Lane wrote:
>> In this particular case, the client wants the best of both worlds.
>> They are using straight JDBC, but they also want to use CMT. In
>> essence they want to execute JDBC interactions in a non-atomic manner
>> only committing the *global* transaction on completion. While Oracle,
>> DB2 etc support this, the savepoint commit()/rollback() issue with
>> the PG driver makes this all but impossible given the current code
>> base. Effectively the underlying JDBC transaction gets terminated on
>> statement failure requiring an explicit savepoint and rollback to
>> return the connection to a usable state which again, when using CMT
>> is not valid because the connection is still enlisted within a global
>> transaction.
I still don't understand what they mean by "the savepoint
commit()/rollback() issue with the PG driver". Or why "when using CMT
[rolling back to a savepoint] is not valid because the connection is
still enlisted within a global transaction"
You can create savepoints, release savepoints, and rollback-to-savepoint
without affecting the top-level transaction at all. And as far as I
know, it's allowable to create, release, and rollback-to-savepoint while
using CMT. You're just forbidden from using commit() / rollback() on the
top-level transaction.
So.. what's the issue? What exactly is the case where a client using
savepoints within a CMT causes problems for the container by affecting
the global transaction?
AFAIK the situation is something like this:
container / transaction manager:
connection.setAutoCommit(false); // start of transaction
client:
Savepoint save = connection.setSavepoint();
try {
// .. execute some query ..
connection.releaseSavepoint(save);
} catch (SQLException s) {
connection.rollback(save);
}
container / transaction manager:
if (getRollbackOnly())
connection.rollback();
else
connection.commit();
I don't see how the client's use of savepoints can interfere with the
container's global rollback/commit. Even if it forgets to either release
or rollback-to the savepoint, the global commit or rollback is going to
clean that up.
When you're using CMT the bean is not meant to mess with transaction
demarcation of the container-initiated transaction, because the
container is expecting to manage the eventual commit or rollback of that
transaction (in particular, your bean might be only part of the work
being done in that transaction). But savepoints don't affect the
container-initiated transaction, and whatever you do with savepoints
isn't going to affect other things in the transaction because (a) you
can't get at a Savepoint that you didn't create and (b) subsequent beans
can't get at your Savepoints to mess with them.. so I'm still confused
as to why your JBoss colleagues think there's a problem here.
> I'm honestly not sure how much of this is "it really would violate some
> spec or other" versus "we don't feel like putting in a special case".
> But the bottom line is they'd like us to act like all the other
> databases they support on this point.
Well, can't you just implement it in the backend then, since all the
other databases do that? ;-P
-O
Tom Lane schrieb: > I got some further comments later in the day, which might mean more to > you than they do to me: > >> In this particular case, the client wants the best of both worlds. >> They are using straight JDBC, but they also want to use CMT. In >> essence they want to execute JDBC interactions in a non-atomic manner >> only committing the *global* transaction on completion. While Oracle, >> DB2 etc support this, the savepoint commit()/rollback() issue with >> the PG driver makes this all but impossible given the current code >> base. Effectively the underlying JDBC transaction gets terminated on >> statement failure requiring an explicit savepoint and rollback to >> return the connection to a usable state which again, when using CMT >> is not valid because the connection is still enlisted within a global >> transaction. Is it possible that they don't really understand that this is not about the JDBC driver at all, but just the way PostgreSQL works, no? So a driver option to do automatic save-pointing is the _only_ way to get the behavious they want, I guess. (Other than invasive backend hacking) > I'm honestly not sure how much of this is "it really would violate some > spec or other" versus "we don't feel like putting in a special case". > But the bottom line is they'd like us to act like all the other > databases they support on this point. If you look at the list archives, there were a number of people who did not expect this PostgreSQL-specific behavior (transaction rollback on statement error). I guess there will be many happy people if someone implements implicit savepoints in the JDBC driver. Best Regards Michael Paesold
On Tue, 2007-04-24 at 12:32 +0200, Michael Paesold wrote: ... > If you look at the list archives, there were a number of people who did > not expect this PostgreSQL-specific behavior (transaction rollback on > statement error). I guess there will be many happy people if someone > implements implicit savepoints in the JDBC driver. I would be one of these happy people. I can say as a maintainer of a large enterprise application which needs to support multiple databases that our PostgreSQL port is by far the easiest to maintain, however there are still a couple of pain points for us. One is the transaction invalidation on any error, because we use complex transactions and rely on the database referential integrity checks to enforce data consistency. This combination means that if any RI check fails we need to be able to re-try a possibly complex operation. It's frustrating because all of the other DB's we support give you the auto-savepoint before each statement semantics for free (instead of the roughly 15% performance penalty mentioned last time the implicit savepoint idea came up on the list). I guess this makes sense because PG is the only database we support with O(1) rollback-- all of the other databases need to do work to implement rollback anyway, so they already have the infrastructure in place out of necessity. Sorry about the rant, must be all the extra tension because I have painful SQL Server and DB2 ports coming up that I'm already dreading :) -- Mark
On Mon, 23 Apr 2007, Tom Lane wrote: > Can anyone see a solution for this in the context of the existing driver > facilities? If not, is it reasonable to ask for a driver option to do > auto-savepointing? There is already an equivalent facility in psql, so > the idea doesn't seem unreasonable to me. > Previous discussions on this topic had the idea that auto-savepointing would be done on a per-connection basis enabled via a URL parameter or DataSource option. In an application server would you have a separate connection pool for auto-savepointing connections? Would you force all transactions to use auto-savepoints even if they didn't want/need them? You could enable/disable it dynamically, but that would require pg specific code, is that what they want to do? Kris Jurka
Kris Jurka <books@ejurka.com> writes:
> Previous discussions on this topic had the idea that auto-savepointing
> would be done on a per-connection basis enabled via a URL parameter or
> DataSource option. In an application server would you have a separate
> connection pool for auto-savepointing connections? Would you force all
> transactions to use auto-savepoints even if they didn't want/need them?
> You could enable/disable it dynamically, but that would require pg
> specific code, is that what they want to do?
The JBoss guys think a URL option would work fine for them.
regards, tom lane
I wrote:
> The JBoss guys think a URL option would work fine for them.
Oh, they also would like it exposed as a DataSource property for the
PGXADataSource.
regards, tom lane