Can my DB?

-- Phase 1: Prepare
BEGIN;
INSERT INTO orders (id, total) VALUES (101, 500);
PREPARE TRANSACTION 'txn_order_101';

-- Phase 2a: Commit (after all participants confirm)
COMMIT PREPARED 'txn_order_101';

-- Phase 2b: Rollback (if any participant fails)
-- ROLLBACK PREPARED 'txn_order_101';

-- Inspect prepared transactions
SELECT gid, prepared, owner FROM pg_prepared_xacts;

• max_prepared_transactions must be set > 0 in postgresql.conf (defaults to 0, disabling 2PC).
• Prepared transactions survive server crashes and remain in pg_prepared_xacts until resolved.
• Used by PostgreSQL JDBC driver and connection poolers (PgBouncer) for XA/JTA support.
• Orphaned prepared transactions block VACUUM; monitor pg_prepared_xacts regularly.

-- Phase 1: Start and prepare
XA START 'txn_order_101';
INSERT INTO orders (id, total) VALUES (101, 500);
XA END 'txn_order_101';
XA PREPARE 'txn_order_101';

-- Phase 2a: Commit
XA COMMIT 'txn_order_101';

-- Phase 2b: Rollback
-- XA ROLLBACK 'txn_order_101';

-- Recover in-doubt transactions
XA RECOVER;

• InnoDB storage engine only; MyISAM and other engines do not support XA.
• XA transactions are used by Java EE/JTA application servers for distributed transactions.
• XA RECOVER lists in-doubt (prepared but not committed/rolled back) transactions.
• innodb_support_xa must be ON (default) for XA support.

XA START 'txn_order_101';
INSERT INTO orders (id, total) VALUES (101, 500);
XA END 'txn_order_101';
XA PREPARE 'txn_order_101';
XA COMMIT 'txn_order_101';

• Same XA syntax as MySQL.
• InnoDB engine required for XA support.
• XA RECOVER lists prepared transactions pending resolution.
• Compatible with Java/JTA transaction managers.

-- Oracle participates as an XA resource manager
-- Coordinated via external transaction manager (e.g., Oracle TX)

-- PL/SQL-level XA via DBMS_XA
DECLARE
  l_xid  DBMS_XA_XID := DBMS_XA_XID(101);
  l_ret  PLS_INTEGER;
BEGIN
  l_ret := DBMS_XA.XA_START(l_xid, DBMS_XA.TMNOFLAGS, 60);
  INSERT INTO orders (id, total) VALUES (101, 500);
  l_ret := DBMS_XA.XA_END(l_xid, DBMS_XA.TMSUCCESS);
  l_ret := DBMS_XA.XA_PREPARE(l_xid);
  l_ret := DBMS_XA.XA_COMMIT(l_xid, FALSE);
END;
/

• Oracle acts as an XA resource manager and can participate in distributed transactions.
• DBMS_XA package provides PL/SQL-level XA control.
• Oracle's DTP (Distributed Transaction Processing) uses the XA protocol.
• Distributed transaction coordinator (e.g., Oracle TX, JTA) manages the two-phase flow.

-- Requires MSDTC service running
BEGIN DISTRIBUTED TRANSACTION;

INSERT INTO orders (id, total) VALUES (101, 500);
-- Operations on linked server also enrolled automatically
INSERT INTO [LinkedServer].db.dbo.order_log (order_id) VALUES (101);

COMMIT;

• Distributed transactions coordinated by Microsoft Distributed Transaction Coordinator (MSDTC).
• MSDTC must be installed, configured, and running on all participating machines.
• BEGIN DISTRIBUTED TRANSACTION explicitly starts a distributed transaction; linked server operations auto-enlist.
• XA-compatible interface available via JDBC XA adapter for Java/JTA applications.
• SET XACT_ABORT ON recommended to auto-rollback on errors in distributed transactions.

• SQLite is a single-file embedded database with no distributed transaction support.
• All operations are local and single-connection.
• No XA protocol or two-phase commit mechanism exists.
• Application-level coordination is required for multi-database consistency with SQLite.

• Redshift does not support two-phase commit or distributed transactions.
• Redshift is designed for single-cluster analytics workloads.
• Use ETL orchestration tools (AWS Glue, Step Functions) for cross-system consistency.

-- DB2 participates as an XA resource manager
-- Coordinated via external JTA transaction manager

-- Inspect in-doubt transactions
SELECT * FROM TABLE(SYSPROC.DB_INDOUBT_TRANSACTIONS()) AS t;

• DB2 supports the XA protocol as a resource manager.
• DRDA (Distributed Relational Database Architecture) supports distributed queries across DB2 instances.
• In-doubt transactions can be examined and manually resolved via system procedures.
• Java applications use the DB2 XA DataSource (com.ibm.db2.jcc.DB2XADataSource) for JTA integration.

• Snowflake does not support two-phase commit.
• Multi-statement transactions use Snowflake's internal snapshot isolation.
• No external transaction coordinator support.
• Use application-level compensating transactions for cross-system consistency.

• BigQuery does not support distributed transactions or two-phase commit.
• Multi-statement transactions are supported within BigQuery itself (since 2022).
• Cross-system consistency requires application-level coordination.
• Use Cloud Spanner for globally distributed ACID transactions if 2PC semantics are needed.