ACID in Simple Terms

When working with databases, you’ll often hear the term ACID. It’s a set of four key principles that ensure data stays reliable and consistent, even when multiple things happen at once or something goes wrong.

What is ACID?

• Atomicity
• Consistency
• Isolation
• Durability

Each of these properties describes how a database should behave during a transaction.

A transaction is simply a group of database operations that should be treated as a single unit. For example, transferring money between two bank accounts: subtracting from one and adding to another. Both steps must succeed together—or not at all.

In SQL, a transaction typically looks like this:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- subtract from Alice
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- add to Bob

COMMIT;

If something fails in the middle, we can use:

ROLLBACK;

This cancels all changes and restores the database to its previous state.

Atomicity – "All or Nothing"

A transaction is atomic if it either completes fully or doesn’t happen at all.

💡 Analogy (Bank Transfer): Imagine moving $100 from your wallet to your friend’s wallet. If you drop the cash halfway and neither of you ends up with the money, that’s a failed transaction. Atomicity ensures that either both wallets are updated, or nothing changes.

Atomicity Example:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- system crashes here!

COMMIT;

Without atomicity, Alice would lose $100 without Bob receiving it. With atomicity, the database rolls everything back so neither account is changed.

Consistency – "Valid Before and After"

The database should always remain in a valid state before and after a transaction.

💡 Analogy (Online Store Rule): A store might have a rule: stock can’t go below zero. If you try to buy more items than are available, the system won’t let it happen. That’s consistency enforcing business rules.

Consistency Example: 

Suppose we enforce the rule that balances can’t go below zero:

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

If a transaction attempts to withdraw more than the account balance allows, the database rejects it. That’s consistency at work.

Isolation – "No Interference"

Transactions shouldn’t mess with each other, even if they run at the same time.

💡 Analogy (Buying the Last Ticket): Two people try to buy the last concert ticket at once. Without isolation, both could succeed, and the system would sell one ticket twice. With isolation, one purchase is processed, and the other is rejected.

Isolation Example: 

Two people attempt to purchase the last concert ticket simultaneously.

BEGIN;
UPDATE tickets SET available = available - 1 WHERE id = 10;
COMMIT;

If both transactions run together, isolation ensures that only one succeeds. The other will wait or fail, so we don’t oversell tickets.

Durability – "It Stays Saved"

Once a transaction is committed, the result is permanent, even if the system crashes.

💡 Analogy (Order Confirmation): You place an order online and see “Payment Successful.” Even if the store’s server restarts right after, your order is safe in the system. That’s durability.

Durability example

For example, once this transaction is committed:

BEGIN;

UPDATE orders SET status = 'PAID' WHERE id = 42;

COMMIT;

The database guarantees the "PAID" status is safely stored on disk. Even if the server restarts right after, the change won’t disappear.