Re: PATCH: two phase commit

On 21/01/2008, Stuart Bishop <[email protected]> wrote:
> Federico Di Gregorio wrote:
> > Il giorno lun, 21/01/2008 alle 11.31 +0900, James Henstridge ha scritto:
> >>> The question is, we want to "lock" the connection untill the current
> >>> prepared transaction is commited or we want to give the client the
> >>> ability to set aside prepared transactions and execute them from a
> >>> different connection? If nobody need the second one the first option
> >> is
> >>> "safe"; you can even abort the currently prepared transaction on
> >> object
> >>> dealloc.
> >> Yep.  The more limited API would handle pretty much everything I need
> >> to.
> >>
> >> I think the primary use case for committing/rolling back a transaction
> >> from a different connection is recovery, which may be out of scope for
> >> a common 2PC API for Python database adapters.
> >
> > Agreed. Then I'll start with a single prepare_current_transaction()
> > method that "locks" the connection (i.e., every other operation raise an
> > exception) untill commit() or rollback() are called.
>
> This makes the API useless for implementing anything more advanced, such as XA.
>
> In particular, if you are dealing with two phase commit you need some sort
> of a watchdog to detect and deal with orphaned transactions or your database
> is going to bloat (just like an IDLE in transaction connection left open,
> they stop vacuum reclaiming space). Nearly every serious environment using
> TPC needs something like this, so is it a common use case rather than an
> uncommon one. I know I'm going to have to write one before we can use TPC in
> production and I'd like to write it in Python. In fact, I just updated one
> of our tools the other day to rollback rogue prepared transactions in order
> to reliably tear down databases, manually sending ROLLBACK PREPARED
> statements via a connection in autocommit mode.

I don't think it'd be a total loss if the API didn't cover the case of
a watchdog script in the first iteration (provided we don't cut off
the possibility of adding support).


> Adding an optional xid argument to commit & rollback, or adding
> commit_prepared and rollback_prepared to the connection would handle this
> use case if locking the connection is considered desirable.
>
> Automatically rolling back a prepared transaction on object dealloc is a
> potential data loss situation. If something catastrophic happens in the
> second phase of a two phase commit, throwing away data may make the
> situation unrecoverable. PostgreSQL tries to guarantee that you can commit
> the data once you have done a PREPARE TRANSACTION and invalidating this
> guarantee is dangerous.

In which situations do you see the connection getting closed other
than the application crashing?  If the application is also acting as
the transaction manager (as the case would be with Zope apps), then it
will also have died.

This also matches the behaviour of closing a connection in the middle
of a normal one-phase transaction.

> I personally would prefer clean access to the PostgreSQL API rather than
> having the driver impose smarts here, thinking it knows best and locking the
> connection on me.

Well, you can always issue whatever you want if you put the connection
in autocommit mode and issue the statements directly.  This API isn't
going to affect that.

If providing a more restricted API allows us to standardise it, then I
think that'd be worth it.  I've sent a mail to the db-sig list with a
proposal for such an API, based on the database adapters I've seen,
and documentation on various databases' 2PC APIs.

James.