KWAS Academy
Learn. Build. Master.
QUICK START:HTMLCSSJavaScriptTypeScriptPythonSQLReactNext.jsNode.jsLinux & UbuntuKotlinSwiftC# / .NETJavaGoRustC++DSASystem DesignDevOpsCybersecurityAI / ML
Advanced 26 min readModule: Module 15: Distributed Transactions: 2PC & Streaming Replication

Distributed SQL: Two-Phase Commit & Replication

Scale databases across nodes: atomic distributed transactions with Two-Phase Commit (2PC / `PREPARE TRANSACTION`), Physical vs Logical Streaming Replication, and synchronous commit quorum consistency.

What You Will Learn in This Lesson

  • The anatomy of Two-Phase Commit (2PC): Prepare Phase (Voting) vs Commit Phase (Resolution)
  • Executing distributed transactions with `PREPARE TRANSACTION 'tx_id'` and `COMMIT PREPARED`
  • Physical Streaming Replication vs Logical Replication (Decoded Write-Ahead Logs)
  • Configuring zero-data-loss synchronous replication with `synchronous_commit = remote_apply`

Introduction & Core Concept

When an enterprise application splits data across multiple database instances or microservices (e.g. User Database and Billing Database), standard single-node transactions cannot guarantee atomicity. Two-Phase Commit (2PC) is a distributed consensus algorithm that coordinates multiple independent database nodes to either commit or abort a transaction together as an atomic unit.
WHY DOES THIS MATTER IN THE REAL WORLD?

Financial settlement systems cannot allow money to be deducted from Database A without guaranteed recording in Database B. 2PC and synchronous streaming replication prevent distributed data divergence.

Syntax & Structure

sql
BEGIN;
UPDATE accounts SET bal = bal - 100 WHERE id = 1;
PREPARE TRANSACTION 'global_tx_99';
COMMIT PREPARED 'global_tx_99';

Executing a Two-Phase Commit (2PC) Transaction Across Nodes

sql
sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Node 1 (Ledger Service Database): Phase 1 - Prepare
BEGIN;
UPDATE customer_wallets SET balance = balance - 500.00 WHERE customer_id = 99;
-- Prepare the transaction for global consensus (Flushes state to WAL and releases connection lock)
PREPARE TRANSACTION 'transfer_tx_global_001';
 
-- Node 2 (Payment Gateway Database): Phase 1 - Prepare
BEGIN;
INSERT INTO processed_transfers (customer_id, amount, status) VALUES (99, 500.00, 'SETTLED');
PREPARE TRANSACTION 'transfer_tx_global_001';
 
-- ==========================================================
-- Distributed Coordinator receives 'PREPARED' vote from BOTH nodes:
-- ==========================================================
 
-- Node 1: Phase 2 - Commit
COMMIT PREPARED 'transfer_tx_global_001';
 
-- Node 2: Phase 2 - Commit
COMMIT PREPARED 'transfer_tx_global_001';
 
-- 3. Monitoring Replication Health and Lag
SELECT 
    client_addr, 
    state, 
    sync_state, 
    sync_priority,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

Line-by-Line Technical Breakdown

1Replication Modes: In Asynchronous Replication (`synchronous_commit = off/local`), commits return instantly, but failover may lose milliseconds of data. In Synchronous Replication (`synchronous_commit = on`), commits wait until the standby confirms writing the WAL to disk.

Try It Yourself (Interactive Editor)

Modify the code in real-time and click Run to test live browser output and console logs.

Intelligent Code Runner & Live Sandbox[SQL]
SQL SOURCE EDITOR
Interactive Live Code

Common Mistakes & How to Avoid Them

#1: Abandoning prepared transactions without committing or rolling them back, causing permanent lock retention.

Prepared transactions hold table locks and prevent VACUUM from cleaning dead tuples until explicitly resolved with COMMIT/ROLLBACK PREPARED.

Incorrect / Antipattern
PREPARE TRANSACTION 'tx_1'; -- Coordinator crashes and never commits
Correct / Professional Solution
SELECT * FROM pg_prepared_xacts; -- Monitor and resolve dangling prepared transactions

Industry Best Practices & Professional Standards

  • Implement automated monitors on `pg_prepared_xacts` to detect orphaned distributed transactions.
  • Use `synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'` for quorum replication.
  • Use Logical Replication for zero-downtime major PostgreSQL version upgrades.

Lesson Summary & Core Takeaways

  • Two-Phase Commit guarantees atomicity across multiple independent database clusters.
  • Prepare Phase ensures durability in WAL; Commit Phase finalizes the transaction.
  • Streaming replication distributes read traffic and provides high-availability failover.