Comprehensive knowledge of data consistency models and transactional guarantees in databases and distributed systems. This includes understanding transaction properties such as Atomicity, Consistency, Isolation, and Durability (ACID) and alternative design philosophies such as Basically Available, Soft state, Eventually consistent (BASE). Candidates should be able to choose appropriate isolation levels including read uncommitted, read committed, repeatable read, serializable, and snapshot isolation and explain performance versus correctness tradeoffs and common anomalies such as dirty reads, non repeatable reads, phantom reads, lost updates, and write skew. Understand consistency models including strong consistency, strict serializability, serializability, snapshot isolation, causal consistency, eventual consistency, monotonic reads, and read your writes, and when each model is acceptable based on latency, availability, and business correctness requirements. Discuss replication strategies and their impact on guarantees, including synchronous versus asynchronous replication, multi region replication, replication lag, and replica divergence. Evaluate distributed transaction and coordination approaches such as two phase commit and consensus based protocols and weigh their performance and failure modes. Propose conflict detection and resolution strategies such as last write wins, version vectors and vector clocks, conflict free replicated data types, application level reconciliation, idempotent operations, retries, and saga or compensation patterns for long running workflows. Consider practical engineering concerns including consistency service level objectives, monitoring and alerting for staleness and replication lag, testing strategies for consistency, implications for caching and sharding, and the tradeoffs between developer complexity and user facing correctness.
MediumTechnical
101 practiced
Analysts complain that their analytics replica is often stale. Propose an SLO for replica freshness for analytics workloads, describe how you would measure freshness (including instrumentation and sampling), and recommend mitigation tactics such as nearline ingestion, replication tuning, or changing topology to reduce staleness while balancing cost.
Sample Answer
Proposed SLO- 99% of analytics queries must read replica data no older than 2 minutes (freshness ≤ 2m) measured over a 30‑day rolling window. Error budget: 1% of queries can exceed 2m.How to measure freshness (instrumentation + sampling)1. Define freshness metric per row/table: freshness = now() - last_ingest_timestamp (or last_commit_timestamp replicated).2. Instrumentation: - Add/ensure a reliable monotonic timestamp: ingestion_time on ETL writes and commit_lsn/tx_timestamp on primary. - On replica, expose a system view/API that returns per-table max(commit_timestamp) and replication_lag_lsn. - Emit metrics to monitoring (Prometheus/Grafana) for: - replica_max_commit_ts (gauge) - primary_max_commit_ts (pulled periodically) - computed freshness = primary_max - replica_max (gauge)3. Sampling: - Continuous lightweight sampling per table every 15s for high-volume tables; every 60-300s for low-volume ones. - Additionally sample at query time: attach a trace tag returning the replica freshness used for that query to get user-visible impact and SLI (percent of queries below 2m). - Aggregate histograms and percentiles; alert on 95th/99th percentile breaches.Mitigation tactics (trade-offs & when to apply)1. Nearline ingestion (best for event/stream pipelines) - Use Kafka/CDC -> stream processors to write to a nearline store (e.g., ClickHouse, BigQuery streaming) with <30s latency. - Pros: low-latency reads for analytics, decouples from OLTP. - Cons: extra infrastructure/cost and potential eventual consistency with primary.2. Replication tuning - Increase apply throughput on replica (parallel apply, batch sizes), tune network (keepalive, increased replication slot resources). - Use logical replication with tuned worker counts for high-concurrency tables. - Pros: cheaper, keeps single source of truth. - Cons: limited by DB engine and can impact primary if synchronous.3. Change topology - Introduce dedicated nearline replicas for analytics (asynchronous, optimized for reads) and isolate from OLTP replicas. - Use multi-tier: primary -> fast replica (for near-real-time) -> archival replicas. - Pros: isolates load, enables different tuning per role. - Cons: higher operational cost.4. Query-side strategies - Route time-insensitive queries to cheaper, eventual replicas; route real-time dashboards to nearline/fast replica. - Implement consumer-facing SLA: flag stale results and show last-update time. - Use caching with TTL aligned to SLO.5. Cost/consistency trade-offs - Tight SLOs (seconds) → nearline + dedicated replicas, higher cost. - Looser SLOs (minutes) → replication tuning + sampling suffices.Recommendation roadmap1. Instrument and deploy freshness metrics and query-time tagging (1–2 weeks).2. Tune replication settings for top 10 tables by volume and measure impact (2–4 weeks).3. If breaches persist for dashboards requiring <2m, deploy nearline stream ingestion for those pipelines and create a dedicated analytics replica (4–8 weeks).4. Review SLO quarterly and adjust error budget based on business needs and cost.
MediumTechnical
91 practiced
Explain read/write quorum parameters (N, R, W) in quorum-based systems similar to Dynamo. Given different SLAs (low-latency reads preferred, high write throughput preferred, or strong consistency required), demonstrate choosing R and W for each SLA and show example failure scenarios and how your choices behave under node failures and partitions.
Sample Answer
N = total replicas for a key. R = number of replicas that must acknowledge a read. W = number of replicas that must acknowledge a write. Quorum systems typically aim for R + W > N to guarantee strong consistency (any read overlaps with the latest write).SLA 1 — Low-latency reads preferred (reads fast, slightly stale OK)- Choose: N=3, R=1, W=2- Reason: Reads hit one replica (fast); writes must reach two replicas to reduce window of inconsistency.- Failure example: One node down → reads still succeed from remaining single replica; writes succeed if two replicas available. If network partition isolates a single replica, reads return possibly-stale data; concurrent writes on partitioned sides may diverge and require reconciliation.SLA 2 — High write throughput preferred (fast, highly available writes)- Choose: N=3, R=2, W=1- Reason: Writes only need single ack (fast, high throughput). Reads require two replicas to reduce returning stale results.- Failure example: One node down → writes still succeed (W=1). If the node holding latest write is down, reads (R=2) may return older values until the updated replica returns. Partition with majority write traffic can accept writes independently causing conflicting versions.SLA 3 — Strong consistency required- Choose: N=3, R=2, W=2 (or R=3,W=1 with R+W>3; common is R=2,W=2)- Reason: R+W=4>3 ensures read/write overlap — reads see latest acknowledged writes.- Failure example: One node down → reads and writes still succeed if two replicas available. If two nodes down → with only one replica left, neither reads (R=2) nor writes (W=2) succeed → system sacrifices availability for consistency (as expected by CAP).Trade-offs and notes:- Increasing W improves consistency but hurts write latency/availability; increasing R reduces stale reads but increases read latency.- In partitions, choices determine whether system favors availability (low R/W) or consistency (R+W>N and higher thresholds).- Also consider hinted handoff, anti-entropy, and client-side read-repair to mitigate staleness when using low R.
EasyTechnical
94 practiced
Explain how snapshot isolation (SI) works and why it prevents some anomalies but can allow write skew. Provide a concrete example with two concurrent transactions in a banking-like system that systemically violates an invariant under SI. As a Solutions Architect, list strategies to mitigate or prevent such write skew when the invariant is critical.
Sample Answer
Snapshot Isolation (SI): each transaction reads from a consistent snapshot taken at start time and writes into private buffers; commits succeed if and only if no other concurrent committed transaction wrote to the same rows (no write–write conflicts). SI prevents many anomalies (dirty reads, non-repeatable reads, lost updates) because reads are stable and conflicting concurrent writes are detected at commit.Why SI allows write skew: SI does not check for conflicts on reads, so two transactions can read overlapping state, make decisions based on that snapshot, and write disjoint rows — both commit — producing a state that never existed in any serial order.Concrete banking example (invariant: “total balance of accounts A+B must stay ≥ 100”):- Initial: A=60, B=60 (total 120).- T1 (starts): reads A=60, B=60; decides withdraw 40 from A → new A=20; writes A.- Concurrent T2 (starts): reads A=60, B=60; decides withdraw 40 from B → new B=20; writes B.- Neither writes the same row as the other, so both commit under SI. Final A=20, B=20 → total 40, invariant violated. This is write skew.Mitigation strategies (Solutions Architect view — trade-offs and recommendations):- Use serializable isolation (true serializability or DB-provided Serializable Snapshot Isolation/SSI). Pros: correctness; Cons: higher aborts, throughput impact.- Use explicit locking: SELECT ... FOR UPDATE or application-level pessimistic locks on the invariant’s rows to create write–write conflicts. Pros: simple; Cons: potential contention/latency.- Centralize invariant-checking: maintain a single “account-pool” row or ledger entry that transactions must update (creates write conflict if invariant might break).- Add database constraints/triggers that enforce invariant at commit (if expressible) or perform final check under lock in a stored procedure.- Application-level coordination: a small coordinator service or token-based mutex for operations that can violate the invariant.- Hybrid: use optimistic concurrency with retry/backoff and detect dangerous patterns (e.g., if reads include other-critical rows, force serialization).Recommendation: for critical invariants choose serializable isolation or a dedicated locking/coordination approach. Document performance trade-offs and test under expected concurrency; prefer DB-native serializability if available (e.g., PostgreSQL SSI) to reduce application complexity.
EasyTechnical
105 practiced
Explain vector clocks and version vectors: how they capture causality across replicas, how to compare two vectors (happens-before, concurrent), and practical limitations such as growth with replica count. Provide a concrete use-case where vector clocks are superior to simple timestamps for conflict detection.
Sample Answer
Vector clocks / version vectors are per-replica counters used to record causality in distributed systems. Each replica maintains a vector V with an entry per replica id. On a local update, a replica increments its own counter; when replicas exchange state, they merge by taking element-wise maxima. The vector records “how many events from each replica” a replica has seen.Comparing two vectors A and B:- A happens-before B (A → B) if for all i, A[i] ≤ B[i] and exists j where A[j] < B[j].- B → A similarly.- A and B are concurrent if neither A ≤ B nor B ≤ A (each has at least one index larger than the other).Why this captures causality: element-wise maxima and per-replica increments ensure that if an event causally depends on another, its vector will dominate or equal the predecessor’s vector; concurrent, independent events produce incomparable vectors.Practical limitations:- Size grows with number of replicas (O(N) metadata).- Requires stable replica identifiers and reliable inclusion of all replicas.- Can be expensive in wide-scale multi-tenant systems; anti-entropy and pruning strategies (e.g., dotted version vectors, compacting tombstones, or using exceptions for long-lived inactive replicas) mitigate growth.Concrete use-case where vectors beat timestamps:In an eventually-consistent key-value store (like Dynamo-style OR-set), two clients update the same object on different partitions nearly simultaneously. Simple timestamps (wall-clock) can be skewed and lose true concurrency ordering, causing lost updates. Vector clocks let the system detect that the updates are concurrent (incomparable vectors) and surface a conflict for application-level merge (or return both versions), preventing silent overwrite and data loss. This is crucial for correctness when merges require business logic (e.g., collaborative editing, shopping cart merges).
EasyTechnical
155 practiced
As a Solutions Architect designing a payment processing API that must tolerate network retries and partial failures, explain idempotency and propose an idempotency key strategy. Include where keys are generated (client vs server), how they are stored, TTLs for stored keys, scaling concerns, and how to handle malicious or replayed keys.
Sample Answer
Clarify requirement: clients may safely retry requests (network timeouts, 5xx) without creating duplicate charges. Idempotency ensures repeated requests with the same logical intent produce one effect.Proposal (high-level):- Idempotency key: client-generated opaque UUID (e.g., UUIDv4) provided in Idempotency-Key header for user-initiated operations; server may generate keys for server-initiated flows. Encourage client to include client_id or merchant_id in auth so keys can be scoped.- Key binding: store keys keyed by (client_id, idempotency_key). This prevents cross-merchant replay.Storage model:- Fast cache (Redis cluster) for quick lookups + optimistic locking + short TTL; durable store (relational DB / append-only ledger) for permanent audit.- Idempotency record fields: key, client_id, request_fingerprint (hash of body+headers), created_at, status (inflight/succeeded/failed), response_payload (or response_reference), last_used_at.Workflow:1. On request, compute lookup key = client_id + ":" + Idempotency-Key.2. Atomically attempt to insert an inflight record (use Redis SETNX or DB unique constraint). If insert succeeds process request. If exists: - If status == succeeded -> return stored response. - If status == inflight -> return 409 or wait/stream status (avoid duplicate processing). - If status == failed -> allow retry or return error depending on policy.3. After processing, persist final status + response to both Redis and durable DB. Expire Redis entry after TTL.TTLs and retention:- Redis TTL: 24–72 hours (common window for client retries). Choose 24h for high-traffic, 72h if clients retry longer.- Durable DB retention: keep full audit/logs per compliance (payments often require 7+ years); store minimal mapping if long-term dedup needed.Scaling concerns:- Use consistent hashing / Redis Cluster to shard idempotency keys; ensure atomic operations (Lua scripts) to avoid race conditions.- Use DB unique constraint on (client_id, idempotency_key) as the ultimate guard against races.- Avoid storing large response payloads in Redis—store references (response_id) in DB; cache small responses.- Monitor cardinality and TTL churn; set eviction policy to noeviction for keys we rely on and rely on TTL instead.Handling malicious or replayed keys:- Scope keys by client_id to prevent one merchant replaying another’s key.- Enforce per-client rate limits and per-key usage limits (e.g., only accept same idempotency key N times within window).- Validate request_fingerprint: if same key submitted with different payloads, reject (409) and log as potential malicious behavior.- Require authentication and tie the key to the auth token; reject keys that don't match client ownership.- Log and alert abnormal patterns (many different keys with same IP, or same key across clients).Edge cases:- Long-running operations: mark inflight with timeout and allow client to query status endpoint rather than retrying immediately.- Partial failures: if downstream commits succeed but response failed, mark succeeded only after durable confirmation; use two-phase acknowledgement where practical.Rationale: client-generated keys are simpler and put retry responsibility with caller; scoping + atomic inserts + durable logging prevents duplicates and provides auditability while Redis gives low-latency checks for high throughput.
Unlock Full Question Bank
Get access to hundreds of Consistency Models and Transactions interview questions and detailed answers.
