---

🟢 Junior Level

ACID is the foundational acronym describing four core requirements of a transaction management system in a Relational Database Management System (RDBMS). These guarantees ensure data integrity, reliability, and predictable processing:

• A — Atomicity: “All or nothing.” A transaction cannot partially execute. If any single operation within a transaction fails, all preceding mutations are rolled back (ROLLBACK), restoring the database to its pristine pre-transaction state.
• C — Consistency: A transaction transitions the database from one valid (consistent) state to another valid state. All schema constraints (PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL, UNIQUE) and domain invariants must be strictly satisfied upon completion.
• I — Isolation: Simultaneously executing transactions must not interfere with one another. Each transaction operates under the illusion that it is the sole consumer executing against the database.
• D — Durability: Once a transaction is formally committed (COMMIT), its changes are guaranteed to persist in non-volatile storage and will never be lost, even during sudden power failure, OS crashes, or hardware reboots.

Practical Example: Banking Fund Transfer

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class BankTransferService {

    private final AccountRepository accountRepository;

    public BankTransferService(AccountRepository accountRepository) {
        this.accountRepository = accountRepository;
    }

    // Atomic transaction: both accounts update, or neither updates
    @Transactional
    public void transferMoney(Long fromAccountId, Long toAccountId, Double amount) {
        // 1. Debit from Account A
        accountRepository.withdraw(fromAccountId, amount);

        // Simulate an unexpected runtime exception (network drop, overdraft, limit breached)
        if (amount > 100_000) {
            throw new IllegalStateException("Transfer limit exceeded");
        }

        // 2. Credit to Account B
        accountRepository.deposit(toAccountId, amount);
    }
}

Analogy: ACID is like sending a high-value parcel via a secured courier service: the parcel is either delivered in full to the recipient or returned completely to the sender with nothing missing (Atomicity); the package must comply with postal dimensions and legal weight rules (Consistency); couriers handle multiple customer packages in parallel without mixing up box contents (Isolation); and once signed for, the delivery record in the ledger cannot be erased or disputed (Durability).

---

🟡 Middle Level

Architectural Implementation of ACID Components

ACID Property	Underlying Engine Mechanism (PostgreSQL / MySQL InnoDB)
Atomicity	Undo Logs (MySQL) / Write-Ahead Logging WAL (PostgreSQL)
Consistency	Schema Constraints (CHECK, FK, UNIQUE) + Application Business Invariants
Isolation	MVCC (Multi-Version Concurrency Control) + Locks (Pessimistic / 2PL)
Durability	Write-Ahead Logging (WAL / Redo Log) + System fsync() kernel calls

1. Atomicity: Mutation Journaling

To reverse mutations when an error or cancellation occurs:

• MySQL (InnoDB): Allocates an Undo Log segment. Prior to modifying any row, its pre-image is written to the Undo Log. On ROLLBACK, InnoDB iterates backward through the Undo Log, applying inverse deltas to restore original row states.
• PostgreSQL: Does not perform in-place updates. A modified row is written as a new tuple version, and the old version is tagged with the modifying transaction’s ID in xmax. On ROLLBACK, PostgreSQL marks the transaction as ABORTED in the transaction status log (pg_xact). The uncommitted tuple versions are simply ignored by subsequent transactions.

2. Consistency: The Two-Tiered Responsibility Model

Data integrity is maintained through a shared contract between the database and application code:

• Database-Level Consistency: Enforced by declarative constraints:

  ALTER TABLE accounts ADD CONSTRAINT chk_positive_balance CHECK (balance >= 0);
  

  If an operation violates a CHECK or FOREIGN KEY constraint, the engine halts execution with a constraint violation and triggers an automatic rollback.

• Application-Level Consistency: Business invariants (such as double-entry bookkeeping, where total debits must equal total credits). The database has no awareness of domain accounting rules; preserving this logic is the responsibility of application services.

3. Isolation: MVCC vs. Lock Contention

Classical concurrency control relied on Two-Phase Locking (2PL), where read queries acquired shared locks that blocked incoming writes. Modern engines implement Multi-Version Concurrency Control (MVCC):

• Writers append new row versions without acquiring read-blocking exclusive locks.
• Readers inspect a point-in-time snapshot of the database established when the statement or transaction began.
• Core Invariant: Readers never block writers, and writers never block readers.

4. Durability: Write-Ahead Logging (WAL)

Directly writing modified 8 KB or 16 KB data pages to random sectors on disk during every commit is prohibitively slow. Modern databases use Write-Ahead Logging (WAL):

1. When data changes, mutations are sequentially written to an in-memory WAL buffer.
2. When COMMIT is executed, the WAL buffer is synchronously flushed to persistent disk storage using the OS system call fsync().
3. The table data pages in memory (shared_buffers in PostgreSQL, Buffer Pool in InnoDB) remain in a “dirty” state and are lazily flushed to disk in the background by a dedicated Checkpointer process.
4. If a sudden crash occurs, the engine runs Crash Recovery upon reboot, scanning the WAL log forward to reapply all confirmed transactions (REDO).

---

🔴 Senior Level

Crash Recovery Internals: The ARIES Algorithm

Enterprise relational crash recovery follows the classic ARIES (Algorithms for Recovery and Isolation Exploiting Semantics) protocol:

1. Analysis Phase: Scans the WAL forward from the latest checkpoint to reconstruct the state of the active transaction table and dirty page table at the exact moment of failure.
2. Redo Phase: Replays all logged operations forward up to the point of failure (repeating history, including operations belonging to uncommitted transactions) to restore the buffer pool to its exact pre-crash state.
3. Undo Phase: Traverses the WAL backwards to roll back the changes of all transactions that were active when the crash occurred and never completed a COMMIT.

PostgreSQL Heap Bloat vs. MySQL Undo Log MVCC Architecture

• PostgreSQL (Append-Only MVCC): Each tuple stores header fields xmin (creating transaction ID) and xmax (deleting/updating transaction ID). Executing an UPDATE writes a brand-new row tuple into the table file (Heap). Dead row versions remain on disk until reclaimed by the VACUUM daemon. High-frequency write workloads suffer from Table Bloat if VACUUM lags behind.
• MySQL InnoDB (Rollback Segments): Performs in-place page updates. The prior row version is displaced into the Undo Tablespace as an undo log record. Concurrent readers reconstruct older snapshots dynamically by traversing the Undo Log chain. Tables do not suffer from row bloat, but long-running transactions prevent undo log pruning, causing the undo log to swell.

Durability Trade-offs in High-Throughput Systems

Executing a physical fsync() on every commit restricts write operations to the IOPS limit of storage hardware.

-- PostgreSQL: Asynchronous Commit
SET synchronous_commit = off;

• Performance Gain: Transactions return success to clients as soon as the write enters the memory WAL buffer without waiting for fsync(). Write throughput typically increases by $3\times$ to $10\times$.
• Trade-off: Relaxes strict Durability. A sudden power outage or OS kernel panic can lose the last 200–600 ms of committed transactions before the walwriter daemon flushes the buffer to disk.

---

4 Tricky Questions

1. What is the fundamental difference between Consistency in ACID and Consistency in the CAP theorem?

Answer: They represent two distinct concepts that happen to share a name:

• ACID Consistency (C-ACID): Local state validity. It guarantees that transactional operations preserve schema constraints (FOREIGN KEY, CHECK, UNIQUE) and application invariants within a single database instance.
• CAP Consistency (C-CAP): Distributed Linearizability (Single-Copy Consistency). It guarantees that all replicas across a distributed cluster observe identical data states simultaneously, such that every read receives the most recent write regardless of which node is queried.

---

2. Does the Durability guarantee mean that table data files on disk are physically updated upon COMMIT?

Answer: No. In relational engines (PostgreSQL, MySQL, Oracle), a COMMIT only guarantees that transaction records are flushed and synced (fsync()) to the Write-Ahead Log (WAL / Redo Log). The primary table data files (.ibd in MySQL or base/* in PostgreSQL) remain dirty in the RAM buffer pool. They are written to disk asynchronously minutes later during a background checkpoint. Durability is fulfilled because should a crash occur before the checkpoint, the engine recovers and reapplies the changes directly from the persistent WAL during reboot.

---

3. Does Spring’s @Transactional guarantee all four ACID properties by default?

Answer: No. The Isolation property is only partially guaranteed by default. Spring’s @Transactional delegates isolation level configuration to the underlying database engine (Isolation.DEFAULT):

• PostgreSQL and Oracle default to Read Committed.
• MySQL InnoDB defaults to Repeatable Read.

Under Read Committed, concurrent transactions remain susceptible to Non-Repeatable Reads and Phantom Reads. Complete transaction isolation is not achieved unless the developer explicitly specifies Isolation.SERIALIZABLE or enforces pessimistic locking (SELECT FOR UPDATE).

---

4. What are Deferred Constraints, and how do they interact with ACID Consistency?

Answer: Relational databases allow constraints to be declared as DEFERRABLE INITIALLY DEFERRED. Normally, schema constraints (FOREIGN KEY, CHECK) are checked immediately after each individual SQL statement. When a constraint is marked DEFERRED, the engine permits intermediate violations of constraints inside the boundaries of an active transaction (e.g., creating mutually dependent circular foreign keys between parents and children). Validation is postponed until the transaction issues COMMIT. If constraints remain violated at commit time, the entire transaction is rolled back. This provides transactional flexibility without compromising the final consistency of the database.

---

🎯 Interview Cheat Sheet

• ACID Core Definitions:
  • A — Atomicity: All-or-nothing execution; implemented via Undo Logs / WAL rollbacks.
  • C — Consistency: Transitioning between valid states; enforced by constraints (FK, CHECK, UNIQUE) and business logic.
  • I — Isolation: Non-interference of concurrent transactions; achieved via MVCC and lock managers.
  • D — Durability: Guaranteed permanence of committed data; achieved via Write-Ahead Logging (WAL) and fsync().
• MVCC Invariant: Readers never block writers; writers never block readers.
• WAL Invariant: Log records are flushed sequentially to disk via fsync() on COMMIT; dirty memory pages are flushed asynchronously during checkpoints.
• Key Traps:
  • Do not confuse ACID Consistency (local constraint validity) with CAP Consistency (distributed linearizability).
  • A COMMIT writes to WAL logs, not directly to main table files.
  • @Transactional runs at the database default isolation level (Read Committed in PostgreSQL), which permits read anomalies.

Related Topics

• What Transaction Isolation Levels Exist
• What is the Default Isolation Level in PostgreSQL
• What is Rollback in Transactions
• What is Serializable