ACID and BASE: Choose Guarantees per Operation

ACID and BASE answer different questions

ACID describes properties of transactions. BASE is a looser design vocabulary associated with systems that accept temporary inconsistency to keep some operations available. They are not opposite database product categories: a system can use atomic transactions internally and asynchronously update a search index or remote replica.

The useful question is: which observation or update must be correct, and what can lag? A checkout needs a defensible stock reservation. A product's recommendation score may tolerate delayed updates. Both can exist in one service.

What the letters do—and do not—promise

The “C” here concerns valid state. In distributed consistency discussions, “consistency” can instead refer to what order or freshness readers observe. ACID consistency and linearizability are not interchangeable terms.

A stock reservation that survives a race

Suppose a product has one unit available and two buyers arrive together. A read followed by an unconditional decrement lets both buyers act on the same old value. In a transactional store, the application can instead reserve conditionally:

UPDATE inventory
SET available = available - 1
WHERE sku = :sku AND available >= 1
RETURNING available;

A returned row indicates that this statement reserved one unit. Zero rows means it did not. Insert the associated reservation or order in the same transaction and commit only if all required steps succeed. Use a unique operation ID so retrying after a lost response cannot reserve twice. A constraint preventing negative stock adds defense, but it does not replace tracking which order owns the reservation.

This example is database-side pseudocode with bound parameters. Exact concurrency and error behavior depend on the database. It illustrates an invariant enforced at the update, rather than a promise that the word “ACID” repairs arbitrary application logic.

Isolation levels need implementation context

Read Committed generally permits two statements in one transaction to observe different committed states. Repeatable Read has stronger protections, but the standard's minimum and a particular database's implementation can differ. PostgreSQL, for example, does not allow phantom reads at Repeatable Read, while serialization anomalies can still occur. Serializable execution prevents outcomes inconsistent with a serial order, sometimes by aborting transactions that the application must retry. PostgreSQL transaction isolation.

Consider a shared equipment room with two emergency kits. Policy requires at least one kit to remain on site. Two transactions each see two available kits, then each checks out a different kit. Row-level constraints on the individual kits can pass while the cross-row policy fails. This is the kind of write-skew problem that a stronger isolation level or an explicit common locking point must address.

Retry the entire transaction after a serialization failure, using fresh reads and a bounded backoff. Do not rerun an unrelated email or card charge every time the database retries. Record that external work through a separate repeatable workflow.

Eventual consistency needs an operational contract

A search index receiving product updates asynchronously may briefly show an old description or availability hint. Convergence requires updates to reach the relevant replicas and a rule for resolving concurrent changes; “eventually” is not a latency bound.

Suppose the product table commits version 42 but search still serves version 40. It may be acceptable to display the old description while the checkout rechecks authoritative stock. It is not acceptable to treat the stale index as permission to sell another unit.

Set a measurable freshness objective, such as 99% of indexed changes visible within an illustrative ten-second target, and monitor lag. Decide what happens when lag reaches ten minutes: show a freshness warning, route selected reads to the primary store, or temporarily disable an affected feature. An availability claim without a degradation policy is incomplete.

Choose guarantees per path

The label “NoSQL” does not imply missing transactions. DynamoDB documents transactional read and write APIs, with specific scope, isolation, and capacity behavior. Product APIs and their limits are more useful than a SQL-versus-NoSQL slogan. DynamoDB transactions.

For our shop, use a transaction to reserve inventory and create the order; publish the search and recommendation changes asynchronously from durable intent. Keep the customer's order confirmation tied to committed order state. Replication, backup, recovery, and external payment reconciliation remain separate design responsibilities.

A review should run the two-buyer race, a cross-row invariant conflict, a serialization retry, and a delayed projection. For every case, write down the allowed result before running it. That is a much sharper database-selection criterion than assuming ACID always means slow or BASE always means data loss.