ACID Properties (Atomicity, Consistency, Isolation, Durability)

A Database Transaction is a sequence of read and write operations executed as a single, logical unit of work. To guarantee reliable data processing in the presence of concurrent users, network partitions, and hardware crashes, a transactional database must satisfy the ACID guarantees:

1. Atomicity ("All or Nothing")

Every statement in a transaction (INSERT, UPDATE, DELETE) is treated as a single indivisible operation. Either all operations succeed and commit, or if any error occurs (e.g., constraint violation, system crash, deadlock), the database rolls back all previous mutations, leaving the database in its exact pre-transaction state.

• Mechanism: Implemented via Write-Ahead Log (WAL) Undo Records or Rollback Segments.

2. Consistency ("Valid State Invariance")

A transaction can only transition the database from one valid state to another valid state, strictly preserving all schema constraints, foreign key relationships, check constraints, and unique indexes. If a transaction attempts to insert a record violating a CHECK (balance >= 0) constraint, the engine aborts the transaction.

3. Isolation ("Invisible Intermediate States")

Concurrent transactions executing simultaneously cannot observe each other's partial, uncommitted intermediate writes. To an external observer, concurrent transactions appear to execute sequentially.

• Mechanism: Implemented via Multi-Version Concurrency Control (MVCC) and Two-Phase Locking (2PL).

4. Durability ("Survives Power Outages")

Once a transaction commits and the database returns a success code to the client, its changes are permanently recorded in non-volatile storage. Even if the database server loses power 1 millisecond later, the data is guaranteed to survive.

• Mechanism: Implemented via synchronous fsync() of WAL log records to persistent disk platters/flash before acknowledging the commit.

When a database crashes mid-transaction and reboots, how does it guarantee Atomicity and Durability? It runs the ARIES (Algorithms for Recovery and Isolation Exploiting Semantics) recovery process across the Write-Ahead Log:

1. Analysis Phase: Scans the WAL forward from the last safe checkpoint to identify:
   • Which transactions were committed (Winners).
   • Which transactions were uncommitted and running when the crash occurred (Losers).
2. Redo Phase (Repeating History): Scans the WAL forward, re-applying all committed changes to data pages to restore the exact memory state at the moment of the crash (Durability).
3. Undo Phase (Rolling Back Losers): Scans the WAL backward, executing inverse operations to rollback all uncommitted changes made by loser transactions (Atomicity).

BEGIN; -- Start atomic transaction

-- Step 1: Debit sender
UPDATE accounts 
SET balance = balance - 100.00 
WHERE id = 1 AND balance >= 100.00;

-- Step 2: Credit receiver
UPDATE accounts 
SET balance = balance + 100.00 
WHERE id = 2;

-- If both steps succeed without constraint violations:
COMMIT; 
-- If an error occurs, the application issues:
-- ROLLBACK;