💡 What is ACID

When working with databases, data integrity is everything. Imagine losing money in a bank transfer because your database didn’t handle operations correctly… scary, right?

This is where ACID transactions come in. ACID stands for:

• A: Atomicity
• C: Consistency
• I: Isolation
• D: Durability

These four properties ensure your database behaves reliably even in complex scenarios.

🔑 Why ACID Matters: A Simple Example

Imagine a user wants to transfer $100 from their checking account to their savings account. Without ACID:

1. $100 could be deducted from checking.
2. A crash happens before adding $100 to savings.

💥 Money disappears.

With ACID, the transaction either succeeds completely or does nothing.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

If anything fails in between, you can ROLLBACK, and neither account is affected.

🛠 The Four Properties of ACID

Let’s break them down with practical examples.

1️⃣ Atomicity – “All or Nothing”

Atomicity ensures a transaction either completes fully or not at all.

Example: Transferring money (same as above). If the second step fails, the first one is undone.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

-- Imagine this fails
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

ROLLBACK; -- No partial updates

✅ No money disappears under my watch, says Atomicity.

2️⃣ Consistency – “Keep It Valid”

Consistency ensures a transaction respects all rules and constraints.

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

-- Will fail if balance < 0
INSERT INTO transfers (from_account, to_account, amount)
VALUES (1, 2, 500);

COMMIT;

💡 Consistency prevents invalid states like negative balances or broken foreign keys.

3️⃣ Isolation – “Mind Your Own Business”

Isolation ensures that concurrent transactions don’t interfere with each other. Before we explore the isolation levels, let’s understand the main phenomena that can occur in concurrent transactions:

📖 Vocabulary

🔹 Dirty Read

• A Dirty Read occurs when one transaction reads data that has been modified by another concurrent transaction, but that modification has not yet been committed (i.e., it is uncommitted data).
• Risk: The reading transaction risks acting upon data that might later be completely undone or rolled back by the modifying transaction. If the modifying transaction fails and rolls back, the data the first transaction read is considered “dirty”—it never actually existed in a consistent, committed state. This leads to data inconsistency and application errors

Dirty Reads are only possible in the lowest isolation level: Read Uncommitted.

🔹 Non-Repeatable Read

• A Non-Repeatable Read occurs when a single transaction attempts to read the same row multiple times and finds that the data has changed (been updated or deleted) in between those reads due to a second, concurrent transaction that has successfully committed its changes.
• Risk: The core problem is inconsistent data within a single transaction. If a transaction needs to make a logical decision based on a value read earlier, and that value silently changes, the transaction’s outcome may be incorrect or illogical, violating the expected Consistency of the overall application logic.

🔹 Phantom Read

• A Phantom Read specifically refers to the case where a transaction re-runs a query (like a SELECT ... WHERE condition) and new rows that satisfy the condition have been inserted by a concurrent, committed transaction. The set of rows changes (a “phantom” row appears).
• Risk: Your query results change because of phantom rows appearing.

Let’🐘 s go thru each isolation levl

🔹 Read Uncommitted (Dirty Reads)

• Can see uncommitted changes
• For PostgreSQL, setting the isolation level to READ UNCOMMITTED has no practical difference from setting it to READ COMMITTED. This design choice eliminates the worst concurrency risk—the Dirty Read—even at the lowest nominal isolation level.

🔹 Read Committed (default)

• Each query sees only committed data.
• Non-repeatable reads and phantoms are possible.

-- Transaction 1
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1; -- sees 100

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
COMMIT;

-- Transaction 1 queries again
SELECT balance FROM accounts WHERE account_id = 1; -- now sees 150
COMMIT;

🔹 Repeatable Read

• Prevents non-repeatable reads.
• All queries within a transaction see the same snapshot of data.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE account_id = 1; -- sees 100

-- Transaction 2 updates balance to 150 and commits

SELECT balance FROM accounts WHERE account_id = 1; -- still sees 100
COMMIT;

🔹 Serializable

• Strongest isolation.
• Transactions behave as if executed serially, one after another.
• Prevents dirty reads, non-repeatable reads, and phantoms.

Imagine we want to ensure that two concurrent withdrawals never overdraw an account:

-- Transaction 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT balance FROM accounts WHERE account_id = 1; -- sees 100

-- Transaction 2 (runs at the same time)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT balance FROM accounts WHERE account_id = 1; -- also sees 100

-- Transaction 1 withdraws 80
UPDATE accounts SET balance = balance - 80 WHERE account_id = 1;

COMMIT;

-- Transaction 2 tries to withdraw 50
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;

COMMIT; -- ❌ PostgreSQL will raise a serialization error here

💡 Explanation:

• Both transactions read the same initial balance.
• If PostgreSQL allowed both updates, the account would go negative (100 - 80 - 50 = -30).
• With Serializable isolation, the second transaction fails with a serialization error and must be retried.
• This ensures the database behaves as if the transactions ran one after another, keeping data consistent.

4️⃣ Durability – “Safe and Sound”

Durability guarantees that once a transaction commits, it’s permanent.

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

💾 Even if the server crashes immediately, your data is safe on disk.

🎯 Conclusion

ACID is essential for applications where data integrity cannot be compromised, like banking, e-commerce, or inventory systems. It let’s you :

• Perform complex multi-step operations safely
• Handle concurrency with different isolation levels
• Ensure your data survives crashes