ACID properties in HLD - Scalability & System Analysis

Scalability Analysis - ACID properties

Growth Table: ACID Properties at Different Scales

Users / Transactions	Atomicity	Consistency	Isolation	Durability
100 users	Simple commit/rollback works well	Enforced by DB constraints easily	Low contention, simple locks suffice	Durability via local disk is reliable
10,000 users	Transactions still manageable, some retries needed	Consistency checks may slow writes	Higher contention, need better isolation levels	Durability requires WAL and backups
1,000,000 users	Atomicity challenged by distributed transactions	Consistency harder, may use eventual consistency in parts	Isolation costly, may use snapshot isolation or weaker	Durability needs distributed consensus and replication
100,000,000 users	Atomicity across shards is complex	Strong consistency expensive, often relaxed	Isolation often relaxed for performance	Durability via geo-replication and multi-region backups

First Bottleneck

The first bottleneck is the database transaction manager. As users and transactions grow, ensuring atomicity and isolation becomes costly. Locking and coordination overhead increase, slowing down throughput.

Scaling Solutions

• Horizontal scaling: Partition data (sharding) to reduce transaction scope.
• Optimistic concurrency control: Reduce locking by retrying conflicts.
• Weaker isolation levels: Use snapshot isolation or read committed to improve performance.
• Distributed transactions: Use two-phase commit or consensus protocols carefully.
• Caching: Cache reads to reduce load on transactional systems.
• Asynchronous durability: Use write-ahead logs and replication with controlled delays.

Back-of-Envelope Cost Analysis

Assuming 1 million users with 1 transaction per second:

• Transactions per second (TPS): ~1,000,000 TPS
• Single DB instance handles ~5,000 TPS → Need ~200 DB instances or sharding
• Network bandwidth for logs and replication: ~100 MB/s or more
• Storage: Each transaction log entry ~1 KB → 1 TB per 1,000,000 seconds (~11.5 days)

Interview Tip

Start by explaining each ACID property simply. Then discuss how each property affects scaling. Identify the bottleneck (usually transaction coordination). Propose solutions like sharding and weaker isolation. Show understanding of trade-offs between consistency and performance.

Self Check

Your database handles 1000 QPS. Traffic grows 10x to 10,000 QPS. What do you do first?

Answer: Add read replicas and implement caching to reduce load. For writes, consider sharding or partitioning data to distribute transactions and reduce contention.

Key Result

ACID properties ensure reliable transactions but become bottlenecks at high scale due to coordination overhead; scaling requires data partitioning, weaker isolation, and distributed transaction 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