[DB-SIG] Two-phase commit API proposal (was Re: Any standard for two phase commit APIs?)

[James Henstridge]

On 21/01/2008, M.-A. Lemburg <mal at egenix.com> wrote:
> On 2008-01-21 11:28, Federico Di Gregorio wrote:
> > I agree with your analisys, I'll add some comments about the proposal
> > below.
> >
> > Il giorno lun, 21/01/2008 alle 19.08 +0900, James Henstridge ha scritto:
> >>  1. Add a Connection.begin(...) method that explicitly starts a
> >>     transaction.  Some argument (possibly the transaction ID) causes
> >>     the transaction to use two-phase commit.  May raise
> >>     NotSupportedError if two-phase commit is not supported.
> >
> > DBAPI always had implicit transaction begin (for backends supporting
> > transactions) and adding an explicit begin() method would just add
> > confusion onto the user. "Should I always call begin()? Or just when I
> > want to start a two-phase?". I'd better like the two-phase begin method
> > named otherwise. Let's call it xa_begin() in this discussion.
>
> Agreed.
>
> I also think that we should prepend all of these methods with
> "xa_" or something similar: database backends may need to be to
> differentiate whether the user wants to e.g. commit in the context
> of a two-phase commit transaction or a regular one and the two-phase
> commit is also likely going to require an argument (the transaction id).
>
> Using a different set of methods would also make it clear to
> the reader of the code, that a two-phase commit transaction is
> happening (which does work a lot different from a one-phase one).

I'm indifferent about this.  I don't think using the same
commit/rollback methods presents much confusion.


> >>  2. Add a Connection.prepare() method that peforms the first stage of
> >>     two-phase commit.  May raise NotSupportedError if two-phase commit
> >>     is not supported, or the transaction was not started in two-phase
> >>     mode.
> >>
> > Ok. (Should be named accordingly with the begin method.)
>
> xa_prepare(xid)

In what cases would you pass a different xid to xa_prepare() vs. what
was passed to xa_begin()?

If not, then I'd leave the argument out: I've already told the
connection what the transaction ID is once already.

> >>  3. Calling commit() or rollback() on the connection after prepare()
> >>     performs the second stage of the commit.
> >>
> > Ok.
>
> xa_commit(xid) and .xa_rollback(xid)

Having these arguments would be quite useful for the recovery use-case.

I think it'd be useful to be able to use the methods without an
argument to operate on the current transaction too though.

> >>  4. Calling commit() or rollback() on the connection prior to
> >>     prepare() performs a one-phase commit or rollback.
> >>
> > IMHO, it should raise an error if the transaction was started for
> > two-phase. Otherwise I don't see any reason for (1).
>
> Agreed. They should raise an error.
>
> In fact, when operating in two-phase commit mode, I think
> using the one-phase methods .commit() and .rollback() should
> raise an error. Mixing the two is normally not a good idea and
> may very well result in an undefined state.

If we have separate rollback vs. xa_rollback, then sure.  But some
rollback method should be allowed before preparing the transaction.
The same goes for committing.


> >>  5. Executing statements after prepare() but before commit() or
> >>     rollback() results in an error (ProgrammingError?)
> >>
> > Ok.
>
> Agreed.
>
> >>  6. Closing a connection with a prepared but uncommitted transaction
> >>     rolls back that transaction.
> >>
> > Stuart's comment on psycopg ML made me think about this one. Maybe we
> > want an option added to xa_begin() to keep the prepared transaction open
> > even if the connection drops.
>
> A connection drop should always trigger an implicit rollback on the
> server side, so I'm not sure how and where you'd keep the required
> state to continue processing the transaction in case the connection
> is reestablished.

Uncommitted prepared transactions survive the connection in PostgreSQL
and can be committed from another connection.

Many 2PC-supporting databases provide some way of listing existing
transactions (e.g. MySQL's "XA RECOVER" statement), so I doubt
PostgreSQL is unique here.

At a minimum it'd be helpful to emit a warning in this case.

James.