Skip site navigation (1) Skip section navigation (2)

Re: JDBC feature request: auto savepoint per command

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-jdbc(at)postgreSQL(dot)org
Subject: Re: JDBC feature request: auto savepoint per command
Date: 2007-04-24 00:37:34
Message-ID: 462D514E.3070405@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2007-04-24 00:39:33
Subject: Re: Can't build postgresql-jdbc-8.2-505 on Fedora 7
Previous:From: Tom LaneDate: 2007-04-24 00:09:30
Subject: Re: Can't build postgresql-jdbc-8.2-505 on Fedora 7

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group