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

[M.-A. Lemburg]

On 2008-01-18 11:40, Jeff Rush wrote:
> M.-A. Lemburg wrote:
>> On 2008-01-18 09:37, James Henstridge wrote:
>>> 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.
> 
> Thank you!  I've been wanting a two-phase commit API for a long time, to use
> with Zope myself.
> 
> 
>>> 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.
> 
> To meet the atomicity requirement of ACID, Zope does need additional APIs, to
> expose hooks into its two-phase mechanism.  If you only have access to the
> conventional .commit() and .rollback() methods of the database interface, you
> cannot handle this case:
> 
> 1. You have made a change to the ZODB and to a record in the PostgreSQL
>    database, which are part of a single transaction.
> 
> 2. The Zope TM invokes the .commit() method of the PostgreSQL interface.
> 
> 3. Then Zope TM invokes the .commit() method of the ZODB interface, which
>    fails for some reason (say a WriteConflict) -- now it is too late to
>    rollback the PostgreSQL commit and you're hosed.

While this would seem desirable, it is not how the Zope TM
works.

Phase 1 is implemented by doing a vote on the success
of the transaction. Phase 2 then finishes or aborts the transaction
depending on the vote.

If something fails in phase 2, there's no guarantee that partial
commits can be undone.

The .commit()/.rollback() calls on the database interface would
be implemented in the phase 2 part.

To avoid your scenario, the ZODB would have to detect the conflict
during phase 1 (ie. the voting phase).

>>> 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.
> 
> Which .prepare() are you referring to as possibly misinterpreted - that for
> his notes about kinterbasdb, pymqi or PostgreSQL?

That of DCOracle2.

The cursor.prepare() method is a DB-API extension that we've discussed
a couple of times.

Its intent it to prepare the execution of
an SQL command on the cursor, ie. parse it, prepare the access
path on the server and possibly fetch the parameter type information
from the server as well.

Using the .prepare() method you can detect errors in the SQL
before actually running the statement with data. It also allows
setting up a pool of cursor objects that are intended to each
only execute one type of SQL command, e.g. to enhance performance
for recurring SQL commands.

I'm not aware of any discussion on a connection.prepare()
method.

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