How can I avoid joining NServiceBus' ambient TransactionScope?

3
votes

We have a service that can work with a SQL Server or Oracle back end. We just started to add messaging capability to allow our service to communicate with an Oracle-only application and decided to go with NServiceBus. It was decided to place the NServiceBus subscriptions/queue/etc. on the application's database, so NServiceBus is always using Oracle.

When the service is used with a SQL Server back end, everything works great. But when we try to use the service with an Oracle back end (a completely separate database from the one used by the application & NServiceBus, often on a different server) we get exceptions.

Exception details:

  • Exception text: "Connection is already part of a local or a distributed transaction" or sometimes "Unable to enlist in a distributed transaction" (a minimal VM test environment instead got "Unable to load OraMTS")
  • the exception occurs when our database layer calls connection.Open()
  • the connection's connection string is standard Data Source=foo;User Id=bar;Password=baz
  • the service is using the unmanaged Oracle provider
  • the service uses a decorator to connect when it starts handling a message then disconnect afterwards; removing the decorator and instead having the service connect when it starts (before setting up NServiceBus) solves the problem but means that if anything happens to that connection the service has to be restarted

The exception text makes us think the connection is trying to join NServiceBus' TransactionScope. Is that correct? If so, is there some option we can add to the connection string or configure on the connection itself to have it avoid joining the ambient TransactionScope?

Note that we use BeginTransaction in our code; we're aware that it doesn't play nice with TransactionScope but we've never had TransactionScope in our code before and don't want to be in a TransactionScope, and in any case the exceptions we're getting are happening before we even get to the BeginTransaction call.

1
c#oracletransactionsnservicebus
This is probably due to the fact the distributed transaction coordinator (MSDTC) isn't running, or the user-account that runs the NServiceBus endpoint, isn't allowed to use MSDTC. In your answer to your own question, you found a way to disable this, but be sure you're aware of the implications. There's hardly any guarantee your messages will arrive or be send safely. You might start missing messages. – Dennis van der Stelt
@DennisvanderStelt NServiceBus lists MSDTC as a requirement so we double-checked that. Note that we modified the connection string for our connections to the database, not NServiceBus's connections. – Oblivious Sage
What did you double check? MSMQ, SQL Server en Oracle can all enlist in the same distributed transaction, but don't have to. You can create a new ambient transaction with TransactionScope and not have it enlist. But it depends on what you're doing, and I don't understand 100% what it is you're trying to achieve, after the last comment. – Dennis van der Stelt
@DennisvanderStelt We weren't trying to use distributed transactions at all. Our service receives messages from NServiceBus, processes them, and stores the results in a database. But when it tried to connect to the database it kept getting sucked into NSB's distributed transaction. We wanted to avoid joining NSB's distributed transaction without messing up whatever NSB was doing, which is what the answer below accomplished. – Oblivious Sage
NServiceBus doesn't create distributed transactions by default, they get created because more than one resource manager is used. So msmq and sql-server, or sql-server and oracle, or msmq and oracle. When reading from msmq and then opening a database connection, both transactions get 'sucked' into a distributed transaction. Use just one resource manager (just Oracle, for example) to make sure it doesn't. DTC provides high reliabilty, at a cost. One might think twice before giving up the reliability? – Dennis van der Stelt

1 Answers

2
votes

Put Enlist=false in the connection string.

Solutions for this were somewhat difficult to find because everyone seems to assume that you either want all of your connections using a distributed transaction or none of them using a distributed transaction. However I eventually found in the documentation for the Microsoft Oracle provider that you can add Enlist=false to your connection string to indicate that you do not want the connection to automatically join the active distributed transaction (it's apparently still possible to manually join the distributed transaction later, but I didn't investigate that very closely since it's not relevant in our case).