Re: Nested transactions - Mailing list pgsql-patches

Barry Lind wrote:
> 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).

-O