Re: JDBC feature request: auto savepoint per command

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