Asynchronous Flow‑Log Approach for Transaction Consistency in Sharded Databases

Overview

As data volume grows in a database, the cost of CRUD operations on large tables increases, so large tables are often split into multiple tables across several databases.

After sharding, it is also necessary to guarantee transaction consistency between the main database and the shards. This article introduces a solution based on asynchronous assurance using non‑transactional messages.

The approach creates a flow (log) table in the primary database, mapping each business operation to a flow record. When the overall transaction finishes (the flow record is inserted), the flow is processed later to achieve eventual consistency.

Problems to Solve

2.1 Original Transaction

After sharding, new tables reside in another database, and ensuring transactional integrity between the primary and shard databases is essential.

Solution: create a flow table in the primary database; each business operation is represented by a flow record. After the large transaction completes, the flow is executed by other means to guarantee eventual consistency.

2.2 Flow

A flow can be understood as a transaction message.

When extracting business logic into a flow, the following must be considered:

Delayed processing: flows are processed asynchronously, so any real‑time dependency must be examined.

Unordered execution: later‑generated flows may be executed earlier without causing issues.

Final success: each inserted flow must eventually succeed.

Therefore, when extracting flows:

Keep flow processing as simple as possible.

Minimize dependencies of flow processing.

Ensure the flow has no real‑time dependency in the business logic.

2.3 Flow Processor

The processor must handle flows quickly while guaranteeing their eventual success.

Scenario: if a flow processing fails and the processor waits for that flow to succeed before proceeding, subsequent flows are blocked, potentially causing system-wide issues.

Thus, the processor defines two tasks:

First task – fast flow processing: execute the flow at the highest speed; failures do not block subsequent flows.

Second task – flow verification: sequentially check flow records, ensure all succeed, retry on failure, and raise alerts after multiple unsuccessful retries.

2.4 Flow Completion

Since the flow table resides in the primary database, after the flow is processed the operation on the shard must also update the flow status, which raises a cross‑database transaction issue.

Solution: create a flow table in the shard database. After processing, instead of updating the old table, insert a record into the shard’s flow table.

Benefits:

Unique index on the flow prevents duplicate execution; duplicate inserts cause a rollback, providing idempotency.

Checking whether a primary flow exists in the shard determines if the flow has completed.

Basic Framework of the Flow Processor

The processor contains no business‑specific logic; its core functions are:

Notify the business side when and which flow should be processed.

Validate the successful execution of the flow.

Note: the processor does not know the business meaning of a flow; the business system must interpret and execute the corresponding logic.

3.1 Flow Execution Task

The execution task scans pending flows and notifies the business system which flow to execute.

Illustration:

3.2 Flow Verification Task

The verification task compares flow records in the primary and shard databases, notifies the business system to retry unsuccessful flows, and raises alerts after repeated failures.

Illustration:

Why Not Use Transactional Messages

In a legacy project transformation (e.g., internet finance), message loss or failure is unacceptable, so transactional messages are avoided for two main reasons:

Introducing a message queue adds system complexity and requires additional logic to handle communication failures.

Transactional messages require manual commit/rollback, which conflicts with Spring’s transaction propagation; determining where to commit the message (in nested transactions) becomes problematic.

Source: www.cnblogs.com/lizo/p/8035036.html