problem with enlisting in the existing transaction scope

[msvitlica]

Hi.

We wrote unit test for shuttle publish to check if it can be enlisted in trasnaction scope without promoting DTC. This is the code of the test

using (var scope = new TransactionScope(TransactionScopeOption.Required))
            {
                using (var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString))
                using (var command = new SqlCommand("INSERT INTO Test VALUES(@Description)", sqlConnection))
                {                    
                    command.Parameters.Add("Description", SqlDbType.VarChar, 100).Value = DateTime.UtcNow.Millisecond.ToString();

                _bus.Publish(new TestMessage() { FreeText = "Milan Svitlica" });

                sqlConnection.Open();
                command.ExecuteNonQuery();

                scope.Complete();
            }
        }`

But when executing on Sql server 2014, we got an error

24.08.2016 10:01:09:51380 117 Fatal Unhandled exception during event 'Shuttle.Esb.OnDispatchTransportMessage': Raising pipeline event 'Shuttle.Esb.OnDispatchTransportMessage' in stage 'Send' for observer 'Shuttle.Esb.DispatchTransportMessageObserver'. / Exception has been thrown by the target of an invocation. / Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool. / The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024) 24.08.2016 10:01:09:53080 69 Error Shuttle.Core.Infrastructure.PipelineException: Raising pipeline event 'Shuttle.Esb.OnDispatchTransportMessage' in stage 'Send' for observer 'Shuttle.Esb.DispatchTransportMessageObserver'. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Transactions.TransactionManagerCommunicationException: Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool. ---> System.Runtime.InteropServices.COMException: The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT:

Could you provide any advice here ? We would like to enlist shuttle publish in ambient transaction without promoting DTC since both our code and shuttle are using same database.

[eben-roux]

Hi,

It isn't really so much about using the same database as the same connection :)

I just need some more information on your configuration though. What are you using as the queue transport? I'm assuming Sql Server since you expect both to enlist in the same transaction.

There are probably a couple of ways around this. You could, instead of using Sql Server as the actual queue (if you are doing this), use a Sql Server-based outbox.

But then again, an initial command should be an atomic message with no other related interaction.

Here is another thread that may help: https://github.com/Shuttle/shuttle-esb/issues/49

Let me know what things look like on your end and we'll take it from there. There should be a way to do what you are trying to achieve.

Regards, Eben

[msvitlica]

Yes, we are using sql server for transport.

I went through the thread you provided above, but to be honest I could not find solution. What we are trying to achieve is to make shuttle SqlQueue reuse our already existing SqlConnection and on this way we would prevent DTC escalation.

I tried to implement IDatabaseConnectionFactory but then I saw that IDatabaseContextFactory has this property read only. If you have any suggestion to achieve this, that would be great ?

Thanks, Milan

[msvitlica]

So basically our problem is: 1) we have the existing code block with transaction scope wrapper 2) the existing code opens sql connection and use it to the end of transaction scope block 3) now when we put (shuttle) servicebus.publish in the middle of this code, it tries to create new sql connection and then it cause DTC escalation

so for us, best possible solution would be if servicebus could reuse that same sql connection..

[eben-roux]

OK,

It is possible to get that done. It is going to take little bit of legwork, though. I would still recommend against using a SqlQueue in a production environment.

Here goes:

Oh yes, it is now IDatabaseContextFactory.

To get an IQueue instance for the given scheme identified by your queue uri Shuttle uses a QueueManager where all IQueueFactories are registered. Therefore the QueueManager should have a factory registered that handles the sql scheme if your uri is sql://abc/xyz.

Since you need to control the connection used you would need to create your own IQueueFactory that returns instance of the existing SqlQueue (no need to create your own here). But your IQueueFactory should then handel the connection. I'm thinking you could also create your own IDatabaseContextFactory that is passed to the SqlQueue. It would return the relevant queue that you could pass to your implementation. Your IQueueFactory can be registered manually with the QueueManager when your endpoint starts up:

public void RegisterQueueFactory(IQueueFactory queueFactory)

That is a quick overview of what you'll need. But I'd rather go with at-least-once delivery and try to keep my messages idempotent :)

I'll be happy to help if you get stuck.

[msvitlica]

What would be a reason you don't recommend using SqlQueue in production envrionment ?

[eben-roux]

Well, it isn't a real queuing mechanism. It would be OK for, say, a hosted environment where one has a sqlexpress db and no access to install something like RabbitMQ or Msmq.

I would go with RabbitMQ though. It is quite quick. The SqlQueue would never give you anywhere near the performance you'd get with RabbitMQ.

RabbiitMQ is also a broker. This means that distributing messages is super-simple. You only need to install a new endpoint (of the same type) and feed off the same inbox.

Since Msmq is a local private queue you'd have to set up a distributor (not a train smash but a little bit more effort). You'd have to handle a SqlQueue the same way (as local).

[msvitlica]

Ok, in our case we are not concerned about performance and our application sits in environment were we have acceess to sql database and we are not able to install rabbit mq or msmq. So Sql Queue fits us best, do you have any other concers, like reliabitly, stabilty ?

[eben-roux]

If that the case it should be OK. Each endpoint would need its own SqlQueue inbox. The endpoint can have multiple threads running.

It should cope... let me know if you do happen to run into any issues that require hardening.

Also, if you go with the connection-sharing plan I'd be happy to help where I can. Currently I'm working some hectic overtime else I would've put together the broad strokes :(

[msvitlica]

We don't handle to many events/messages per second. we just want as simple as possible publish/subscribe integration between multiple application/services via sql server transport. the only important thing for us is to not have some unexpected behaviour in production like memory leak or unhandled exceptions..

Thanks for help