Explain ACID properties in databases and how they apply to a real-world money transfer scenario

ACID Properties

A — Atomicity (All or Nothing)

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A'; -- Step 1
  -- Server crashes here!
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B'; -- Step 2 (never runs)
ROLLBACK; -- Step 1 is undone — A's balance restored

C — Consistency (Valid State → Valid State)

-- Constraint: total money in system must stay constant
-- Before: A=$500, B=$300, C=$200, Total=$1000
-- After:  A=$400, B=$400, C=$200, Total=$1000 ✅
-- Never:  A=$400, B=$300, C=$200, Total=$900  ❌ (money disappeared)

I — Isolation (Concurrent Transactions Don't Interfere)

-- Without isolation (dirty read problem):
-- T1: A transfers $100 to B (in progress, not committed)
-- T2: Reads A's balance as $400 (uncommitted)
-- T1: Rolls back! A is actually still $500
-- T2 made a decision based on wrong data

-- With isolation (SERIALIZABLE):
-- T2 waits for T1 to commit or rollback before reading A's balance

D — Durability (Committed Data Survives Crashes)

-- Write-Ahead Log (WAL):
-- 1. Log the transaction to disk BEFORE committing
-- 2. Acknowledge commit to client
-- 3. Apply changes to data files (can be deferred)

-- If crash after step 2: WAL is replayed on recovery → data restored

The Concurrent Transfer Problem

-- Transaction 1: A → B ($100)
-- Transaction 2: A → C ($50)
-- Both read A's balance = $500

-- Without proper isolation (READ UNCOMMITTED):
-- T1: A = 500 - 100 = 400
-- T2: A = 500 - 50 = 450  (used stale value!)
-- Final: A = 450 (should be 350!) ❌ LOST $50

-- With SERIALIZABLE isolation:
-- T2 blocks until T1 completes
-- T2 reads A = 400, then A = 400 - 50 = 350 ✅

Isolation Levels vs Problems

Practical Implementation

async function transferMoney(fromId: string, toId: string, amount: number) {
  return await prisma.$transaction(async (tx) => {
    // Lock the sender's row
    const sender = await tx.account.findUnique({
      where: { id: fromId },
    });

    if (sender.balance < amount) {
      throw new Error("Insufficient funds");
    }

    // Both updates succeed or both fail
    await tx.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } },
    });

    await tx.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    });

    await tx.transactionLog.create({
      data: { fromId, toId, amount, status: "COMPLETED" },
    });
  }, {
    isolationLevel: "Serializable",
    timeout: 5000,
  });
}