Re: XA support (distributed transactions)

Dawid Kuroczko wrote:
> On 11/20/06, Florian G. Pflug <fgp(at)phlo(dot)org> wrote:
>> Danny Milosavljevic wrote:
>> > Hi,
>> >
>> > We (Fabalabs) are looking into adding XA support (distributed
>> > transactions) to Postgresql.
>>
>> Postgres already supports 2 phase commits, which is can basis
>> on which XA can be implemented I think. The "only" missing
>> part is an transaction manager, but that wouldn't have to
>> be integrated into postgres. As far as I understand things,
>> the only thing you really need the transaction manager for
>> is for automatic recovery if one member of a distributed
>> transaction fails while the transaction is still in progress.
>> In that case the transaction manager needs to either rollback
>> the transaction, if it wasn't already prepared on all nodes,
>> or commit it.
>
> Hmm, a bit not on topic, but how big is 2PC overhead in
> PostgreSQL. I mean, I have an application which could
> benefit from 2PC but it is not mandatory -- it is connected
> to two DBs, and the second transaction is committed
> if first one succeeds. There is virtually no chance that
> the second commit won't succeed (rows are locked
> for update and all transactions lock rows in same
> order, etc, etc), but using 2PC would make a nice
> warm feeling. Then again, the process is quite
> stressful for that DB so I want to push as little
> overhead there as possible.

I think the biggest problem of XA without a transaction manager
is that leftover 2PC-Transactions will hold their locks
until a DBA manually resolves the situation by either comitting
or rolling back the transaction. If the transaction only
inserted new rows, than you might never realized that it's
still there (because it's holding no row-level locks for
rows visible to other transaction) until you notice that
vacuum is no longer reclaiming free space (Because those
old rows that vacuum could reclaim are still visible to
your lingering 2pc transaction). So 2PC without some monitoring
that ensures that "lost" transactions are dealt with seems
like a guarantee for trouble to me.

greetings, Florian Pflug