ACID properties in HLD - System Design Exercise

Design: Database Transaction System with ACID Properties

Design focuses on transaction management and ACID enforcement within a relational database system. Out of scope are distributed database replication and eventual consistency models.

Functional Requirements

FR1: Support multiple concurrent transactions without data corruption

FR2: Ensure each transaction is atomic: fully completed or fully rolled back

FR3: Maintain consistency of data before and after transactions

FR4: Isolate transactions so they do not interfere with each other

FR5: Guarantee durability so committed transactions survive system failures

Non-Functional Requirements

NFR1: Handle up to 10,000 concurrent transactions

NFR2: Transaction commit latency under 200ms (p99)

NFR3: Availability target of 99.9% uptime

NFR4: Support relational data with strong consistency

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

Key Components

Transaction Manager

Lock Manager or MVCC system

Write-Ahead Log (WAL) for durability

Buffer Pool for caching data pages

Recovery Manager for crash recovery

Design Patterns

Two-Phase Commit for atomicity in distributed transactions

Write-Ahead Logging for durability

Locking protocols or Multi-Version Concurrency Control (MVCC) for isolation

Checkpointing for recovery optimization

Reference Architecture

                    +---------------------+
                    |   Client Application |
                    +----------+----------+
                               |
                               v
                    +---------------------+
                    |   Transaction       |
                    |   Manager           |
                    +----------+----------+
                               |
               +---------------+---------------+
               |                               |
       +-------v-------+               +-------v-------+
       | Lock Manager  |               | Write-Ahead   |
       | or MVCC       |               | Log (WAL)     |
       +-------+-------+               +-------+-------+
               |                               |
               v                               v
       +----------------+             +----------------+
       | Buffer Pool    |             | Recovery       |
       | (Data Cache)   |             | Manager        |
       +-------+--------+             +----------------+
               |
               v
       +----------------+
       | Storage Engine |
       +----------------+

Components

Transaction Manager

Custom component within DBMS

Coordinates transaction lifecycle, ensures atomicity and consistency

Lock Manager or MVCC

Lock tables or versioned data storage

Provides isolation by controlling concurrent access to data

Write-Ahead Log (WAL)

Append-only log file

Ensures durability by recording changes before applying them

Buffer Pool

In-memory cache

Caches data pages to improve read/write performance

Recovery Manager

Crash recovery module

Restores database to consistent state after failures using WAL

Storage Engine

Disk-based storage

Persists data and indexes on durable storage

Request Flow

1. Client sends transaction request to Transaction Manager

2. Transaction Manager requests locks or versions from Lock Manager/MVCC

3. Transaction Manager writes changes to Write-Ahead Log before applying

4. Data pages are updated in Buffer Pool

5. Upon commit, Transaction Manager flushes WAL and Buffer Pool changes to Storage Engine

6. Recovery Manager uses WAL to restore data after crashes

Database Schema

Entities: Transaction (id, status, start_time, commit_time), Lock (resource_id, transaction_id, lock_type), Data Page (page_id, data), WAL Entry (log_id, transaction_id, operation, data_before, data_after). Relationships: Transaction holds Locks; WAL Entries belong to Transactions; Data Pages are locked by Transactions.

Scaling Discussion

Bottlenecks

Lock contention causing transaction delays

Write-Ahead Log becoming a write bottleneck

Buffer Pool size limiting caching efficiency

Recovery time increasing with WAL size

Solutions

Use finer-grained locks or switch to MVCC to reduce contention

Partition WAL or use group commit to improve write throughput

Increase Buffer Pool size and use smarter eviction policies

Implement incremental checkpointing to limit recovery time

Interview Tips

Time: Spend 10 minutes clarifying requirements and constraints, 20 minutes designing components and data flow, 10 minutes discussing scaling and trade-offs, 5 minutes summarizing.

Explain each ACID property with how the system enforces it

Discuss concurrency control methods and their trade-offs

Describe durability mechanisms like Write-Ahead Logging

Mention recovery process after crashes

Highlight bottlenecks and scaling strategies