Do SQL Server Database Commits have characteristics like a web service call or any remote call for that matter?

1

Hello,

I took udi’s course and one thing he talked about was that with an web api call there can be a situation in which you send the request but never got a response back YET the web api did indeed complete the request. Does the same hold true for sql server transactions? For Example, in the below code, is it possible for the commit call to happen in the database but my handler never gets a response back ( some connection exception )? I’m Assuming that yes this can happen and essentially any call across the network can exhibit this type of behavior

public class MyHandler :
    IHandleMessages<AddOrderLine>
{
    IMyOrm orm;

    public NonIdempotentHandler(IMyOrm orm)
    {
        this.orm = orm;
    }

    public Task Handle(AddOrderLine message, IMessageHandlerContext context)
    {
        using (var session = orm.OpenSession())
        {
            var order = session.Get(message.OrderId);
            order.AddLine(message.Product, message.Quantity);
            session.Commit(); // <-- here
        }
        return Task.CompletedTask;
    }
}
2

Hi

Yes, you are right. Any remote call can fail in this manner. In your example the database can commit your transaction but the TCP connection to it might be broken before you get the response. In that case the database driver will throw an exception that will bubble up to your handler and make it fail. The AddOrderLine message will go back to the queue and will be picked up again.

This time the handler will see a modified database state so in your case you will add the same order line twice. You can prevent this by adding some business logic to your handler to check the state or the order before modifying it. As an example, you can make message Id (a unique ID that NServiceBus associates with each message) part of the OrderLine object. This way you can check if you processed a given message by looking for already existing OrderLine with given message Id. You can learn more about this approach in exercise 7 of my consistent messaging workshop or here in the video.

That said, NServiceBus supports three ways of automatically ensuring exactly-once message processing so that you don’t need to modify your business logic:

  • Using one transactional store for both data and messages e.g. when using SQL Server transport
  • Using distributed transactions to ensure atomic store and send e.g. when using MSMQ transport and SQL Server database as data storage. Distributed Transaction Coordinator (DTC) service needs to be enabled and configured for it to work
  • Using the Outbox pattern that automatically keeps track of processed messages in a special table in your application data store

You can find more information about accessing data in NServiceBus handler here. If you want to read more about the problems and solutions related to ensuring exactly-once message processing take a look at the blog run by @tmasternak and myself.