Bird
Raised Fist0

ACID Properties - Atomicity, Consistency, Isolation, Durability Deep Dive

HLDmediumInterview freq: high
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🏗️
ACID Properties - Atomicity, Consistency, Isolation, Durability Deep Dive
Design and analyze ACID properties in distributed SQL databases at scale
mediumDatabases - SQL and TransactionsFreq: high
⚙️
Functional Requirements
  • Support atomic transactions ensuring all-or-nothing execution of multiple operations.
  • Guarantee consistency by enforcing database constraints and rules after each transaction.
  • Provide isolation levels to control concurrent transaction visibility and prevent anomalies.
  • Ensure durability so committed transactions survive crashes and power failures.
  • Support multi-statement transactions with rollback and commit semantics.
  • Allow concurrent reads and writes with minimal contention and deadlocks.
📊
Non-Functional Requirements
  • Support 2 billion daily active users generating 40 messages per user per day.
  • Achieve 99.99% availability for transactional operations.
  • Maintain 100ms P99 latency for transaction commit operations under peak load.
  • Handle peak write throughput of approximately 925,000 writes per second.
  • Ensure data durability with replication factor of 3 and crash recovery within 5 seconds.
❓
Clarifying Questions to Ask
  • What is the expected transaction size in terms of number of operations or rows?
  • Are distributed transactions across multiple shards or partitions required?
  • What isolation levels must be supported (e.g., Serializable, Repeatable Read)?
  • Is synchronous or asynchronous replication preferred for durability?
  • What is the expected read-to-write ratio in the workload?
  • Are there any latency SLAs for transaction commit or rollback?
Out of Scope
✗ Designing NoSQL or eventual consistency models (focus is on strict ACID).
✗ Query optimization and complex SQL parsing internals.
✗ Physical hardware provisioning and network topology design.
✗ User authentication and authorization mechanisms.
📌
Assumptions
  • 2 billion DAU
  • 40 messages per user per day
  • 100 bytes per message
  • Replication factor of 3
⚡
QPS Calculation
write qps2,000,000,000 × 40 / 86,400 ≈ 925,926 writes/sec
peak write qpsAssuming 2× peak factor: 925,926 × 2 ≈ 1,851,852 writes/sec
read qpsAssuming 5 reads per write: 925,926 × 5 ≈ 4,629,630 reads/sec
💾
Storage Estimate
daily storage gb(2,000,000,000 × 40 × 100) / (1024^3) ≈ 7.45 TB/day
retention days30
total storage gb7.45 TB × 30 ≈ 223.5 TB
with replication223.5 TB × 3 = 670.5 TB
🖥️
Server Estimate
transaction coordinatorTo handle 1.85M peak writes/sec, assuming 10K TPS per coordinator, need ~185 servers
storage nodesAssuming 1 TB usable per node with replication, need ~670 storage nodes
🏗️
Architecture Diagram
Architecture Diagram
          +----------------------+
          |   Client Applications |
          +----------+-----------+
                     |
          +----------v-----------+
          | Transaction Coordinator|
          +----------+-----------+
                     |
          +----------v-----------+
          |  Distributed LockMgr  |
          +----------+-----------+
                     |
          +----------v-----------+
          |  Distributed Storage  |
          |  (e.g. CockroachDB)   |
          +----------+-----------+
                     |
          +----------v-----------+
          | Replication & WAL Log |
          +----------------------+
🧩
Components
Transaction Coordinator
Manages transaction lifecycle including begin, commit, rollback, and two-phase commit across shards
Why thisCockroachDB's transaction coordinator supports distributed ACID transactions with linearizable consistency
Why notPostgreSQL's coordinator is single-node and does not scale linearly beyond ~10K TPS
Distributed Lock Manager
Provides concurrency control and isolation by managing locks on keys or rows during transactions
Why thisUses etcd-based lock service for strong consistency and fault tolerance in distributed environment
Why notZookeeper has higher latency and less efficient lease renewal impacting transaction latency
Distributed Storage Layer
Stores data shards with replication, supports MVCC for snapshot isolation and durability
Why thisRocksDB-based storage engine with MVCC enables efficient versioning and snapshot reads
Why notMySQL InnoDB lacks native distributed sharding and MVCC at scale
Replication & WAL Log
Ensures durability by replicating write-ahead logs synchronously to 3 replicas before commit
Why thisRaft consensus algorithm provides strong consistency and fast failover
Why notAsynchronous replication (e.g. MySQL binlog) risks data loss on crash
Client Applications
Initiate transactions and execute SQL queries with ACID guarantees
Why thisUse standard PostgreSQL wire protocol for compatibility and tooling support
Why notCustom protocols increase client complexity and reduce ecosystem support
🔄
Data Flow
1
Client Applications → Transaction Coordinator
Begin transaction and send SQL statements
Coordinator assigns transaction ID and tracks state
2
Transaction Coordinator → Distributed Lock Manager
Acquire locks on keys/rows needed for transaction
Ensures isolation and prevents write-write conflicts
3
Transaction Coordinator → Distributed Storage Layer
Read/write data with MVCC snapshots
Reads see consistent snapshot; writes buffered until commit
4
Transaction Coordinator → Replication & WAL Log
Synchronously replicate WAL entries to replicas
Durability ensured before commit response
5
Transaction Coordinator → Distributed Lock Manager
Release locks after commit or rollback
Allows other transactions to proceed
6
Transaction Coordinator → Client Applications
Return commit success or failure
Client receives final transaction outcome

Practice

(1/5)
1. For the AI Code Assistant's latency budget of 100ms per request, which architecture best balances freshness of code context and response time?
medium
A. Use a synchronous call to the code repository for every request to get the latest code
B. Use a batch offline indexing updated once a day and serve completions from cached embeddings
C. Use a purely generative model without retrieval to avoid retrieval latency
D. Use a hybrid RAG architecture with pre-indexed code embeddings updated periodically and a fast vector search

Solution

  1. Step 1: Evaluate synchronous repository calls

    Use a synchronous call to the code repository for every request to get the latest code causes high latency due to network and disk IO, violating 100ms budget.

  2. Step 2: Compare batch offline indexing

    Use a batch offline indexing updated once a day and serve completions from cached embeddings sacrifices freshness, unsuitable for frequently changing codebases.

  3. Step 3: Assess purely generative model

    Use a purely generative model without retrieval to avoid retrieval latency lacks grounding in latest code, reducing accuracy.

  4. Step 4: Identify hybrid RAG architecture

    Use a hybrid RAG architecture with pre-indexed code embeddings updated periodically and a fast vector search balances freshness (periodic updates) and latency (fast vector search), meeting constraints.

  5. Final Answer:

    Option D -> Option D

  6. Quick Check:

    Hybrid RAG balances freshness and latency [check]
Hint: Hybrid RAG updates embeddings periodically for freshness and speed [check]
Common Mistakes:
2. For real-time query processing with low latency, which architecture is better suited for serving search results, and why?
medium
A. Use a distributed file system to store raw crawled pages and query them directly
B. Use a batch indexing system that updates the index once per day for consistency
C. Use an incremental indexing system with near real-time updates to the inverted index
D. Use a centralized relational database to store all document metadata and perform queries

Solution

  1. Step 1: Compare batch vs incremental indexing

    Batch indexing has high latency, unsuitable for real-time queries. Incremental indexing supports near real-time updates.

  2. Step 2: Evaluate storage/query approaches

    Querying raw pages or centralized DBs is too slow and unscalable for low latency search serving.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Incremental indexing enables low-latency query serving [check]
Hint: Incremental indexing enables near real-time search [check]
Common Mistakes:
3. You need to model a social media feed with millions of users and high write throughput. Which data modeling approach is best to optimize for write scalability and read performance?
medium
A. Embed all posts and comments inside each user document to minimize joins.
B. Use a graph database to model user relationships and posts for efficient traversal.
C. Store all posts in a single collection with a compound index on user and timestamp for fast queries.
D. Use a referencing pattern with separate collections for users, posts, and comments, and denormalize popular posts into user feed documents.

Solution

  1. Step 1: Analyze write scalability and read patterns

    High write throughput and millions of users require scalable writes and efficient feed reads.

  2. Step 2: Evaluate referencing with denormalization

    Referencing separates concerns; denormalizing popular posts into feeds optimizes read performance without large documents.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Referencing plus denormalization balances write scale and read speed [check]
Hint: Reference plus denormalize popular data for scalable writes and fast reads [check]
Common Mistakes:
4. Without the DAU Estimator component, what exactly breaks in the system's capacity planning process?
medium
A. Bandwidth estimation fails because DAU directly affects total data transfer calculations
B. The system cannot estimate peak QPS, causing under-provisioning of servers
C. Storage estimation becomes inaccurate because DAU is a key multiplier for daily data volume
D. User authentication fails because DAU tracks active sessions

Solution

  1. Step 1: Identify DAU Estimator role

    DAU Estimator provides daily active user count critical for bandwidth calculations as bandwidth depends on total active users.

  2. Step 2: Eliminate other options

    The system cannot estimate peak QPS, causing under-provisioning of servers is incorrect because peak QPS depends on instantaneous requests, not just DAU; Storage estimation becomes inaccurate because DAU is a key multiplier for daily data volume is partially true but storage depends more on total objects, not DAU alone; User authentication fails because DAU tracks active sessions is unrelated to authentication.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Bandwidth depends directly on DAU for total data transfer [check]
Hint: Bandwidth depends directly on DAU count [check]
Common Mistakes:
5. Without a properly chosen partition key in Cassandra, what exact failure occurs when querying a large dataset?
medium
A. Queries return stale data due to eventual consistency delays
B. Data is duplicated across multiple partitions causing inconsistency
C. Writes fail silently without error messages
D. Queries time out or fail due to coordinator node overload from large partitions

Solution

  1. Step 1: Understand impact of partition key on data distribution

    Improper partition keys cause very large partitions, leading to coordinator overload during queries.

  2. Step 2: Trace failure mode

    Large partitions cause coordinator to timeout or fail when scanning data; stale data and silent write failures are unrelated; data duplication is controlled by replication, not partition key choice.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Large partitions -> coordinator overload -> query timeouts [check]
Hint: Large partitions overload coordinator -> query timeouts [check]
Common Mistakes:
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard