Particular
Close connection
We are using Transaction Scopes and have experienced an issue after migrating to azure. The error seems to be caused by the way Azure SQL handles DTC (Elastic transactions). I have created a bug in SqlClient to get help with the original error: https://github.com/dotnet/SqlClient/issues/2970 IN the meantime we are looking into possible workarounds.
The reason we get into a DTC in the first place seems to be that the TransactionScopeSqlOutboxTransaction opens a connection and leaves this open. When our code later opens a connection, the transaction will be escalated to DTC.
Is there any reason why the connection is left open and not closed? If it is closed and opend later when needed, the transaction will not be escalated to DTC. https://github.com/Particular/NServiceBus.Persistence.Sql/blob/0c5822f60879e9b420b0d4819164b0f09daeba01/src/SqlPersistence/Outbox/TransactionScopeSqlOutboxTransaction.cs#L48
Another approach would be to use the connection that is opened, i guess with ISqlStorageSession, but that would require a massive refactoring of our code.
Looks related to this issue: https://github.com/Particular/NServiceBus.Persistence.Sql/issues/594
@Abrissirba can you elaborate a little on why you need the transaction scope option?
https://docs.particular.net/persistence/sql/outbox#transaction-type
What transport are you using?
Seems like the improvement that you might be looking for is tracked by https://github.com/Particular/NServiceBus.Persistence.Sql/issues/645, can you take a look?
We are using Azure Sevice Bus as transport with Outbox and pessimistic concurrency.
The reason we use Transaction Scope is because we are new:ing up DbContext with a ConnectionString in a lot of places (legacy code) and it has been considered too big of an effort to rewrite all these to use ISqlStorageSession injected from DI at the moment.
From what I understand we need to use the connection from ISqlStorageSession to make sure that we are in the same transaction as the outbox if we would skip the TransactionScope?
From what I understand we need to use the connection from ISqlStorageSession to make sure that we are in the same transaction as the outbox?
That is correct.
That said I do have a faint memory that if the connection strings are identical the client should be smart enough not to escalate even though 2 connections are used but I can't find anything official that confirms this.
For pessimistic locking to work, we need to keep the connection open to maintain the lock on the outbox record so I'm not sure it would be possible to close the connection to avoid the issue you are mentioning
Yeah, we acctually made a change so that the connection string would be identical but it did not help.
What seems to cause the escalation is the fact that we have two connections open at the same time. When I experminted with the code available in the SqlClient Bug issue linked above it doesn't escalate if the first connection is closed before the next one is opened.
The first connection (outbox creation) will set up the transaction and keep the connection open. When the DbContext then creates its own connection, it will enlist into the the first transaction which will be escalated to DTC.
Interesting about that the connection needs to be open to lock the outbox record. I have not digged into that part yet.
Looking into pessimistic concurrency I wonder if we acctually need that in our setup. At the moment we only process one message at a time, with LimitMessageProcessingConcurrencyTo(1).
Am I correct when I believe that Pessimistic Concurreny is unnessecary when LimitMessageProcessingConcurrencyTo is set to 1?
It doesn't matter much though since the connection is still opened but not closed in TransactionScopeSqlOutboxTransaction.Begin for both pessimistic and optimistic concurrency
Am I correct when I believe that Pessimistic Concurreny is unnessecary when LimitMessageProcessingConcurrencyTo is set to 1?
That is correct as long as you are not scaled out
It doesn't matter much though since the connection is still opened but not closed in TransactionScopeSqlOutboxTransaction.Begin for both pessimistic and optimistic concurrency
Looking into the code you are correct. @danielmarbach @SzymonPobiega I remember that we talked about this, did we conclude that we should be able to close the connection when doing optimistic concurrency?
The original issue we had was confirmed as a bug by Microsoft and will be fixed in a future release of MIcrosoft.Data.SqlClient. We have also started the refactoring that will enable us to use the ISqlStorageSession. So we will not do any workarounds for the time being.
You can close the issue if you want. This issue might however be useful for someone else that investigates why DTC escalation is happening.
@Abrissirba we had an internal discussion and decided to close https://github.com/Particular/NServiceBus.Persistence.Sql/issues/645#issuecomment-2505660497 which is a similar proposal to this.
In short: Not opening the connection breaks the assumption the connection is always available in the InvokeHandler part of the pipeline