[DB-SIG] Any standard for two phase commit APIs?

[James Henstridge]

On 18/01/2008, M.-A. Lemburg <mal at egenix.com> wrote:
> On 2008-01-18 09:37, James Henstridge wrote:
> > Hello,
> >
> > I've been looking at implementing two phase commit for the psycopg2
> > driver for PostgreSQL.  It was suggested that I bring up the issue on
> > this list to see if there were any suggestions about what form the API
> > should take.
> >
> > The API from the initial patch I produced stuck pretty close to the
> > PostgreSQL API, adding three methods to the connection object:
> >
> > prepare_transaction(xid) - prepare the transaction, using the given
> > ID.  This closes off the transaction, allowing a new one to be started
> > (if needed).
> >
> > commit_prepared(xid) - commit a previously prepared transaction . Must
> > be called outside of a transaction (i.e. no execute() calls since the
> > last commit/rollback).
> >
> > rollback_prepared(xid) - rollback a previously prepared transaction.
> >
> > The idea being that this should be enough to plug psycopg2 into a
> > transaction manager such as Zope's transaction module or similar.
>
> Zope doesn't require any specific additional APIs to hook
> the database module into its transaction mechanism. While
> you do need a wrapper (the Zope DA), the three methods used
> by the Zope TM easily map onto the standard .commit() and
> rollback() methods of the database interface.

I already have an understanding of how the Zope transaction manager
works.  The point is:

1. The database adapter needs to provide some API for use by a Zope
DataManager.  This API needs to co-exist with the standard DB-API
transaction handling.

2. If the database adapter is going to provide an API used to
implement two-phase commit, does it make sense to standardise such an
API across different database adaptrers?  This leads on to the
question I asked in my previous email:

> > I understand that this API might not be implementable by other
> > database adapters, which brings up the question: what would be a good
> > API?


> >>From a quick search, I found two other adapters implementing 2pc both
> > with incompatible APIs:
> >
> > kinterbasdb implements a Connection.prepare() method, which performs
> > the first phase and causes a subsequent commit() or rollback() to
> > complete that transaction.  Transaction identifiers are not exposed by
> > the API.
> >
> > pymqi provides a patch to the DCOracle2 adapter.  It doesn't seem to
> > add any explicit API to the connection object, but DCOracle2 does have
> > an incompatible prepare() method used for prepared statements.
>
> pymqi is a wrapper for IBM MQSeries which can act as XA-compliant
> two-phase commit transaction manager (TM). For this to work, the underlying
> database interface has to be compatible to the XA specification,
> which is essentially a C interface used directly by the TM.
>
> Note that XA implements transactions completely outside the
> normal scope of the Python database module, ie. you may not
> call .commit() or .rollback() on the connection objects, but
> instead have to register with the XA TM any action that
> you plan to have as part of a two-phase commit transaction.

Yep.  I am not sure how easy it would be to expose a Python level
two-phase commit API for DCOracle2 -- I just brought it up as an
example of a database adapter that people are using with a transaction
manager (albeit at the C level).


> BTW, I'm not sure whether you are interpreting the .prepare() correctly:
> this only prepares a statement for later execution, it doesn't
> do the first part of a two-phase commit which would be to save
> the current transaction log and check whether it could potentially
> be committed without problems.

I guess I was a bit unclear.  When I said that DCOracle had an
incompatible Connection.prepare() method, I meant that it is
incompatible with respsect to kinterbasdb's Connection.prepare().

Therefore, standardising a prepare() method for use in two-phase
commit would be problematic.


> > So is there any recommendations for what a two-phase commit API should
> > look like?
>
> It depends a lot on what you're trying to solve.
>
> In general, you usually have to adjust to an existing
> transaction manager and that then defines the interface
> to use. The are two industry standards for this: XA (X/Open)
> and DTC (MS).

I realise that tying a database adapter into a transaction manager
will often involve some level of database-specific code.

I just wonder if there is enough commonality to justify some level of
standardisation.  It seems silly for everyone to do things differently
for no good reason.

James.