How to handle multiple messages in a single database transaction

(Nathan Richards) #1

We have an endpoint that handles several types of messages (commands).

The processes sending the commands sometimes need to have a combination of these commands executed in a particular order and in a single database transaction (for data consistency reasons). If any of the commands fail, the transaction will be rolled back and subsequent commands aborted.

Rather than create a separate command for every unique combination of commands that need to be executed in a single transaction, I want to create a more general solution to the problem that allows for any combination of commands.

I’ve been reading the documentation on Unit of Work but I can’t see examples of the scenario I describe - how would you know when to start and end the transaction if the messages are separate?

I’m currently considering creating a special ‘composite’ command message, which contains a polymorphic list of inner commands. The message handler for the composite command would create the transaction, then forward the inner messages to the relevant message handlers (in order, and with the database transaction), and finally commit the transaction.

I have two questions about this:

  1. Am I correct in assuming the NSB Unit of Work does not solve this problem? If not, how would you use it to solve this problem?
  2. Assuming a composite command is the correct approach, should I ‘send to self’ the inner commands, or should I directly call the message handlers?

In case it affects the answer, here are the details of our set-up:

  • NServiceBus 6
  • MSMQ transport (no DTC)
  • SQL Server persistence
  • Single process for handling the commands (running on a Windows cluster for availability)
  • SQL Server database being updated as part of the message handling

(Szymon Pobiega) #2

Hi Nathan

Yes, you are correctly assuming the UoW won’t solve it for you. It is designed to solve the opposite problem i.e. how to execute multiple handlers for a single command and store the results atomically.

Regarding the composite command handling, in order to store the results atomically you need to call the inner-command handlers directly, not via a message to self. Messages sent locally are still messages. They are added at the end of the queue and they will be handled separately.

Can you also give some examples of these inner commands? Depending on what they are, using sagas might also be an option. In that case you would have a saga that handles all these inner commands. When a command arrives, the saga stores the command data in its internal state. Once all the commands arrive (you need to count them so that the saga knows that all the commands from a given batch arrived), the saga would execute the business logic according to the data accumulated in its state.

Hope it helps,

(Dennis van der Stelt) #3

The processes sending the commands sometimes need to have a combination of these commands executed in a particular order and in a single database transaction (for data consistency reasons)

You probably need to work on the problem I quoted. It would be really helpful to have more details on what it is you are trying to achieve. Not so much technically and with how many messages, etc, etc. But rather what the business problem is you are trying to solve. Perhaps, with our experience in message driven systems and so, we can come up with a different solution.

For example, it’s pretty complicated to have messages delivered in a specific order. You can do it, but it’s very, very slow and doesn’t scale at all. So it comes at great cost. It’s probably better, as @SzymonPobiega says, to use a saga. Just have the messages be delivered and processed in random order, and report back to some saga that will know when everything has completed. Then mark it as such and you should have consistent state.

(Dennis van der Stelt) #4

Also, if you’re interested, @nathan, we could have a quick conference call and have a talk about your issue. If you send an email to we can have a chat and see how to best solve this problem.

(Nathan Richards) #5

Thanks, that might help. I’ve sent an email to the support address. :slight_smile:

(Nathan Richards) #6

How do you get a reference to the message handler instance if you haven’t overridden the default container? I can’t see anything in the Containers documentation.

A saga at the command consumer end is an interesting idea, and could work for us I think. In order to support arbitrary groups of commands, each would need to be supplemented with metadata for the group, such as a group identifier, the message’s order within the group and the total number of messages (in the headers perhaps). The downside of this approach is mopping up sagas that were never completed (e.g. because one of the messages was never sent).

I’m not sure that that approach gives us much benefit however; both ends will have to deal with the commands as a single group, even if they are sent over the wire separately. It helps limit the message size, but that shouldn’t be an issue for our scenarios anyway.