Designing Distributed Transaction Solutions for Financial Reimbursement Workflows

When a business operation requires both local MySQL writes and remote calls to third‑party systems (such as BPM and SAP), traditional distributed transaction solutions may not be applicable, leading to consistency problems.

The example focuses on a financial reimbursement workflow that involves three systems: a document system that stores voucher data, a BPM system that manages approval processes, and an SAP system that receives finalized voucher information.

The "approval passed" flow consists of saving business data and audit logs, invoking BPM APIs to approve and fetch the next reviewer, and finally generating a voucher and pushing it to SAP if no further reviewers exist.

Risk analysis shows that if the remote call in step 3 fails after the local MySQL transaction commits, the document system loses its data while BPM already marks the process as approved, causing an inconsistent state visible to users.

The root cause is that MySQL transactions cannot control remote systems. The proposed solution is to "拆" (split) the large transaction into smaller, independent transactions, each containing at most one remote write, placed at the end of the method, and equipped with retry mechanisms.

Implementation steps:

Create a transaction_job table to store the essential data of each small transaction, including type, data, error message, context (operator), timestamps, and retry count.

Use a scheduled task to scan the transaction_job table for unfinished jobs and execute them via a strategy‑pattern interface, separating framework logic from business logic.

Modify business code so that after the first small transaction, a new entry is inserted into transaction_job to trigger the second transaction later.

Sample DDL (kept intact):

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='事务任务表';

To reduce latency introduced by the scheduled jobs, Spring's TransactionSynchronizationManager.afterCommit() (or a similar utility) can be used to execute the task immediately after the surrounding transaction commits, making the user experience appear instantaneous.

Care must be taken to avoid race conditions where both the main thread and the scheduled task pick up the same job; locking or status checks are recommended.

The article concludes that while only the core idea is presented, real projects would include additional features and will be updated over time.