In the world of database management systems (DBMS), ensuring data integrity is paramount. When multiple users access and modify data simultaneously, or when system failures occur, we need a set of guarantees to ensure our data remains accurate and consistent. This is where the ACID properties come into play.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four principles form the foundation of reliable transaction processing in relational databases like PostgreSQL, MySQL, and SQL Server.

What is a Transaction?

Before diving into ACID, let’s define a transaction. A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database.

For example, transferring money from Account A to Account B involves two operations:

1. Deduct money from Account A.
2. Add money to Account B.

Even though these are two separate steps, logically, they are one “transaction”. If one fails, the whole process should fail.

1. Atomicity: All or Nothing

Atomicity guarantees that a transaction is treated as a single, indivisible unit. Either all of its operations are executed successfully, or none of them are. There is no “partial” completion.

The Scenario

Imagine you are transferring from Alice to Bob.

1. Step 1: Debit from Alice’s account.
2. Step 2: Credit to Bob’s account.

If the system crashes after Step 1 but before Step 2, Alice loses money, and Bob receives nothing. The database is now in an inconsistent state.

The Solution

Atomicity ensures that if Step 2 fails, Step 1 is rolled back (undone). The database reverts to its state before the transaction started.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 'Alice';
-- System crash happens here? No problem. The changes aren't committed yet.
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'Bob';

COMMIT; -- Only now are the changes permanent.

2. Consistency: Valid State to Valid State

Consistency ensures that a transaction brings the database from one valid state to another valid state, maintaining all defined rules, constraints, and cascades.

The Scenario

Your database has a rule: balance cannot be negative. Alice has in her account. She tries to transfer to Bob.

The Solution

If the transaction executes, Alice’s balance would become -, violating the database constraint. The Consistency property ensures that this transaction is aborted and an error is raised, preserving the integrity of the data.

Note: Consistency in ACID is different from Consistency in the CAP theorem. In ACID, it refers to data integrity rules. In CAP, it refers to all nodes seeing the same data at the same time.

3. Isolation: Concurrency Control

Isolation ensures that concurrent transactions occur as if they were executed sequentially. One transaction should not be aware of the intermediate state of another transaction.

The Scenario

Two transactions happen at the exact same time:

1. Transaction A: Reads Alice’s balance ().
2. Transaction B: Reads Alice’s balance ().
3. Transaction A: Adds (New balance: ) and saves.
4. Transaction B: Subtracts (New balance: ) and saves.

If isolated poorly, Transaction B might overwrite Transaction A’s work, resulting in a final balance of instead of the correct ( + - ). This is known as a Lost Update.

The Solution

Databases use locking and isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) to manage this.

• Serializable (Highest Level): Transactions are run completely sequentially. Safe but slow.
• Read Committed (Common Default): A transaction only sees changes committed by other transactions.

4. Durability: Surviving Failures

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage, crash).

The Scenario

You buy a concert ticket online. The system says “Purchase Successful”. One second later, the server center loses power.

The Solution

Durability ensures that the record of your purchase is written to non-volatile storage (hard drive/SSD) before the system confirms the success. Databases use techniques like Write-Ahead Logging (WAL) to ensure that even if the database crashes immediately after a commit, it can reconstruct the data upon restart.

Summary

Conclusion

Understanding ACID properties is crucial for backend engineers and system architects. While NoSQL databases (like MongoDB or Cassandra) often trade some ACID properties for scalability (following the BASE model), relational databases remain the gold standard for financial systems, inventory management, and any application where data integrity is non-negotiable.