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
_______________________________________________
DB-SIG maillist - DB-SIG python.org
http:/
/mail.python.org/mailman/listinfo/db-sig
|
