In simple words

A transaction is a group of database changes that must be treated as one unit — for example moving money: take ₹1000 from Alice and add ₹1000 to Bob. ACID is the four promises a database makes about such a group: it happens completely or not at all (Atomicity), it leaves the data valid (Consistency), it isn't confused by other people working at the same time (Isolation), and once it is saved it survives a crash (Durability). If the server dies between the two steps, the database undoes the first one, so money never disappears.

---

What is ACID?

ACID is a set of four properties that guarantee database transactions are processed reliably, even in the face of errors, crashes, and concurrent access.

A — Atomicity     →  all or nothing
C — Consistency   →  data stays valid before and after
I — Isolation     →  concurrent transactions don't interfere
D — Durability    →  committed data survives crashes

Without ACID, a payment system could charge you twice, credit the wrong account, or lose your transaction during a server crash.

---

What is a Transaction?

A transaction is a sequence of one or more database operations treated as a single unit of work.

-- Example: Transfer ₹1000 from Alice to Bob
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE user_id = 1;  -- debit Alice
UPDATE accounts SET balance = balance + 1000 WHERE user_id = 2;  -- credit Bob

COMMIT;  -- make changes permanent
-- OR
ROLLBACK;  -- undo all changes if something went wrong

If the server crashes after debiting Alice but before crediting Bob, ACID properties ensure Alice gets her money back and Bob is not credited.

---

A — Atomicity

Definition

"A transaction is treated as a single unit — it either completes fully or not at all. There is no partial execution."

All operations in a transaction:
  ✅ ALL succeed → COMMIT (changes made permanent)
  ❌ ANY fails   → ROLLBACK (all changes undone — back to original state)

"Atomic" = indivisible, like an atom — can't be split

❌ Without Atomicity — The Nightmare

Transfer ₹1000 from Alice to Bob:

Step 1: Debit Alice's account (balance: 5000 → 4000)  ✅ done
        SERVER CRASHES HERE
Step 2: Credit Bob's account                           ❌ never happened

Result:
  Alice lost ₹1000
  Bob received nothing
  ₹1000 vanished from the system
  → Financial disaster

✅ With Atomicity

Transfer ₹1000 from Alice to Bob:

BEGIN TRANSACTION
Step 1: Debit Alice's account (balance: 5000 → 4000)  ✅ done
        SERVER CRASHES HERE
Step 2: Credit Bob's account                           ❌ never happened

On restart:
  DB detects incomplete transaction in the WAL (Write-Ahead Log)
  Rolls back Step 1 → Alice's balance restored to 5000
  Transaction treated as if it never happened

Result: Alice keeps her ₹1000. No data corruption.

How Atomicity is Implemented

Write-Ahead Log (WAL) / Undo Log:
  Before changing any data, the DB writes the intended changes to a log
  If crash occurs mid-transaction, the DB reads the log on restart
  and reverses any incomplete changes

ROLLBACK mechanism:
  Every change is tracked in an undo log
  ROLLBACK replays the undo log to reverse all changes in reverse order

---

C — Consistency

Definition

"A transaction brings the database from one valid state to another valid state. All data integrity constraints must be satisfied before and after the transaction."

Valid state = all constraints hold:
  - Primary keys are unique
  - Foreign keys reference existing rows
  - CHECK constraints are satisfied
  - NOT NULL constraints are satisfied
  - Application-level rules are satisfied (e.g. balance cannot go negative)

❌ Without Consistency

Rule: bank accounts cannot have negative balance

Transaction: Transfer ₹5000 from Alice (balance: ₹1000)

Without consistency check:
  Alice's balance: 1000 - 5000 = -4000  ← negative balance allowed?!
  DB accepts this → invalid state

✅ With Consistency

-- Define constraint at DB level
ALTER TABLE accounts
ADD CONSTRAINT chk_positive_balance
CHECK (balance >= 0);

-- Now if Alice has ₹1000 and tries to transfer ₹5000:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE user_id = 1;
-- DB checks: 1000 - 5000 = -4000 → violates CHECK constraint
-- → Transaction ABORTED → ROLLBACK automatically
-- Alice's balance stays at ₹1000

Consistency — DB Responsibility vs App Responsibility

DB enforces:
  ✅ Primary key uniqueness
  ✅ Foreign key references
  ✅ NOT NULL constraints
  ✅ UNIQUE constraints
  ✅ CHECK constraints

Application must enforce:
  ✅ Business rules (e.g. "a user can't place more than 10 orders per day")
  ✅ Cross-table business logic (e.g. "inventory must be deducted when order is placed")
  ✅ Domain-specific rules (e.g. "loan amount cannot exceed 5x monthly income")

Consistency = BOTH layers working together

---

I — Isolation

Definition

"Concurrent transactions execute as if they were running serially — each transaction is isolated from the effects of others running at the same time."

Problem: 1000 transactions running simultaneously on the same data
         Without isolation: they interfere with each other → incorrect results

Goal: each transaction sees a consistent snapshot of data
      as if it were the only transaction running

What Goes Wrong Without Isolation

Problem 1: Dirty Read

Transaction A updates Alice's balance to ₹9000 (not yet committed)
Transaction B reads Alice's balance → sees ₹9000
Transaction A rolls back → Alice's balance is back to ₹5000
Transaction B used a value (₹9000) that never really existed → DIRTY READ

Problem 2: Non-Repeatable Read

Transaction A reads Alice's balance → sees ₹5000
Transaction B commits a change → Alice's balance is now ₹8000
Transaction A reads Alice's balance again → sees ₹8000

Same transaction, same query, two different results → NON-REPEATABLE READ

Problem 3: Phantom Read

Transaction A: SELECT COUNT(*) FROM orders WHERE status = 'PENDING' → 50 rows
Transaction B: INSERT new pending order → commits
Transaction A: SELECT COUNT(*) FROM orders WHERE status = 'PENDING' → 51 rows

New row "appeared" within the same transaction → PHANTOM READ

Problem 4: Lost Update

Transaction A: reads inventory count = 10
Transaction B: reads inventory count = 10
Transaction A: sets count = 10 - 1 = 9, commits
Transaction B: sets count = 10 - 1 = 9, commits (overwrites A's change!)

Net result: count = 9, but TWO items were sold → count should be 8
→ LOST UPDATE

Isolation Levels (4 Standard Levels)

Isolation has a cost — more isolation = less concurrency = lower throughput. Different isolation levels trade off between consistency and performance.

Weakest ←────────────────────────────────────────── Strongest
READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE
(fastest, least safe)                               (slowest, fully safe)

Isolation Level    │ Dirty Read │ Non-Repeatable │ Phantom Read
───────────────────┼────────────┼────────────────┼─────────────
READ UNCOMMITTED   │ Possible   │ Possible       │ Possible
READ COMMITTED     │ Prevented  │ Possible       │ Possible
REPEATABLE READ    │ Prevented  │ Prevented      │ Possible (*)
SERIALIZABLE       │ Prevented  │ Prevented      │ Prevented

(*) MySQL InnoDB prevents phantoms in REPEATABLE READ via gap locks

READ UNCOMMITTED

Reads see uncommitted changes from other transactions.
Almost never used in production.
Only for scenarios where approximate data is acceptable (rough analytics).

Use case: real-time approximate dashboards where stale/dirty data is acceptable

READ COMMITTED

Reads see only committed data — no dirty reads.
Each read within a transaction sees the latest committed snapshot.

Default in: PostgreSQL, Oracle, SQL Server
Problem: Two reads in the same transaction can return different results
         (non-repeatable read)

Use case: most OLTP applications where this level of isolation is sufficient

REPEATABLE READ

Within a transaction, repeated reads return the same data.
A snapshot is taken at the start of the transaction.

Default in: MySQL InnoDB
Prevents: dirty reads, non-repeatable reads
Allows: phantom reads (rows added by other transactions may appear)
       (MySQL InnoDB prevents phantoms too via next-key locks)

Use case: financial reports, statements that need consistent data within a transaction

SERIALIZABLE

Transactions execute as if they were serial (one at a time).
Completely prevents all concurrency anomalies.
Most restrictive — uses range locks / predicate locks.

Prevents: dirty reads, non-repeatable reads, phantom reads
Cost: lowest throughput — transactions block each other heavily

Use case: banking transactions, inventory deduction where any anomaly is unacceptable

Setting Isolation Level

-- Session level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Per transaction
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE user_id = 1;
UPDATE accounts SET balance = balance - 1000 WHERE user_id = 1;
COMMIT;

-- Check current level (MySQL)
SELECT @@transaction_isolation;

---

D — Durability

Definition

"Once a transaction is committed, it remains committed — even in the event of system failure, power loss, or crash."

COMMIT = permanent
         The data is guaranteed to be on stable storage

After COMMIT:
  Power outage → data survives
  Server crash → data survives
  OS crash     → data survives
  
The ONLY way committed data is lost:
  Physical disk failure (mitigated by replication)
  Catastrophic data center disaster (mitigated by backups + multi-region)

How Durability is Implemented

Write-Ahead Log (WAL)

Before writing data to the actual data pages on disk:
  1. Write the change to the WAL (sequential log file) first
  2. WAL is flushed to disk (fsync)
  3. Return success to the client
  4. Later: apply the change to the actual data pages (background)

On crash recovery:
  DB reads the WAL
  Replays committed transactions that weren't yet applied to data pages
  Rolls back uncommitted transactions

WAL is sequential writes → much faster than random writes to data pages
This is how DBs achieve both durability AND performance

Timeline:
  Client → BEGIN → [operations] → COMMIT
                                       ↓
                               Write to WAL (sequential, fast)
                               fsync WAL to disk
                               Return "COMMIT OK" to client
                                       ↓ (async, background)
                               Apply changes to actual data pages

If crash happens AFTER commit acknowledgement:
  WAL has the record → on restart, DB replays the WAL → data is recovered ✅

If crash happens BEFORE commit acknowledgement:
  WAL may have partial transaction → DB rolls it back on restart ✅

Durability in Distributed Systems

Single machine durability:
  WAL + fsync = data survives crashes on that machine
  But if the disk fails → data lost

Distributed durability:
  Replication: same data on 3+ machines
  Commit = acknowledged only after majority of replicas confirm write
  Any single machine failure → data survives on others

  Example: MySQL with semi-synchronous replication
  COMMIT acknowledged only after at least 1 replica confirms receipt

  Example: Raft consensus (etcd, CockroachDB)
  COMMIT acknowledged only after majority of nodes (quorum) confirm write

---

ACID Together — The Bank Transfer Example

BEGIN TRANSACTION;  -- start the transaction

-- A: Atomicity — either both updates happen or neither
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'ACC001';
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'ACC002';

-- C: Consistency — constraints checked: balance >= 0 must hold
--    If ACC001 has insufficient funds → constraint violated → ROLLBACK

-- I: Isolation — concurrent transfers don't interfere
--    If another transaction reads ACC001 during this, it sees either
--    the old value or waits (depending on isolation level)
--    It never sees the deducted amount without the corresponding credit

COMMIT;  -- D: Durability — once committed, this transfer is permanent
         -- even if server crashes 1ms after this line

---

ACID vs BASE — Know the Difference

Relational databases are ACID. Many NoSQL databases are BASE.

ACID                              BASE
────────────────────────          ────────────────────────────────
Atomic                            Basically Available
Consistent                        Soft state
Isolated                          Eventually consistent
Durable

Strong consistency                Eventual consistency
No anomalies allowed              Anomalies may temporarily exist
Lower throughput                  Higher throughput / availability
Relational DBs                    NoSQL (Cassandra, DynamoDB, MongoDB)

Example:
ACID: Bank transfer — must be exactly right
BASE: Social media like count — showing 1001 instead of 1000 for a moment is fine

---

System Design Context

When Full ACID is Non-Negotiable

Payment processing:   Charge ₹5000 → debit one account, credit another
                      Any partial failure = money lost or created from thin air

Order placement:      Reserve inventory + create order + charge payment
                      All must succeed or all must roll back

Bank account transfer: Classic ACID use case
                      Never acceptable to debit without crediting

Medical records:      Update prescription + billing + appointment atomically

When You Relax ACID for Scale

Shopping cart:        User adds item → eventual consistency acceptable
                      If cart shows stale data for 100ms → acceptable

Social media feed:    Feed may show slightly stale post counts
                      Not seeing a like for 500ms → acceptable

Product catalog:      Price change takes 200ms to propagate
                      Most users see old price briefly → acceptable

Recommendation engine: Personalized feed slightly stale → acceptable

ACID Across Microservices — The Hard Problem

Problem: ACID transactions work within ONE database
         Microservices have SEPARATE databases
         How do you do atomic operations across services?

Example:
  Place order: debit wallet (PaymentService DB) + create order (OrderService DB)
               These are TWO separate databases
               Standard ACID transactions don't span multiple databases

Solutions:
  1. Saga Pattern: series of local transactions with compensating transactions
     (OrderService creates order → PaymentService charges →
      if charge fails → OrderService cancels order via compensating transaction)

  2. Two-Phase Commit (2PC): distributed protocol for cross-DB atomicity
     (coordinator asks all participants to "prepare" → all confirm → "commit")
     Rarely used — complex + performance bottleneck

  3. Outbox Pattern: write to local DB + outbox table atomically
     Background worker reads outbox and publishes to other services

This is a critical system design topic — ACID limitations at scale drive
many architectural decisions in distributed systems.

---

How This Connects

• Transactions & Isolation Levels — the "I" in ACID unpacked: the four read anomalies and the levels that trade concurrency for safety.
• Concurrency Control - Locks & MVCC — how isolation is actually delivered: shared/exclusive locks, or multi-version snapshots.
• Relational Model - Tables, Keys, Relationships — referential integrity and CHECK constraints are the "C" the database enforces on every commit.
• Storage Engines - LSM-Tree vs B-Tree / WAL — atomicity and durability both ride on the write-ahead log: log first, fsync, then acknowledge.
• CAP Theorem / PACELC Theorem — ACID's strong consistency is exactly what a partitioned distributed system may have to give up for availability.
• Module 11 — Async & Event-Driven — Saga, 2PC, and the Outbox pattern are the answers to "ACID stops at one database, but my transaction spans services".

---

In the Interview

When to say it. Any payments / orders / inventory design, any "what happens if the server crashes here?", any "how do you stop two requests from double-spending?".

The ~20-second version:

"ACID makes transactions reliable. Atomicity is all-or-nothing — debit Alice, credit Bob; if we crash between them, the debit rolls back via the write-ahead log. Consistency means every commit leaves constraints satisfied — balance >= 0, foreign keys valid — with business rules enforced by the app on top. Isolation means concurrent transactions produce the same result as if they ran one at a time; it's tunable — Postgres defaults to Read Committed, MySQL InnoDB to Repeatable Read — and more isolation costs throughput. Durability means once COMMIT returns, the data is on disk (WAL fsynced) and survives a crash; in a cluster that extends to a quorum of replicas acknowledging. I treat ACID as non-negotiable for money and accept BASE / eventual consistency for things like like-counts or feeds."

Follow-ups to expect:

1. "Which property does a WAL give you?" — Both A and D: on restart the DB replays committed records and rolls back incomplete ones.
2. "Whose job is consistency — the DB's or the app's?" — Both. The DB enforces keys/uniqueness/CHECK; the app enforces cross-entity business rules ("reserve stock when the order is placed").
3. "ACID across microservices?" — You can't get a single ACID transaction across separate DBs. Use a Saga (local transactions + compensating actions), the Outbox pattern for reliable event publishing, or 2PC (rarely — it's a availability/latency bottleneck).
4. "ACID vs BASE?" — BASE = Basically Available, Soft state, Eventually consistent: trade immediate consistency for availability and throughput. Right for social/catalog data, wrong for a bank transfer.

Mistake to avoid: conflating ACID's "Consistency" with CAP's "Consistency". ACID-C is "constraints hold after each transaction"; CAP-C is "every read sees the latest write across all nodes". Different words, different guarantees.

---

Quick Revision — 6 Lines

1. Atomicity — all or nothing; partial transactions are rolled back via WAL/undo log
2. Consistency — DB moves between valid states; constraints enforced at DB + app level
3. Isolation — concurrent transactions don't interfere; 4 levels from Read Uncommitted to Serializable
4. Durability — committed data survives crashes; WAL written and fsynced before commit ACK
5. More isolation = fewer anomalies but lower throughput — choose level based on risk tolerance
6. ACID works within one DB; cross-service atomicity needs Saga, 2PC, or Outbox pattern