ACID Properties in DBMS (Deep Dive)

Interview Definition:

"ACID properties are simply a set of rules a database follows to make sure your data stays safe and accurate, even if the power goes out, the system crashes, or many people use it at once."

A transaction is a sequence of database operations treated as a single logical unit of work.

1. Atomicity (The "All or Nothing" Rule)

Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely or fails completely. If any statement within a transaction fails, the entire transaction is rolled back.

The Problem it Solves:

Imagine a bank transfer of ₹10,000 from Account A to Account B.

1. Deduct ₹10,000 from A.
2. Add ₹10,000 to B. If the database crashes after step 1 but before step 2, the money vanishes. Atomicity prevents this.

ASCII Visualization:

How Databases Implement Atomicity:

They use a mechanism called Rollback Segments or Undo Logs. Before changing data, the old data is written to the undo log. If a rollback is needed, the database uses this log to restore the original state.

2. Consistency

Consistency ensures that a transaction can only bring the database from one valid state to another. The data must adhere to all defined rules, including constraints, cascades, triggers, and combinations thereof.

The Problem it Solves:

If a column balance has a CHECK (balance >= 0) constraint, a transaction attempting to deduct more money than is available will violate consistency and be rolled back.

Note: Atomicity is about the "all or nothing" execution, while Consistency is about the "correctness" of the data based on business rules defined in the schema.

Example in a Payment System:

• Foreign Key Consistency: An order_id in the payments table must exist in the orders table.
• Application-Level Consistency: Total credits minus total debits across the entire system must equal zero (double-entry bookkeeping).

3. Isolation

Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. The intermediate states of a transaction are invisible to other transactions.

The Problem it Solves:

Two users transferring money simultaneously shouldn't read stale data or step on each other's toes.

ASCII Visualization of Poor Isolation (Lost Update):

How Databases Implement Isolation:

• Locking: (Pessimistic) Locking rows or tables so others wait.
• MVCC (Multi-Version Concurrency Control): (Optimistic) Keeping multiple versions of a row. Readers don't block writers, writers don't block readers. Each transaction sees a "snapshot" of data.

4. Durability

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 or crash).

The Problem it Solves:

You receive a "Payment Successful" message. Two seconds later, the database server loses power. When it restarts, your payment must still be there.

How Databases Implement Durability: Write-Ahead Logging (WAL)

Modifying data directly on the hard drive (disk pages) is slow because it requires random I/O. Instead, databases use a sequential log.

1. Transaction makes changes in RAM (Buffer Pool).
2. When the user types COMMIT, the database writes a record of this change to the Write-Ahead Log (WAL) on the disk. This is a sequential write (very fast).
3. The database tells the user "Success!".
4. Later, asynchronously, the database flushes the actual modified data pages from RAM to the permanent table files on disk.

If power fails before step 4, the database reads the WAL upon reboot and applies the changes to the table data.