Cannot use transaction after commit

[bgrainger]

Connector/NET allows the following code:

var connection = new MySqlConnection("connection string");
connection.Open();
var transaction = conn.BeginTransaction();
conn.Query<int>("select 1;", transaction: transaction); // using Dapper
transaction.Commit();
conn.Query<int>("select 1;", transaction: transaction);

MySqlConnector throws an InvalidOperationException: The transaction associated with this command is not the connection's active transaction. on the last line.

See #331 for a similar incompatibility.

[bgrainger]

This code succeeds with SqlConnection. It only fails if a new conn.BeginTransaction() call is inserted immediately before the last line. (But that code will still work with Connector/NET.)

[bgrainger]

I'll make the code more explicit:

var transaction = conn.BeginTransaction();
using (var cmd = conn.CreateCommand())
{
	cmd.CommandText = "SELECT 1;";
	cmd.Transaction = transaction;
	cmd.ExecuteScalar();
}
transaction.Commit();
transaction.Dispose(); // transaction is all done

using (var cmd = conn.CreateCommand())
{
	cmd.CommandText = "SELECT 1;";
	cmd.Transaction = transaction; // attempt to use that transaction
	cmd.ExecuteScalar();
}

It feels like it would be masking a programming bug to silently permit the second command to request the use of an invalid transaction for its command. (One can imagine more complex scenarios where a committed transaction is being passed to different methods where this could cause a significant data integrity problem.)

I'm going to close this as "by design". If it turns out that it's causing significant backwards compatibility problems, we could consider adding a connection string option to opt in to the old behaviour.

[myamolane]

@bgrainger I encountered a similar problem, I created a transaction and did some changes to db, and meanwhile stored the transaction to a object, after I commit the transaction, I cannot use current db context again, the db context was set to use that disposed transaction.

However, the same code works fine with sqlserver db connection, below is detail exception:

System.InvalidOperationException: The transaction associated with this command is not the connection's active transaction.
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.<ExecuteAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at lambda_method(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass17_0`1.<CompileQueryCore>b__0(QueryContext qc)
   at System.Linq.Queryable.Single[TSource](IQueryable`1 source, Expression`1 predicate)
   at Gongfuge.BuildingBlocks.IntegrationEventLogEF.Services.IntegrationEventLogService.MarkEventAsPublishedAsync(IntegrationEvent event) in xxx\Services\IntegrationEventLogService.cs:line 45
   at xxx.IntegrationEvents.OrderingIntegrationEventService.<PublishThroughEventBusAsync>d__5.MoveNext() in xxx\IntegrationEvents\OrderingIntegrationEventService.cs:line 37
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Gongfuge.Services.Ordering.API.Controllers.OrderingController.<SaveStatus>d__21.MoveNext() in xxx\Controllers\OrderingController.cs:line 551
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeNextExceptionFilterAsync>d__23.MoveNext()

Thanks for your help :D

[bgrainger]

after I commit the transaction, I cannot use current db context again, the db context was set to use that disposed transaction

Can you post (or link to) example code that shows this?

[myamolane]

@bgrainger Sure, there is a official sample project in sqlserver connection on github, this is a whole project and I have to explain in detail for UpdateProduct method, in UpdateProduct method:

1. Check if product price was changed
2. If changed, raise a ProductPriceChangedEvent, SaveEventAndCatalogContextChangesAsync
3. In SaveEventAndCatalogContextChangesAsync method, a new transaction is created, in this transaction, firstly save changes for catalogContext, then the transaction is saved to _eventLogService, then eventLogContext add the event and save changes.
4. In SaveEventAndCatalogContextChangesAsync, the transaction is created in ExecuteAsync method of ResilientTransaction class, after executing SaveEventAndCatalogContextChangesAsync method, the transaction isdisposed, but connection is still active
5. The Product price and ProductPriceChangedEvent are successfully saved to local db, and the event needs to be published through event bus and shoud be marked as published in db after being successfuly published, so in PublishThoughEventBusAsync method, _eventLogService invoke MarkEventAsPublishedAsync method, in that method, eventLogContext has to query the event by eventId and update its state, then exception threw.

[bgrainger]

Do you know if there's a fork of eShopOnContainers that has been changed to use MySQL and (I assume) Pomelo.EF? (There are so many forks it's not possible to browse them on GitHub.)