Re: Consistency of distributed transactions

On 06/01/2011 11:11 AM, Rob Sargent wrote:

> My recollection of distributed transactions is that the manager tells
> all players to do their thing. Then they each tell the manager "ok, did
> the deed". Then, if all players say ok, each of them is told by the
> manager to go ahead and commit, else rollback. Sorry, I don't recall the
> procedure for commit failure but I think you're in manual undo-land at
> that point.

Distributed transactions only work well when all participants (or all
but one participant) support two phase commit or a similar scheme where
they can almost totally guarantee a successful commit. The idea is that
each participant is told "I'm done doing work, prepare to commit and
tell me when you can guarantee - as far as is ever possible - that
commit will succeed". Once each participant affirms, the transaction
manager can tell each to go ahead and be - almost - totally confident
that all will go well.

See:
http://www.postgresql.org/docs/current/static/sql-prepare-transaction.html

http://en.wikipedia.org/wiki/Two-phase_commit_protocol

Of course, even with 2PC it's possible that something will go wrong when
the final commit is issued, but it's (a) extremely unlikely and (b)
likely that the failure can be remedied later by successfully applying
the changes, rather than having to roll everything back across all the
servers.

A good example of where 2PC is important: think ATMs. There are two
transactions - the writing of the accounting records, and the vending of
cash. It is utterly vital that both succeed or fail together, but one
is a physical operation that cannot be rolled back or undone. So you do
the account updates, do the logical equivalent of PREPARE TRANSACTION to
make sure the changes are on record and safe but not yet applied, vend
the cash, and then COMMIT PREPARED. If the cash roller jammed, you can
ROLLBACK PREPARED.

(Of course, if the UPS has a hiccup between PREPARE TRANSACTION and
COMMIT PREPARED, you don't know for sure if you successfully vended the
cash, only that you intended to. You have to examine the ATM to find
out, but then you can decide whether to commit or rollback the prepared
transaction(s), instead of having to shrug your shoulders and say "er,
yeah, we vended some cash but we don't know to whom or when because we
lost those open transactions in a power failure").

--
Craig Ringer