"Nested/Concurrent transactions aren't supported" while using TransactionScope

[ygoe]

Sometimes, when using nested TransactionScopes (none of which was aborted) and Entity Framework, I repeatedly get the error "Nested/Concurrent transactions aren't supported." of type NotSupportedException, inner exception of "The underlying provider failed on EnlistTransaction.", EntityException.

For a simple myContext.MyEntities.AsNoTracking().FirstOrDefault() the stack trace is this:

   bei Npgsql.NpgsqlConnection.BeginTransaction(IsolationLevel level)
   bei Npgsql.NpgsqlPromotableSinglePhaseNotification.Enlist(Transaction tx)
   bei System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   bei System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.EnlistTransaction(DbConnection connection, EnlistTransactionInterceptionContext interceptionContext)
   bei System.Data.Entity.Core.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
   ------ Inner Exception ends -----
   bei System.Data.Entity.Core.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
   bei System.Data.Entity.Core.Objects.ObjectContext.EnsureContextIsEnlistedInCurrentTransaction[T](Transaction currentTransaction, Func`1 openConnection, T defaultValue)
   bei System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
   bei System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   bei System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   bei System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)

The same code works perfectly with SQL Server. It is reproducible 100 % for me.

I've seen this kind of error message before when I was still using DbTransactions when I wanted to enlist a connection with the same ambient transaction more than once. Again, this worked perfectly with SQL Server and resulted in the same error message with Npgsql. It should probably check whether the same transaction is assigned and ignore the call instead of acting as if it were different transactions.

• Npgsql.dll version 3.0.1
• EntityFramework6.Npgsql.dll version 3.0.1
• .NET Framework 4.6
• Windows 7 x64, 64-bit process
• PostgreSQL server 9.4 on Windows Server 2012 R2 x64

PS: The same application code runs fine with ADO.NET providers for SQL Server, SQLite, MySQL, and Oracle.

[ygoe]

I assume the problem lies in /src/Npgsql/NpgsqlPromotableSinglePhaseNotification.cs, in the Enlist method that does not compare the new Transaction with the currently active transaction. Looking at similar code from SQL Server, SQLite and MySQL (DbConnection.EnlistTransaction method), they simply return and do nothing if it's the same transaction.

[ygoe]

The problem only occurs within a TransactionScope and after an inner TransactionScope with the TransactionScopeOption.Suppress option. The next database interaction after the Suppress scope is left will fail.

using (var outerScope = new TransactionScope())
{
    // Database work
    using (var innerScope = new TransactionScope(TransactionScopeOption.Suppress))
    {
        // Database work
    }
    // Database work -> fails
    scope.Complete();
}

I've downloaded the source code and debugged through it but I can't seem to find an equivalent in Npgsql code of the currently enlisted transaction of a connection (which is System.Transactions.Transaction, not NpgsqlTransaction). That would need to be maintained as well, like when the connection is closed. I've seen such code in the System.Data.SQLite library but can't transfer it to Npgsql.

[ygoe]

The error only occurs when I create a new DbContext instance with passing an already opened NpgsqlConnection. (I can't use fixed app.config configuration because the user must be able to set the connection data from a GUI form. And I can't just pass a connection string because there can be multiple providers and a connection string can't specify the provider.) If I do pass a prepared connection that is still closed, everything works fine. Using app.config-defined connection strings also doesn't trigger the error.

The other ADO.NET providers show inconsistent behaviour about this: Some require a connection that is already open, all (except Npgsql) accept a connection this is already open.

[roji]

Thanks for reporting this, we do have some issues with TransactionScopes and distributed transactions, and plan on a serious look at all this for Npgsql 3.1. I'll try to look at your specific issue before then though (hopefully we can do a simple fix for this in 3.0.x).

[ygoe]

Using TransactionScope does not work at all now. Transactions are always committed, or never started at all. All the changes that I make are persistent, even if I cancel the transaction scope. That seems to be a major problem now. It works with SQL Server and SQLite but not other providers.

Considering such a major failure of the .NET Framework-recommended way of handling transactions, I have now modified my database layer back to using more traditional and comprehensible DbContextTransactions, wrapping DbTransaction. I added the missing nesting capability for reusable methods as I had done in the past. This works well now with all providers. I probably won't come back to System.Transactions anymore.

[roji]

@dg9ngf, I know that there are some issues with TransactionScope in Npgsql and I understand the frustration, but it's definitely not supposed to be that broken.

Can you take a look at the SystemTransactionTests, especially at Rollback which demonstrates a working rolled-back TransactionScope? I'd definitely be interested in precise bug scenarios which we could work to fix.

Also, are you seeing broken TransactionScope behavior with other database providers? Can you provide a bit more details?

[roji]

@ygoe, am finally getting around to work on System.Transactions support - am pretty much rewriting the whole thing to be safer and simpler. I've included the check for enlisting on the same transaction twice (return and do nothing). If you can help test the upcoming 3.2, you should be able to grab recent 3.2 CI nugets from the unstable feed, or you can wait until a beta is released (hopefully not too long from now).

[jakubprusak]

@roji I read in this issue (#122) that TransactionScope was not released in 3.2 version.

[roji]

@jakubprusak, TransactionScope is part of .NET's System.Transactions. Support for System.Transactions was completely rewritten in Npgsql 3.2, so any problems with using TransactionScope (such as this issue) should have gone away.

Are you encountering some problem, or this a question?

[jakubprusak]

Hi @roji
We are having a base class for our integration tests(please have a look on the code pasted below). As You can see, we use temporary solution - cleaning the tables on TearDown, cos we cannot use TransactionScope.
When we use that, data in db exist instead of to be clean.
What I can say more, the solution works well on Sql Server.

We try to use that using libraries like:
"Npgsql" version="3.2.0"
"EntityFramework" version="6.1.3"
"EntityFramework6.Npgsql" version="3.1.1"

` private IDisposable dbContextScope;
private TransactionScope _transaction;

    protected SnpocDbContext Db;

    [SetUp]
    public void SetUp()
    {
        IoCContainer.Container.Install(new IoCInstaller(null));
        ResetDb();
        //_transaction = new TransactionScope(TransactionScopeOption.Required);
        FakeWorkContext.SetLoggedInUserGetCallback(() => LoggedInUser );
    }

    [TearDown]
    public void TearDown()
    {
        //TODO.JT - temporary solution. Use TransactionScope when version 3.2 of Npgsql is released: https://github.com/npgsql/npgsql/issues/731
        TruncateTables();
        //_transaction.Dispose();
        dbContextScope.Dispose();
        IoCContainer._resetContainer();
    }

`

[roji]

@jakubprusak, I can see your workaround, but what's missing is of course the problem itself - the exception and stack trace...

Could you please open a new issue with all the required details?

[jakubprusak]

yes, I can open, but there is no exception from code side.
we call _transaction.Dispose(); and It should clean data in DB.
It seems like the transaction does not open for the scope.

[roji]

@jakubprusak did you specify Enlist=true in the connection string?

[jakubprusak]

@roji
I set the Enlist to true and the exception is : the transaction is aborted. commands ignored until end of transaction block.

StackTrace from test:

Test Name:	AddOTractor_CorrectDataInParameter_ItsAdded
Test FullName:	SNPOC.IntegrationTests.Tractor.AddOrUpdateTractorTests.AddOTractor_CorrectDataInParameter_ItsAdded
Test Source:	C:\git\kombajn\SNPOC\SNPOC.IntegrationTests\Tractor\AddOrUpdateTractorTests.cs : line 14
Test Outcome:	Failed
Test Duration:	0:00:12,968

Result StackTrace:	
w System.Data.Entity.Internal.InternalContext.SaveChanges()
w System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
w System.Data.Entity.DbContext.SaveChanges()
w SNPOC.IntegrationTests.Tractor.AddOrUpdateTractorTests.AddOTractor_CorrectDataInParameter_ItsAdded() w C:\git\kombajn\SNPOC\SNPOC.IntegrationTests\Tractor\AddOrUpdateTractorTests.cs:wiersz 16
--UpdateException
w System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
w System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut)
w System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
w System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
w System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35()
w System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
w System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
w System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
w System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
w System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
w System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
w System.Data.Entity.Internal.InternalContext.SaveChanges()
--PostgresException
w Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
--- Koniec śladu stosu z poprzedniej lokalizacji, w której wystąpił wyjątek ---
w System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
w System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
w System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
w Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- Koniec śladu stosu z poprzedniej lokalizacji, w której wystąpił wyjątek ---
w System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
w System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
w System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
w Npgsql.NpgsqlConnector.<ReadExpecting>d__154`1.MoveNext()
--- Koniec śladu stosu z poprzedniej lokalizacji, w której wystąpił wyjątek ---
w System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
w System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
w System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
w Npgsql.NpgsqlDataReader.<NextResult>d__31.MoveNext()
--- Koniec śladu stosu z poprzedniej lokalizacji, w której wystąpił wyjątek ---
w System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
w System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
w Npgsql.NpgsqlDataReader.NextResult()
w Npgsql.NpgsqlCommand.<Execute>d__69.MoveNext()
--- Koniec śladu stosu z poprzedniej lokalizacji, w której wystąpił wyjątek ---
w System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
w System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
w System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
w Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__90.MoveNext()
--- Koniec śladu stosu z poprzedniej lokalizacji, w której wystąpił wyjątek ---
w System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
w System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
w System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
w Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
w System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
w System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
w System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
w System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
w System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
w System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
w System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
w System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
Result Message:	
System.Data.Entity.Infrastructure.DbUpdateException : An error occurred while updating the entries. See the inner exception for details.
  ----> System.Data.Entity.Core.UpdateException : An error occurred while updating the entries. See the inner exception for details.
  ----> Npgsql.PostgresException : 25P02: bieżąca transakcja została przerwana, polecenia ignorowane do końca bloku transakcji

[mgulden]

I had the voiding TransactionScope issue with EntityFramework6.Npgsql 3.1.1 and Npgsql 3.1.2. When i add enlist=true to connection string, it started to give "The maximum number of enlistments for the specified transaction has been reached" error.

After i update Npgsql package to v3.2.1 TransactionScope worked (if you do not specify enlist=true on connection string it still voids to transactionscope)

What does the enlist=true mean? if i do not use transaction scope with enlist=true does it effect behaviour?

[roji]

@mgulden I'm not sure what you mean by "voiding TransactionScope".

As of 3.2, you still need to enlist in TransactionScope by either adding Enlist=true in your connection string, or explicitly calling .Enlist() on your connection, passing it the TransactionScope. If you don't do these, Npgsql won't interact with the TransactionScope at all - it's as if it doesn't exist as far as Npgsql is concerned. That requirement may be removed in the future.

It's not surprising that updating 3.2.1 resolved your issue - support was totally rewritten and several bugs were presumably fixed. It's good to hear it helped.

[mgulden]

yes, definitely helped, thank you.

[ykirkanahtar]

Hi,
I upgraded my ORM tool from EF6 to EFCore.

After that i receive an error when i try to create an entity inside the TransactionScope. (in my connection string Enlist setted to true)

The error text : Nested/Concurrent transactions aren't supported

P.S : Same code was working with EF6

Microsoft.EntityFrameworkCore => v1.1.2
Npgsql.EntityFrameworkCore.PostgreSQL => v1.1.0
Npgsql => v3.2.3

I have a base class like this;

protected async Task CommonOperationWithTransaction(Func<Task> func, BusinessBaseRequest businessBaseRequest)
{
using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
try
{
var result = await func.Invoke();
scope.Complete();
return result;
}
catch (Exception ex)
{
_logger.Error(ex.Message, ex);
throw;
}
}
}

and stacktrace

konum: Npgsql.NpgsqlConnection.BeginTransaction(IsolationLevel level)
konum: Npgsql.NpgsqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
konum: System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
konum: Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionWithNoPreconditions(IsolationLevel isolationLevel)
konum: Microsoft.EntityFrameworkCore.Storage.RelationalConnection.d__25.MoveNext()
--- Özel durumun oluşturulduğu önceki konumdan başlayan yığın izlemesinin sonu ---
konum: System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
konum: Microsoft.EntityFrameworkCore.Storage.RelationalConnection.d__23.MoveNext()
--- Özel durumun oluşturulduğu önceki konumdan başlayan yığın izlemesinin sonu ---
konum: System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
konum: Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.d__10.MoveNext()
--- Özel durumun oluşturulduğu önceki konumdan başlayan yığın izlemesinin sonu ---
konum: System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
konum: Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.d__54.MoveNext()
--- Özel durumun oluşturulduğu önceki konumdan başlayan yığın izlemesinin sonu ---
konum: System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
konum: Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.d__52.MoveNext()
--- Özel durumun oluşturulduğu önceki konumdan başlayan yığın izlemesinin sonu ---
konum: System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
konum: Microsoft.EntityFrameworkCore.DbContext.d__35.MoveNext()
--- Özel durumun oluşturulduğu önceki konumdan başlayan yığın izlemesinin sonu ---
konum: System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter.GetResult()
konum: StockApp.Business.VehicleEngine.<>c__DisplayClass2_0.<b__0>d.MoveNext() C:\Users\User1\Documents\Visual Studio 2017\Projects\StockApp\StockApp.Business\StockEngine.cs içinde: satır 76
--- Özel durumun oluşturulduğu önceki konumdan başlayan yığın izlemesinin sonu ---
konum: System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
konum: System.Runtime.CompilerServices.TaskAwaiter1.GetResult() konum: StockApp.BusinessEngineBase.<CommonOperationWithTransaction>d__121.MoveNext() C:\Users\User1\Documents\Visual Studio 2017\Projects\StockApp\StockApp.Business\BusinessEngineBase.cs içinde: satır 90