From ACID to CAP and BASE: Mastering Consistency, Availability, and Distributed Transactions

ACID Basics

Atomicity : All operations in a transaction must either complete fully or not at all.

Consistency : The transaction must preserve data integrity.

Isolation : Transactions run as if they are the only ones accessing the database.

Durability : Once a transaction commits, its changes survive crashes or power loss.

Example: If account A has 500 ¥ and account B has 300 ¥, a transaction that transfers 50 ¥ from A to B will always leave A with 450 ¥ and B with 350 ¥, regardless of concurrency or failures.

How SQL Server Guarantees ACID

SQL Server stores data in a data file and a log file. Every write operation first records a redo log entry, flushes the log to disk, and only then modifies the data file. If a power loss occurs, the engine uses the log to undo incomplete work or redo committed work, ensuring strong consistency.

CAP Theorem

CAP states that a distributed system can simultaneously provide at most two of the following three guarantees:

Consistency : All nodes see the same data at the same time.

Availability : Every request receives a response, even if some nodes fail.

Partition Tolerance : The system continues to operate despite network partitions.

Because a partition can always occur, designers must choose between consistency and availability for a given workload.

BASE Model

BASE relaxes the strictness of ACID for distributed systems:

Basically Available : The system remains operational.

Soft state : State may change without immediate consistency.

Eventual consistency : Data will become consistent over time.

BASE is a practical extension of CAP, acknowledging that strong consistency is often unattainable in large‑scale environments.

Distributed Transaction Importance

With micro‑service architectures, distributed transactions become unavoidable; any system that spans multiple services must handle cross‑service data consistency.

Distributed Transaction Solutions

Two‑Phase Commit (2PC)

Advantages : Provides strong consistency, suitable for critical domains.

Disadvantages : Complex to implement, reduces availability, and incurs high latency, making it unsuitable for high‑throughput scenarios.

Compensating Transaction (TCC)

Advantages : Simpler than 2PC, but consistency is weaker.

Disadvantages : Requires explicit confirm and cancel APIs for each business operation, increasing development effort.

Local Message Table (Asynchronous Assurance)

Implements eventual consistency by storing messages in a table, then delivering them via a message broker (e.g., RabbitMQ). This approach avoids the blocking nature of 2PC and the complexity of TCC.

Typical flow:

Pre‑store a message with status WAIT_SEND.

After the business transaction succeeds, update the status to SENDING and let the message service push it to the broker.

If the broker acknowledges receipt, mark the message as SENT. If not, retry with exponential back‑off.

A separate “message status confirmation” subsystem periodically checks messages stuck in WAIT_SEND or SENDING, queries the business service for the real transaction status, and decides whether to resend or discard.

A “message recovery” subsystem re‑sends messages that remain in SENDING beyond a configurable timeout.

Code example for RabbitMQ confirmation callbacks:

connectionFactory.setChannelCacheSize(100);

rabbitTemplate.setConfirmCallback((correlationData, ack, cause) -> {
    if (!ack) {
        // retry logic
    } else {
        // mark message as sent
    }
});

rabbitTemplate.setReturnCallback((message, replyCode, replyText, exchange, routingKey) -> {
    try { Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); }
    log.info("send message failed: " + replyCode + " " + replyText);
    // retry after delay
});

Rigid vs. Flexible Transactions

Rigid (ACID‑compliant) transactions operate at the data‑resource layer (e.g., XA 2PC). They guarantee strong consistency but lock resources globally, leading to low throughput.

Flexible (BASE‑compliant) transactions operate at the application layer, using patterns such as reliable async messaging, max‑effort notification, or TCC. They achieve higher availability and scalability at the cost of eventual consistency.

Idempotency Techniques

Use a unique request identifier and reject duplicate processing.

Maintain a state machine for each business entity and skip steps that have already reached a terminal state.

Practical Recommendations

Design message tables with partitioning or sharding to handle growth.

Prefer asynchronous communication (e.g., Dubbo async) when reporting business status to the message service to avoid blocking the original transaction.

Consider distributed scheduling frameworks for the periodic subsystems (status confirmation, recovery, management).

When high real‑time consistency is required (e.g., order payment), use TCC; otherwise, use async reliable messaging or max‑effort notification.

By selecting the appropriate model—rigid ACID for critical, low‑latency operations, or flexible BASE‑based patterns for high‑availability, large‑scale services—architects can balance consistency, availability, and partition tolerance according to business needs.