Do Transaction scope uses MSDTC when running on one database

Question

Do Transaction scope uses MSDTC when running on one database

Itay Hay 6

Hey,

As part of an OutBox pattern architecture - where there is a requirement to atomically update the database and publish messages/events.

so the problem is :

How to reliably/atomically update the database and send messages/events?

To answer this problem I Plan on using Transaction scope to manage the transaction.

My question is :

If all transactions are ran against a single database -

do transaction scope still uses MSDTC(Distributed Transaction Coordinator) when running on a single database?

My concern is performance issues that might be caused due to this kind of transactions.

Many thanks

Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-03-23T22:04:38.15+00:00

@Itay Hay Thank you for reaching out.

My understanding is that you are looking to use DTC when running on a single database but concern about the performance issues. Is it for an Azure SQL Managed Instance.?

Regards,

Oury
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-03-24T17:09:51.34+00:00

@Itay Hay Can you clarify the performance concern maybe?

1 answer

Your answer

Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-03-23T22:04:38.15+00:00

@Itay Hay Thank you for reaching out.

My understanding is that you are looking to use DTC when running on a single database but concern about the performance issues. Is it for an Azure SQL Managed Instance.?

Regards,

Oury
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-03-24T17:09:51.34+00:00

@Itay Hay Can you clarify the performance concern maybe?

Answer 1

Oury Ba-MSFT 20,931 Microsoft Employee Moderator

@Itay Hay

TransactionScope (and System.Transactions) use lightweight promotabble transactions. So you can avoid MSDTC unless your code requires it. EG if two seperate SqlConnection objects are open at the same time and enlist in the same transaction, then it will trigger promotion. But if the SqlConnections run one after the other, then they will share the same underlying connection and lightweight SQL Server transaction. See: System.Transactions Integration with SQL Server - ADO.NET | Microsoft Learn

Regards,

Oury

Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-03-27T05:48:47.41+00:00

Hi Itay Hay Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Itay Hay 6 Reputation points

2023-03-28T10:19:46.5+00:00
thanks Oury Ba-MSFT for your answers.

regarding your questions:

the environment is a Paas -Sql azure (not managed instance).

My performance concern is worse performance of Distributed transaction compared to local transaction.

Could you elaborate about the promotion of local transaction to Distributed transaction?

specifically if it might happen if all transactions from the ADO System.Transactions are on the same database.

Could there still be a promotion to distributed transaction?

This baffles me because the whole point of DTC is to coordinate transactions across multiple data sources, what if there is only One data source?

So the main question is

Does promotion from LTM(local transaction manager) to DTC(distributed transaction Co-Ordinator)

depends on number of connections rather that number of data source?
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-04-03T22:25:32.1866667+00:00

Itay Hay Could there still be a promotion to distributed transaction? Yes. If the code uses the two transactions concurrently they would need to be promoted to a distributed transaction. This is no different from SQL Server. Distributed Transaction Coordinator (DTC) (preview) - Azure SQL Managed Instance | Microsoft Learn Please let tus know if this answers your query. Regards, Oury
Oury Ba-MSFT 20,931 Reputation points Microsoft Employee Moderator

2023-04-04T17:10:47.07+00:00

Itay Hay We haven’t heard from you on the last response and was just checking back to see if you that above answer or comment clarifies your ask. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Do Transaction scope uses MSDTC when running on one database

1 answer

Your answer