Reduce duplicates in outbox pattern in event driven systems

Question

Trying to implement the outbox pattern for an event driven system. The outbox pattern in a nutshell is a way to ensure system events are sent to the event log/queue/bus at least once (using the term event bus loosely here):

a separate process that periodically checks the contents of the Outbox and processes the messages. After processing each message, the message should be marked as processed to avoid resending.

My concern is I want to reduce the chance of duplicates because:

it is possible that we will not be able to mark the message as processed due to communication error with Outbox.

In this case when connection with Outbox is recovered, the same message will be sent again.

The proposed solution is idempotency which is fine with me but does it make sense to at least improve the message relay and reduce chance of duplicates? Are there patterns that exist to improve the message relay? If none, I'm thinking of the following:

Use 2 tables:

• OutboxTable (stores messages you want to send to the event bus)
• PublisherTable (stores messages already sent to the event bus)

Pseudocode for worker that reads the OutboxTable 24/7:

loop through each message...

if item is currently processing (by other workers)
    - skip

if item is marked as "published" in the PublisherTable (meaning a previous worker was able to publish the event but wasn't able to "confirm" it to the OutboxTable for whatever reason)
    - mark item as "published" in OutboxTable
    - skip
    
if item is was never processed or was processed but wasn't published
    - mark item as "processing" in PublisherTable  (so other workers can skip it)
    - publish/send item to the event bus 
    - mark item as "published" in PublisherTable
    - mark item as "published" in OutboxTable

(Each ongoing process has a defined timeout that other workers use to skip or process the item).

What I gather here is that I essentially made my own mini message queue just to achieve this. But since there's no way to make an atomic transaction between a DB and a real message queue, this approach kind of make sense.

So my question is, are there patterns similar to what I'm trying to do?

Answer 1

I have never seen any pattern or variation that wasn't just a shell game.

Fundamentally, you have two writes that you are trying to commit

• An update to the event bus (really, an update to the durable store of the event bus)
• An history in your own durable store

Because these writes are fundamentally using two different locks, there's always going to be some risk that your process exits before completing the second unit of work.

Welcome to the laws of physics.

Now, if the durable store for the event bus is your durable store, then you may be able to avoid some kinds of problems, because the transaction semantics of a single lock are all or nothing.

But if the stores are separate, then you are reduced to figuring out how to spend money to make the network more reliable, how to spend money to make your processes more reliable, and so on.