Implement distributed/promoted transactions in System.Transactions for Windows only

[StephenBonikowsky]

@jimcarley commented on Wed Nov 09 2016

---

@XieJJ99 commented on Tue Jun 11 2019

@ajcvickers @stephentoub The SQL Server team had already provided MSDTC for SQL Server on Linux. Is there any plan to promote the MSDTC for linux as a generic service so that it's possible to support distributed transaction on Linux?

---

@ULSTechology commented on Wed Jun 26 2019

A lack of support for distributed transactions is pretty disastrous for anyone wanting to migrate their old system onto .NET Core.

This puts an end to our hopes that we can start using Blazor - we can't rewrite our entire application to work without distributed transactions, there's just too much of it.

---

@popcatalin81 commented on Wed Jun 26 2019

we can't rewrite our entire application to work without transactions, there's just too much of it.

Surely, if you can rewrite for Blazor, which is a significant rewrite anyway, you can also eliminate distributed transactions.

---

@ULSTechology commented on Wed Jun 26 2019

we can't rewrite our entire application to work without transactions, there's just too much of it.

Surely, if you can rewrite for Blazor, which is a significant rewrite anyway, you can also eliminate distributed transactions.

Using Blazor requires a rewrite of the UI, and we can start small with that.

Rewriting some fairly large chunks of our data access layer, and probably even some of our business layer, is a whole other matter. Unfortunately, it's not an option we could consider at the moment.

---

@mmaderic commented on Thu Jul 18 2019

I would like to vote up for this feature at least for databases on single SQL server instance.

I have case where I would like to enclose two different database commits into single system transaction. (Sort of micro-service architecture).

---

@abbotware commented on Mon Jul 29 2019

@LaterStart - I have the same use case so +1

Creating multiple exe microservices (service fabric) sharing a single SQL database (migration of legacy code / data)

I found it is possible on Net Core to share transactions/connections between ADO. and EF Core within a single process (https://docs.microsoft.com/en-us/ef/core/saving/transactions) ,

However, what options currently exist when 2 separate Net Core exes are involved for implementing a 2 phase-commit like pattern?

1. Can the DTC / Resource Manager be simulated via base classes in Net Standard? (https://docs.microsoft.com/en-us/previous-versions/ms229975(v=vs.90))

2. Call the DTC directly via Win32 Api and some how enlist?

1-2 seem like a lot of work, but from what I have read (somewhere) I could just target Net Standard 2.0 and run within a Net Framework host - That will allow System.Transactions to enlist / promote into a DTC transaction, (I can settle for Netstandard + 4.7.2 + new sdk project format if that is my only option)

Even if I use a Net Framework Host (or Net Core if this open issue ever gets implemented), does that mean it should it is possible to obtain the transaction token / correlation id from process A so it can be sent/serialized to process B for manual enlistment?

---

@pappasa commented on Tue Sep 03 2019

How do you use .NET Core with IBM WebSphere MQ queue? For example, I do not want to remove the message from the queue, and the put to the database to fail.

---

@aajmot commented on Fri Sep 20 2019

can someone tell me if the issue has been fix or still there? It's almost 2020 now? Is there any other way around, please tell me.

---

@pi3k14 commented on Fri Oct 11 2019

@dasetser wtf - you moved this into the unforeseeable future? Don't you want to on-board enterprise solutions to .Net Core, you want to push us all over to the dark side (java)?

---

@am11 commented on Fri Oct 11 2019

Going by https://referencesource.microsoft.com/#System.Transactions/System/Transactions/Oletx/DTCTransactionManager.cs,2c1154236b6b7acf, seems like there are some win32 specific components involved which probably is the main hurdle. Could someone shed light on what are those and what would require to bring them cross platform?

---

@dasetser commented on Tue Oct 15 2019

This got moved to future because after investigating it we determined it's going to be a significant rewrite of the feature rather than just a port. We're looking into options for this, but we aren't able to commit to getting this done in the upcoming release.

The main hurdle is that System.Transactions depends on MSDTC when it has to promote transactions, but MSDTC is only available on Windows. In order to get this to work cross platform we would need to rewrite the feature to allow it to work with other distributed transaction coordinators that are available on other platforms, or look into doing something similar to what the SQL Server team did where they brought the DTC components inside the SQL component. Either approach would be a significant change to this feature.

---

@Peperud commented on Tue Oct 15 2019

Where do I vote for this?

---

@pi3k14 commented on Tue Oct 15 2019

@dasetser - I appreciate your clarification. But, us current users of Distributed transactions don't care about cross platform. Just support it on Windows, and put this cross platform paradigm that we haven't request into the future.

Thank you.

---

@am11 commented on Tue Oct 15 2019

Thanks for the explanation @dasetser. If the target linux machine has MSDTC component from SQL Server installed, and its C API is the same as Windows, can the implementation take advantage -- as in, SQL Server as a prerequisite (for now)? We can separately request and poll SQL Server team to ship MSDTC component as a standalone library. :)

---

@randyburden commented on Thu Dec 05 2019

We were planning to begin porting many of our current applications to .NET Core starting next week as well as start writing our new apps on .NET Core but we just ran into this issue of distributed transactions not working which feels like a deal breaker. Almost every application we write or will write in the future requires distributed transactions.

It is very odd to have experienced a platform not supported exception with this particular feature when we all know that it already exists in the full .NET Framework. It's hard to understand why a Windows platform specific implementation wasn't implemented either in CoreFX or as a shim NuGet package.

Is there a workaround?

---

@pi3k14 commented on Fri Dec 06 2019

@dasetser - Wouldn't implementing WS-AtomicTransaction be the way to go. And since MSDTC supports it and WCF has an implementation of it https://docs.microsoft.com/en-us/dotnet/framework/wcf/feature-details/using-ws-atomictransaction this shouldn't take that much time. Those wanting distributed transactions on Unix can then get a third party solution.

[iCodeWebApps]

I am happy that the resolve to this issue has been added to the 5.0 Milestone. Cheers!

[turabek]

For Enterprise applications it is very important to have a distributed transaction support. Happy to see this feature as part of milestone 5.

[pappasa]

Do you mean DTC will be supported in .NET 5?

[RobThaBlob]

I'd like to add my voice to those requesting this feature. It seems a backward step not to support distributed transactions.

[xuyichenklkkop]

this's is why we don't use .NET Core any more.

[NullEntity]

This is a big issue for my team too. We have a large net fx system in the fintech space. We use distributed transactions pretty heavily to guarantee atomicity. What I don't like about them is that it's really easy to accidently promote a TransactionScope which makes it incredibly hard to audit. We've been slowly replacing them with an outbox implementation (they're mostly used to atomically process a message from a queue and update the db), but it's a huge amount of work.

[jimmyzimms]

While I can appreciate the fact that not every platform has a transaction manager that works in a compatible enough methodology to the Net Framework implemtation, I find it gobsmacking that years on there's still not an on-Windows implementation as a package for this. There's nothing preventing an adapter pattern to be used with the default implementation being the current, NotSupportedException, approach and allowing 3rd party packages to pick things up. About every 6 months we pop over and check on this issue and until it's dealt with, NetCore is a deal breaker. Unfortunate.

[NullEntity]

@dasetser is this still on the roadmap for .NET 5? I've been looking forward to this functionality, but haven't heard about any updates to it in the previews.

[precision-sean]

Can we receive an update on this? Knowing whether or not this is still on the roadmap for .NET 5 has a significant impact on my company's plan to migrate from .NET Framework to .NET Core.

Thank you.

[NullEntity]

Can we receive an update on this? Knowing whether or not this is still on the roadmap for .NET 5 has a significant impact on my company's plan to migrate from .NET Framework to .NET Core.

Thank you.

I expect it to not happen because it's so late in the 5.0 release cycle and there's been no news yet. I recommend working to move away from distributed transactions.

[precision-sean]

Thank you, @HongGit, for the milestone confirmation.

[rizi]

@HongGit it seems distributed/promoted transactions still not working with .net 5.0 RC2, any time line for this? br

[imilchev]

I am also interested in the roadmap for this. At least some indication would be useful

[jimmyzimms]

Now that we went GA on the Net 5 release I've confirmed that they still have yet to tackle this with a simple console app opening two connections in a transactionscope. [slow clap]

The lack of a pluggable middleware component into the transaction manager with a windows specific nuget package enlisting DTC is criminal at this point.

[pouryax7x]

[impworks]

This limitation can be overcome by using SQL Server's BEGIN DISTRIBUTED TRANSACTION command manually. Here's a proof-of-concept wrapper:

https://gist.github.com/impworks/877ba85dd1b7b3e968ca4b4964eedde6

Here's how to use it with EF Core:

await using var fooDb = CreateFooDbContext();
await using var barDb = CreateBarDbContext();

await using (var txn = await Transaction.CreateAsync(fooDb, barDb))
{
    fooDb.Foos.Add(new Foo());
    await fooDb.SaveChangesAsync();

    barDb.Bars.Add(new Bar());
    await barDb.SaveChangesAsync();

    await txn.CommitAsync();
}

This approach has a limitation - it requires MARS to be turned off in the connection string:

MultipleActiveResultSets=false

[fededim]

This issue has been opened for a year....is there any hope of seeing it implemented in 2021 ?

[mesutpiskin]

@fededim you are right, dotnet 5 does not support DTC!

[pappasa]

Should distributed transactions be replaced with Kafka? Or are there any other suggestions?

[precision-sean]

I'll explain what I did to work around this limitation in case it's helpful. It's not generally usable but worked for my specific use case.

The code I was working on was writing to two databases, the main database and a history database to track changes. The Historian class that writes to history was only doing inserts, so I suppressed the ambient transaction context, tracked the keys for the inserted records myself, and added a Rollback function. Then I wrote a small class implementing IEnlistmentNotification and tied it to the ambient transaction to call my Rollback function when the transaction rolled back.

Here's some pseudocode to illustrate.

In the Historian class:

public void WriteHistory()
{
    // suppress ambient transaction context
    using (var transactionScope = new TransactionScope(TransactionScopeOption.Suppress))
    {
        // add rollback support
        Transaction.Current.EnlistVolatile(new HistorianEnlistment(this), EnlistmentOptions.None);

        // inserts to history, track the keys
        ...
    }
}

public void Rollback()
{
    // suppress ambient transaction context
    using (var transactionScope = new TransactionScope(TransactionScopeOption.Suppress))
    {
        // delete the records associated with the tracked keys
    }
}

Here's the HistorianEnlistment class:

public class HistorianEnlistment : IEnlistmentNotification
{
    private Historian _historian;
    public HistorianEnlistment(Historian historian)
    {
        _historian = historian;
    }

    public void Commit(Enlistment enlistment)
    {
        enlistment.Done();
    }

    public void InDoubt(Enlistment enlistment)
    {
        enlistment.Done();
    }

    public void Prepare(PreparingEnlistment preparingEnlistment)
    {
        preparingEnlistment.Prepared();
    }

    public void Rollback(Enlistment enlistment)
    {
        _historian.Rollback();
    }
}

[natalie-o-perret]

@roji 👋 Hi, I would like to have some clarifications, cause I'm a little confused about all the jargon being used all around the place.

Lss, we've been using the TransactionScope for what we thought a 2PC distributed transaction as part a .NET Core 3.1 and now 5.0 project, when we were testing whether the whole "distributed" transaction was rollback due to an exception, we got resp.

• Worked when writing to 2 distinct PG DBs (thru npgsql)
• Worked when writing to 1 Sql Server DB and 1 PG DB
• Didn't work with the exception mentioned in https://github.com/dotnet/runtime/issues/715#issuecomment-621737775 when writing to 2 distinct Sql Server DBs (this how I ended up on this thread)

Considering what I'm reading in this thread, I have some concerns that we didn't really understand what we were doing 🍀...

My (pretty wild) guess is the fact that it works or not might depend on the transaction implementation of the related ADO providers but I'm not really too sure if that's really the case.

🙇‍♀️ Thanks! 🙇‍♀️

Also I tried to gather some resources:

• https://stackoverflow.com/questions/52484614/transactionscope-and-2-phase-commit-in-two-different-sql-server-server/52486735#52486735
• https://www.codeproject.com/Articles/690136/All-About-TransactionScope
• https://github.com/dotnet/runtime/issues/715

[roji]

@kerry-perret at this point, no version of .NET Core supports distributed transactions - so it shouldn't be possible for you to be actually doing distributed transactions, regardless of driver. Note, however, that both Npgsql and SqlClient implement a specific optimization, whereby if a TransactionScope encompasses multiple connections to the same database, but only one is open at a given time, a single connection will be used (no distributed connection is necessary). That sometimes gives people the illusion they're doing distributed transactions.

If you're convinced your code has been doing actual distributed transactions on .NET Core, can you please open a new issue on github.com/npgsql/npgsql (to avoid making this issue even longer)?

[natalie-o-perret]

@roji thanks for your answer!

That being said, I've noticed a case with System.Data.SQLite for which the distributed transaction with a TransactionScope instance seems to work... I'm puzzled.

open System.IO
open System.Data.SQLite
open System.Transactions

[<RequireQualifiedAccess>]
module Try =
    let catch f =
        try
            f()
        with
        | e -> printfn "Error: %s" e.Message

[<RequireQualifiedAccess>]
module SQLiteOps =
    let private createTable (cnn: SQLiteConnection) =
        use command = cnn.CreateCommand()
        command.CommandText <- "
            CREATE TABLE atable (
            akey INTEGER PRIMARY KEY AUTOINCREMENT,
            afield TEXT NULL
        );"
        command.ExecuteNonQuery()

    let private insertInto (cnn: SQLiteConnection) =
        use command = cnn.CreateCommand()
        command.CommandText <- "
            INSERT INTO atable (afield)
            VALUES('Michelle');
        "
        command.ExecuteNonQuery()

    let conflictInsertInto (cnn: SQLiteConnection) (number: int32) =
        printfn "Conflict Insert Into with '%s'... start" cnn.ConnectionString
        use command = cnn.CreateCommand()
        command.CommandText <- $"
            INSERT INTO atable (akey, afield)
            VALUES({number}, 'Michelle');
        "
        command.ExecuteNonQuery() |> printfn "insert into: %d"
        printfn "Conflict Insert Into with '%s'... stop" cnn.ConnectionString

    let readStuff (cnn: SQLiteConnection) =
        use command = cnn.CreateCommand()
        printfn "Reading Stuff with '%s'... start" cnn.ConnectionString
        command.CommandText <- "SELECT COUNT(*) FROM atable;"
        command.ExecuteScalar() :?> int64 |> printfn "select count(*): %d"
        printfn "Reading Stuff with '%s'... start" cnn.ConnectionString

    let createConnection dataSource =
        let cnnString = $"Data Source={dataSource};"
        new SQLiteConnection(cnnString)

    let persistStuff (cnn: SQLiteConnection) count =
        printfn "Persisting Stuff with '%s'... start" cnn.ConnectionString
        createTable cnn |> printfn "create table: %d"
        [ 0 .. count - 1 ]
        |> List.iter (fun i -> insertInto cnn |> printfn "[%d]insert into: %d" i)
        printfn "Persisting Stuff with '%s'... stop" cnn.ConnectionString

    let readStuffFrom dataSource =
        dataSource
        |> createConnection
        |> readStuff

[<RequireQualifiedAccess>]
module TransactionOps =
    let whenBothWork() =
        let dataSource1Name = "Michelle1.db"
        let dataSource2Name = "Michelle2.db"
        File.Delete(dataSource1Name)
        File.Delete(dataSource2Name)
        use ts = new TransactionScope()
        use cnn1 = SQLiteOps.createConnection dataSource1Name
        use cnn2 = SQLiteOps.createConnection dataSource2Name
        cnn1.Open()
        cnn2.Open()
        SQLiteOps.persistStuff cnn1 1
        SQLiteOps.persistStuff cnn2 2
        ts.Complete()
        cnn1.Close()
        cnn2.Close()
        ts.Dispose()
        cnn1.Open()
        cnn2.Open()
        SQLiteOps.readStuff cnn1
        SQLiteOps.readStuff cnn2

    let whenOneDoesntWork() =
        let dataSource1Name = "Michelle1.db"
        let dataSource2Name = "Michelle2.db"
        File.Delete(dataSource1Name)
        File.Delete(dataSource2Name)
        use ts = new TransactionScope()
        use cnn1 = SQLiteOps.createConnection dataSource1Name
        use cnn2 = SQLiteOps.createConnection dataSource2Name
        cnn1.Open()
        cnn2.Open()
        SQLiteOps.persistStuff cnn1 1
        SQLiteOps.persistStuff cnn2 2
        (fun () ->
            SQLiteOps.conflictInsertInto cnn1 1
            ts.Complete())
        |> Try.catch
        cnn1.Close()
        cnn2.Close()
        ts.Dispose()
        cnn1.Open()
        cnn2.Open()
        (fun () -> SQLiteOps.readStuff cnn1) |> Try.catch
        (fun () -> SQLiteOps.readStuff cnn2) |> Try.catch

[<EntryPoint>]
let main _ =
    TransactionOps.whenOneDoesntWork()
    0

I don't think there is an illusion, or am I getting something wrong here?

[roji]

@kerry-perret I don't know the precise details here (and am unfamiliar with System.Data.Sqlite), but I wouldn't infer from the lack of an exception that an actual distribution transaction is going on; in other words, it's possible that the above code just sets up two regular transactions, each on its own connection/database, without any sort of 2PC protocol.

Having an actual distributed transaction requires communicating with an (external) distributed transaction coordinator (MSDTC on Windows), and in .NET Core that part does not exist.

[akhansari]

Hello, is there please any plan or roadmap about this?

[thtznet]

Hello, I just want to know when the topic can implement in .Net 5.0?

[AppChenX]

any ideas? its very impotant with micro services, shoud we go java?????

[alan-yeung]

It's a MAJOR roadblock preventing us from upgrading existing apps to .NET 5 and beyond.

Since 2006, we've built numerous apps using .NET Core 1., 2. with .NET Framework (hybrid). Since .NET Core 3.*, .NET Framework support has been dropped, we can no longer proceed with newer versions because what we have are mission-critical financial apps that require distributed transactions with MSDTC to rollback database operations when there is any failure.

Please help us move forward by adding this to the next .NET version!!!

[hotdiggitydoddo]

@StephenBonikowsky Any updates on timeline for implementing this feature? I am among the many here which am hoping for this sooner rather than later.

[SimonCropp]

a possible workaround for some: if you are targeting multiple DBs on one sql server instance, you can use Sql synonyms to proxy all operations+queries through one database. then your code can use one SqlConnection and one SqlTransaction to talk to all the databases.

[mmahlies]

a possible workaround for some: if you are targeting multiple DBs on one sql server instance, you can use Sql synonyms to proxy all operations+queries through one database. then your code can use one SqlConnection and one SqlTransaction to talk to all the databases.

Dear SimonCropp, Kindly can you give me more information about this workaround and how it helps in applying distributed transaction.

Note that my solution due to now is to use 2pc protocol in SOA environment.

[SimonCropp]

@mmahlies my workaround does not "help in applying distributed transaction", it prevents the transaction from escelating to DTC. Since you are always talking to one database, you can perform multiple sql operations inside on db transaction.

[AshleyMedway]

Very disappointing to see this dropped from .NET 5, will it be included in .NET 6?

[lscorcia]

This is a huge deal for me too. Actually, I couldn't care less about EF Core, but I need to share business logic between asp.net 4.7.2 and asp net 5 and I discovered that EF6 does not support netstandard2.0 (BUT supports netfw4.7.2 which is compliant with .netstandard2.0, therefore the lack of support for netstd2 is plain crazy). I was evaluating the migration of the data layer to EF Core to work around this, but then I discovered this showstopper.

Between this and the WCF-client issues (serialization bugs, missing MTOM, etc), .net core for the fintech/gov enterprises is basically dead in the water.

[csrowell]

Yeah, I think this is a big deal for a lot of enterprises.

[torgashov]

Is it possible to use distributed transaction with MSDTC in windows applications with .net5/6 ?

[alan-yeung]

Is it possible to use distributed transaction with MSDTC in windows applications with .net5/6 ?

No... hopefully in .NET 7 or 8... =(

[jimmyzimms]

Is it possible to use distributed transaction with MSDTC in windows applications with .net5/6 ?

no, not at all. There's no code in Net5 (and NetCore of any version) that interacts with the MSDTC. You simply get a PlatformNotSupportedException when you do something that escalated to a Distributed Transaction (or it just doesn't do anything-depends on the provider following the pattern)

[pappasa]

Is it possible to use distributed transaction with MSDTC in windows applications with .net5/6 ?

No... hopefully in .NET 7 or 8... =(

Is this in the roadmap for .NET 7 or 8? When are these releases expected?

[precision-sean]

This Milestone is tagged as 'Future' so there's no indication of if/when it will be implemented.

.NET's release cadence is a new version of .NET each November; even numbers will be LTS. .NET 6 will be released November 2021, .NET 7 in November 2022, and so forth.

[fschmied]

Note that this lack of DTC support not only affects transactions involving multiple different databases, but also prevents having multiple open SQL connections to the same database within a TransactionScope.

[filipe-assis]

Why not maintain functionality on windows and throw exception on other platforms?

[torgashov]

Why not maintain functionality on windows and throw exception on other platforms? May be maintain for windows could be extends with some nuget package?

[pcbl]

This is the blocker that does not allow a project I work on getting updated. I would love to have this feature on .net core at least on Windows platform, throwing on other platforms as many people on this thread suggested.

[aajmot]

Did anyone found any workaround of this issue for any platform.

-sorry for any errors in this response.

[lscorcia]

There is no workaround. We will be staying on EF6 for the foreseeable future...

[natalie-o-perret]

Did anyone found any workaround of this issue for any platform.

-sorry for any errors in this response.

Architectural workaround: use sagas (if the limitations aren't a problem for you)

https://www.youtube.com/watch?v=xDuwrtwYHu8

[impworks]

@aajmot @lscorcia there is a workaround for some scenarios, we're been successfully using it in production for half a year. See my comment: https://github.com/dotnet/runtime/issues/715#issuecomment-748934621

[jimmyzimms]

@aajmot @lscorcia there is a workaround for some scenarios, we're been successfully using it in production for half a year. See my comment: #715 (comment)

Sadly that doesn't work across servers and in addition, only works with SQL databases (you can't use that to transactionally use SQL and say Transaction NTFS or a Transactional Queueing system with the DB commit)

[Mr-Lei-web]

Why not guarantee single-platform distributed transactions first? If even a single platform cannot use this, It's really disappointing