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

[James Henstridge]

On 22/01/2008, Dieter Maurer <dieter at handshake.de> wrote:
> James Henstridge wrote at 2008-1-21 23:40 +0900:
> > ...
> >= DB-API Two-Phase Commit =
> >
> >Many databases have support for two-phase commit.  Adapters for some
> >of these databases expose this support, but often through mutually
> >incompatible extensions to the DB-API standard.
> >
> >Standardising the API for two-phase commit would make it easier for
> >applications and libraries to support two-phase commit with multiple
> >databases.
> >
> >
> >== Connection Methods ==
> >
> >A database adapter that supports two phase commit (2PC) shall provide
> >the following additional methods on its connection object:
> >
> >    .xa_begin(xid)
> >
> >        Begins a 2PC transaction with the given ID.  This method
> >        should be called outside of a transaction (i.e. nothing may
> >        have executed since the last .commit() or .rollback()).
> >
> >        Furthermore, it is an error to call .commit() or .rollback()
> >        within the 2PC transaction (what error?).
> >
> >        If the database does not support 2PC, a NotSupportedError will
> >        be raised.
> >
> >    .xa_prepare()
> >
> >        Performs the first phase of a transaction started with
> >        xa_begin().  It is an error to call this method outside of a
> >        2PC transaction.
> >
> >        After calling xa_prepare(), no statements can be executed
> >        until xa_commit() or xa_rollback() have been called.
> >
> >    .xa_commit(xid=None, onephase=False)
> >
> >        When called with no arguments, xa_commit() commits a 2PC
> >        transaction previously prepared with xa_prepare().
> >
> >        When called as xa_commit(onephase=True), it may be used to
> >        commit the transaction prior to calling xa_prepare().  This
> >        may occur if only a single resource ends up participating in
> >        the global transaction.
> >
> >        When called as xa_commit(xid), it commits the given
> >        transaction.  If an invalid transaction ID is provided, a
> >        DatabaseError will be raised.  This form should be called
> >        outside of a transaction, and is intended for use in recovery.
> >
> >        On return, the 2PC transaction is ended.
> >
> >    .xa_rollback(xid=None)
> >
> >        When called with no arguments, xa_rollback() rolls back a 2PC
> >        transaction.  It may be called before or after xa_prepare().
> >
> >        When called as xa_commit(xid), it rolls back the given
> >        transaction.  If an invalid transaction ID is provided, a
> >        DatabaseError will be raised.  This form should be called
> >        outside of a transaction, and is intended for use in recovery.
> >
> >        On return, the 2PC transaction is ended.
> >
> >    .xa_recover()
> >
> >        Returns a list of pending transaction IDs suitable for use
> >        with xa_commit(xid) or xa_rollback(xid).
> >
> >        If the database does not support transaction recovery, it may
> >        return an empty list or NotSupportedError.
>
> I would prefer, if
>
>   *  "xa_begin" would be optional
>
>      the current DB API performs automatic "begin" when there is a
>      need for it.

Please see the notes I wrote about the requirements for 2PC in various
databases.  For some of them, there is a different set of commands to
start a normal transaction and a 2PC transaction.  Going with implicit
begin would lead to ambiguity about what sort of transaction to start.


>   *  the transaction id be chosen automatically, optinally guided
>      by "Connection" configuration (to obtain "readable" transaction ids)

Note that transaction IDs are per-transaction rather than
per-connection, and usually assigned by the transaction manager (so
that there is a common portion for the IDs of all participating
resources).  I don't think a connection-time setting will cut it.


>   *  the use of "prepare_transaction" triggers a two phase commit --
>      otherwise a one phase commit is used.

As mentioned earlier, some of the databases want to know that 2PC is a
possibility at the start of the transaction.  As setting up a 2PC
transaction is more expensive, you probably don't want to enable them
in cases where they won't be used.

Deferring the decision until the prepare() stage essentially forces
the application to pay the price with some databases.

James.