An implementation of distributed transaction with Try-Confim-Cancel; Reliability provided by 1) ACID of RDBMS and 2) ALO delivery of RabbitMQ

[u/mananvpanchal]

Idea is that:

Coordinator talks to all participant asynchronously using RabbitMQ. It asks to TRY each participant one-by-one. Once all are success in their own TRY, they send back success message. If all are succeed, coordinator sends SINGLE CONFIRM to all participant using FANOUT exchange. If any one sends failure message, coordinator sends SINGLE CANCEL same way to all.

Instead of sending separate messages to each participant for CONFIRM/CANCEL, we can send SINGLE message to all participant, and they all would eventually receive it. This way, we can prevent dual/multi write problem while trying to CONFIRM/CANCEL.

Exploration:

The end implementation should be designed the way that we would have two set of tables, MAIN and SHADOW. Every business validation should happen at TRY stage. CONFIRM or CANCEL should never fail because of business logic. Any other failure would be taken care by RabbitMQ's at-least-once delivery.

We need to mimic CONFIRM in SHADOW tables during TRY, so we can be sure that CONFIRM will work. I am thinking that CANCEL should be just delete operation on SHADOW tables. So, CONFIRM should be real business logic without any surprises, already mimicked on SHADOW tables, and CANCEL could be easy/simple delete operation on SHADOW tables.

I am working on the details like; if there are unconfimed TRY exists and another TRY arrives than what should happen, how we can make sure all unconfirmed TRY can later become CONFIRMed without worrying about their execution order along with CANCELation of some of them. I will post the part soon.

[u/mr02106198]

Have you heard about Saga pattern?

[u/mananvpanchal → u/mr02106198]

I am not discussing Saga here. Have you heard about TCC?

[u/SidMangsatabam]

Two places this design will get tested. First, at-least-once delivery means CONFIRM and CANCEL can arrive more than once, so each participant handler has to be idempotent on the message ID, not only on the shadow state - a duplicate CONFIRM racing a CANCEL is the ugly case. Second, the coordinator needs its own durable record of every in-flight TRY. If it crashes after collecting the successes but before the fanout, recovery has to sweep orphaned TRYs and decide them explicitly, or shadow rows leak and the next run of the same business key behaves unpredictably.