Data Migration & Distributed Transactions: XA, BASE, TCC, AT Demystified

Preface

After vertical or horizontal data partitioning, capacity and performance issues are solved, but data migration and consistency become new challenges. Migration must be fast, smooth, and ideally non‑downtime, with post‑migration data integrity verification. Consistency decisions depend on business needs: whether to introduce distributed transactions, and if so, whether to use XA or BASE‑based flexible transactions.

Data Migration

Data migration is a fault‑prone step; it requires careful planning to minimize system downtime and ensure accurate, rapid transfer, especially when dealing with heterogeneous data structures.

Full Migration

System downtime.

Database migration and data consistency verification.

System upgrade to connect to the new database.

Drawbacks: Requires complete system shutdown, long migration time, and greater impact on business, particularly for heterogeneous data that needs custom processing.

Full + Incremental Migration

This method relies on the data creation timestamp.

Synchronize data up to a recent timestamp.

Announce system upgrade and maintenance.

Synchronize recent changes.

Upgrade the system to use the new database.

Compared with full migration, this approach greatly reduces downtime and business impact.

Binlog + Full + Incremental Migration

Uses binlog parsing and reconstruction to replicate data. It typically requires middleware tools that support multithreading, resumable transfers, and automatic scaling.

Common tools: Canal, ShardingSphere‑scaling.

Distributed Transactions

XA Distributed Transaction

XA is a protocol supported by the database itself, providing strong consistency.

Components:

Application Program (AP): defines transaction boundaries.

Resource Manager (RM): e.g., database, file system.

Transaction Manager (TM): assigns transaction IDs, monitors progress, handles commit/rollback.

XA commands:

xa_start – start or resume a transaction branch.

xa_end – detach the thread from the branch.

xa_prepare – ask RM if ready to commit.

xa_commit – instruct RM to commit.

xa_rollback – instruct RM to roll back.

xa_recover – recover XA transactions.

MySQL supports XA transactions for InnoDB starting from version 5.0.3.

Issues with XA:

Synchronization blocking leads to poor performance, especially with SERIALIZABLE isolation.

Single point of failure at the TM.

Potential data inconsistency due to network failures during the two‑phase commit.

Flexible Transactions (BASE)

BASE emphasizes basic availability, soft state, and eventual consistency, allowing higher throughput by relaxing strong consistency requirements.

Core principles:

Basically Available – participants may not be simultaneously online.

Soft State – system state can change over time without immediate consistency.

Eventually Consistent – final state becomes consistent via mechanisms such as messaging.

Common patterns:

TCC (Try‑Confirm‑Cancel): explicit compensation.

AT (Automatic Transaction): auto‑generated reverse SQL for compensation.

TCC Introduction

TCC splits each service operation into two phases. The Try phase checks and reserves resources; the Confirm phase executes the business logic using reserved resources and must be idempotent; the Cancel phase releases resources and also must be idempotent.

Key considerations: allow empty rollback, enforce cancel after try, ensure idempotency.

AT Mode

AT implements a two‑phase commit with automatically generated reverse SQL to roll back data when exceptions occur.

Seata’s support for AT:

First phase records business data and rollback log in the same local transaction, releasing locks and resources.

Second phase performs asynchronous commit; rollback uses the recorded log for compensation.

Transaction Characteristics under Flexible Transactions

Atomicity – generally guaranteed.

Consistency – temporary inconsistencies may exist, but eventual consistency is achieved.

Isolation – dirty reads can occur.

Durability – same as local transactions; committed data is persisted.

Summary

Distributed transactions aim to solve data consistency problems. XA provides strong consistency but suffers from low throughput, making it unsuitable for high‑concurrency scenarios. Flexible transactions sacrifice strong consistency, achieving eventual consistency through compensation mechanisms such as retries, scheduling, or manual intervention.