Transactions, Concurrency Control, and Isolation Levels Questions

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 roadmap

1. 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.

Situation: For a high-throughput analytical workload composed mostly of read-only transactions, you must balance correctness (isolation guarantees) against throughput, latency, and memory/management overhead.

Analysis:

• Snapshot Isolation (SI) behavior: MVCC provides each read-only transaction a consistent snapshot (typically a read timestamp or stable snapshot id). Reads never block writers; readers scan versions visible to their snapshot, yielding excellent throughput and low latency. Memory costs: the system must retain older row versions until all active snapshots that might reference them have completed — so long-running analytic scans increase version retention and storage pressure (GC backlog). Correctness: SI prevents many anomalies but allows write skew and certain non-serializable outcomes (rare in purely read-only sets, but problematic if concurrent writes create invariant violations that later reads assume).

• Serializable Isolation behavior: true serializability requires detecting or preventing anomalies. In MVCC, that means either:

  • Predicate locking / range locks (prevent phantom reads) — heavy-weight and blocks concurrent writers/readers, or
  • Serializable Snapshot Isolation (SSI)/conflict tracking: track read-write conflicts at runtime and abort transactions that could violate serializability. For read-only transactions, strongly consistent techniques can provide “safe snapshots” (read-only snapshot known not to conflict) to avoid tracking. Otherwise, active read tracking increases memory (per-transaction read sets / predicate representations) and CPU for conflict detection, and can increase aborts and retries — hurting throughput.

Trade-offs summarized:

• Throughput & Latency: SI > SSI/serializable in most cases for reads because no conflict tracking and no blocking.
• Memory & GC: SI & SSI both require MVCC retention; SSI may retain versions longer because of more conservative snapshot choices and aborted transaction cleanup complexity.
• Correctness: Serializable > SI. If business invariants must never observe anomalies, serializable required.
• Runtime overhead: Serializable imposes CPU for tracking/conflict checks and likely more transaction aborts and retries under contention.

Recommendation:

• For analytic workloads that are read-mostly and where occasional small anomalies are acceptable (e.g., reporting, dashboards, BI where eventual consistency is tolerated), use Snapshot Isolation with:
  • Short-lived read transactions where possible
  • Mechanisms to bound snapshot age (accelerated GC, separate analytic replica with follower snapshots) to limit MVCC bloat
  • Documented acceptance of possible stale/inconsistent reads
• For workloads where correctness/invariants matter (financial reconciliations, billing, regulatory reports), use Serializable. Mitigate performance impact by:
  • Serving analytics from a serializable read replica optimized for snapshot creation (safe read-only snapshots)
  • Using safe-read optimization: mark some read-only snapshots as “safe” to skip tracking
  • Precompute or materialize invariant-critical views

When to relax guarantees:

• Relax from serializable → SI when results are non-actionable (exploratory analytics), SLAs favor latency/throughput, and business accepts occasional transient inconsistencies.
• Keep serializable when results drive automated actions, legal compliance, or require absolute correctness.

Conclusion: default to SI for high-throughput analytical reads and use measures (replicas, bounded snapshot retention). Choose serializable only where correctness is non-negotiable and offset cost via replicas, safe-read optimizations, or materialized snapshots.

Requirements clarification:

• 100M users, 1k global writes/s, heavy global reads with p99 < 50ms
• Logged-in users must see read-your-writes inside their home region
• Tolerate a region failure

Design summary (high-level):

• Partitioning: consistent-hash on user_id → shards; each shard lives in a single “home” region (user’s home region assigned on signup). Shards map stored in fast metadata service (cached).
• Replication strategy & consistency model: primary-for-shard (single-writer) located in shard’s home region; async multi-region replication of updates to read replicas (eventually consistent globally). For stronger guarantees inside home region use synchronous replication to local replicas (quorum reads/writes) to keep local durability and low latency.
• Read routing: reads from logged-in users route to their home region (session affinity via cookie/token region tag) to ensure read-your-writes. Unauthenticated/geo-closest reads go to nearest region read-replica (may be slightly stale).
• Conflict handling: because writes are routed to the shard primary, conflicts are rare. For failover scenarios (home region down and writes redirected) use a promoted backup primary with monotonic sequence numbers or Lamport timestamps to avoid lost updates. If multi-primary is ever allowed, use last-writer-wins with client-generated timestamps + application merge hooks or CRDTs for fields that can be merged (counters, sets).
• Durability & region-failure tolerance: maintain 3+ replicas per region; async replicate to at least two other regions. On region failure, metadata service promotes a replica in another region as new primary for affected shards (leader election via Paxos/Raft). Update shard-to-region mapping and invalidate caches.

Operational details & tech choices:

• Storage: distributed KV DB (e.g., Cassandra/Scylla with per-shard primary semantics, or DynamoDB Global Tables with preferred-writes). Use CDN/edge cache for public profile reads, TTL-based invalidation.
• Session affinity: include home-region claim in auth token + client SDK logic to prefer that region.
• Replication pipeline: write → local durable commit → append to change-log (Kafka) → async CDC to other regions → apply to read replicas. Use per-shard ordering in the log to preserve causality.

Trade-offs and justification:

• Single-writer-per-shard simplifies consistency and avoids complex merge logic; meets read-your-writes easily.
• Asynchronous cross-region replication reduces write latency and scales with low global write rate (1k/s), but accepts eventual consistency for remote readers—acceptable per requirements.
• Promoting primaries on failover adds complexity (leader election + fast metadata propagation) but required for region-failure tolerance.

Validation plan:

• Load test to validate p99 read latency <50ms from target regions (use geo-distributed load generators).
• Chaos tests: region kill, network partitions, replica lag injection; confirm failover correctness and RYW behavior.
• SLO monitoring: p50/p95/p99 read/write latency, replication lag, stale-read rate.
• Safety tests: conflicting write scenarios, timestamp skew tests, and recovery drills.

Edge cases:

• Clock skew handled by logical clocks for ordering in promotion.
• Hot users: split-hot-user into its own shard or use caching + rate limiting.
• Metadata cache stale: use short TTL and watch/notify for immediate invalidation on migrations.

This design balances low latency reads (local primary/read-replicas + CDN), read-your-writes for logged-in users (home-region writes + affinity), region-failure tolerance (replica promotion and metadata mapping), and operational simplicity (single-writer shards) while keeping global write load easily handled.

Requirements & goals:

• Detect violations of consistency SLOs: read staleness (time-based), monotonicity breaches (out-of-order reads), and read-your-writes (RYW) failures.
• Low added latency/CPU (<2% overhead), scalable across clusters.
• Fast detection for automated mitigation with low false-positive rate.

Metrics to collect (with tags: client_id, region, replica_id, request_id, trace_id):

• write_timestamp (ts) when mutation committed on primary
• visible_timestamp(replica) when replica exposes a version
• read_returned_version_ts per read
• read_intent_id / session_id + last_written_version_ts per client session
• op_latency, op_success, op_error_code
• logical_clock / vector clock comparisons for causal systems
  Derived metrics:
• staleness_ms = now - read_returned_version_ts
• staleness_from_primary_ms = read_returned_version_ts - primary_commit_ts (via causal trace)
• monotonicity_violations_count: count of reads where read_ts < prev_read_ts for same session
• ryw_violations_count: read returned ts < session_last_write_ts
• percent_reads_stronger_consistency: fraction of reads served with strong consistency
  Sampling frequency & retention:
• High-resolution sampling for a small subset: per-request traces for 0.5–1% of reads (always sample failed/latency outliers).
• Aggregate metrics emitted at 1s–5s intervals for hot paths; 1m rollups for dashboards.
• Traces/logs retained 7–30 days depending on compliance.

Acceptable overhead:

• CPU: <2% on dataplane; network: metadata per request ~200 bytes; enable sampling to limit.
• Store aggregate counters; use streaming pipeline (Kafka + real-time aggregation) to avoid per-request storage.

Dashboards:

1. Overview: cluster-level staleness P50/P95/P99, monotonicity violations rate, RYW violation rate, read latency, percent strong reads.
2. Session health: heatmap of sessions with repeated violations.
3. Replica view: per-replica exposed lag vs primary, leader-election events, network partition markers.
4. Trace explorer: sampled traces linking write commit -> read served location -> version timestamps.

Alert thresholds & policy (multi-tier):

• Warning (P1): staleness_p95 > SLO_threshold * 0.8 for 5m AND monotonicity_rate > 0.1% — notify on-call.
• Severe (P0): staleness_p99 > SLO_threshold for 1m OR ryw_violation_rate > 0.05% over 1m — paging, trigger automated mitigation.
• Replica-specific: single replica staleness > 2x threshold for 30s — mark degraded.
• Use composable alerts: require both metric spike and corroborating signals (error codes, topology change) to reduce false positives.

Automated mitigations (ordered, idempotent, reversible):

• Step 0: Increase sampling and start detailed tracing for affected clients.
• Step 1: Failover reads to primary (or nearest primary-like replica) for affected client IDs or sessions (route via service mesh rule).
• Step 2: Temporarily switch affected clients to stronger consistency mode (read-after-write or linearizable) for session scope.
• Step 3: If a replica is lagging, mark it as read-only and remove from read pool; rebalance traffic.
• Step 4: If network partition detected, enact cross-region throttling or degrade non-critical traffic.
• All mitigations are gated by circuit-breaker: require persistent signals for configured duration and backoff with automatic rollback when metrics recover.

Handling false positives & tuning:

• Correlate multiple signals (staleness + elevated write latency + replica lag + topology events) before automation.
• Use sliding-window anomaly detection and baseline per-client baselines to avoid noise from bursty workloads.
• Start with conservative thresholds (tighten over time) and use canary rollout for automation in new regions.
• Provide a manual override and "quiet period" after deployments to avoid alerts during expected perturbations.
• Continuous tuning: track false-positive rate (alerts closed as false) and adjust sensitivity with a feedback loop; use ML anomaly scoring only for hints, not sole trigger.

Why this works:

• Combines per-request causal metadata with aggregated metrics for scalable detection.
• Sampling reduces overhead while preserving forensic traces.
• Multi-signal alerts and staged automated mitigations minimize harm and false positives while restoring SLOs quickly.

Synchronous replication vs asynchronous replication

• Synchronous: Writes are acknowledged only after primary and replica(s) confirm persistence. Guarantees no data loss but increases write latency (round-trip to replicas). Failover is safer (no committed writes lost). Read scalability similar to async if reads served from replicas. Risk: higher tail latency and potential write stalls if replicas are slow or network partitions occur.
• Asynchronous: Primary acknowledges immediately; replicas catch up later. Lower write latency, better write throughput. Read scalability can use replicas but may serve stale data. Failover risk: data loss of recent commits; easier to tolerate slow replicas. Conflict resolution not needed for single-writer setups but stale reads must be considered.

Master-slave (single-master) vs multi-master

• Master-slave: Single writable master, replicas for reads. Write latency depends on sync mode; simpler conflict model (no conflicts). Read scalability excellent via replicas. Failover: requires promotion of a replica (possible split-brain/lag concerns); automation and point-in-time sync matter.
• Multi-master: Multiple nodes accept writes. Enables locality and low write latency per region. Read and write scalability improved. Failover: each node can continue; more resilient. Conflict resolution required (last-write-wins, CRDTs, application-level reconciliation) and adds complexity.

Implications summary

• Write latency: synchronous > async; multi-master can lower local write latency vs single-master global writes.
• Read scalability: replicas (master-slave async) scale reads well; multi-master also scales reads.
• Failover: sync + single-master safer but slower; async requires careful promotion; multi-master more available but needs conflict handling.
• Conflict resolution: none needed for single-master synchronous; required for multi-master and eventual-consistency async models.

Recommendation for read-heavy CMS with infrequent writes

• Use master-slave (single-master write) with asynchronous replication to scale reads and keep low write latency. Optionally use semi-sync (ack from at least one replica) if you need stronger durability. Keep writes routed to master, serve public reads from replicas behind a CDN, and implement promotion automation + monitoring to handle failover without risking frequent conflicts.