Database Design • ACID vs BASEEasy⏱️ ~3 min
ACID Properties: Correctness Through Coordination
ACID databases prioritize transactional correctness by enforcing four guarantees: Atomicity (all or nothing commits), Consistency (invariants always hold), Isolation (concurrent transactions don't interfere), and Durability (committed data survives crashes). These properties require coordination mechanisms like locks, consensus protocols, or synchronous replication, which fundamentally add latency to every write operation.
The performance cost is tangible. Amazon Aurora commits by writing to a distributed log replicated across 6 replicas in 3 Availability Zones (AZs), waiting for 4 of 6 acknowledgments. This quorum write adds one intra region Round Trip Time (RTT) plus disk flush latency, resulting in 10 to 20 ms P50 commit times and 20 to 40 ms P99 for small transactions. Cross region ACID systems like Google Spanner pay even steeper costs: 100 to 200+ ms for cross continent writes due to Wide Area Network (WAN) RTTs and two phase commit over Paxos groups.
The tradeoff is availability versus correctness. ACID systems can become unavailable during network partitions if they cannot reach quorum. Aurora targets 10s of seconds failover with under 30 second Recovery Time Objective (RTO), but during that window writes are blocked. This coordination overhead is acceptable when correctness is non negotiable: money transfers, inventory decrements at checkout, ledgers, and entitlement checks all require ACID to prevent double spend or lost updates.
Real systems often use ACID within boundaries where coordination is cheap (single shard, single region) and relax guarantees across boundaries. Amazon uses Aurora or Relational Database Service (RDS) for transactional workloads while employing DynamoDB for high scale eventual consistency workloads, accepting the mental model shift between services.
💡 Key Takeaways
•Aurora commits require 4 of 6 replica acknowledgments across 3 AZs, adding 10 to 20 ms P50 and 20 to 40 ms P99 latency per transaction within a region
•Cross region ACID via Spanner costs 100 to 200+ ms due to WAN RTT and global consensus, making it unsuitable for latency sensitive user facing writes
•ACID systems block writes during partitions when quorum is unavailable, trading availability for correctness guarantees (typical failover is 10 to 30 seconds)
•Multi Version Concurrency Control (MVCC) reduces read write contention but allows write skew anomalies unless using serializable isolation, requiring careful isolation level selection per transaction
•Two phase locking ensures serializability but introduces deadlock risk, requiring deadlock detection and timeout tuning as transaction complexity grows
📌 Examples
Money transfer requiring ACID: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; ensures both updates happen or neither, preventing lost money
Aurora failover scenario: Primary fails at 10:00:00, cluster detects failure by 10:00:05, promotes replica to writer by 10:00:15, DNS updates by 10:00:20, total RTO under 30 seconds with no data loss due to quorum replication
Inventory decrement at Amazon checkout uses ACID to prevent overselling: UPDATE inventory SET count = count - 1 WHERE product_id = 123 AND count > 0; if concurrent checkouts happen, one succeeds and others fail atomically