ACID vs BASE

// ACID transaction example — bank transfer
BEGIN;
  -- Atomicity: both succeed or both roll back
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;

  -- Consistency: CHECK constraint prevents negative balance
  -- ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

  -- Isolation: other transactions see either the old or new state, not partial
  -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  -- Durability: once COMMIT returns, data survives power failure
COMMIT;

// Isolation levels (weakest → strongest):
// READ UNCOMMITTED → dirty reads possible
// READ COMMITTED   → no dirty reads (PostgreSQL default)
// REPEATABLE READ  → no non-repeatable reads (MySQL InnoDB default)
// SERIALIZABLE     → full isolation (slowest)

// BASE example — e-commerce order flow
//
// 1. Order placed → write to Orders DB (committed)
// 2. Inventory decremented → async event to Inventory service
// 3. Payment charged → async event to Payment service
//
// Window of inconsistency:
//   Between step 1 and step 3, the order exists but payment
//   is not yet confirmed. The system is in a "soft state."
//
// Eventually consistent:
//   If payment fails, a compensating action cancels the order.
//   Given enough time, all services converge to a consistent state.

// Cassandra — BASE with tunable consistency
// Write with QUORUM (majority) for stronger consistency
// Read with ONE for availability
INSERT INTO orders (id, user_id, total)
VALUES ('ord-1', 'u-42', 99.99)
USING CONSISTENCY QUORUM;

SELECT * FROM orders WHERE id = 'ord-1'
USING CONSISTENCY ONE;  -- fast, might be stale

// Decision guide
//
// Use ACID when:
//  ✅ Financial transactions (transfers, payments)
//  ✅ Inventory management (prevent overselling)
//  ✅ User registration (unique constraints)
//  ✅ Any operation where partial state = data corruption
//
// Use BASE when:
//  ✅ Social media feeds (eventual delivery is fine)
//  ✅ Analytics / counters (approximate counts acceptable)
//  ✅ Notification delivery (retry until delivered)
//  ✅ Shopping cart (last-write-wins is acceptable)
//  ✅ Geo-distributed reads (stale-by-seconds is tolerable)
//
// Hybrid approach:
//  Payment service → PostgreSQL (ACID)
//  Recommendation engine → Cassandra (BASE)
//  Order service → ACID locally, BASE across services (Saga pattern)