Splitting Large Transactions to Ensure Distributed Consistency in Backend Systems

When a business operation requires both local MySQL writes and remote calls to third‑party systems, the process encounters distributed transaction consistency issues, and mature distributed transaction solutions are not always applicable.

Case Example : A financial reimbursement workflow involves three systems:

Document system – generates application data and financial vouchers (implemented in Java).

BPM – drives the workflow (purchased mature product such as Fanwei).

SAP – financial system that receives voucher data via API after audit.

The audit‑pass flow consists of saving business data and audit logs, calling BPM to approve, fetching the next reviewer, and, if no reviewer remains, generating the voucher and pushing it to SAP.

Risk Analysis : If the remote calls (steps 1 or 2) fail, MySQL transactions roll back and no data changes. However, if steps 1 and 2 succeed and step 3 fails, the MySQL transaction rolls back but the BPM side has already recorded an approval, leading to an inconsistent state where the user sees an error, the document system has no record, yet BPM shows the process moved forward.

Problem Root : MySQL transactions can only manage their own data and cannot control remote systems.

Solution Idea : Split the large transaction into multiple small transactions, each containing at most one remote write placed at the end of the method, and add a retry mechanism for possible failures.

Implementation steps:

Create a transaction_job table to store the essential data of each small transaction:

CREATE TABLE `transaction_job` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `type` varchar(255) NOT NULL COMMENT '任务类型',
  `data` varchar(255) NOT NULL COMMENT '任务数据',
  `error_message` varchar(255) DEFAULT NULL COMMENT '错误信息',
  `context` varchar(255) DEFAULT NULL COMMENT '任务上下文（主要是保存当前操作人）',
  `create_time` bigint(20) NOT NULL COMMENT '创建时间',
  `update_time` bigint(20) NOT NULL COMMENT '更新时间',
  `retry_times` int(11) NOT NULL DEFAULT '0' COMMENT '重试次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='事务任务表';

The table records the data needed to replay each small transaction and the operator context.

A scheduled task periodically scans unfinished rows in transaction_job and executes them using a strategy‑pattern interface that isolates framework code from business logic.

Each small transaction implements a separate strategy class; for example, the task that updates the next reviewer simply copies the original code.

Modify business code so that after the first small transaction it inserts a row into transaction_job to trigger the second small transaction later.

The concrete insertion code is encapsulated in transactionJobService.create (see the corresponding image).

Optimization : The delayed execution of later transactions can degrade user experience. By using Spring's TransactionSynchronizationManager.afterCommit() (or the utility method afterCommit() ), the job can be executed immediately after the surrounding transaction commits, eliminating the visible delay.

When a small transaction succeeds, the job status is set to "success", so the user perceives no latency.

Note : Adding a job may cause the scheduled scanner to pick it up immediately, leading to concurrent execution by the main thread and the scheduler. In practice, locking or a pre‑execution status check should be added.

Conclusion : Splitting a large distributed transaction into independent small transactions, persisting their state in a task table, and executing them via scheduled jobs or Spring's after‑commit hook provides a reliable way to handle consistency across MySQL and remote systems while keeping user‑visible latency low.