CODEWITHSUNDEEP
c#.netfunctional-programmingqueueintegration
Robin De Clercq
Robin De Clercq

Reputation: 27

Sending data to external API add/update/delete with snapshots of data .NET

I'm facing a challenge with processing updates/deletes asynchronously for a record that undergoes multiple changes at the same time.

Scenario:

Whenever an add/update/delete occurs in my system, I enqueue a snapshot of the data in a "queue" table. Another service then processes these records synchronously and grouped together. The problem arises when multiple records for the same entity are queued at the same time.

For example, I need to create a meeting in Teamleader for deal X. My queue has the following 3 records:

Update – Order X planned at 11h. Update – Order X planned at 12h. Delete – Order X is deleted.

The records are created at the same time, so my queued JSON data does not reflect changes from earlier records in the queue.

Current Processing:

Update (11h) – Creates a meeting for deal X (since no external meeting ID is present in the paylaod of the order). I store the meeting ID on the order after creation.

Update (12h) – This also creates a new meeting because the external meeting reference (from the first update) isn’t reflected in the queue data yet but it has been saved in the db.

Delete – The external reference for the meeting ID isn't set, so I don’t know which meeting to delete. Deleting all meetings for the deal is not an option, as other meetings may exist that shouldn't be deleted.

Potential Solutions:

Fetch latest data from the database during processing: After each queue record is processed, I save the changes. This works for updates, but on delete, the record is already gone, so I can't retrieve the external meeting ID to delete it from Teamleader.

Smarter grouping of queue records: I could group related queue records (add/update/delete) and process them in a way that handles multiple updates more intelligently, e.g., ignoring certain updates if they're overridden by subsequent actions like a delete.

Does anyone have suggestions on how to best handle this, especially when it comes to managing deletes and multiple updates for the same record?

Because I can imagine this is an general problem when syncing data with external systems.

Thanks in advance!

I already tried fetching the order from the database, so whenever I have an update, this will work with the previous set ID. But I still have a problem for a delete.

Upvotes: 2

Views: 45

Answers (1)

Mark Seemann
Mark Seemann

Reputation: 233277

As a general observation, it sounds like you're in the process of (re)discovering various patterns related to distributed processing, transaction management, and lock-free transactions.

Overall, when distributing transactions among several systems, you should strongly consider not relying on database IDs and instead use universal IDs. Thus, when you create Order X, give it a GUID and save that GUID together with the record. You can still have the database use a clustered integer index, if you need to for performance reasons, but that primary key should be regarded as in implementation detail. Each Order gets a unique GUID even before it's created, and that GUID travels around on the queue as well.

When you create the meetings, you associate them with the order GUIDs, not the database IDs. This means that you can always uniquely identify each kind of association, even if you've deleted the actual database record.

I'd also recommend that you look into CQRS-style architectures, since they tend to be particularly well-suited for this kind of high-contention scenario. Particularly, you may find the notion of task-based messages useful. Instead of CREATE, UPDATE, etc. messages can capture domain-specific intent, such as Create meeting, Move meeting, etc. This enables you to make message processing more sophisticated, because the message type tells you what the goal of the Command is, rather than what the mechanics might be.

Upvotes: 0

Related Questions