The other thing that I have been meaning to say in this thread is that I don't like using COMMIT to mean subtransaction commit (vs. introducing a new command for it) because of the following situation.
Lets say that I have a java method that takes a jdbc connection and this code starts a transaction and does some work then issues a commit to ensure the changes have been committed to the database, and then does some other work outside the database based on the fact that the commit was sucessfull and it therefore knows the data is saved to disk (i.e. send out an email notification, or any number of other non-database tasks).
Now lets suppose that someone calls this method with a database connection that already has a transaction started, so that this method really is beginning and working with a sub-transaction. Now when it commits it doesn't know if the changes will ever get to disk since its commit could be rolled back later. So this code gets broken.
Note that there is no standard way in JDBC to enter a subtransaction unless you issue the BEGIN explicitly or invoke the savepoint API. There are lots of ways to confuse the driver's transaction handling already if you issue arbitary transaction control instructions (althugh I'm working on making the driver recover better -- the v3 transaction status indicator in ReadyForQuery helps).
And, well, how do you expect the code to ever not be broken? If commit() really does commit the whole transaction, the caller code that expects to still be in a transaction is going to be unhappy (if it doesn't expect to still be in a transaction, why is it opening a subtransaction at all?). The callee should be using JTA's registerSynchronization() to get callbacks after transaction commit, or using 2PC (yes I know it's not supported yet), depending on the guarantees it needs.
Earlier Alvaro was looking at ways to provide the transaction nesting level via the client protocol; I suggested doing it as a parameter (so you get ParameterStatus on nesting change) but I'm not sure what happened with it after that. Assuming something does get done here, the driver can track where it is in subtransactions quite easily, and so if you want Connection.commit() to really mean "commit this transaction and all subtransactions" even in the face of the user messing around with BEGIN themselves, we can do that even if multiple COMMITs are needed -- we just look at the current nesting level to work out how many to issue.
This behaviour (of commit()/rollback()) actually makes sense as things like connection pools will expect Connection.commit() or Connection.rollback to produce a reasonably "vanilla" connection state, and transaction monitors are likely to want those methods to affect the entire transaction too.
I like the functionality of nested transactions, I just think that there needs to be different commands other than BEGIN/COMMIT to work with them. So that there is no possiblity for misunderstanding what COMMIT really means.
"BEGIN NESTED WORK" / "COMMIT NESTED WORK" / "END NESTED WORK" or something? And make plain BEGIN inside a transaction a warning (as it currently is) and plain COMMIT/END inside a subtransaction an error? (or should they affect all subtransactions?) I can see this having some value for dealing with existing applications that issue redundant BEGIN/COMMIT/ROLLBACK statements (and get warnings, but ignore them).
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?