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

[M.-A. Lemburg]

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 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.

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.

> 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).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jan 18 2008)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611