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

Practice

1. Which of the following ACID properties ensures that a transaction is completed fully or not at all, preventing partial updates?

easy

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Solution

1. Step 1: Understand Atomicity in ACID

   Atomicity means a transaction is treated as a single unit. It either happens completely or not at all.

2. Step 2: Compare with other properties

   Consistency ensures data correctness, Isolation manages concurrent transactions, Durability guarantees data persistence after commit.

3. Final Answer:

   Atomicity -> Option A

4. Quick Check:

   Atomicity = All or nothing [OK]

Hint: Atomicity means all steps succeed or none do [OK]

Common Mistakes:

• Confusing Atomicity with Consistency
• Thinking Isolation means no partial updates
• Mixing Durability with Atomicity

2. Which ACID property is correctly described by this statement: Once a transaction commits, its changes are permanent even if the system crashes.

easy

A. Isolation

B. Atomicity

C. Durability

D. Consistency

Solution

1. Step 1: Identify property about permanence after commit

   Durability guarantees that once a transaction commits, changes survive crashes.

2. Step 2: Check other properties

   Isolation controls concurrent access, Atomicity ensures all-or-nothing, Consistency maintains valid data states.

3. Final Answer:

   Durability -> Option C

4. Quick Check:

   Durability = Permanent changes after commit [OK]

Hint: Durability means changes survive crashes [OK]

Common Mistakes:

• Mixing Durability with Atomicity
• Confusing Isolation with Durability
• Thinking Consistency means permanence

3. Consider two transactions T1 and T2 running concurrently. T1 reads a value, T2 updates it and commits. Which ACID property ensures T1 does not see T2's partial changes before commit?

medium

A. Isolation

B. Durability

C. Atomicity

D. Consistency

Solution

1. Step 1: Understand concurrent transaction behavior

   Isolation ensures transactions do not interfere and see only committed data.

2. Step 2: Relate to other properties

   Atomicity is about all-or-nothing, Durability about permanence, Consistency about valid data states.

3. Final Answer:

   Isolation -> Option A

4. Quick Check:

   Isolation = No dirty reads [OK]

Hint: Isolation prevents seeing uncommitted changes [OK]

Common Mistakes:

• Confusing Isolation with Atomicity
• Assuming Consistency controls concurrency
• Mixing Durability with Isolation

4. A developer notices that after a system crash, some committed transactions are lost. Which ACID property is most likely violated?

medium

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Solution

1. Step 1: Identify property related to crash recovery

   Durability ensures committed transactions survive crashes.

2. Step 2: Analyze the problem

   Lost committed transactions after crash means Durability is violated.

3. Final Answer:

   Durability -> Option D

4. Quick Check:

   Durability = No data loss after crash [OK]

Hint: Lost commits after crash means Durability failure [OK]

Common Mistakes:

• Blaming Atomicity for crash data loss
• Confusing Isolation with crash recovery
• Assuming Consistency fixes crash issues

5. You design a banking system where multiple users transfer money concurrently. Which combination of ACID properties is most critical to prevent incorrect balances and lost updates?

hard

A. Consistency and Durability

B. Atomicity and Isolation

C. Isolation and Durability

D. Atomicity and Consistency

Solution

1. Step 1: Identify needs for concurrent money transfers

   Atomicity ensures each transfer completes fully or not at all, preventing partial money moves.

2. Step 2: Ensure no interference between concurrent transfers

   Isolation prevents transactions from seeing intermediate states, avoiding lost updates and incorrect balances.

3. Step 3: Evaluate other properties

   Consistency ensures valid data but depends on Atomicity and Isolation; Durability ensures persistence but not concurrency control.

4. Final Answer:

   Atomicity and Isolation -> Option B

5. Quick Check:

   Atomicity + Isolation = Safe concurrent transfers [OK]

Hint: Atomicity + Isolation prevent partial and conflicting updates [OK]

Common Mistakes:

• Ignoring Isolation leads to lost updates
• Assuming Consistency alone prevents concurrency issues
• Overlooking Atomicity causes partial transfers