Transaction Modes

, ,
1

Hi

We’ve been using NSB for a number of years with MSMQ transport and SQL persistence (MSSQL), with additional business data stored in that same SQL catalog. We’ve got a bunch of legacy code that uses NHibernate for modifications of that business data that we are happy with.

We are in the process of removing the MSMQ dependency for obvious reasons, and have chosen to go with SQL transport and put the queues into the one existing catalog.

One of the benefits of this would be removal of the dependency on MSDTC, however we’re having some trouble determining the right transaction mode to use to achieve that, in particular, getting NHibernate to play nicely with an existing connection.

I had initially assumed that we should use TransportTransactionMode.SendsAtomicWithReceive and WrapHandlersInATransactionScope, and we’ve setup our NHibernate code so we can open a session with an existing connection that we access via ISqlStorageSession (and we’ve followed the guidance around identical connection strings for transport and persistence for pooling). Unfortunately this doesn’t work; opening the NHibernate session causes Cannot enlist in the transaction because a local transaction is in progress on the connection. In the samples Entity Framework integration with SQL Persistence • Sql Persistence Samples • Particular Docs and SQL Server Transport and SQL Persistence • NServiceBus Samples • Particular Docs EF seems to provide the ability provide an existing connection and transaction, which is not something that NHibernate supports that I’m aware of.

Because we are just using the one transactional resource, I think we can still avoid MSDTC in TransactionScope mode because the transaction never needs to escalate. Only thing is that the MSSQL transaction has been started as DISTRIBUTED.

I’m looking for any advice on this situation and recommendations on path forward, even if that is just stick with TransactionScope.

Thanks in advance
Simon

2

Have you tested with the following sample?

What versions of NServiceBus and other NServiceBus.* packages are you using? Are you on the latest versions?

Are you running on .NET Framework or .NET?

3

We are on .NET Framework, but are making these changes so we can get rid of MSMQ to move forward to .NET…one step at a time though :wink:

NServiceBus 7.3.2
NServiceBus.Transport.SqlServer 6.3.3
NServiceBus.Persistence.Sql 6.5.1

That sample uses NHibernate persistence so isn’t quite us.

Message handlers have ctor dependency on NHibernate.ISession and we’ve configured IoC like

For<IDbConnection>().Use(ctx => {
return ctx
  .GetInstance<ISqlStorageSession>()
  .Connection;
}

For<ISession>().Use(ctx => {
return ctx
  .GetInstance<ISessionFactory>()
  .WithOptions()
  .Connection(ctx.GetInsance<IDbConnection>())
  .OpenSession();
}

It works fine in TransportTransactionMode.TransactionScope, and I can disable MSDTC and confirm the transaction isn’t escalating. So maybe I just have to be happy with that for now?

4

TransactionScope just means it will use a TransactionScope object. We are opening/closing when invoking the pipeline and when the pipeline completes we do the same to delete the corresponding message row from the queue table.

It will only be promoted when required by there are overlapping connections.

In your last comment you would then be using TransactionScope and the transaction isn’t managed by SQL Transport via a SqlTransaction and also not by NHibernate its transaction abstraction. While not tested by me just now should work.

5

Thanks Ramon.

I think the problem is related to NHibernate and TransactionScope is it not?

In my first post I mentioned I was trying to use the combination of TransportTransactionMode.SendsAtomicWithReceive and WrapHandlersInATransactionScope (following guidance here Transport Transactions; Avoiding Partial Updates for transaction mode lower than TransactionScope). WrapHandlersInATransactionScope means I need to configure NHibernate for System.Transaction i.e. use AdoNetWithSystemTransactionFactory. But doing that means sharing the connection between transport and NHibernate doesn’t work because the connection that we configure the NHibernate session to use already has an explicit SqlTransaction (I assume due to Sql Persistence and TransportTransactionMode.SendsAtomicWithReceive?).

6

The WrapHandlersInATransactionScope feature sits between the transport and any message processing. Meaning that scope is created after any transaction required for the transport message fetch operation. It would prevent partial updates across any storage operation invoked somewhere in the pipeline but exclude the transport operation.

There is another solution and that is to not have the transport and any persistence share the same transaction and still get the same consistency by using the outbox feature:

However, then you have the issue that all persistence operations must share the same storage transaction to guard consistency.

That would make much sense for SQL transport unless your organization might consider to transition to another transport in the future.

So, yes, when using SendsAtomicWithReceive all storage commands must share the same connection object via the storage context. When using TransactionScope its the opposite, you should not use the storage context and rely on a TransactionScope and create your own connection and ensure connections do not have any overlap in their lifetimes.

– Ramon

7

But there is no problem with sharing the (storage context) connection under TransactionScope when all parts are using the same catalog on the same instance (i.e. sql transport, persistence and then business data all in one place)?

8

The problem isn’t any catalog but overlapping database connections and if the operations on these should be a single transaction.

9

Sorry what do you mean by overlapping database connections?

If TransactionScope is being used, doesn’t it all succeed or fail as a unit?

10

@simonfox That is correct but transaction scope does not support overlapping open connections without promoting to a distributed transaction

1 Like