Two-Phase Commit (Distributed)
Two-Phase Commit (Distributed) is a SQL statement in the Transactions category. Distributed transaction coordination across multiple databases or resources using a transaction manager. The syntax is PREPARE TRANSACTION 'txn_id'; COMMIT PREPARED 'txn_id';. It returns distributed transaction. A typical example: -- Phase 1: Prepare (vote) PREPARE TRANSACTION 'order_payment_123'; -- Database guarantees it can commit -- Transaction is held in 'prepared' state (survives crashes) -- Phase 2: Commit (decision) COMMIT PREPARED 'order_payment_123'; -- Or rollback: ROLLBACK PREPARED 'order_payment_123'; -- Application-level 2PC: -- 1. DB1: INSERT INTO orders ... -- 2. DB2: UPDATE inventory ... -- 3. If both OK: PREPARE both, then COMMIT both -- 4. If any fails: ROLLBACK both… A close relative is BEGIN / START TRANSACTION, which begins a new transaction. Changes made within are invisible to others until COMMIT. A close relative is COMMIT, which saves all changes made in the current transaction permanently. A close relative is ROLLBACK, which undoes all changes made in the current transaction (or to a savepoint).