<< back to Guides

🧪 Deep Dive into ACID: Reliable Transactions in Databases

ACID is a set of properties that guarantee reliable processing of transactions in a database system. It stands for:

These properties are the foundation of relational databases like MySQL, PostgreSQL, and also apply (with variations) to some NoSQL systems.

🧩 What is a Transaction?

A transaction is a sequence of operations performed as a single logical unit of work. All operations within a transaction must complete successfully, or none should take effect.

🔹 A — Atomicity

All or nothing.

If a transaction involves multiple steps, either all succeed, or the whole transaction is rolled back.

Example

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If the second update fails, the first is rolled back automatically.

🔹 C — Consistency

Transactions must move the database from one valid state to another, preserving integrity constraints.

Examples of constraints:

Example

-- Withdraw that causes balance to go negative (invalid state)
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

If there's a rule preventing negative balances, the DBMS will reject the transaction, preserving consistency.

🔹 I — Isolation

Concurrent transactions do not interfere with each other.

Why Isolation Matters

Imagine two users trying to transfer money at the same time. Isolation ensures data remains correct and conflict-free.

Isolation Levels (SQL Standard)

Level Dirty Read Non-repeatable Read Phantom Read
Read Uncommitted ✅ ✅ ✅
Read Committed ❌ ✅ ✅
Repeatable Read ❌ ❌ ✅
Serializable ❌ ❌ ❌

Each level trades performance vs correctness.

Example in PostgreSQL

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Note: Some databases (like MySQL with InnoDB) default to Repeatable Read.

🔹 D — Durability

Once a transaction is committed, it is permanently stored, even in the event of power loss or crash.

How Databases Achieve Durability

Example

If your app crashes after a commit, the data is still saved.

COMMIT;

🧪 Practical Example

START TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE id = 42;
INSERT INTO orders (product_id, user_id) VALUES (42, 5);

COMMIT;

If any part fails, the transaction won't apply—ensuring atomicity and consistency.

🛠️ ACID in Distributed Systems

While traditional RDBMSs implement ACID on a single node, distributed systems often face trade-offs due to:

Examples:

DBMS ACID Support
PostgreSQL Full ACID
MySQL (InnoDB) Full ACID
MongoDB ACID (since 4.0, multi-doc)
Cassandra Tunable Consistency, not full ACID
CockroachDB Distributed ACID

❌ Common Pitfalls

✅ Summary Table

Property What It Ensures Failure Without It
Atomicity All steps succeed or none do Partial updates, inconsistent state
Consistency Integrity constraints remain enforced Invalid data (e.g., foreign key errors)
Isolation No conflict between concurrent txs Race conditions, dirty reads
Durability Data is safe after commit Lost transactions on crash

📚 Resources

<< back to Guides