postgres two‐phase commit 2‐phase - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/sql-prepare-transaction.html
• https://www.postgresql.org/docs/current/logicaldecoding-two-phase-commits.html
• https://github.com/andrej/two-phase-commit
• https://martinfowler.com/articles/patterns-of-distributed-systems/two-phase-commit.html

PREPARE TRANSACTION does everything that could potentially fail during a commit except for the commit itself. That is to guarantee that a subsequent COMMIT PREPARED cannot fail.

The idea is that processing is as follows:

• Run START TRANSACTION on all database involved in the distributed transaction.
• Do all the work. If there are errors, ROLLBACK all transactions.
• Run PREPARE TRANSACTION on all databases. If that fails anywhere, run ROLLBACK PREPARED on those database where the transaction was already prepared and ROLLBACK on the others.
• Once PREPARE TRANSACTION has succeeded everywhere, run COMMIT PREPARED on all involved databases.

That way, you can guarantee “all or nothing” across several databases.

distributed transaction manager - software that persistently memorizes where in the above algorithm processing currently is so that it can clean up or continue committing after a crash.

PostgreSQL has disabled prepared transactions by default for a good reason, and enabling them can lead to orphaned transactions and data inconsistencies if not monitored carefully.

select * from pg_prepared_xacts;