ACID 🧱 — The Foundation of Reliable Transactions

ACID describes four important properties of database transactions: Atomicity, Consistency, Isolation, and Durability.

flowchart LR
    T["Database Transaction"] --> A["Atomicity"]
    T --> C["Consistency"]
    T --> I["Isolation"]
    T --> D["Durability"]

1. Atomicity — All or Nothing

Atomicity means a transaction is treated as one unit. Either all required operations succeed, or the transaction is rolled back.

Detailed Example — Bank Transfer

Assume Account A has ₹10,000 and Account B has ₹5,000. We transfer ₹2,000 from A to B.

flowchart LR
    T["Transfer ₹2000"] --> D["Debit A ₹2000"]
    T --> C["Credit B ₹2000"]

    D --> S["COMMIT"]
    C --> S

    D -. "Failure" .-> R["ROLLBACK"]
    C -. "Failure" .-> R

If the debit succeeds but the credit fails, the transaction must roll back the debit. We should never end up with money disappearing from the system.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 2000
WHERE AccountId = 1;

UPDATE Accounts
SET Balance = Balance + 2000
WHERE AccountId = 2;

COMMIT;

If the second operation fails:

ROLLBACK;

Interview line: Atomicity means all operations happen, or none of them happen.

2. Consistency — Valid State to Valid State

Consistency means a successfully committed transaction takes the database from one valid state to another valid state while respecting defined constraints and rules.

Example — Account Balance

Suppose the system has a business rule that an account cannot have a negative balance.

flowchart LR
    B1["Valid State
Balance = ₹1000"]
    T["Transaction"]
    B2["Valid State
Balance = ₹500"]

    B1 --> T --> B2

A transaction attempting to withdraw ₹2,000 from an account with only ₹1,000 should fail rather than leave the database in an invalid state.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 2000
WHERE AccountId = 1
  AND Balance >= 2000;

-- Verify that the update succeeded

COMMIT;

Consistency is maintained through database constraints, transaction rules, application rules, and correct transaction logic.

3. Isolation — Concurrent Transactions Should Not Interfere Incorrectly

Isolation controls how concurrent transactions interact with each other. One transaction should not observe intermediate changes from another in ways that violate the selected isolation level.

sequenceDiagram
    participant A as Transaction A
    participant DB as Database
    participant B as Transaction B

    A->>DB: BEGIN
    A->>DB: Update Account
    B->>DB: BEGIN
    B->>DB: Read / Update Data
    DB-->>B: Result according to isolation level
    A->>DB: COMMIT
    B->>DB: Continue

Example — Two Users Buying the Last Product

Suppose inventory contains only 1 laptop. Two customers try to purchase it at almost the same time.

flowchart LR
    A["Customer A"] --> DB["Inventory"]
    B["Customer B"] --> DB

    DB --> I["Stock = 1"]

    I --> C["Concurrency Control"]
    C --> R["Only Valid Purchase Allowed"]

Without appropriate concurrency control, both transactions could incorrectly believe that the product is available.

Isolation levels determine how much one transaction can observe from concurrent transactions. This is why isolation levels are an important separate interview topic.

4. Durability — Committed Means Persisted

Once a transaction successfully commits, its changes should survive a subsequent application or database failure, subject to the database's durability guarantees.

sequenceDiagram
    participant A as Application
    participant DB as Database
    participant S as Storage

    A->>DB: COMMIT
    DB->>S: Persist Transaction
    S-->>DB: Stored
    DB-->>A: Commit Success

    Note over A,DB: Application crashes
    Note over S: Committed data remains persisted

Durability is supported through mechanisms such as transaction logs and persistent storage.

5. Complete ACID Example — Order Placement

Consider an e-commerce order where the system needs to create an order and reduce inventory.

flowchart LR
    T["Order Transaction"]

    T --> O["Create Order"]
    T --> I["Reduce Inventory"]
    T --> P["Create Order Item"]

    O --> C["COMMIT"]
    I --> C
    P --> C

    O -. "Failure" .-> R["ROLLBACK"]
    I -. "Failure" .-> R
    P -. "Failure" .-> R

6. ACID in SQL Server

A typical transaction in SQL Server can look like this:

BEGIN TRANSACTION;

BEGIN TRY

    UPDATE Accounts
    SET Balance = Balance - 2000
    WHERE AccountId = 1
      AND Balance >= 2000;

    IF @@ROWCOUNT = 0
        THROW 50001, 'Insufficient balance', 1;

    UPDATE Accounts
    SET Balance = Balance + 2000
    WHERE AccountId = 2;

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;

END CATCH;

This gives the application an explicit transaction boundary and rollback path when an operation fails.

7. ACID vs BASE

This is not a simple rule that SQL means ACID and NoSQL means BASE. Database capabilities vary, and many modern NoSQL databases provide transactional and strong-consistency features.

8. ACID and Microservices

ACID transactions are straightforward when operations belong to the same transactional database.

flowchart LR
    A["Application"] --> DB["Single Database"]

    DB --> O["Order"]
    DB --> I["Inventory"]
    DB --> P["Payment Record"]

    O --> T["One Transaction"]
    I --> T
    P --> T

In microservices, different services may own different databases. A single local database transaction cannot automatically provide atomicity across all those databases.

flowchart LR
    O["Order Service"] --> DB1["Order DB"]
    P["Payment Service"] --> DB2["Payment DB"]
    I["Inventory Service"] --> DB3["Inventory DB"]

    DB1 -. "Separate Transaction" .-> X["Distributed Workflow"]
    DB2 -. "Separate Transaction" .-> X
    DB3 -. "Separate Transaction" .-> X

Such workflows may use patterns such as Saga and compensating actions instead of trying to make every service participate in one large database transaction.

9. ACID — Easy Interview Explanation

flowchart LR
    T["Transaction"]

    T --> A["A — Atomicity
All or Nothing"]
    T --> C["C — Consistency
Valid State"]
    T --> I["I — Isolation
Safe Concurrency"]
    T --> D["D — Durability
Committed Data Persists"]

• A — Atomicity: all operations succeed or roll back.
• C — Consistency: database rules remain valid.
• I — Isolation: concurrent transactions are controlled.
• D — Durability: committed changes survive failures according to the database's guarantees.

Important Interview Questions

• What does ACID stand for?
• Explain Atomicity with a real-world example.
• What is the difference between Atomicity and Consistency?
• What does Isolation mean?
• What does Durability mean?
• How does a database provide durability?
• How does ACID apply to a bank transfer?
• Can you have ACID transactions across microservices?
• What is the difference between ACID and BASE?
• Why are isolation levels important?

Key Takeaways

• Atomicity → All or Nothing.
• Consistency → Valid State to Valid State.
• Isolation → Control concurrent transactions.
• Durability → Committed data survives failures according to database guarantees.
• ACID is especially important for transactional workloads such as payments, banking, orders, and inventory.
• In microservices, cross-database workflows require different transaction patterns.

Conclusion

For interviews, remember one simple example: Bank Transfer = Debit + Credit. ACID ensures the transaction is handled as a reliable unit: All or Nothing → Valid State → Safe Concurrency → Durable Commit.