Skip to content

02ACID

How Databases Enforce ACID

1. Atomicity (A)

Goal: The transaction is all-or-nothing. If any part fails, the entire transaction must be undone.

How It's Enforced: The Transaction Log (WAL)

Databases primarily use a technique called Write-Ahead Logging (WAL) or a similar Transaction Log to achieve atomicity and durability (we'll see why).

  • The Mechanism: Before the database applies any change to the main data files, it first records the change in a special, highly sequential file called the Transaction Log. This log contains two types of records:
    • "Before Images" (Undo Records): The state of the data before the transaction started.
    • "After Images" (Redo Records): The state of the data after the transaction is complete.
  • The Process:
    1. When a transaction starts, it writes a "BEGIN" record to the log.
    2. For every change (UPDATE, INSERT, DELETE), the "Before" and "After" images are recorded in the log.
    3. If the transaction successfully completes, a "COMMIT" record is written to the log.
    4. If the transaction fails (e.g., system crash, error), the database uses the "Before Images" (Undo Records) in the log to Rollback the partially completed changes, restoring the database to the state it was in before the transaction began.

2. Consistency (C)

Goal: A transaction moves the database from one valid state to another, always adhering to schema rules.

How It's Enforced: Constraints and Immediate Rollback

Consistency is enforced at both the application layer (client code) and the database layer (engine). The database ensures all internal rules are met.

  • The Mechanism: The database engine relies on schema definition features to define a valid state:
    • Integrity Constraints: Rules defined on tables, such as Primary Keys (for uniqueness), Foreign Keys (to maintain relationships between tables), NOT NULL constraints, and CHECK constraints (e.g., age > 0).
    • Triggers: Stored procedures that automatically execute when a specific event (INSERT, UPDATE, DELETE) occurs, often used to enforce complex business rules that simple constraints can't handle.
  • The Process:
    1. The transaction attempts to execute.
    2. If any operation within the transaction attempts to violate a defined constraint (e.g., inserting a duplicate Primary Key value), the database immediately detects the violation.
    3. The database engine prevents that specific operation from completing, and because of Atomicity, the entire transaction is automatically rolled back to prevent the database from entering an invalid state.

3. Isolation (I)

Goal: Concurrent transactions must not interfere with each other; they appear to execute sequentially.

How It's Enforced: Locking and Multi-Version Concurrency Control (MVCC)

Isolation is perhaps the most complex and critical property for high-performance systems.

  • Traditional Mechanism: Locking: A transaction locks the specific data it needs to modify.
    • Shared Locks (Read Locks): Allow multiple readers but prevent any writer.
    • Exclusive Locks (Write Locks): Allow only one writer and block all other readers/writers.
    • The problem with simple locking is it can lead to deadlocks and reduced concurrency.
  • Modern Mechanism: Multi-Version Concurrency Control (MVCC): This is the advanced technique used by most modern relational databases (like PostgreSQL, and to a degree, MySQL's InnoDB).
    • The Concept: Instead of locking data, MVCC allows multiple transactions to read and write simultaneously by keeping multiple versions of data.
    • The Process: When a transaction starts, it's given a unique timestamp (or transaction ID). When it reads data, it only sees the version of the data that existed before its timestamp. When it writes, it creates a new version of the data. Other concurrent transactions continue to read the old version until the new version is committed. This allows readers to never block writers, and writers to never block readers.
    • Isolation Levels: Databases offer different levels of isolation (e.g., Read Committed, Repeatable Read, Serializable), which control how strictly this separation is enforced, balancing consistency with performance.

4. Durability (D)

Goal: Once a transaction is committed, the changes are permanent and will survive a system crash.

How It's Enforced: Write-Ahead Logging (WAL) and Checkpointing

Durability is closely tied to Atomicity through the use of the Transaction Log.

  • The Mechanism (Log Writing):
    1. As mentioned above, the system first writes the transaction's "After Images" (Redo Records) to the Transaction Log (WAL).
    2. The engine ensures the WAL record is physically written ("flushed") to stable storage (disk/SSD) before the "COMMIT successful" message is sent back to the client. This is the absolute guarantee.
  • The Mechanism (Data Writing - Checkpointing): Writing the actual data blocks to the disk is much slower than writing the sequential log. The database delays this step to optimize performance.
    • The database periodically performs a Checkpoint. During a checkpoint, all in-memory changes are forcefully written to the main data files on disk. This reduces the time needed for recovery, as the system only has to process log records since the last checkpoint, not all log records ever.
  • Recovery: If the system crashes, the database engine executes a Recovery Process upon restart:
    1. It reads the WAL from the last successful checkpoint.
    2. It uses the Redo Records to re-apply any committed transactions that hadn't yet been fully written to the main data files.
    3. It uses the Undo Records to roll back any transactions that started but never wrote a "COMMIT" record (enforcing Atomicity).