I've created a minimal reproduction here: https://github.com/mushishi78/transaction-minimal-reproduction
Essentially I've hit a NotImplementedException given by mono's TransactionInterop that seems to imply that it's not ready for async transactions. But as I'm not that experienced with .NET, I was hoping for some second opinions or advice.
Have I done something odd in the implementation and if I wrote it correctly this would be completely fine?
Should I wrestle with upgrading to a later version of Npgsql even though I had trouble with that originally?
Should I give up on async for the time being because it possible doesn't make as much difference in .NET world?
Should I give up on SQLProvider as other ORM/object mapper libraries don't have these issues (eg. Dapper)?
Would I have better luck not using Postgres as SQL Server is much better supported in .NET world?
Or is it mono that's just not ready for primetime and I'm better off making a server run on a windows box?
open FSharp.Data.Sql
open System.Transactions
let [<Literal>] connectionString = "Host=localhost;Database=example;Username=postgres;Password=postgres;Enlist=true"
let [<Literal>] resolutionPath = __SOURCE_DIRECTORY__ + @"..\packages\Npgsql.3.1.0\lib\net451"
type sql = SqlDataProvider<Common.DatabaseProviderTypes.POSTGRESQL,
connectionString,
ResolutionPath = resolutionPath,
UseOptionTypes = true>
let withTransaction fn =
async {
use transaction = new TransactionScope (TransactionScopeAsyncFlowOption.Enabled)
let ctx = sql.GetDataContext ()
let! result = fn ctx
transaction.Complete ()
return result
}
let createPerson (ctx: sql.dataContext) =
let row = ctx.Public.Person.Create ()
row.Id <- 1
row.Name <- "Hello"
ctx.SubmitUpdatesAsync ()
let editPerson (ctx: sql.dataContext) =
query {
for row in ctx.Public.Person do
take 1
}
|> Seq.iter (fun row -> row.Name <- "Hi there!")
|> ctx.SubmitUpdatesAsync
let deletePerson (ctx: sql.dataContext) =
query {
for row in ctx.Public.Person do
take 1
}
|> Seq.iter (fun row -> row.Delete ())
|> ctx.SubmitUpdatesAsync
let run (ctx: sql.dataContext) =
async {
do! createPerson ctx
do! editPerson ctx
do! deletePerson ctx
}
[<EntryPoint>]
let main argv =
withTransaction run
|> Async.RunSynchronously
|> ignore
0
The method or operation is not implemented.
at System.Transactions.TransactionInterop.GetTransmitterPropagationToken (System.Transactions.Transaction transaction) [0x00000] in /Users/builder/jenkins/workspace/build-package-osx-mono/2019-06/external/bockbuild/builds/mono-x64/mcs/class/System.Transactions/System.Transactions/TransactionInterop.cs:57
at Npgsql.NpgsqlPromotableSinglePhaseNotification.Enlist (System.Transactions.Transaction tx) [0x00070] in <301d14fab821450fa5cc07ec7c940a17>:0
at Npgsql.NpgsqlConnection.OpenInternal () [0x0012c] in <301d14fab821450fa5cc07ec7c940a17>:0
at Npgsql.NpgsqlConnection.Open () [0x00000] in <301d14fab821450fa5cc07ec7c940a17>:0
at FSharp.Data.Sql.Common.Sql.connect[a] (System.Data.IDbConnection con, Microsoft.FSharp.Core.FSharpFunc`2[T,TResult] f) [0x00009] in <5d776594de6dfdbfa74503839465775d>:0
at FSharp.Data.Sql.Providers.PostgresqlProvider.FSharp-Data-Sql-Common-ISqlProvider-GetColumns (System.Data.IDbConnection con, FSharp.Data.Sql.Schema.Table table) [0x000ae] in <5d776594de6dfdbfa74503839465775d>:0
at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-CreateEntity (System.String tableName) [0x0001f] in <5d776594de6dfdbfa74503839465775d>:0
at Program.createPerson (System.Object ctx) [0x00000] in /Users/max/dev2/TransactionMinimalReproduction/TransactionMinimalReproduction/Program.fs:22
at Program+run@45.Invoke (Microsoft.FSharp.Core.Unit unitVar) [0x00000] in /Users/max/dev2/TransactionMinimalReproduction/TransactionMinimalReproduction/Program.fs:45
at Microsoft.FSharp.Control.AsyncPrimitives.CallThenInvoke[T,TResult] (Microsoft.FSharp.Control.AsyncActivation`1[T] ctxt, TResult result1, Microsoft.FSharp.Core.FSharpFunc`2[T,TResult] part2) [0x00005] in <039b17603f7a807e0eeaa652dc64c784>:0
at Program+withTransaction@16-4[a].Invoke (Microsoft.FSharp.Control.AsyncActivation`1[T] ctxt) [0x00000] in /Users/max/dev2/TransactionMinimalReproduction/TransactionMinimalReproduction/Program.fs:16
at Microsoft.FSharp.Control.Trampoline.Execute (Microsoft.FSharp.Core.FSharpFunc`2[T,TResult] firstAction) [0x00020] in <039b17603f7a807e0eeaa652dc64c784>:0
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.FSharp.Control.AsyncResult`1[T].Commit () [0x0002c] in <039b17603f7a807e0eeaa652dc64c784>:0
at Microsoft.FSharp.Control.AsyncPrimitives.RunSynchronouslyInCurrentThread[a] (System.Threading.CancellationToken cancellationToken, Microsoft.FSharp.Control.FSharpAsync`1[T] computation) [0x00028] in <039b17603f7a807e0eeaa652dc64c784>:0
at Microsoft.FSharp.Control.AsyncPrimitives.RunSynchronously[T] (System.Threading.CancellationToken cancellationToken, Microsoft.FSharp.Control.FSharpAsync`1[T] computation, Microsoft.FSharp.Core.FSharpOption`1[T] timeout) [0x00013] in <039b17603f7a807e0eeaa652dc64c784>:0
at Microsoft.FSharp.Control.FSharpAsync.RunSynchronously[T] (Microsoft.FSharp.Control.FSharpAsync`1[T] computation, Microsoft.FSharp.Core.FSharpOption`1[T] timeout, Microsoft.FSharp.Core.FSharpOption`1[T] cancellationToken) [0x0006e] in <039b17603f7a807e0eeaa652dc64c784>:0
at Program.main (System.String[] argv) [0x00000] in /Users/max/dev2/TransactionMinimalReproduction/TransactionMinimalReproduction/Program.fs:52
Any and all help would be appreciated.
Async/await may return to a new thread or may return to the same thread. So that broke the old .NET Framework transactions which were not continuing over threads. So with .NET 4.5.1 they created an option Transactions.TransactionScope(Transactions.TransactionScopeAsyncFlowOption.Enabled) to support transaction spanning over multiple threads. Sadly that option was never properly implemented in Mono (but just copied the parameter as compatibility reasons). At that point of time the focus was already .NET Core.
So to keep it reliable, don't use Async on Mono (at least not in production).