Understanding Local Transactions, ACID, CAP/BASE Theories, and Distributed Transaction Solutions (Seata, 2PC, TCC, Saga)

Local Transaction

A transaction consists of a set of SQL statements and must satisfy the four ACID properties: Atomicity, Consistency, Isolation, and Durability.

ACID

Atomicity : All SQL statements in the transaction either succeed together or fail together.

Consistency : The database moves from one valid state to another; atomicity describes behavior, consistency describes the result.

Isolation : Transactions operate on their own data objects without affecting each other.

Durability : Once a transaction commits, its effects persist even after a crash.

Implementation in MySQL (InnoDB)

Isolation is achieved through lock mechanisms of varying granularity; atomicity, consistency, and durability are guaranteed by redo logs and undo logs.

Redo Log : Records changes after a dirty page is modified in the buffer pool; during a crash, the redo log can replay modifications to restore consistency.

Undo Log : Stores the before‑image of modified data, enabling rollback and supporting MVCC. It includes insert undo logs (deleted after commit) and update/delete undo logs (retained for MVCC).

Problem Introduction

CAP Theory

CAP states that a distributed system can at most provide two of the three guarantees: Consistency, Availability, and Partition tolerance. Since partition tolerance is inevitable, a trade‑off between consistency and availability is required.

Traditional DBMS like MySQL typically adopt a CA combination; in master‑slave architectures, read‑write separation sacrifices some consistency due to replication lag.

BASE Theory

Basic Availability : The system remains partially functional during failures, ensuring core services stay up.

Soft State : Intermediate states may exist without affecting overall availability.

Eventually Consistent : The system converges to a consistent state after a short period.

How to Solve

Scenario Example

System A calls System B’s refund service, updates its own refund status, then calls System C’s SMS service to notify the user.

Network unreliability can cause consistency issues among A, B, and C.

Local Tables

Design two tables: Refund Record and SMS Send Record , plus a compensation job.

Implementation Steps:

Add a refund record with status “processing”.

Call System B’s refund service.

Update the refund record status (success/failure).

If refund succeeds, add an SMS record with status “pending”.

Call System C’s SMS service.

Mark the SMS record as “sent”.

Refund Compensation Job : Query processing refund records and retry the refund service; on success, create and send the SMS as above.

SMS Compensation Job : Query pending SMS records and resend the SMS.

Notes:

Systems B and C must support idempotency via a UUID.

Temporary inconsistencies are acceptable as eventual consistency is ensured.

Transactional Messages

Transactional messages act like a two‑phase commit to guarantee eventual consistency across distributed systems; the sender cannot obtain the consumer’s processing result, making them suitable when the sender does not need immediate feedback.

Related Theories

Two‑Phase Commit (2PC)

2PC ensures all participants either commit or roll back together but suffers from blocking and single‑point‑of‑failure issues.

Three‑Phase Commit (3PC)

3PC adds a “CanCommit” phase to avoid blocking and introduces timeout mechanisms on participants to mitigate the coordinator’s single‑point‑of‑failure.

DTP Model

The Distributed Transaction Processing (DTP) model defines four components: Application Program (AP), Resource Manager (RM), Transaction Manager (TM), and Communication Resource Manager (CRM).

XA Specification

XA is an X/Open standard for distributed transactions, using 2PC to guarantee ACID across multiple resources.

TCC

Try‑Confirm‑Cancel (TCC) splits a global transaction into three phases: prepare (try), commit (confirm), and rollback (cancel), allowing custom logic for each branch.

Saga

Saga breaks a distributed transaction into a series of local transactions with compensating actions; it is asynchronous, high‑throughput, but cannot guarantee isolation.

Open‑Source Project: Seata

Seata is an open‑source distributed transaction solution supporting AT, TCC, Saga, and XA modes.

It consists of three modules: Transaction Coordinator (TC), Transaction Manager (TM), and Resource Manager (RM).

AT Mode : Non‑intrusive; Seata intercepts business SQL, records before/after images, and generates undo logs for automatic rollback.

TCC Mode : Requires developers to implement prepare, commit, and rollback logic for each branch.

Saga Mode : Based on a state‑machine engine; each state can define a forward service and a compensating service, with execution driven by events.

Seata’s workflow: TM starts a global transaction, registers resources with TC, TM ends the transaction (first phase), TC decides commit/rollback, and TC notifies all RMs for the second phase.