Rediscovering ACID – The Foundation of Reliable Databases

[Lomanu4]

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;

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);

BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE name = 'Alice';
COMMIT; -- rejected if Alice only has ₹1000
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);

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;

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!

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.