Transaction support

Component: SQL Server Transport
NuGet Package NServiceBus.SqlServer (4.x)
Target NServiceBus Version: 7.x

The SQL Server transport supports the following transport transaction modes:

  • Transaction scope (distributed transaction)
  • Transport transaction - Send atomic with receive
  • Transport transaction - receive only
  • Unreliable (transactions disabled)

TransactionScope mode is particularly useful as it enables exactly once message processing with distributed transactions. However when transport, persistence, and business data are all stored in a single SQL Server catalog, it is possible to achieve exactly-once message delivery without distributed transactions. For more details, refer to the SQL Server native integration sample.

Exactly once message processing without distributed transactions can be achieved with any transport using the Outbox feature. It requires business and persistence data to share the storage mechanism but does not put any requirements on transport data storage.

Transaction scope (distributed transaction)

Transaction scope mode is not supported in .NET Core 2.0

The default transaction mode, i.e. transaction scope, is currently not supported in .NET Core 2.0. Any attempt to use it will result in the following exception:

NServiceBus.Transport.SQLServer.MessagePump|Sql receive operation failed
System.NotSupportedException: Enlisting in Ambient transactions is not supported.

To resolve this, choose one of the other transaction modes. Support for TransactionScope is expected to be added in future releases of .NET Core.

In this mode, the ambient transaction is started before receiving the message. The transaction encompasses all stages of processing including user data access and saga data access.

If either the configured NServiceBus persistence mechanism or the user data access also support transactions via TransactionScope, the ambient transaction is escalated to a distributed one via the Distributed Transaction Coordinator (DTC).

If the persistence mechanisms use SQL Server 2008 or later as an underlying data store and the connection string configured for the SQL Server transport and the persistence is the same, there will be no DTC escalation as SQL Server is able to handle multiple non-overlapping connections via a local transaction.
Microsoft SQL Server does not support DTC transactions in all deployment models (such as database mirroring or Always On configurations) and support differs between versions of SQL Server. See Transactions - Always On availability groups and Database Mirroring for more information.

See also a sample covering this mode of operation using either SQL Persistence or NHibernate Persistence.

Native transactions

In this mode, the message is received inside a native ADO.NET transaction

There are two available options within native transaction level:

  • ReceiveOnly - An input message is received using native transaction. The transaction is committed only when message processing succeeds.
This transaction is not shared outside of the message receiver. That means there is a possibility of persistent side-effects when processing fails, i.e. ghost messages might occur.
  • SendsAtomicWithReceive - This mode is similar to the ReceiveOnly, but transaction is shared with sending operations. That means the message receive operation and any send or publish operations are committed atomically.

Unreliable (transactions disabled)

In this mode, when a message is received it is immediately removed from the input queue. If processing fails the message is lost because the operation cannot be rolled back. Any other operation that is performed when processing the message is executed without a transaction and cannot be rolled back. This can lead to undesired side effects when message processing fails part way through.

User provided transactions

When sending messages outside of a handler context it is possible to provide custom SqlConnection and SqlTransaction instances that will be used to perform transport operations. This enables sending two or more message in a single, atomic transaction or share the same transaction between transport and relational data store.

4.1 - N NServiceBus.SqlServer
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (var transaction = connection.BeginTransaction())
    {
        var sqlCommand = new SqlCommand(commandText, connection, transaction);

        //Exectute SQL statement
        sqlCommand.ExecuteNonQuery();

        var options = new SendOptions();

        options.UseCustomSqlConnectionAndTransaction(connection, transaction);

        //Send bunch of messages using the same transaction
        await session.Send(new Message(), options);
        await session.Send(new Message(), options);
        await session.Send(new Message(), options);

        transaction.Commit();
    }
}

Last modified