Understanding ACID in Databases: Ensuring Reliable Transactions

Atomicity

All or nothing. A transaction is a group of operations. Either all of them succeed, or none of them do.

Example

Initial balances:

Begin
    update Alice account 1000-500
    update Bob account 500+500
Commit

✅ Both succeed → Fine.

But if Bob’s update fails:

❌ 500 is LOST → breaks Atomicity.

Isolation

Transactions should not interfere with each other. When multiple transactions run at the same time, they should appear as if they happened in order.

1. Dirty Read

Reading uncommitted data.

If DB crashes before T1 commits → Alice=700 stays, Bob=500 → invalid.

2. Non-Repeatable Read

Same query gives different results in one transaction.

Transaction-1:
Begin
   SELECT balance FROM Alice → 1000
   ... (meanwhile T2 updates Alice)
   SELECT balance FROM Alice → 800
Commit

Result changed inside the same transaction → Non-repeatable.

3. Phantom Read

Rows appear/disappear between queries.

Transaction-1:
Begin
   SELECT * FROM Accounts WHERE balance > 500
   → returns [Alice]
   ... (T2 inserts Charlie with balance 600)
   SELECT * FROM Accounts WHERE balance > 500
   → returns [Alice, Charlie]
Commit

❌ A “phantom” row appeared.

Consistency

Data must always follow rules (constraints).

Examples of rules:

• Balance can’t be negative
• Account name can’t be NULL
• Primary key must be unique

Example

This breaks the rule (balance ≥ 0). Consistency ensures such invalid states are never saved.

Durability

Once committed, data is permanent. Even if the system crashes, committed data survives.

Summary

• Atomicity → All or nothing
• Consistency → Data follows rules (no negatives, no NULLs, etc.)
• Isolation → No interference
• Durability → Once saved, always safe

That’s ACID — the foundation of reliable databases.