ACID,
Distributed
Transactions
& Saga

// 1. DIRTY READ: read uncommitted data that later rolls back
T1: UPDATE balance = 50 WHERE user='alice'  (not yet committed)
T2: SELECT balance FROM users WHERE user='alice'  → 50  (dirty!)
T1: ROLLBACK  → balance never changed to 50
T2 read data that never existed.

// 2. NON-REPEATABLE READ: same row returns different values in same transaction
T1: SELECT balance FROM users WHERE user='alice'  → 100
T2: UPDATE balance = 200 WHERE user='alice'; COMMIT
T1: SELECT balance FROM users WHERE user='alice'  → 200  (changed!)

// 3. PHANTOM READ: new rows appear in a repeated range query
T1: SELECT COUNT(*) FROM orders WHERE status='pending'  → 5
T2: INSERT INTO orders (status) VALUES ('pending'); COMMIT
T1: SELECT COUNT(*) FROM orders WHERE status='pending'  → 6  (phantom!)

// 4. LOST UPDATE: two transactions overwrite each other's changes
T1: READ balance = 100
T2: READ balance = 100
T1: WRITE balance = 110  (+10)
T2: WRITE balance = 120  (+20, but only saw original 100 — lost T1's update!)
// Should be 130. T2 overwrote T1. 10 dollars vanished.

// A customer places an order. This requires:
// 1. Order Service     → INSERT order  INTO orders_db
// 2. Payment Service   → UPDATE balance IN payment_db
// 3. Inventory Service → UPDATE stock   IN inventory_db
// 4. Notification      → send email     via external SMTP

// These are FOUR SEPARATE DATABASES. There is NO single transaction spanning them.
// What happens if Step 3 fails after Steps 1 and 2 succeed?

BEGIN TRANSACTION on orders_db:
  INSERT INTO orders ...    ✓ committed
  
BEGIN TRANSACTION on payment_db:
  UPDATE balance - $100 ... ✓ committed  ← alice is charged
  
BEGIN TRANSACTION on inventory_db:
  UPDATE stock - 1 ...      ✗ FAILS  ← item out of stock!

// Alice was charged $100 but cannot receive her item.
// Order DB shows order created. Payment DB shows deduction. Inventory unchanged.
// System is in an INCONSISTENT state across services.

// For every Saga step, design its compensation BEFORE building the step:

Step 1: Create Order        → Compensation: Cancel Order
Step 2: Deduct Payment     → Compensation: Issue Refund
Step 3: Reserve Inventory  → Compensation: Release Reservation
Step 4: Book Shipping Slot → Compensation: Cancel Booking
Step 5: Send Email         → Compensation: Send Cancellation Email
// (cannot un-send; notify user of cancellation instead)

// Pivot transaction: the step after which compensation is impossible/impractical
// Design: put pivot transaction AS LATE AS POSSIBLE in the saga
// Notifications, external API calls = typically pivot transactions

// Idempotency requirement:
// Compensation may run multiple times (network retry, at-least-once delivery)
// REFUND must be idempotent: cannot refund twice for one order
// Check: INSERT INTO refunds (order_id, amount) ON CONFLICT DO NOTHING
// Or: idempotency key = order_id → check before processing

// Payment Service receives "ProcessPayment" command

// Step 1: update DB
UPDATE accounts SET balance = balance - 100 WHERE user='alice';
COMMIT;  ← succeeds

// 💥 SERVER CRASHES HERE

// Step 2: publish event
kafka.publish("PaymentProcessed", {...});  ← NEVER RUNS

// Result: alice's balance is deducted, but no "PaymentProcessed" event was published.
// The saga is stuck. Alice pays but gets nothing.
// Dual write problem: two separate systems (DB + Kafka) cannot be updated atomically.

// Within a SINGLE LOCAL DB TRANSACTION:
BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE user='alice';
  INSERT INTO outbox (id, event_type, payload, sent, created_at)
  VALUES (uuid(), 'PaymentProcessed', '{"order":"123","amount":100}', false, NOW());
COMMIT;  ← both update AND outbox row are atomic

// Separate "Outbox Relay" process (runs continuously):
while (true) {
  rows = db.query("SELECT * FROM outbox WHERE sent = false ORDER BY created_at LIMIT 100")
  for (row of rows) {
    kafka.publish(row.event_type, row.payload)  // at-least-once
    db.update("UPDATE outbox SET sent=true WHERE id=?", row.id)
  }
  sleep(100ms)
}

// Guarantee: if DB committed → event row exists → relay will publish it
// Relay may publish twice (retry on crash) → consumer must be idempotent
// Alternative: CDC (Debezium) watches DB transaction log → publishes to Kafka

// Client sends payment with idempotency key
POST /payments
Headers: Idempotency-Key: "order-123-payment-attempt-1"
Body:    {"amount": 100, "currency": "USD", "user": "alice"}

// Server logic:
function processPayment(idempotencyKey, amount, user) {
  // Check if already processed
  existing = db.query("SELECT result FROM idempotency_cache WHERE key = ?", idempotencyKey)
  if (existing) return existing.result  // return same result, don't charge again

  // Process payment
  result = stripe.charge(amount, user)

  // Store result with key (within same transaction as the charge)
  db.insert("INSERT INTO idempotency_cache (key, result, expires_at) VALUES (?,?,?)",
    idempotencyKey, result, now + 24h)

  return result
}

// If client retries (network timeout, didn't receive response):
// POST /payments with same Idempotency-Key → returns cached result, no double charge

// Idempotency key design:
// order_id + step_name = "order-123-payment"  (scoped to specific operation)
// UUID per attempt = allows retry after timeout, prevents replay after success
// TTL: 24 hours (after which key expires; client should create new order)