Bird
Raised Fist0

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

HLDmediumInterview freq: high
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🎯
Design a System Ensuring ACID Properties at Scale
45 min · senior
senior

You are building a financial transaction processing system for a 50 million daily active user (DAU) startup. The system must guarantee strict ACID properties to prevent data corruption, lost updates, or inconsistent reads. Transactions include money transfers, balance updates, and audit logging. The system must handle 10,000 transactions per second with P99 latency under 200ms. Data must be encrypted at rest and in transit. Offline delivery of transaction results to clients is required, and the system must gracefully handle partial failures without violating ACID guarantees.

Constraints
  • End-to-end encryption of data at rest and in transit
  • Offline delivery of transaction results with exactly-once semantics
  • P99 transaction commit latency under 200ms at 10K TPS
  • Support for multi-row, multi-entity transactions with strong consistency
  • Graceful recovery from partial failures without data loss or corruption
📋
Guided Steps
1/5
Step 1
Choosing the Storage Engine for ACID Compliance
Which storage technology would you select to guarantee ACID properties at scale, and why?
💡 Hint
Consider distributed transactional databases that support multi-row ACID transactions with strong consistency and low latency.
Key Decision
Use Google Spanner for its globally-distributed, strongly consistent, multi-version concurrency control (MVCC) with TrueTime API enabling external consistency and linearizability at scale.
Interviewer Expectation
Mid-level: Names Spanner or CockroachDB. Senior: Explains Spanner's TrueTime and how it prevents anomalies under high concurrency and geo-distribution.
⚠️Common Wrong Answer
Use Cassandra - it lacks multi-row ACID transactions and only offers eventual consistency, leading to data anomalies under concurrent writes.
Step 2
Ensuring Atomicity and Isolation in Transaction Processing
How would you implement atomic commit and isolation levels to prevent dirty reads, lost updates, and phantom reads?
💡 Hint
Think about two-phase commit protocols and MVCC isolation levels supported by the chosen storage.
Key Decision
Leverage Spanner's two-phase commit protocol combined with snapshot isolation to ensure atomic commit and serializable isolation without blocking reads.
Interviewer Expectation
Mid-level: Describes two-phase commit usage. Senior: Details how snapshot isolation avoids read locks and how Spanner's concurrency control prevents write skew and phantom reads.
⚠️Common Wrong Answer
Rely on application-level locking - leads to deadlocks and poor scalability under 10K TPS.
Step 3
Designing Durable and Encrypted Data Storage
How do you guarantee durability and encryption of transaction data both at rest and in transit?
💡 Hint
Consider storage-level encryption and TLS for network communication, plus write-ahead logs or commit logs for durability.
Key Decision
Use Spanner's built-in encryption at rest with Google Cloud KMS integration and enforce TLS 1.3 for all client-server communication; rely on Spanner's commit logs replicated across multiple zones for durability.
Interviewer Expectation
Mid-level: Mentions encryption at rest and TLS. Senior: Explains integration with KMS for key rotation and how commit logs replicate synchronously to prevent data loss on node failure.
⚠️Common Wrong Answer
Encrypt only at the application layer - risks data exposure in transit and complicates query processing.
Step 4
Supporting Offline Delivery with Exactly-Once Semantics
How would you design the system to deliver transaction results to clients who may be offline, ensuring exactly-once delivery?
💡 Hint
Think about durable message queues with idempotent consumers and client-side cursors or checkpoints.
Key Decision
Use Apache Kafka with transaction_id as partition key to guarantee ordered, exactly-once delivery; clients maintain offsets to resume consumption after offline periods.
Interviewer Expectation
Mid-level: Names Kafka for messaging. Senior: Explains Kafka's transactional APIs and how partitioning by transaction_id ensures ordering and idempotency for offline clients.
⚠️Common Wrong Answer
Use HTTP polling for delivery - causes massive load spikes and inconsistent delivery at 50M DAU.
Step 5
Handling Partial Failures and Recovery Without Violating ACID
What failure modes do you anticipate, and how does your design recover without violating ACID guarantees?
💡 Hint
Consider network partitions, node crashes, and transaction coordinator failures; think about distributed consensus and retry mechanisms.
Key Decision
Rely on Spanner's Paxos-based replication for consensus and leader election; transaction coordinator retries aborted transactions transparently; Kafka's exactly-once semantics prevent duplicate delivery during recovery.
Interviewer Expectation
Mid-level: Mentions retries and replication. Senior: Details Paxos quorum requirements, how Spanner handles coordinator failover without losing transaction state, and Kafka's idempotent producer guarantees.
⚠️Common Wrong Answer
Ignore partial failures or rely on manual reconciliation - leads to data corruption and inconsistent state.
🔌
API Design Challenge
Sketch 3 core API endpoints for the transaction system that ensure ACID compliance and support offline delivery.
Hints
  • How does the client initiate and commit a transaction atomically?
  • How does the client query transaction status or results, including offline scenarios?
  • How does the system acknowledge and guarantee durability and isolation of transactions?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Use of a distributed transactional database with strong consistency (e.g., Google Spanner)
  • Implementation of atomic commit and isolation via two-phase commit and snapshot isolation
  • Encryption at rest and in transit integrated with key management
  • Exactly-once offline delivery using Kafka with partitioning and transactional APIs
Good to Have - 30%
  • Detailed failure recovery using Paxos consensus and transaction coordinator retries
  • Client-side offset management for offline resumption
  • Explanation of concurrency control preventing anomalies like phantom reads
  • Use of commit logs for durability guarantees
Bonus - 10%
  • Discussion of TrueTime API's role in external consistency
  • Trade-offs between latency and consistency in multi-region deployments
  • Handling of key rotation and encryption lifecycle management
Anti-patterns to Avoid
Using Cassandra or other eventually consistent stores - leads to lost updates and inconsistent reads under concurrent writes
HTTP polling at 50M DAU: 50M reconnects/min just to check for messages - collapses under load
✅
Model Answer
▼ Reveal

This system design uses Google Spanner as the distributed transactional database to guarantee ACID properties at scale, leveraging its TrueTime API for external consistency. Atomicity and isolation are ensured via Spanner's two-phase commit protocol and snapshot isolation. Durability and encryption are provided by Spanner's built-in encryption integrated with Google Cloud KMS and TLS 1.3 for data in transit. Offline delivery with exactly-once semantics is achieved using Apache Kafka with transaction_id partitioning and transactional APIs. The system handles partial failures using Spanner's Paxos-based replication and Kafka's idempotent producers.

Key Decisions
  • Select Google Spanner for its global distribution, strong consistency, and TrueTime API enabling linearizability.
  • Implement atomic commit using Spanner's two-phase commit protocol combined with snapshot isolation to prevent anomalies.
  • Use Spanner's encryption at rest integrated with Google Cloud KMS and enforce TLS 1.3 for all client-server communication.
  • Employ Apache Kafka with transaction_id as partition key to guarantee ordered, exactly-once delivery to offline clients.
  • Handle partial failures with Spanner's Paxos-based replication and leader election, plus Kafka's idempotent producers and client offset management.
  • Google Spanner for globally-distributed, strongly consistent ACID transactions with TrueTime API
  • Two-phase commit protocol combined with snapshot isolation to ensure atomicity and serializability
  • Spanner's built-in encryption at rest integrated with Google Cloud KMS and TLS 1.3 for in-transit encryption
  • Apache Kafka with transaction_id partition key for ordered, exactly-once offline delivery
  • Paxos-based replication and leader election in Spanner for fault tolerance and recovery
  • Kafka's transactional APIs and idempotent producers to guarantee exactly-once message delivery
  • Client-side offset management in Kafka consumers to support offline resumption
  • Write-ahead commit logs replicated synchronously across zones for durability

Practice

(1/5)
1. A system must enforce a maximum number of API calls per user per minute to prevent abuse. Which component in an API Gateway architecture is best suited to handle this requirement?
easy
A. The API Gateway applies rate limiting before routing requests to backend services
B. The backend microservices implement rate limiting individually to distribute load
C. A centralized database tracks request counts and enforces limits synchronously
D. Clients are responsible for self-throttling their request rates

Solution

  1. Step 1: Identify rate limiting responsibility

    The API Gateway is the entry point and can efficiently enforce rate limits before requests reach backend services, preventing overload.

  2. Step 2: Eliminate other options

    The backend microservices implement rate limiting individually to distribute load spreads rate limiting, risking inconsistent enforcement and backend overload. A centralized database tracks request counts and enforces limits synchronously adds latency and complexity by synchronous DB checks. Clients are responsible for self-throttling their request rates trusts clients, which is insecure.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    API Gateway enforces rate limits upfront to protect backend [check]
Hint: Rate limiting belongs at the gateway entry point [check]
Common Mistakes:
2. In the message delivery trace, at Step 4 the Delivery Worker checks Bob's presence TTL in the presence service cache. What happens if Bob's presence TTL has expired at this step?
easy
A. The Delivery Worker queues the message for offline storage and does not send over WebSocket
B. The Delivery Worker immediately marks the message as delivered without sending
C. The Delivery Worker retries presence check indefinitely before sending
D. The Delivery Worker sends the message over WebSocket regardless of presence TTL

Solution

  1. Step 1: Trace Delivery Worker decision at Step 4 -- Bob's presence TTL

    If Bob's presence TTL expired, Delivery Worker treats Bob as offline and queues message for offline storage.

  2. Step 2: Eliminate other options

    Marking delivered without sending is incorrect; retrying indefinitely blocks delivery; sending regardless ignores offline status.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Expired presence TTL -> queue for offline delivery [check]
Hint: Expired presence TTL means offline -> queue message [check]
Common Mistakes:
3. If the vector storage layer loses durability guarantees (e.g., no persistent writes), what exact failure occurs during a node crash?
medium
A. The HNSW index rebuilds automatically with stale data but no data loss
B. Queries return incorrect nearest neighbors due to corrupted index links
C. Recent vector inserts are lost, causing missing search results for those vectors
D. The system falls back to brute-force search with no performance impact

Solution

  1. Step 1: Understand durability impact on vector storage

    Without persistence, recent inserts are lost on crash, but existing index data remains intact.

  2. Step 2: Analyze index rebuild behavior

    HNSW index rebuilds from last persisted snapshot, so it uses stale data but does not corrupt links.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Index rebuild uses stale data; recent inserts lost but no corruption [check]
Hint: No durability -> stale index rebuild, recent inserts lost [check]
Common Mistakes:
4. You must choose between a centralized logging system and a distributed tracing system to debug a latency spike affecting only a subset of microservices. Which is the better choice and why?
medium
A. Distributed tracing, because it shows end-to-end request paths and pinpoint latency bottlenecks
B. Centralized logging, because it aggregates metrics and alerts on latency spikes
C. Centralized logging, because it stores all events and can be searched easily
D. Distributed tracing, because it collects numerical metrics for alerting

Solution

  1. Step 1: Identify the debugging need for latency spike localization

    Tracing provides end-to-end request paths, revealing which microservices cause latency.

  2. Step 2: Eliminate logging options

    Logging stores events but doesn't easily correlate distributed latency; it's not optimized for path visualization or bottleneck pinpointing.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Tracing visualizes request paths to find latency hotspots [check]
Hint: Tracing maps request paths; logs record events [check]
Common Mistakes:
5. At 10x scale, which component is most likely to fail first due to stateful connection limits, and why?
hard
A. The chat servers, because WebSocket connections are stateful and cannot be resharded easily
B. The message queue, because partition count limits throughput
C. The database cluster, because query volume exceeds CPU capacity
D. The load balancer, because it cannot distribute traffic evenly at high scale

Solution

  1. Step 1: Identify stateful bottleneck

    Chat servers maintain WebSocket connections bound to server memory, limiting horizontal scaling and resharding.

  2. Step 2: Compare other components

    Database (A) can be scaled with sharding; message queue (C) partitions can be increased; load balancer (D) scales horizontally.

  3. Step 3: Explain failure mechanism

    WebSocket statefulness causes connection limits per server, leading to failure under 10x load.

  4. Final Answer:

    Option A -> Option A

  5. Quick Check:

    Stateful WebSocket limits -> chat servers fail first at scale [check]
Hint: Stateful WebSockets limit scaling; stateless scale easier [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