Re: XA support (distributed transactions)

Hi,

On Mon, 20 Nov 2006 18:47:12 +0100, Florian G. Pflug 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.

Yes, among other things needed.

 > The "only" missing
 > part is an transaction manager, but that wouldn't have to
 > be integrated into postgres. [...]

Our parent company, Fabasoft, already wrote a transaction manager
("Fabasoft Transaction Manager"). I doubt that it is Open Source but
I'll ask.

On the other hand, it's there and already works on Oracle so we aren't
stepping in the dark, testing-/interface-wise.

 > The only difficulty I see in this "transaction manager" is that

 > it will need to (persistenly) keep track of transactions,

Indeed, it does keep track of the state of the global transactions, in a
sophisticated text file :).

 > because
 > due to the design of 2-phase-commit, you cannot deduce the faith
 > of a transaction by just looking at the nodes.

But you can find out the prepared transactions of a two phase commit
(there is a pg system table for that, "pg_prepared_xacts").

What's important is that if the database management system were to
crash, the prepared entries in that table _need to persist_ and be
available after a restart (so that the transaction manager can find out
what actually worked and what didn't work per database before the
connection broke - in XA slang, with a "XA RECOVER").

What's also important is that PREPARED transactions must not complete
(not commit, not rollback) when the pq connection to the database is
closed. This ensures that the database doesn't lose (nor commit) already
prepared transactions in the unlikely but evil case of a
crash/disconnect just between a 2PC PREPARE and a 2PC COMMIT.
(Transactions not yet prepared can just vanish in thin air on the DBMS'
discretion)

Also, XA has some kind of semi-parallelism where one global transaction
branch is supposed to be able to be suspended, another global
transaction branch to be worked on "instead", then this one suspended
too, then the original resumed, and so on, leading to something
reminiscent of time slices :). I have no idea how far Postgresql
supports this particularily tricky part, and whether it makes any sense
to (or whether just a commit/rollback of a previously
crashed-but-prepared transaction suffices).

So my actual questions are:
what is a postgresql transaction tied to, if anything? Or is it a
first-class object?
What happens to prepared-but-not-yet-committed 2PC transactions on a crash?

regards,
   Danny Milosavljevic