ACID transactions

ACID is a set of guarantees that make database transactions reliable. A transaction is a group of operations that should behave as one unit.

The simplest way to see it is a transfer of ₹100 from Alice to Bob.

Before          After
Alice: ₹500     Alice: ₹400
Bob:   ₹300     Bob:   ₹400

The database has to make sure this always happens correctly, no matter what goes wrong in the middle. The four letters, Atomicity, Consistency, Isolation, and Durability, are the promises that make that true.

Atomicity

All or nothing. If any step fails, everything is rolled back.

1. Deduct ₹100 from Alice   ok
2. Server crashes           fail
3. Bob never receives ₹100

Without atomicity, you'd be left with:

Alice: ₹400
Bob:   ₹300

₹100 disappeared. With atomicity, the failed transaction is undone and nothing changes:

Alice: ₹500
Bob:   ₹300

Consistency

The database always stays in a valid state, obeying the rules you declared.

Rule: an account balance cannot go negative.

Alice balance = ₹50
Transfer      = ₹100

The database rejects the transfer instead of writing an invalid value. The data stays valid.

Isolation

Multiple transactions running at once don't interfere with each other.

Two ATMs withdraw from the same account at the same time.

Balance = ₹1000
ATM 1 withdraws ₹700
ATM 2 withdraws ₹700

Without isolation, both read ₹1000, both pass the check, and you get:

Balance = -₹400

With isolation, one completes first:

ATM 1 completes  ->  Balance = ₹300
ATM 2 now sees ₹300  ->  withdrawal fails

Durability

Once a transaction is committed, the data is permanent.

Transfer committed
Power goes off

When the database restarts, the committed transfer is still there:

Alice: ₹400
Bob:   ₹400

The full transaction

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

COMMIT;

If everything succeeds, the database COMMITs. If anything fails, it ROLLBACKs and returns to its previous state.

Why banks use ACID

A real money transfer isn't one write. It's several operations that have to move together:

1. Check balance
2. Deduct money
3. Credit receiver
4. Record the transaction
5. Send notification

If step 3 fails after step 2, the database rolls back the whole thing, so money is never lost or duplicated. That's why banking and payment systems run on databases like PostgreSQL, MySQL (InnoDB), and Oracle.

Can NoSQL do ACID?

Yes, but it depends on the database. There are two cases.

The NoSQL database supports ACID

Many modern NoSQL databases provide ACID transactions:

• MongoDB: multi-document ACID transactions
• Couchbase: ACID transactions
• Azure Cosmos DB: ACID within a logical partition

In MongoDB it looks a lot like SQL, and needs no extra application logic:

session.startTransaction();

accounts.updateOne(...); // deduct
accounts.updateOne(...); // credit

session.commitTransaction();

The NoSQL database doesn't support full ACID

Some stores only go part of the way:

• Cassandra: limited transaction support
• DynamoDB: has transactions, but with different trade-offs
• Redis: atomic commands, but not full relational-style transactions

Here you implement consistency in the application yourself:

1. Deduct money
2. Credit receiver
3. If step 2 fails:
     - retry
     - or compensate by refunding

This is the Saga pattern, or compensating transactions, in distributed systems.

Why not always use NoSQL?

Even when you can implement ACID yourself, it adds real complexity.

With SQL, the database guarantees correctness:

BEGIN;
  -- deduct
  -- credit
  -- insert transaction record
COMMIT;

With a NoSQL store that lacks the right transaction support, you own every failure path:

Deduct
  -> service crashes
  -> recover
  -> retry
  -> maybe refund
  -> handle duplicates
  -> handle timeouts

Now you need idempotency, retries, compensation logic, and failure recovery, all by hand.

What large companies do

Most use both:

• SQL for payments, billing, orders, inventory
• NoSQL for chat, feeds, logs, analytics, caching, user sessions

The call is usually driven by the data model and access patterns, not simply whether ACID is possible.

Rule of thumb: if your application logic is recreating features a relational database already provides well, SQL is usually the simpler and safer choice. Reach for NoSQL when its flexibility, scale, or data model gives you a clear advantage. See SQL vs NoSQL and when to use for that side of the decision.