Two-Phase Commit Protocol (2PC)

-- Konceptuelt eksempel af 2PC

-- Scenario: Transfer penge mellem banker
-- Bank A database og Bank B database skal begge committe

-- PHASE 1: PREPARE
-- Coordinator (Transaction Manager):
BEGIN DISTRIBUTED TRANSACTION txn_123;

-- Send PREPARE til Bank A:
PREPARE TRANSACTION txn_123;
-- Bank A executor:
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A-123';
-- Write to transaction log (WAL)
-- Lock resources
-- Respond: VOTE-YES or VOTE-NO

-- Send PREPARE til Bank B:
PREPARE TRANSACTION txn_123;
-- Bank B executor:
BEGIN;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B-456';
-- Write to transaction log
-- Lock resources
-- Respond: VOTE-YES or VOTE-NO

-- PHASE 2: COMMIT
-- Coordinator modtager votes fra alle participants
IF all_voted_yes THEN
  -- Send COMMIT til alle
  COMMIT PREPARED txn_123; -- til Bank A
  COMMIT PREPARED txn_123; -- til Bank B
  -- Both execute COMMIT
  -- Release locks
  -- Acknowledge to coordinator
ELSE
  -- Send ROLLBACK til alle
  ROLLBACK PREPARED txn_123; -- til Bank A
  ROLLBACK PREPARED txn_123; -- til Bank B
  -- Both execute ROLLBACK
  -- Release locks
END IF;

-- PostgreSQL eksempel (prepared transactions)
-- Session 1 (Bank A):
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 123;
PREPARE TRANSACTION 'txn_transfer_001';
-- Transaction er nu prepared (logged men ikke committed)
-- Locks holdes!

-- Session 2 (Bank B):
BEGIN;
UPDATE accounts SET balance = balance + 1000 WHERE id = 456;
PREPARE TRANSACTION 'txn_transfer_002';

-- Coordinator logic (separate process):
IF both_prepared_successfully THEN
  -- Commit both
  COMMIT PREPARED 'txn_transfer_001';
  COMMIT PREPARED 'txn_transfer_002';
ELSE
  -- Rollback both
  ROLLBACK PREPARED 'txn_transfer_001';
  ROLLBACK PREPARED 'txn_transfer_002';
END IF;

-- Node.js eksempel med XA transactions
const { XATransactionManager } = require('mysql-xa');

const tm = new XATransactionManager();
const xid = tm.generateXid();

try {
  // Phase 1: Prepare
  await db1.query('XA START ?', [xid]);
  await db1.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', 
    [amount, fromAccount]);
  await db1.query('XA END ?', [xid]);
  await db1.query('XA PREPARE ?', [xid]);
  
  await db2.query('XA START ?', [xid]);
  await db2.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', 
    [amount, toAccount]);
  await db2.query('XA END ?', [xid]);
  await db2.query('XA PREPARE ?', [xid]);
  
  // Phase 2: Commit
  await db1.query('XA COMMIT ?', [xid]);
  await db2.query('XA COMMIT ?', [xid]);
  
  console.log('Distributed transaction committed');
  
} catch (error) {
  // Phase 2: Rollback
  try {
    await db1.query('XA ROLLBACK ?', [xid]);
    await db2.query('XA ROLLBACK ?', [xid]);
  } catch (rollbackError) {
    console.error('Rollback failed:', rollbackError);
  }
  
  console.error('Distributed transaction failed:', error);
}

-- Failure scenarios

-- Scenario 1: Participant crashes after voting YES
-- Coordinator sends COMMIT
-- When participant recovers, it checks transaction log
-- Sees prepared transaction and commits it

-- Scenario 2: Coordinator crashes after prepare but before commit decision
-- Participants are stuck waiting (BLOCKING PROBLEM)
-- Requires timeout og coordinator recovery

-- Scenario 3: Network partition
-- Some participants don't receive commit message
-- Requires retry logic og idempotency

-- Alternative: Three-Phase Commit (3PC)
-- Tilføjer en pre-commit fase for at reducere blocking
-- Men stadig komplekst og sjældent brugt

-- Modern alternative: Saga Pattern
-- For microservices
-- Choreographed eller orchestrated compensating transactions
-- Eventual consistency i stedet for strong consistency