DEV Community

Mandar Gokhale
Mandar Gokhale

Posted on • Edited on

Rediscovering ACID – The Foundation of Reliable Databases

#sql #database #postgres #mysql

As engineers, we use transactions all the time — BEGIN, COMMIT, and maybe a rollback if things go south. But recently, I realized I hadn’t truly internalized what makes those transactions reliable.

That curiosity led me to revisit the ACID properties — a foundational concept we all learn early on but rarely pause to deeply understand. What started as a quick refresher turned into a deep dive into atomicity, consistency, isolation, and durability, and how real databases like PostgreSQL and MySQL implement them.

Along the way, I discovered that one core mechanism — the Write-Ahead Log (WAL) — is key to both Atomicity and Durability, playing a foundational role in keeping transactions safe and recoverable.

In this first part of a multi-part series, I’ll walk through what each property means, why it matters, and where it shows up in real-world systems. We'll ground the theory with SQL examples you can run yourself.

🔹 What is ACID?

ACID stands for:

  • Atomicity – All or nothing
  • Consistency – The database moves from one valid state to another
  • Isolation – Transactions don’t interfere with each other
  • Durability – Once committed, data is forever

Let’s look at each one with practical examples.

🧱 Atomicity – All or Nothing

A transaction must either complete entirely or not at all.

Imagine transferring ₹100 from Alice to Bob:

Debit Alice. Credit Bob. If only one of those happens, the system is in a bad state.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
-- crash happens here
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Since COMMIT never happened, PostgreSQL will rollback the entire transaction automatically.

📐 Consistency – Enforcing Rules

A database should always transition from one valid state to another. That includes enforcing:

  • Constraints
  • Data types
  • Foreign keys
  • Application invariants

✅ Example 1: Check constraint 

ALTER TABLE accounts ADD CONSTRAINT check_balance CHECK (balance >= 0);
Enter fullscreen mode Exit fullscreen mode 
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE name = 'Alice';
COMMIT; -- ❌ rejected if Alice only has ₹1000
Enter fullscreen mode Exit fullscreen mode

✅ Example 2: Foreign key constraint 

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id)
);

-- ❌ This fails
INSERT INTO orders (customer_id) VALUES (999);
Enter fullscreen mode Exit fullscreen mode

PostgreSQL blocks the insertion because customer 999 doesn’t exist.

ℹ️ Note:

The database does not define consistency on its own — it simply enforces the rules you define.
If your schema or application logic doesn't include constraints, the database won't protect them automatically.

🔒 Isolation – Transactions Don’t Bump Into Each Other

This is the most subtle — and arguably the most important — part of ACID. Isolation means each transaction should act like it’s the only one running, even in a highly concurrent system.

Databases offer multiple isolation levels to balance correctness and performance:

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable

In upcoming part, we’ll go deep into isolation levels, MVCC, and real-world concurrency bugs.

💾 Durability – Survives Crashes

Once you COMMIT, it’s permanent — even if the database crashes 1ms later.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;
Enter fullscreen mode Exit fullscreen mode

How is that possible? That’s what we’ll explore in detail in Part 2 — where we discuss WAL (Write-Ahead Logging).

🧠 Coming Up Next

In the next post, we’ll dive into how databases like PostgreSQL use the Write-Ahead Log (WAL) — the invisible hero behind safe commits and crash recovery — to guarantee atomicity, ensuring that a transaction either completes fully or not at all.

✅ TL;DR Cheatsheet

Property What it Means Example Scenario
Atomicity All-or-nothing transactions Debit and credit in money transfer
Consistency Rules must be upheld Check constraints, foreign key enforcement
Isolation Concurrent transactions don't clash Read same row, don't see other's changes
Durability Committed = permanent Crash-safe after COMMIT

Thanks for reading! If you enjoyed this, stay tuned for the next post in the series — and feel free to share your own “aha” moments around ACID in the comments!

Top comments (0)