Distributed Transaction Handling on single Database (SpringBoot, JMS, JPA)

Question

I have a setup which I can scale by adding nodes listening to a JMS Queue. All resources (nodes) write into the same database and work together on one or many "Jobs" in parallel.

Job A --> Queue1 --> Sub-Task A.1 --> Queue 2 --> Resource 1
                     Sub-Task A.2 --> Queue 2 --> Resource 2
                     Sub-Task A.n --> Queue 2 --> Resource n


Job B --> Queue1 --> Sub-Task B.1 --> Queue 2 --> Resource 1
                     Sub-Task B.2 --> Queue 2 --> Resource 3
                     Sub-Task B.n --> Queue 2 --> Resource n

Every resource is performing CRUD Operations on the same database and tables, currently in their own JMS and JPA transaction.

In case an error occurs in Sub-Task A.n, I need to rollback all changes done previously for Job A, but not the changes done for Job B. Even if the Sub-Tasks are related by their "Job", during execution they are independent from each other and can be performed in parallel.

I am looking for a framework or pattern, that allows the rollback of all database transaction done within Job A.

I am using a SpringBoot CommandLine application (no Webserver involved) with Hibernate/JPA and ActiveMQ Artemis.

Answer 1

With each task part of the same job running in its own thread or even a distinct JVM there is no way to out of the box rollback what task A.1 has done when task A.2 failed. You will have to manually implement this depending of your specific business rules. The way I would do it would be to have a rollback table with a structure like below (not exactly accurate but intended to give you an idea)

CREATE TABLE IF NOT EXISTS rollback_instructions(
    id BIGSERIALL PRIMARY KEY,
    jms_message_id VARCHAR(64),
    job_id VARCHAR(10),
    task_id INTEGER,
    rollback_sequence NUMBER,
    rollback_command CLOB,
    scheduled boolean,
    executed boolean
);

Each task should be responsible for populating the rollback commands for the database changes they done in the sequence they done it. If the task commits then the rollback_instruction commits. If something goes wrong with a task will mark all instructions from that jms_message_id as scheduled and commit. Then you have a scheduled job executing all scheduled rollback instructions in the reverse of their sequence. This is not an easy approach, you quite likely will need some locking mechanisms in place, once a task fails the other ones should stop but is doable.

Maybe a better approach would be to have a duplicate schema and replicate the data once a job is marked as completed.

Anyway, a complete solution will depends on lots of your own use cases.

Hope these ideas gave you a starting pont.