System design deep dive
ACID vs BASE: deep dive
ACID and BASE describe two philosophies for keeping data correct: ACID guarantees each transaction is all-or-nothing and isolated, while BASE accepts temporary inconsistency in exchange for availability and scale. Good designs pick the right model for each piece of data rather than one for everything.
What happens inside an ACID transaction, step by step
- A transfer of 500 rupees from account A to account B starts with
BEGIN. - The database takes row locks (or creates new row versions under MVCC) for A and B, so concurrent transactions cannot interleave unsafely.
- It debits A and credits B. A
CHECK (balance >= 0)constraint is evaluated; if A would go negative, the whole transaction is rejected. - Before acknowledging
COMMIT, the database appends the changes to its write-ahead log and flushes it to disk with fsync. This is what durability means: a crash one millisecond later cannot lose the transfer. - On commit, both changes become visible together. If the server crashed midway, recovery replays or discards log records so you never see a debit without its credit.
What BASE looks like in practice
In a BASE system such as Cassandra or a Dynamo-style store, a write is accepted by whichever replicas are reachable and propagated to the rest in the background. The system is basically available because it answers even when some replicas are down, keeps soft state because replicas may temporarily disagree, and is eventually consistent because anti-entropy repair, read repair, and hinted handoff bring them back into agreement. Multi-step business operations are not wrapped in one transaction; they become a saga of messages with compensating actions if a later step fails.
Isolation levels and the anomalies they allow
| Isolation level | Dirty read | Non-repeatable read | Phantom read | Notes |
|---|---|---|---|---|
| Read uncommitted | Possible | Possible | Possible | Rarely used |
| Read committed | Prevented | Possible | Possible | Default in Postgres and Oracle |
| Repeatable read | Prevented | Prevented | Possible in the standard (Postgres and InnoDB largely prevent it) | Default in MySQL InnoDB |
| Serializable | Prevented | Prevented | Prevented | Safest; more aborts or locking |
A subtle anomaly worth naming is write skew: two doctors each check that at least one other doctor is on call, then both go off call. Each transaction saw a valid state, but together they broke the rule. Only serializable isolation, or explicit locking with SELECT ... FOR UPDATE, prevents it.
When to use each model
| Data | Model | Reason |
|---|---|---|
| Payments, ledgers, wallet balances | ACID | Every rupee must be accounted for exactly once |
| Orders and inventory reservation | ACID or saga with strict checks | Overselling has a real cost |
| User profiles, settings | Either | Low write contention; ACID is simple if it fits on one node |
| Likes, view counts, feeds | BASE | Huge write volume, small errors are invisible |
| Sensor and event telemetry | BASE | Append-only, availability matters more than exactness |
NewSQL databases such as Spanner, CockroachDB, and YugabyteDB blur the line by offering serializable transactions across shards using consensus replication, at the price of higher write latency, especially across regions.
Common mistakes in interviews
- Equating ACID with SQL and BASE with NoSQL. MongoDB supports multi-document transactions, and a misconfigured SQL setup with async replicas can serve stale reads.
- Mixing up the C in ACID (constraints hold after each transaction) with the C in CAP (linearizable reads).
- Assuming the default isolation level prevents all race conditions; read committed allows lost updates unless you lock or use atomic updates like
SET balance = balance - 500. - Choosing BASE for money because it scales, without describing reconciliation.
Interview questions
›How does a database guarantee atomicity if it crashes mid-transaction?
Changes are recorded in a write-ahead log before data pages are modified. On restart, the recovery process redoes committed transactions whose pages were not yet written and undoes uncommitted ones. The log is the source of truth, so the final state reflects only whole transactions.
›What is MVCC?
Multi-version concurrency control keeps several versions of a row so readers see a consistent snapshot without blocking writers. Each transaction reads the versions committed before its snapshot. Postgres and InnoDB both use it, cleaning up old versions in the background.
›How do you implement a transaction across two microservices?
Two-phase commit is possible but blocks if the coordinator fails, so most systems use sagas: each service commits locally and emits an event, and failures trigger compensating actions. The transactional outbox ensures events are published only when the local commit succeeds.
›What is a lost update and how do you prevent it?
Two transactions read the same value, both modify it, and the second write silently overwrites the first. Prevent it with atomic operations, row locks via SELECT ... FOR UPDATE, or optimistic concurrency with a version column that makes the second update fail and retry.
›Can a BASE system ever give strong guarantees?
Yes, per operation. Quorum reads and writes, lightweight transactions (Cassandra uses Paxos for compare-and-set), or conditional writes in DynamoDB give strong guarantees for specific operations, while the rest of the workload stays eventually consistent and fast.