ACID Properties

ACID Properties in Relational Databases

ACID is a set of four core properties that guarantee database transactions are processed reliably, maintaining data integrity even amidst hardware failures, software crashes, or high concurrency.

1. Atomicity ("All or Nothing")

A transaction is atomic: either all operations succeed, or all operations are rolled back as if nothing ever happened. There is no partial state.

• Real-World Scenario: In a bank transfer of $500 from Account A to Account B, if the deduction from Account A succeeds but crediting Account B fails, the debit is completely rolled back.
• Mechanism: Managed by undo logs and transaction rollback logs.

2. Consistency ("Valid State to Valid State")

A transaction must bring the database from one valid state to another, satisfying all declared schema constraints, including:

• Primary and Foreign Key relationships (referential integrity).
• CHECK constraints, NOT NULL limits, and column data type rules.
• Database triggers and cascading rules.

3. Isolation ("Concurrent Independence")

Determines how and when changes made by one operation become visible to other concurrent operations. Controlled via four standard isolation levels:

• Read Uncommitted: Allows dirty reads; lowest isolation, highest concurrency.
• Read Committed: Default in PostgreSQL; prevents dirty reads.
• Repeatable Read: Default in MySQL InnoDB; prevents non-repeatable reads.
• Serializable: Highest isolation level; transactions execute as if run serially.

4. Durability ("Permanent Persistence")

Once a transaction commits, its modifications are permanently recorded on non-volatile storage and will survive subsequent power loss, crashes, or system restarts.

• Mechanism: Achieved via Write-Ahead Logging (WAL), where changes are written sequentially to a log on disk before being applied to the actual data pages.

Summary Matrix

SQL Transaction Example

BEGIN;

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

-- Commits atomically and durably
COMMIT;