⚗️ ACID Properties

The Bank Transfer Analogy

You're transferring money from Account A to Account B:

1. Deduct the amount from Account A
2. Add the amount to Account B

What if the system crashes between step 1 and 2? You can end up in an inconsistent state.

ACID properties are the goals that help prevent this kind of inconsistency.

The Four ACID Properties

A - Atomicity

"All or nothing"

A transaction either completes entirely or doesn't happen at all. No partial changes.

Example

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
    UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

If either UPDATE fails, BOTH are rolled back. Money can't disappear.

Without Atomicity

1. Deduct money from A ✓
2. [CRASH!]
3. Add money to B ✗
Result: transfer is incomplete.

C - Consistency

"The database aims to stay in a valid state"

All defined rules (constraints, foreign keys, etc.) are enforced at transaction boundaries (commit/rollback).

Note: in ACID, “Consistency” is about preserving invariants. Some invariants are enforced by the database (constraints), while others are enforced by application logic.

Example

-- Constraint: balance >= 0
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
    -- Account A only has $500
COMMIT;
-- Transaction FAILS - would violate constraint

Consistency Rules

• Foreign keys should reference existing records
• Unique constraints remain unique
• Check constraints stay valid
• Data types are correct

I - Isolation

"Transactions don't see each other's uncommitted changes"

Isolation is about how concurrent transactions interact. Stronger isolation makes concurrent execution behave more like some serial (one-at-a-time) order.

The Problem Without Isolation

Initial: Account has $100

Transaction T1:                Transaction T2:
Read balance: $100
                               Read balance: $100
                               Withdraw $50
                               Balance now: $50
Add $20
Balance now: $120 (wrong!)
COMMIT                         COMMIT

Expected: $70 ($100 - $50 + $20)
Got: $50 (T1 overwrote T2's change)

Isolation Levels

Higher isolation = safer but slower.

Note: exact behavior varies by database (and some use snapshot isolation / MVCC), so treat this table as intuition.

D - Durability

"Once committed, it stays committed"

Even if power fails, committed transactions are saved.

How It Works

1. Transaction commits
2. Changes written to disk (WAL - Write-Ahead Log)
3. Power failure
4. Database restarts
5. Recovers from WAL - transaction preserved

Real-World Examples

1. E-commerce Order

BEGIN TRANSACTION;
    -- Reduce inventory
    UPDATE products SET stock = stock - 1 WHERE id = 123;
    -- Create order
    INSERT INTO orders (product_id, user_id) VALUES (123, 456);
    -- Charge payment
    INSERT INTO payments (order_id, amount) VALUES (LAST_INSERT_ID(), 50);
COMMIT;

All three must succeed, or none happen.

2. Flight Booking

BEGIN TRANSACTION;
    -- Reserve seat
    UPDATE seats SET available = false WHERE flight = 100 AND seat = '12A';
    -- Book ticket
    INSERT INTO bookings (passenger, flight, seat) VALUES ('Alice', 100, '12A');
COMMIT;

3. Banking System

Every transfer is a transaction. Every deposit. Every withdrawal. ACID is meant to reduce the chance of partial updates and inconsistent state.

Common Mistakes

Not Using Transactions

# Bad: Two separate operations
db.execute("UPDATE balance SET amount = amount - 100 WHERE user = 'A'")
# Crash here = lost money!
db.execute("UPDATE balance SET amount = amount + 100 WHERE user = 'B'")

# Good: One transaction
with db.transaction():
    db.execute("UPDATE balance SET amount = amount - 100 WHERE user = 'A'")
    db.execute("UPDATE balance SET amount = amount + 100 WHERE user = 'B'")

Ignoring Isolation Levels

Default isn't necessarily enough. For financial operations, consider Serializable.

ACID vs BASE

Note: "SQL vs NoSQL" is not a perfect split here. Many modern databases offer a mix of consistency and transaction features with trade-offs.

FAQ

Q: Do all databases support ACID?

Many SQL databases provide strong transaction guarantees. Many NoSQL databases started with weaker transaction guarantees, but plenty now support transactions in some form. Be sure to check the specific database and configuration.

Q: What's the performance cost of ACID?

Higher isolation and durability have overhead. Choose the right level for your use case.

Q: Can I have partial ACID?

Yes. You can configure isolation levels and durability guarantees.

Q: How does atomicity work across microservices?

It doesn't directly. You need Saga patterns or distributed transactions.

Q: What happens during a timeout?

Often the database aborts the transaction and rolls back its uncommitted work, but details depend on the database/client configuration.

Q: Is ACID enough for distributed systems?

No. Distributed systems face additional challenges (CAP theorem).

Summary

ACID properties describe goals for reliable database transactions.

Key Points:

• Atomicity: All or nothing
• Consistency: Rules stay valid
• Isolation: Transactions don't interfere
• Durability: Committed = permanent
• Essential for financial and critical applications