Part 190: Distributed Transactions: 2PC vs Saga Orchestration in SQL

Coordinating transactions across multiple isolated database instances.

1. Two-Phase Commit (2PC / XA)

Traditional distributed transaction standard: 1. Prepare Phase: The Coordinator asks all databases: "Can you commit this change?" Databases lock rows and vote YES/NO. 2. Commit Phase: If all voted YES, Coordinator issues COMMIT PREPARED. Otherwise, ROLLBACK PREPARED.

-- Two-Phase Commit in PostgreSQL:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
PREPARE TRANSACTION 'tx_transfer_001';

-- On second database:
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
PREPARE TRANSACTION 'tx_transfer_002';

-- If both succeed:
COMMIT PREPARED 'tx_transfer_001';
COMMIT PREPARED 'tx_transfer_002';

Why 2PC is Avoided in Modern Systems: - Holding locks across network round-trips destroys system throughput and creates single-point-of-failure vulnerabilities.

2. The Saga Pattern with Compensating SQL

Instead of distributed locks, each microservice executes a local ACID transaction. If step 3 fails, the orchestrator triggers compensating undo transactions:

-- Step 1: Reserve Credit
UPDATE user_credit SET balance = balance - 50 WHERE user_id = 10;

-- Step 2: Reserve Inventory (Fails due to out-of-stock!)

-- Compensating Transaction triggered for Step 1:
UPDATE user_credit SET balance = balance + 50 WHERE user_id = 10;

Key Takeaways

• Sagas prioritize Availability over Consistency (Eventual Consistency), enabling massive horizontal scale.

In Part 191, we enter Module 14: Enterprise Administration, High Availability & Troubleshooting, starting with Connection Pooling Architecture!