Are distributed database transactions guaranteed to commit/rollback?

Question

I have application code that inserts a record in a local database and a record in a remote database (via Oracle Database Link). When I commit this distributed transaction is it guaranteed that both the local and remote databases will both commit or both rollback, or is there a chance that the remote one could commit but the local commit would fail (or vice versa)?

Answer 1

I'd be astonished if Oracle does not use the equivalent of the Two-Phase Commit (2PC) protocol, which ensures that either both commit or both rollback.

With 2PC, there is a stage called the pre-commit phase where the master (coordinator) instance records its own decision and tells all participants to get ready to commit (and report their status - must fail, or can commit). The participants also get ready to commit, and (if they can commit) await further instructions from the coordinator after telling the coordinator they are ready to commit. When all the participants have responded, the coordinator records the final decision, and sends that decision to the participants, and acts on its decision. If the master fails after recording the decision but before successfully sending the decision to the participants, the participants can be hung up in a state where they can neither commit nor rollback. There are ways to recover from that. If the coordinator stays down long enough (for example, is taken out of service as a result of catastrophic h/w failure) you can end up with problems; the participants end up doing a heuristic rollback (presumed rollback), typically - but this requires astonishingly bad luck to cause any trouble.

There are alternatives to 2PC; the net result is the same - all commit or all rollback.